It’s recommended to use shared spfile in RAC; the post explains a few things around this.
What spfile srvctl uses?
When starting database with srvctl, it uses the spfile that’s defined in OCR. The same applies when clusterware auto starts database.
To find out what spfile is configured in OCR, use the below command:
$ srvctl config database -d racdb -a Database unique name: racdb .... Spfile: +DATA/racdb/spfileracdb.ora
How to change spfile in OCR for database?
Use the below command to change spfile in OCR for the database:
$ [DB_HOME]/bin/srvctl modify database -d [db-name] -p [shared-spfile]
What spfile/pfile sqlplus uses?
When starting database with sqlplus, if no pfile is specified in the startup command, sqlplus will determine which spfile/pfile to use in the following sequence:
- [DB_HOME]/dbs/spfile[SID].ora
- [DB_HOME]/dbs/spfile.ora
- [DB_HOME]/dbs/init[SID].ora
If none exists, it will error out:
SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '[DB_HOME]/dbs/init[ORACLE_SID].ora'
How to specify a temporary pfile during troubleshooting?
During troubleshooting, a temporary pfile can be created and used:
SQL> create pfile='/tmp/p1.tmp' from spfile; SQL> startup pfile=/tmp/p1.tmp
How to verify which spfile is being used?
To verify which spfile/pfile is being used, connect to each instance and execute the following command:
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/racdb/spfileracdb.ora
If the spfile parameter is null, it means pfile is being used.
What if spfile/pfile is out of sync?
1. Take time to sort out what parameters are needed (reviewing alert[ORACLE_SID].log on all nodes etc), most of the parameters will have same value except the following:
- instance_number
- thread
- undo_tablespace
- local_listener
For above parameters, be sure to have unique value for each instance. Once the temporary pfile is created, be sure to remove any reference of “SPFILE” as nested spfile is not supported.
2. Create a shared spfile from pfile in Step 1:
SQL> create spfile='[shared_spfile]' from pfile='[temporary_pfile]';
3. On each node, go to [DB_HOME]/dbs, rename spfile[ORACLE_SID].ora, spfile.ora and init[ORACLE_SID].ora if exist.
4. On each node, create pfile [DB_HOME]/dbs/init[ORACLE_SID].ora with the following line only:
SPFILE='[shared_spfile]'
5. Change spfile in OCR with srvctl command.
Common errors when different spfile/pfile is being used
Majority of the init.ora parameters can be different on each instance, however certain parameters must be identical otherwise other instances won’t start with errors like:
ORA-01174: DB_FILES is 2500 buts needs to be 200 to be compatible ORA-01105: mount is incompatible with mounts by other instances ORA-01606: gc_files_to_locks not identical to that of another mounted instance
On Windows, the location is [DB_HOME]\database instead of [DB_HOME]/dbs.