How to Resolve SPFILE Does Not Work

You might notice that the newly created SPFILE for a RAC database was not taking effective after restarts. 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

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 if the first file is missing, it will try the second one, then the third one in order. Once the database finds out an available file (SPFILE or PFILE), it uses the file to startup.

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

It points to the shared SPFILE in ASM.

A local SPFILE could be created unawarely. 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/initsid.ora';
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

I think the above result would 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.

Now, let’s revert the above careless action:
  1. Remove the new local spfile.
  2. $ rm $ORACLE_HOME/dba/spfile<SID>.ora
  3. Edit the default pfile file containing only one line.
  4. $ echo "SPFILE='+DATA/compdb/spfileprimdb.ora'" > $ORACLE_HOME/dba/init<SID>.ora
  5. Apply the modified pfile with a specified spfile location.
  6. SQL> create spfile='+DATA/compdb/spfileprimdb.ora' from pfile='/tmp/initsid.ora';
Then bounce the database, it will be back to normal.

Leave a Reply

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