Archive for June, 2008

How to make changes to spfile / pfile or generate spfile / pfile on a RAC database

Posted on June 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, RAC | Tags: pfile, RAC, spfile, srvctl |

Not all init.ora parameters can be changed using ‘alter system set’ commands. For example audit_syslog_level parameter

SQL> alter system set audit_syslog_level=local0.warning scope=both;
alter system set audit_syslog_level=local0.warning scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

In this scenario you need to bounce the database after making the changes in init.ora file.

Here are the detailed steps to generate pfile from spfile and vice-versa.

1. Connect to a database instance (any instance of the rac database) as sysdba user
2. Generate pfile from spfile

sql> create pfile=’/tmp/initSID1.ora’ from spfile;

—Make sure you mention a location for the pfile else it will overwrite the initSID1.ora file under $ORACLE_HOME/dbs

3. Make necessary changes eg.

audit_syslog_level=local1.warning

4. Shutdown instance 1
sql> shutdown immediate

5. Shutdown instance 2
sql> shutdown immediate

You can also use srvctl command to stop the database by issuing

srvctl stop database -d SID

6. Backup existing spfile.

In RAC architecture the spfile which is common to both the instances will be usually in a location (viz., nfs or nas filesystem ) which is accessible to both the instances. Its a good practice to maintain single copy of spfile for instances that are part of RAC database, instead of having individual copies of spfile.ora under $ORACLE_HOME/dbs.

When the spfile is in a common location for the instances, the spfileSID[instance_num].ora eg: spfileSID1.ora under $ORACLE_HOME/dbs will be a soft link to the common location where the file name would be spfileSID.ora

eg:
cd /common_location
mv spfileSID.ora spfileSID.ora.lastbutone

7. Startup the database on instance 1 with the new pfile
8. sqlplus ‘/ as sysdba’
sql> startup pfile=’/tmp/initSID1.ora’

9. After the instance comes up, generate spfile from this pfile.
sql> create spfile=’location of existing spfile.ora with filename’ from pfile=’/tmp/initSID1.ora’;

eg: create spfile=’/common_location/spfileSID.ora’ from pfile=’/tmp/initSID1.ora’;

10. Verify that spfile is created on the mentioned location.
11. After confirmation, shutdown the instance
sql> shutdown immediate

12. sqlplus ‘/ as sysdba’ on instance 1
sql> startup [ this startup will start the instance using the spfile]

13. You can also issue show parameter spfile to verify that the instance had been started with the spfile.
14. After instance 1 comes up, start instance 2.

cheers,
OraclePitStop.