PFILE and SPFILERAC

Why SPFILE Does Not Work in RAC

Shared SPFILE

You might have noticed that the newly created SPFILE for a RAC database was not taking effective after restarts. And I think you might create the SPFILE in the wrong places.

Oracle 11g RAC stores all the shared files in ASM, including control files, SPFILE, data files. Thus, it starts all the instances with the shared SPFILE. If you check the initialization parameter after startup, it will show the shared SPFILE location for current RAC.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/compdb/spfileprimdb.ora

File Order to Startup

Actually, the shared SPFILE is not the first choice used to startup. The database software will search for the following files in priority when startup:

  1. $ORACLE_HOME/dbs/spfile<SID>.ora
  2. $ORACLE_HOME/dbs/spfile.ora
  3. $ORACLE_HOME/dbs/init<SID>.ora

Which means that if the first file is missing, it will try the second one, then the third one in order. Once the database finds out any available file (SPFILE or PFILE), it uses the file to startup the database.

For RAC, there’s only the third file in respective local destination by default, and the content of this file (PFILE) is extremely simple, it contains only one line to indicate the location of the shared SPFILE:

[oracle@primary01 ~]$ cat $ORACLE_HOME/dbs/initprimdb.ora
SPFILE='+DATA/compdb/spfileprimdb.ora'

It points to the shared SPFILE in ASM.

Local SPFILE

A local SPFILE could be created unconsciously and mistakenly in a RAC environment. For example, if you created a SPFILE without specifying the path of the shared SPFILE like the following statement, the SPFILE will be created in the default location and used it to startup on the instance.

SQL> create spfile from pfile='/tmp/initprimdb.ora.bak';

After a bounce, you can see a newly created local SPFILE is using:

SQL> startup
...
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfileprimdb1.ora

I think the above result should have deviated from your expectation. Moreover, Node 1 of the cluster could startup the instance with the local SPFILE, but Node 2 startup the instance with the shared SPFILE. This is definitely wrong.

For Oracle database software, the default location of spfile shall be at local under $ORACLE_HOME/dbs. Without specifying any location of SPFILE in the statement, the newly created SPFILE will go to the local, not ASM. This could be the root cause of your problem.

Fix the Mistake

To correct the mistake we made in the above, we should take some actions:

1. Remove the new local spfile.

$ rm $ORACLE_HOME/dba/spfile<SID>.ora

2. Edit the default pfile file containing only one line.

$ echo "SPFILE='+DATA/compdb/spfileprimdb.ora'" > $ORACLE_HOME/dba/init<SID>.ora

3. Apply the modified pfile with a specified spfile location.

SQL> create spfile='+DATA/compdb/spfileprimdb.ora' from pfile='/tmp/initprimdb.ora.bak';

Then bounce the RAC database, it will be back to normal.

Further reading: How to Change SPFILE Location

Leave a Reply

Your email address will not be published. Required fields are marked *