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.

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:

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.

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: