check DB restart required or not after parameter change

[former_member409456]

Dear Expert,

Is there any way to find that specific DB parameter change require db restart or not?

Also DB parameter  change will be in pfile or spfile or both?

Any db command which can give me above answer?

Regards

[former_member267192 · Accepted solution]

Hi Tarun,

1. SPFILE

first of all - you should switch to spfile for it is an sap recommendation. If you have switched to the spfile mechanism, you dont need the pfile any more. If you want to use the pfile and dont want to Switch to spfile mechanism - you should always restart your database after changing an Parameter in your init<sid>.ora File.

Read SAP Note 601157 - Oracle Server Parameter File for informations and recommendations about the SPFILE Mechanism AND the the correct usage of dynamic/static parameters.

2. ORACLE-PARAMETERS

To find out which Oracle Parameter can be changed with the "ALTER SYSTEM" command and WHEN the change is becoming to be active use the Database View "V$PARAMETER2".

For Example:

select name, issys_modifiable from V$PARAMETER2;

take a look to the values of the field issys_modifiable:

IMMEDIATE - dynamic parameter, it takes effect immediately. You can change this parameters with scope 'memory', 'both' or 'spfile'.

DEFERRED - static parameter, it takes effect in subsequent sessions, You can change this parameter with scope 'spfile' only.

FALSE - static Parameter, it takes effect in subsequent instances. You can change this parameter with scope 'spfile' only.

best regards...

[former_member409456 · Accepted solution]

HI Winfried,

Thanks for reply.

As per your point 2 i was checking parameter LOG_ARCHIVE_DEST_STATE_2 via below command:

select * from V$PARAMETER2 where name = 'LOG_ARCHIVE_DEST_STATE_2';

Output was no row selected.

So my question is how can i get information(Like db restart is required or not, also change will be in spfile of pfile) about such parameter? Has oracle or SAP documented such details some where? or any command which can give me above answers?

Regards,

[former_member267192 · Accepted solution]

You have to search case senstitive - so for all Parameters are written in lowercase use

'log_archive_dest_state_2' instead

[former_member267192 · Accepted solution]

please read the sap note and my reply - for example if the paramter has the value "IMMEDIATE" then no restart is neccessary - you can decide to save it only to Memory, only to spfile or to both - if you want it to be active immediately i would put it in BOTH.

[former_member409456 · Accepted solution]

Hello Winfried,

Thanks for reply.

In case of DEFERRED and FALSE value returned,  DB restart is must correct?

Regards,

[former_member267192 · Accepted solution]

sap note 601157 says: Yup!

to be exactly...there is a difference between session and instance of course - session is starting a new session for example by starting an Oracle session with a Oracle user - Instance is the whole Oracle Instance -> to be correctly: only if "FALSE" a restart is a must - but due to the way sap connects to the Oracle DB and Session Usage - i would strongly prefer to be sure - restart DB - except for Parameters with "IMMEDIATE" is the best way...

[former_member409456 · Accepted solution]

Thanks Winfried,

Very well explained.