Using ALTER SYSTEM to Change Initialization Parameter Values

Whenever you use ALTER SYSTEM SET parameter_name=value what will happened to the parameter? Will the setting is permanent or temporary or will the settings will be affected after restarting the database instance? I will try to make clear this point in my next section.

1)The first thing is need to remember that whenever we issue ALTER SYSTEM command nothing to do with pfile.That is if you start your database with pfile the settings using ALTER SYSTEM can never be permanent. From database pfile can't be modified.

2)If you use ALTER SYSTEM SET command only without SCOPE then an scope is automatically appended with the command.The settings of the SCOPE is as follows.

-Scope parameter can have three values, BOTH,MEMORY,SPFILE.

-The default is SCOPE=BOTH if a server parameter file was used to start up the instance.

-The default is SCOPE=MEMORY if a text initialization parameter file was used to start up the instance.

3)If you explicitly assign SCOPE value then their persistence is as follows.

SCOPE = SPFILE

-The change is applied in the server parameter file only. The effect is as follows:
-For dynamic parameters, the change is effective at the next startup and is persistent.
-For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY

-The change is applied in memory only.
-For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
-For static parameters, this specification is not allowed.

SCOPE = BOTH

-The change is applied in both the server parameter file and memory.
-For dynamic parameters, the effect is immediate and persistent.
-For static parameters, this specification is not allowed.

4)SID

The SID clause is relevant only in a RAC environment. With ALTER SYSTEM SET SID clause is specified to indicate in which database the settings will be invoked.

-Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.

-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.

5)For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions. An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

6)If you start your database by SPFILE then you have three option to choose, but if you start your database by PFILE then you have the only option to choose SCOPE=MEMORY which is default.

Example
To set in ALL instances in RAC of sga_taget to 160M both in memory and spfile issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=both SID='*';

To set sga_target for the current instances only in memory to 160M issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=memory
SID='dbase';

To set audit_file_dest for the subsequent session use,
alter system set audit_file_dest='/oradata2' DEFERRED ;


Related Link:
------------------
Know Database Initialization Parameter