1. Real Application Clusters Administration and Deployment Guide
  2. Overview of Initialization Parameter Files in Oracle RAC

Overview of Initialization Parameter Files in Oracle RAC

The initialization parameters for an Oracle RAC database are stored in a SPFILE.

When you create the database, Oracle Database creates an SPFILE in the file location that you specify. This location can be either an Oracle Automatic Storage Management (Oracle ASM) disk group or a cluster file system. If you manually create your database, then Oracle recommends that you create an SPFILE from an initialization parameter file (PFILE).

Note:

Oracle RAC uses a traditional PFILE only if an SPFILE does not exist or if you specify PFILE in your STARTUP command. Oracle recommends that you use an SPFILE to simplify administration, to maintain parameter setting consistency, and to guarantee parameter setting persistence across database shutdown and startup events. In addition, you can configure Oracle Recovery Manager (RMAN) to back up your SPFILE.

All instances in the cluster database use the same SPFILE at startup. Because the SPFILE is a binary file, do not directly edit the SPFILE with an editor. Instead, change SPFILE parameter settings using Oracle Enterprise Manager or ALTER SYSTEM SQL statements.

About Creating an SPFILE for Oracle RAC

All instances in an Oracle Real Application Clusters environment must use the same server parameter file.

However, when otherwise permitted, individual instances can have different settings of the same parameter within this one file. Instance-specific parameter definitions are specified as SID.parameter = value, where SID is the instance identifier.

For Oracle RAC, the location of the SPFILE is an attribute of the database resource managed by Oracle Clusterware. When creating a new SPFILE, if the instance from which you issued the command is running, then the following command creates a new SPFILE and automatically updates the database resource with the new SPFILE location:

CREATE SPFILE='location' FROM PFILE;

In this case, you can start up the database without referring to the server parameter file by name.

If the instance from which you issued the command is not running, then the SPFILE in the database resource must be updated manually using srvctl modify database -db dbname -spfile spfile_path. Also, if you use the following commands, then the SPFILE location is not automatically updated in the database resource:

CREATE SPFILE FROM PFILE [AS COPY];
CREATE SPFILE='location' FROM PFILE AS COPY;
CREATE SPFILE FROM MEMORY;

When creating an SPFILE, if you include the FROM MEMORY clause (for example, CREATE PFILE FROM MEMORY or CREATE SPFILE FROM MEMORY), then the CREATE statement creates a PFILE or SPFILE using the current system-wide parameter settings. Because the FROM MEMORY clause requires all other instances to send their parameter settings to the instance that is trying to create the parameter file, the total processing time depends on the number of instances, the number of parameter settings on each instance, and the amount of data for these settings.

Setting SPFILE Parameter Values for Oracle RAC

You can change SPFILE settings with Oracle Enterprise Manager or by using the SET clause of the ALTER SYSTEM statement.

Note:

Modifying the SPFILE using tools other than Oracle Enterprise Manager or SQL*Plus can corrupt the file and prevent database startup. To repair the file, you might be required to create a PFILE and then regenerate the SPFILE.

The examples in this section appear in ASCII text although the SPFILE is a binary file. Assume that you start an instance with an SPFILE containing the following entries:

*.OPEN_CURSORS=500
prod1.OPEN_CURSORS=1000

The value before the period (.) in an SPFILE entry identifies the instance to which the particular parameter value belongs. When an asterisk (*) precedes the period, the value is applied to all instances that do not have a subsequent, individual value listed in the SPFILE.

For the instance with the Oracle system identifier (SID) prod1, the OPEN_CURSORS parameter is set to 1000 even though it has a database-wide setting of 500. Parameter file entries that have the asterisk (*) wildcard character only affect the instances without an instance-specific entry. This gives you control over parameter settings for instance prod1. These two types of settings can appear in any order in the parameter file.

If another DBA runs the following statement, then Oracle Database updates the setting on all instances except the instance with SID prod1:

ALTER SYSTEM SET OPEN_CURSORS=1500 sid='*' SCOPE=SPFILE;

The SPFILE now has the following entries for OPEN_CURSORS:

*.OPEN_CURSORS=1500
prod1.OPEN_CURSORS=1000

Run the following statement to reset OPEN_CURSORS to its default value for all instances except prod1:

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE;

The SPFILE now has only the following entry for prod1:

prod1.OPEN_CURSORS=1000

Run the following statement to reset the OPEN_CURSORS parameter to its default value for instance prod1 only:

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE SID='prod1';

Parameter File Search Order in Oracle RAC

Oracle AI Database searches for your parameter file in a particular order depending on your platform. For Oracle RAC databases, you can easily determine the location of the parameter file by using the srvctl config database command.

On Linux and UNIX platforms, the search order is as follows:

  1. The location specified by the -spfile attribute for the database resource managed by Oracle Clusterware.
  2. The spfilesid.ora file in the location returned by the $ORACLE_HOME/bin/orabaseconfig utility, in the subdirectory /dbs.
  3. The spfile.ora file in the location returned by the $ORACLE_HOME/bin/orabaseconfig utility, in the subdirectory /dbs.
  4. The initsid.ora file in the location returned by the $ORACLE_HOME/bin/orabaseconfig utility, in the subdirectory /dbs.

On Windows platforms, the search order is as follows:

  1. %ORACLE_HOME%\database\spfile sid .ora
  2. %ORACLE_HOME%\database\spfile.ora
  3. %ORACLE_HOME%\database\init sid .ora

Note:

Oracle recommends that you do not use the default SPFILE names because all instances must use the same file and they all have different SIDs. Instead, store the SPFILE on Oracle ASM. If you store the SPFILE on a cluster file system, then use the following naming convention for the SPFILE: path/dbs/spfiledb_unique_name.ora. Create a PFILE named path/dbs/initsid.ora that contains the name SPFILE=path/dbs/spfiledb_unique_name.ora.

Related Topics

Backing Up the Server Parameter File

Oracle recommends that you regularly back up the server parameter file for recovery purposes.

Do this using Oracle Enterprise Manager or use the CREATE PFILE statement. For example:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'
FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

You can use Recovery Manager (RMAN) to create backups of the server parameter file. You can also recover an SPFILE by starting an instance using a client-side initialization parameter file. Then re-create the server parameter file using the CREATE SPFILE statement. Note that if the parameter file that you use for this operation was for a single instance, then the parameter file does not contain instance-specific values, even those that must be unique in Oracle RAC instances. Therefore, ensure that your parameter file contains the appropriate settings as described earlier in this chapter.

To ensure that your SPFILE (and control files) are automatically backed up by RMAN during typical backup operations, use Oracle Enterprise Manager or the RMAN CONTROLFILE AUTOBACKUP statement to enable the RMAN autobackup feature

Related Topics