5 Configuring Server Parameter Files in Oracle RAC

Oracle Database uses parameter settings in parameter files to determine how to control various database resources. You can use two types of files for parameter administration: the server parameter file (SPFILE) or one or more traditional client-side parameter files (PFILEs). Oracle recommends that you manage parameters using an SPFILE. If you use client-side parameter files, then Oracle does not preserve parameter changes made for self-tuning after shutdown.

This chapter describes server parameter file (SPFILE) placement and configuration in Oracle Real Application Clusters (Oracle RAC) environments.

This section contains the following topics:

About the Server Parameter File
Creating Server Parameter Files for Oracle RAC
Storage Requirements for the Server Parameter File in Oracle RAC
Parameter File Search Order in Oracle RAC
Migrating to the SPFILE in Oracle RAC Environments
Server Parameter File Errors in Oracle RAC

5.1 About the Server Parameter File

When you create the database, Oracle Database creates an SPFILE in the file location that you specify. This location can be an Oracle ASM disk group, a cluster file system, or a shared raw device. The Oracle ASM instance also uses a 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.

Note:

Oracle recommends that you avoid modifying the values for self-tuning parameters; overriding these settings can affect performance adversely.

With Oracle Database 11g release 2 and later databases, Database Agent automatically maintains the SPFILE based on the value you specify in the SRVCTL command srvctl add/modify -d dbname -p spfile, where dbname is the database unique name and spfile is the name of the SPFILE. You do not need to create or maintain initORACLE_SID.ora files.

The default location of the database server parameter file (SPFILE) is in the following path:

Oracle_home\database\SPFILE%ORACLE_SID%.ORA

Using local storage as the default location of the server parameter file is inappropriate for Oracle RAC databases and Oracle ASM instances, because all instances used in a cluster database must use the same server parameter file. See "Storage Requirements for the Server Parameter File in Oracle RAC".

5.2 Creating Server Parameter Files for Oracle RAC

To use DBCA to create your database and configure it to use a server parameter file, perform the following steps:

Start DBCA.
When the Initialization Parameters page appears, select Create server parameter file (SPFILE) under the File Locations tab.
After you have made this selection, then enter either a shared file system filename, or enter the storage path in the Server Parameters Filename field.

Note:

When you use DBCA to create the server parameter file, the default SPFILE file name is Oracle_home\database\init%ORACLE_SID%.ora

If you manually create your database, then Oracle recommends that you create an SPFILE from an initialization parameter file (PFILE).

If you must use local storage, then, for Windows-based platforms, Oracle recommends that you use a PFILE in this directory:

Oracle_home\database\init%ORACLE_SID%.ora

This recommended location is valid for each instance and refers to a single, non-shared initialization parameter file. Each PFILE should contain an entry for the parameter SPFILE that points to the location of the shared server parameter file to use. For example, if you use NFS for shared storage, then the local PFILE should contain an entry similar to the following:

SPFILE='\\.\dbname_SPFILE'

However, if you use a cluster file system, then use a SPFILE location similar to the following:

SPFILE=shared_location\dbname\spfiledbname.ora

In this syntax example, the variable shared_location is the shared storage location, and the variable dbname is the database name.

If you are upgrading from a previous Oracle Database release, then review the section "About the Server Parameter File"

5.3 Storage Requirements for the Server Parameter File in Oracle RAC

All instances in an Oracle Real Application Clusters environment must use the same server parameter file. Place the server parameter file on shared storage, such as in an Oracle ASM disk group, a cluster file system, or on a network file system (NFS). Otherwise, place the server parameter file on a shared raw device that is at least 5 MB in size, although this is not recommended.

5.4 Parameter File Search Order in Oracle RAC

Oracle Database searches for your parameter file in the following order:

Grid Plug and Play profile
Oracle_home\database\spfilesid.ora
Oracle_home\database\spfile.ora
Oracle_home\database\initsid.ora

5.5 Migrating to the SPFILE in Oracle RAC Environments

Migrate to the SPFILE by creating and editing the server parameter file using the procedures described in this section.

If your Oracle RAC database does not already use a SPFILE, then you can migrate to the SPFILE file by completing the following procedures:

Combine the initialization parameter files (PFILEs) for all the instances of your Oracle RAC database into one initdbname.ora file by copying all shared IFILE contents as is. All parameters defined in your IFILE parameter files are global. Therefore, create them in the format parameter=value, without SID prefixes.
Copy all instance-specific parameter definitions from initsid.ora files using the following syntax, where the variable sid is the SID of the instance:
```
sid.parameter=value 
```
For example, if your Oracle RAC database is named sales, and the instances have SIDs of sales1, sales2, and so on, then the parameters copied from each IFILE would have the following format:
```
sales1.LICENSE_MAX_USERS=value
sales2.LICENSE_MAX_USERS=value
...
```
If you are using a cluster file system, then Oracle recommends that you use the following command to define the location of the new SPFILE, where db_unique_name is the database unique identifier and spfile is the name of the SPFILE:
```
C:\..bin> srvctl modify database -d db_unique_name -p spfile
```
You can also create the server parameter file using the CREATE SPFILE statement as in the following example:
```
SQL> CREATE SPFILE='?\database\spfile_dbname.ora'
2>   FROM PFILE='?\database\initdbname.ora'
```
If you are using Oracle ASM for shared storage, then you can use the following syntax to create the server parameter file, where disk_group_name is the disk group name, db_uniquename is the database name, dbname is the database name, Oracle_home is the Oracle home path, and initdbname.ora is the file you created in Step 1:
```
SQL> CREATE SPFILE='+disk_group_name\db_uniquename\spfiledbname.ora'
SQL> FROM PFILE='Oracle_home\database\initdbname.ora'
```
The CREATE SPFILE SQL statement reads the initdbname.ora file that you created by merging your IFILEs, and then transfers the settings for the parameters from the merged file into your server parameter file.
Oracle recommends that you use the SPFILE by configuring the PFILE to point to the location of the SPFILE. Do this by including an entry for SPFILE in the local PFILE, and then running the STARTUP command as in the following example, where the SID variable in initsid.ora is the SID on the local Oracle RAC instance. For example:
```
SQL> STARTUP PFILE=Oracle_home\database\initsid.ora
```
If you use this STARTUP command syntax, then Oracle Database uses SPFILE parameter setting in the PFILE to obtain the server parameter file specified in the file initsid.ora.

Note:
With Oracle ASM 11g release 2 and later releases, the SPFILE for the Oracle ASM instance can be stored in Oracle ASM. You can also place an Oracle ASM instance SPFILE on a shared file system, or you can use a traditional client-side parameter file for each Oracle ASM instance.

See Also:
Oracle Database Storage Administrator's Guide for more information about administering an Oracle ASM instance

5.6 Server Parameter File Errors in Oracle RAC

Oracle Database reports errors that occur during server parameter file creation, or while reading the file during startup. If an error occurs during a parameter update, then Oracle records the error in the database alert log file, and ignores subsequent parameter updates to the file. If this happens, then do either of the following:

Shut down the instance, recover the server parameter file, and restart the instance.
Enable the instance to continue running without regard for subsequent parameter updates.

Oracle Database displays errors for parameter changes that you attempt when you incorrectly use the ALTER SYSTEM SET statement. Oracle Database does this when an error occurs while reading from or writing to the server parameter file.