Sunday, March 26, 2017

Facts about srvctl/sqlplus and pfile/spfile in RAC

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

[oracle@rac2 ~]$ srvctl config database -d orcl -a
Database unique name: orcl
Database name: orcl
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain: dba.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl1,orcl2
Disk Groups: DATA
Services: orcl_stest01
Database is enabled
Database is administrator managed

How to change spfile in OCR for database?

<DB_HOME>/bin/srvctl modify database -d <db-name> -p <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 

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/orcl/spfileorcl.ora

If the spfile parameter is null, it means pfile is being used.


What if spfile/pfile is out of sync?

1. List down 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='+DATA' from pfile='/tmp/initorcl.ora';

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 with the following line only:

SYS@orcl2>create pfile from spfile;
File created.

[oracle@rac2 dbs]$ cd $ORACLE_HOME/dbs

[oracle@rac2 dbs]$ ls -ltr initorcl*
-rw-r----- 1 oracle oinstall 1268 Mar 26 20:04 initorcl2.ora

5. Change spfile in OCR with srvctl command

<DB_HOME>/bin/srvctl modify database -d <db-name> -p <spfile>

No comments:

Post a Comment