Disclaimer – Must read !!
Table of Contents
Short explanation of V$SPPARAMETER and V$PARAMETER
V$SPPARAMETER displays information about the contents of the server parameter file. If a server parameter file was not used to start the instance, then each row of the view will contain FALSE in the ISSPECIFIED column. ISSPECIFIED Indicates whether the parameter was specified in the server parameter file (TRUE) or not (FALSE) V$PARAMETER displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view. ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect: IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately. DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions. FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances. ISINSTANCE_MODIFIABLE For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
SPFile location for RAC installation
Parameter File Search Order : Oracle Database searches for your parameter file in the following order: $ORACLE_HOME/dbs/spfilesid.ora $ORACLE_HOME/dbs/spfile.ora $ORACLE_HOME/dbs/initsid.ora For RAC installation Oracle recommends that you use a PFILE in $ORACLE_HOME/dbs pointing to a SPFile located in ASM $ pwd /u01/app/oracle/product/11203/racdb/dbs $ cat initGRACE2_1.ora SPFILE='+DATA/GRACE2/spfileGRACE2.ora' # line added by Agent
Create a pfile which we can use to restore the current parameters in case of troubles
SQL> create pfile from spfile; File created. SQL> !ls $ORACLE_HOME/dbs/initGRACE2_1.ora /u01/app/oracle/product/11203/racdb/dbs/initGRACE2_1.ora
Restore ASM based SPFile from a file system based PFile
Start a RAC instance with the saved PFiles SQL> startup mount pfile=/u01/app/oracle/product/11203/racdb/dbs/initGRACE2_1.ora ORACLE instance started. Total System Global Area 1570009088 bytes Fixed Size 2228704 bytes Variable Size 1124077088 bytes Database Buffers 436207616 bytes Redo Buffers 7495680 bytes Database mounted. Check the current SPFILE location $ asmcmd ls -l +DATA/GRACE2/ Type Redund Striped Time Sys Name Y ASMPARAMETERFILE/ Y CONTROLFILE/ Y DATAFILE/ Y OCRFILE/ Y ONLINELOG/ Y PARAMETERFILE/ Y TEMPFILE/ N spfileGRACE2.ora => +DATA/GRACE2/PARAMETERFILE/spfile.268.821451245 --> ASM itself stores the spfile in +DATA/GRACE2/PARAMETERFILE/spfile.268.821451245 and links or aliases the spfile in the location +DATA/GRACE2/spfileGRACE2.ora Ensure one of the database instances is mounted before attempting to recreate the spfile. SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance; INSTANCE_NAME HOST_NAME STATUS ---------------- ----------------------- ------------ GRACE2_1 grac1.example.com MOUNTED Create the new SPFile SQL> create spfile='+DATA/GRACE2/spfileGRACE2.ora' from pfile='/u01/app/oracle/product/11203/racdb/dbs/initGRACE2_1.ora'; File created. Verify that spfileGRACE2.ora is pointing to a new spfile $ asmcmd ls -l +DATA/GRACE2/spfileGRACE2.ora Type Redund Striped Time Sys Name N spfileGRACE2.ora => +DATA/GRACE2/PARAMETERFILE/spfile.268.828636685 For further details read Note: Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM (Doc ID 554120.1)
Check the current status of memory_target and memory_max_target parameter
Check the currents status of memory_target and memory_max_target parameter SQL> select name,value,type,ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from v$parameter where name like 'memory%target'; NAME VALUE TYPE ISSYS_MOD ISINS -------------------- ----------------------- ---------- --------- ----- memory_target 1526726656 6 IMMEDIATE TRUE memory_max_target 1577058304 6 FALSE FALSE --> memory_target parameter can be changed via alter system ( ISSYS_MODIFIABLE=IMMEDIATE) and can be different value for all instances --> memory_max_target can't be changed via alter system ( ISSYS_MODIFIABLE=FALSE) and can need to consistent for all instances SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ -------------------- ----------------------- ---------- ------ GRACE2_1 memory_target 1520435200 big integer TRUE * memory_target 1459617792 big integer TRUE * memory_max_target 1572864000 big integer TRUE --> memory_max_target is currently defined in Spfile(ISSPECIFIED=true) for all RAC instances (SID=*) with a value of 1500 MByte --> memory_target is currently defined in Spfile(ISSPECIFIED=true) for all RAC instances (SID=*) with a value of 1400 MByte --> memory_target is currently defined in Spfile(ISSPECIFIED=true) for instances (GRACE2_1) with a value of 1450 MByte
Modify a parameter for all instances or a single instance
Note: Be carefull playing with these paramters as this can lead to ORA-845 and your instances won’t start anymore. Double check that you have a valid backup of your spfile and you know what your doing
SQL> alter system set memory_target=1392m scope=spfile sid='*'; System altered. SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ ---------------------- ----------------- -------------- ------- * memory_target 1459617792 big integer TRUE * memory_max_target 1572864000 big integer TRUE --> All instances will use memory_target of 1392m Modify a paramter for a single instances SQL> alter system set memory_target=1450m scope=spfile sid='GRACE2_3'; SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ -------------------- -------------------- ------------- ------ * memory_target 1459617792 big integer TRUE GRACE2_3 memory_target 1520435200 big integer TRUE * memory_max_target 1572864000 big integer TRUE --> After a reboot instance GRACE2_3 will use 1450m for memory target, where all the other instances still uses memory_target=1392m for memory_target SQL> alter system set memory_max_target=1600m scope=memory sid='*'; alter system set memory_max_target=1600m scope=memory sid='*' * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified --> As expected memory_max_target can only modified with scope = spfile; SQL> alter system set memory_max_target=1600m scope=spfile sid='*'; System altered. SQL> @sp SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ ---------------------- ---------------- -------------- -------- * memory_target 1459617792 big integer TRUE GRACE2_3 memory_target 1520435200 big integer TRUE * memory_max_target 1677721600 big integer TRUE
Deleting an SPFile parameter
Note: Be carefull playing with these paramters as this can lead to ORA-845 and your instances won’t start anymore. Double check that you have a valid backup of your spfile and you know what you are doing !!
Deleting an SPFile parameter from all instances SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ ---------------------- --------------- ----------- ------ GRACE2_1 memory_target 1520435200 big integer TRUE * memory_target 1459617792 big integer TRUE * memory_max_target 1572864000 big integer TRUE SQL> alter system reset memory_target; System altered. SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------------ ----------------------- ----------------- ------------ ------- GRACE2_1 memory_target 1520435200 big integer TRUE * memory_max_target 1572864000 big integer TRUE --> memory_target_parameter for SID='*' is deleted but the instance specific paramter still exists ! Delete an instance specific parameter SQL> alter system reset memory_target sid='GRACE2_1'; System altered. SQL> select sid,name,value,type, ISSPECIFIED from v$spparameter where name like 'memory%target'; SID NAME VALUE TYPE ISSPEC ------ -------------------------- ----------- ------------ ------ * memory_target big integer FALSE * memory_max_target 1572864000 big integer TRUE --> memory_target is deleted from spfile ( ISSPECIFIED=false )
References:
Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM (Doc ID 554120.1)