Database Parameter change when will it take effect

[Former Member]

Hi,

Using brtools when we change a particular Database Parameter a screen appears as below

Pos. Parameter Modif. Spfile Inst. Deft.

1 - active_instance_count spfile no * yes

2 - aq_tm_processes both no * yes

3 - archive_lag_target both no * yes

4 - audit_file_dest spfile no * yes

What i wanted to know is if I change any parameter from this, when will it come into effect. Do these changes happen online or do they need a database stop and start

Thanks in advance,

Regards

Alfred

[stefan_koehler · Accepted solution]

Hello Alfred,

it depends on the parameter if it is dynamic or not, you can query this by using the view V$PARAMETER:

Take a look at the column ISSYS_MODIFIABLE .. the documentation describes the values.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#I1030176

SQL> SELECT NAME, ISSYS_MODIFIABLE FROM V$PARAMETER;

http://www.dbasupport.com/oracle/ora10g/spfile01.shtml

If the ISSYS_MODIFIABLE value is set to FALSE for a parameter, it means that the parameter cannot change its value in the lifetime of the instance; the database needs to be restarted for changes to take effect. A parameter set to IMMEDATE value means that it is dynamic and can be set to change the present active instance as well as future database restarts. A parameter set to DEFERRED is also dynamic, but changes only affect subsequent sessions, currently active sessions will not be affected and retain the old parameter value.

Regards

Stefan