Static and Dynamic parameters

 

Sometimes we need to change the parameter values in the instance. Some parameters needs downtime and some or not needed. Whatever the parameters needs downtime those parameters are called as  Static Parameters and whatever the parameters doesn't require downtime those parameters are called as dynamic  parameters. We need to identify the  parameter which needs downtime or not.That information we can get from the table v$parameter. 

 

This is how we can get the details. 

 

select NAME,ISINSTANCE_MODIFIABLE from  v$parameter where NAME='<<Parameter name>>'; 

Here  ISINSTANCE_MODIFIABLE value  shows TRUE then we don't need to bounce the instance. 

If the value shows FALSE then we require downtime to change the parameter. 

Here is example : 

SQL> select NAME,ISINSTANCE_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where NAME='large_pool_size';

NAME                 ISINS    ISSYS_MOD

-------------------- ----- ---------

large_pool_size      TRUE  IMMEDIATE

SQL> select NAME,ISINSTANCE_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where NAME='undo_management';

NAME                     ISINS ISSYS_MOD

------------------------------ ---------

undo_management          FALSE FALSE

 

Above query shows 2 parameters one is large_pool_size and another one is undo_management. 

Large_pool_size parameter doesn't require downtime because the value is TRUE but undo_management needs downtime because the value is FALSE. 

How to update the  parameter in the spfile  ?

There are three options to set the database parameters 

1. scope=memory

2. scope=spfile

3. scope=both (memory + spfile)

scope=memory ---> means it writes the value in the memory only. After restart the instance the parameter value will be erased.It works only for Dynamic parameters.

scope=spfile ---> means it writes only into spfile, parameter value doesn't effect immediatly.After restart the instance the value of the parameter effects.It works mainly for Static parameters.

scope=both   ---> It updates both spfile and memnory,parameter value effects immediatly.It works mainly for dynamic parameters.

Note : 

scope=spfile and scope=both works when we are using spfile.If you are using pfile we can't use scope=spfile and scope=both only scope=memory possible.

If you don't specify scope option while setting parameter then it takes default option scope=memory for pfile and scope=both for spfile.

 

SQL>alter system set  undo_management=manual   scope=spfile; 

SQL>shutdown  

SQL>startup  

How to update the parameter in the pfile ? 

SQL>shutdown immediate 

$ cd  $ORACLE_HOME/dbs 

$ vi   initbsprod.ora 

add the below parameter in the pfile 

undo_management=Manual 

$sqlplus   /   as sysdba 

SQL>startup 

How to update the parameter in the memory ? 

SQL>alter system set   large_pool _size=200m scope=memory; 

How to update the parameter in the spfile ? 

SQL>alter system set   large_pool _size=200m scope=spfile; 

How to update the parameter in the memory as well as spfile? 

SQL>alter system set large_pool_size=200m scope=both;