In this post, I am going to explain how we can create Recreating the Spfile for RAC Instances.

We had issue on our EBS R12.1.3 system with 11.2.0.1 2 node RAC system on AIX 6.1. We need to recreate our spfile on ASM. So here is the steps:

By default init<SID>.ora file represent at $ORACLE_HOME/dbs path.

In this case the parameter file was saved to /u01/app/oracle/11.2.0/dbs/initPROD001.ora

1. Using this text based format of the parameter file after having corrected the parameters that have caused the issue, start one of the RAC instances to the mount phase.

SQL> startup mount pfile=’/u01/app/oracle/11.2.0/home/oracle/initPROD01.ora’;

2. The current location for spfile is; +oradata/PROD00/spfilePROD00.ora so the new file will need to replace with this file. ASM itself stores the spfile in +oradata/PROD00/PARAMETERFILE/spfile.267.737949031 and links or aliases the spfile in the location +oradata/PROD00/spfilePROD00.ora

We can also check where spfile is in ASM diskgroup by using the ASMCMD command

For example(Do not forget to set ASM env):

ASMCMD [+oradata/PROD00] > ls -l
Type Redund Striped Time Sys Name
Y CHANGETRACKING/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilePROD00.ora => +ORADATA/PROD00/PARAMETERFILE/spfile.267.737949031

3. Before start be ensure one of the database instances is mounted in RAC system to recreate the spfile.

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance

INSTANCE_NAME HOST_NAME STATUS
—————- —————– ————
PROD001 orapdb11 mount

4. Create the new spfile

SQL> create spfile=’+oradata/PROD00/spfilePROD00.ora’ from pfile=’/u01/app/oracle/11.2.0/dbs/initPROD001.ora’;
File created.

5. ASMCMD will show that a new spfile has been created as the alias spfilePROD00.ora is now pointing to a new spfile under the PARAMETER directory in ASM.

ASMCMD> pwd
+oradata/PROD00
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilePROD00.ora =>
+oradata/PROD00/PARAMETERFILE/spfile.298.731252301

6. Shutdown the instance and restart the database using srvctl using the newly created spfile.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

[oracle@node1 ~]$ srvctl start database -d PROD00

7. ASMCMD will now show a number of spfiles exist in the PARAMETERFILE directory for this database. We need to remove old spfile .

ASMCMD> pwd
+oradata/PROD00

ASMCMD> cd PARAMETERFILE

ASMCMD> ls -l
Type Redund Striped Time Sys Name
ASMCMD [+oradata/PROD00/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAR 06 16:00:00 Y spfile.267.737949031
PARAMETERFILE UNPROT COARSE MAR 06 16:40:00 Y spfile.298.731252301
ASMCMD> rm spfile.267.737949031
ASMCMD> ls
spfile.298.731252301

Reference:
Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM [ID 554120.1]

 

Advertisements