Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command

Question: You maintain several SPFILE files:

$ ls -l spfile*
-rw-r-----   1 sme      dba         2560 Mar 20 08:20 spfile.ora
-rw-r-----   1 sme      dba         2560 Mar 20 08:20 spfileRel14.ora

Which one is modified when you run the “ALTER SYSTEM” SQL command to permanently change parameter values in the spfile?

The DBAs must be cautious when using ALTER SYSTEM command since it permanently modifies an SPFILE parameter file. They need to know which parameter file will be modified.

Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command?

Determine which SPFILE was used at startup, if any was used:

1. At instance STARTUP, there are 3 possibilities for the instance to retrieve instance parameter values:

• read the initSID.ora referring to an spfile referenced by the new parameter SPFILE=spfile.ora (CASE 1)
• read spfileSID.ora containing all instance parameters (CASE 2)
• read the initSID.ora containing all instance parameters (CASE 3)

2. When you execute an ALTER SYSTEM command to modify the spfile parameter values, the SPFILE impacted is always the spfile used at startup.

3. Which one was used at startup?

There are 3 places to retrieve this information:

1. alert.log file
2. V$PARAMETER
3. V$SPPARAMETER view

4. There are 3 possible cases:

Case 1

1. If, in the alertSID.log file, the name of the spfile used is displayed, then it is clearly explicit that the instance started with the initSID.ora calling an spfile.

Starting up ORACLE RDBMS Version: 9.0.0.0.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 67108864
  large_pool_size          = 1048576
  java_pool_size           = 20971520
  spfile                   = spfile.ora

2. The V$PARAMETER view displays the full name of the spfile used:

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           /export/home1/ora900/dbs/spfile.ora

3. In this example, when you use the “ALTER SYSTEM” SQL command to change a parameter value, the modification is made to the spfile.ora and not to the spfileSID.ora file.

Example:

$ cp spfile.ora spfileRel14.ora

SQL> alter system set sort_area_size=65000 scope=spfile;
System altered.

$ more spfileRel14.ora
...
*.sort_area_size=524288
...

$ more spfile.ora
...
*.sort_area_size=65000
...

Case 2

If, in the alert.log file, no SPFILE parameter is mentioned, then an ALTER SYSTEM command would alter the spfileRel14.ora file by default if used at startup.

The V$PARAMETER view displays the name of the spfile used. The value can be:
– spfile@.ora for spfileSID.ora
– spfile.ora for spfile.ora

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           ?/dbs/spfile@.ora

You can check that the spfileRel14.ora file was used at startup – in V$SPPARAMETER view, some of the parameters have the ISSPECIFIED column
value set to TRUE and therefore the VALUE column is not null:

SQL> select name, value,isspecified 
  2  from v$spparameter where name like 'undo%'

NAME                           VALUE                          ISSPEC
------------------------------ ------------------------------ ------
undo_management                MANUAL                         TRUE
undo_tablespace                RBS                            TRUE
undo_suppress_errors                                          FALSE
undo_retention                                                FALSE

SQL> alter system set processes =100 scope=spfile;
System altered.

$ more spfileRel14.ora
...
*.processes=100
...

The previous spfile.ora showed above displays the following information:

$ more spfile.ora
...
processes=150
...

Case 3

If, in the alert.log file, no SPFILE parameter is mentioned, then an ALTER SYSTEM command would issue an error if no spfile was used at startup.

SQL> alter system set open_cursors=500 scope=spfile; 
alter system set open_cursors=500 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

The V$PARAMETER view displays a NULL value for the name of the spfile:

SQL> select name,value from v$parameter where name='spfile';

NAME             VALUE
---------------- -------------------------------------------------
spfile           

You can check that no spfile file was used at startup: in V$SPPARAMETER view there are no parameters for which the ISSPECIFIED value is set to TRUE, nor is the VALUE column not null. For example:

In initRel12.ora, no SPFILE parameter used:

SQL> startup pfile=initRel12.ora
ORACLE instance started.

Total System Global Area  143433028 bytes
Fixed Size                   286020 bytes
Variable Size             100663296 bytes
Database Buffers           41943040 bytes
Redo Buffers                 540672 bytes
Database mounted.
Database opened.

SQL> select * from v$spparameter where VALUE is NOT NULL;
no rows selected

SQL> select * from v$spparameter where ISSPECIFIED ='TRUE';
no rows selected