The Geek Diary

CONCEPTS | BASICS | HOWTO

Oracle Interview Questions : Using srvctl V/s sqlplus and pfile V/s spfile in RAC

By

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:

  1. [DB_HOME]/dbs/spfile[SID].ora
  2. [DB_HOME]/dbs/spfile.ora
  3. [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.

You May Also Like