• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value
  2. How to Enable or Disable Automated Statistics Collection in Oracle Database
  3. List of OCFS2 threads
  4. How to check the status of OMS and Agent in OEM 13cR2
  5. Oracle database : Basics about pfile and spfile
  6. How to Move tables and indexes to a different tablespace
  7. How to Drop/Truncate Multiple Partitions in Oracle 12C
  8. How to Use Udev Rules to Create oracleasm Disks in CentOS/RHEL 8
  9. Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
  10. How to install and configure Oracle ASMLIB on Native Linux multipath mapper devices

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright