Which SPFILE Parameter File is Impacted when Issuing an ALTER SYSTEM Command? [ID 138119.1]

***Checked for relevance on 17-Jul-2012***
PURPOSE 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? SCOPE & APPLICATION 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: * alert.log file * V$PARAMETER * V$SPPARAMETER view 4. 3 possible cases: ---------------------------------------------------------------------------- Case 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 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 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: 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 RELATED DOCUMENTS ----------------- Note:137483.1 How to modify the content of an SPFILE parameter file