Oracle9i Real Application Clusters Administration
Release 1 (9.0.1)

Part Number A89869-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Parameter Management in Real Application Clusters Environments

Oracle uses parameter settings in the server parameter file to control database resources. You can also use the traditional client-side parameter files, however, Oracle Corporation recommends that you use the server parameter file. The server parameter file is easier to use

This chapter describes how to administer the server parameter and client-side parameter files. It also describes parameter use in Real Application Clusters and how parameters affect startup processing.

The topics in this chapter are:

Administering the Server Parameter File in Real Application Clusters Databases

This section describes administering the server parameter file and includes the following topics:

Backing Up the Server Parameter File

Oracle Corporation recommends that you regularly create copies of the server parameter file for recovery purposes. Do this using the CREATE PFILE FROM SPFILE statement.

You can also recover your database's server parameter file by starting up an instance using a client-side initialization parameter file. Then re-create the server parameter file using the CREATE SPFILE statement.

See Also:

Oracle9i SQL Reference for more information about the CREATE PFILE and CREATE SPFILE statements  

Setting the Server Parameter File Parameter Values for Real Application Clusters

Oracle automatically updates the values in the server parameter file for parameter settings that you change using Oracle Enterprise Manager or ALTER SYSTEM SET statements. In addition, the ALTER SYSTEM RESET syntax enables you to undo the effects of parameter settings in the server parameter file and parameters that you manually set.

For example, assume you start an instance with an the server parameter file containing:



Sample settings only appear as text in these examples. However, the server parameter file is a binary file.  

For the instance with sid proddb1, the parameter remains set to 1000 even though it is followed by a database-wide setting of 500. This prevents any database-wide alteration of the setting and gives the Database Administrator (DBA) of the instance proddb1 complete control over parameter settings.

If another DBA executes the following:


Then Oracle updates the setting on all instances except the instance with sid proddb1. If you later change the parameter setting by executing the following on the instance with sid proddb1, then the parameter begins accepting future ALTER SYSTEM values set by other instances:


More precisely, if you execute the following on another instance, the instance with sid proddb1 also assumes the new setting of 2000:


If the server parameter file contains the entries:




Makes Oracle disregard the first entry from the server parameter file.

To reset a parameter to its default value throughout your cluster database, enter the syntax:



Not all ALTER SYSTEM statement changes and modifications are recorded in the server parameter file. Some ALTER SYSTEM statements only change in-memory parameter settings.  

Exporting the Server Parameter File For Backward Compatibility

You can revert to a pre-release 1 (9.0.1) version of Oracle and convert from using the server parameter file to the traditional client-side parameter file type. Do this by using the FROM option of the CREATE PFILE statement. Export the contents of a server parameter file into a pre-release 1 (9.0.1) release parameter file using the following syntax:

CREATE PFILE[= 'pfile-name'] FROM SPFILE [='raw_device_name'];

This statement exports the contents of an the server parameter file into a pre-release 1 (9.0.1) release initialization parameter file named 'pfile-name'. If you do not specify a PFILE or an SPFILE file name, Oracle uses the platform-specific default PFILE and SPFILE names.

Oracle creates the PFILE as a text file on the server. This file contains all parameter settings of all instances. Entries for overrides appear as sid.parameter=value. The PFILE also contains any comments associated with the parameter. Comments appear in the same line as the parameter setting. You must move any sid-specific entries to an instance-specific parameter file and delete the sid specification. The CREATE PFILE statement requires DBA privileges.

You can execute the CREATE PFILE statement to:

Setting Parameters within the Server Parameter File

Use the sid designator to set instance-specific parameter values in the server parameter file. For example, the following:

proddb1.OPEN_CURSORS = 1000
proddb2.OPEN_CURSORS = 1500

Sets OPEN_CURSORS to 1000 for instance proddb1, and to 1500 for instance proddb2. These entries are recognized as entries for specific sids in a Real Application Clusters database. The value 1000 is applied to the parameter when the instance is started up with sid proddb1 and the value 1500 is applied to when the instance is started up with sid proddb2.

The entry:


Sets the value of parameter DB_FILE_MULTIBLOCK_READ_COUNT to 16 for all instances. Parameter DB_FILE_MULTIBLOCK_READ_COUNT takes the value 16 for all instances because a sid of * is considered special and the value specified in the parameter setting is applied to all the sids.

Note that the server parameter file supports the pre-release 1 (9.0.1) syntax of:

parameter1 = value1

In addition, parameter1 takes the value value1 regardless of the sid. You can override parameters for specific sids as follows:

proddb1.OPEN_CURSORS = 1500

In this case, OPEN_CURSORS takes the value 1000 for all the instances that have a sid other than proddb1 and takes the value 1500 in the instance with sid proddb1.

Specifying Comments in the Server Parameter File

Specify comments with parameter settings on the same line with the parameter setting. For example, if init.ora contains the following lines:

# first comment 
OPEN_CURSORS = value # second comment 

The string second comment is associated with OPEN_CURSORS's setting. Oracle displays this comment in the V$PARAMETER and V$PARAMETER2 views. Oracle also displays comments such as the entry #first comment in the example.

Using Client-Side Parameter Files

You can use one or more client-side parameter files to manage parameter settings in Real Application Clusters. By default, if you do not specify PFILE in your STARTUP command, Oracle uses a server parameter file.

You can set global parameters within instance-specific parameter files. To do this, you must have identical parameter settings for global parameters in all of your instance-specific parameter files. You can also maintain one file for global parameters and point to it with the IFILE parameter.


If you use client-side parameter files, self-tuning parameter changes that Oracle makes are lost after shutdown. In addition, using client-side parameter files increases your parameter administration overhead.  

Client-Side Parameter File Naming Conventions

Base your file names for the client-side parameter filenames on the sid of each instance and the global database name. For example, name each instance-specific parameter file initsid.ora, where sid is the system identifier of the instance. Name the common parameter file, initdb_name.ora, where db_name is the database name of your Real Application Clusters database as shown in Figure 2-1.

Figure 2-1 Traditional Instance-Specific Initialization Files

Text description of pss81001.gif follows
Text description of the illustration pss81001.gif

The parameter file can contain both instance-specific and global parameter settings. If you include global parameter settings, the entries for these must be identical in each instance's file.

Oracle reads entries in this file beginning at the top of the file. When interpreting parameters, Oracle uses the last value for any parameters in this file that are duplicates. This is true for both instance-specific and global parameter settings. Table 2-1 describes the optional initialization parameter files:

Table 2-1 Naming Conventions and Descriptions for Optional Parameter Files
Initialization Parameter File/
Naming Convention

Instance Specific File:


Each node's instance can have its own initsid.ora file, where sid is the Oracle system identifier (sid) of the instance. This file uniquely defines the instance-specific parameter settings. Use the IFILE parameter from within this file to call the initdb_name.ora file.  

Global File:


If you do not include global parameter file settings in each instance-specific file, you must store common parameters in an initdb_name.ora file, where db_name is the database name. It lists the common database parameters shared by all instances. A copy of this file must reside on each instance in the database cluster. If you use a cluster file system (CFS), all instances that run from the same Oracle home share this file.  

Purpose of initsid.ora

The initsid.ora file uses the IFILE parameter to point to the common file for common parameter settings. The initsid.ora file defines the following for each instance:

The convention for deriving a sid is to use the value of the DB_NAME parameter in the initdb_name.ora file and the thread ID. For example, if the DB_NAME is db, and the first instance has a thread ID of 1, its sid is db1; the second instance uses the sid db2 to identify its instance; and so on. This is the logic that the DBCA uses when it derives a sid. A sid, however, can have any value you choose.

Example 2-1 and Example 2-2 show the contents of initsid.ora files for two instances for each node numbered 1 and 2 respectively:

Example 2-1 initdb1.ora


Example 2-2 initdb2.ora


See Also:

Oracle9i Database Reference for complete parameter descriptions  

Purpose of initdb_name.ora

Name the initdb_name.ora parameter file using the IFILE parameter setting in each initsid.ora file as shown in Figure 2-2.

Figure 2-2 Common Initialization File Example

Text description of pss81002.gif follows
Text description of the illustration pss81002.gif

All instances must use the same common file if you use the traditional parameter files.

Example 2-3 shows an initdb_name.ora file (initdb.ora) created for a hybrid or General Purpose database:

Example 2-3 initdb.ora

db_files=1024  # INITIAL
control_files=("\\.\db_control1", "\\.\db_control2")
db_file_multiblock_read_count=8  # INITIAL
db_block_buffers=13816  # INITIAL
shared_pool_size=19125248  # INITIAL
processes=50  # INITIAL
parallel_max_servers=5  # SMALL
log_buffer=32768  # INITIAL
max_dump_file_size=10240  # limit trace file size to 5M each


DB_BLOCK_BUFFERS will be obsolete in a future Oracle release. If you set DB_BLOCK_BUFFERS, Oracle uses the value but also records a warning in your alert log file.  

See Also:

Oracle9i Database Reference for complete parameter descriptions  

Placement and Use of IFILE Parameters in Instance-Specific Files

If you duplicate parameter entries in a parameter file, the last value specified in the file for the parameter overrides previous values. To ensure Oracle uses the correct common parameter values, place the IFILE parameter at the end of any instance-specific parameter files. Conversely, you can override common parameter values by placing the IFILE parameter before the instance-specific parameter setting.


The DBCA places the IFILE parameter at the top of the parameter file.  

Using Multiple IFILE Entries in Client-Side Parameter Files

You can specify IFILE more than once in your initial parameter file to include multiple global parameter files. However, do not accidentally reset a parameter value in subsequent common parameter files. Otherwise, each subsequent entry in the files specified by IFILE overrides previous values. For example, an instance-specific parameter file might include an init_dbname.ora file and separate parameter files for other parameter settings as in this example:


In this example, the value of LOG_ARCHIVE_START=false overrides any value specified in the parameter file INIT_LOG.ORA for this parameter. This is because the LOG_ARCHIVE_START parameter appears after the IFILE entry.

See Also:


Parameter Settings in Real Application Clusters

As mentioned, some parameters must be identical across all instances in Real Application Clusters. Other parameters can have unique values within each instance.

Unique Identification of Instances with Parameters in the Sever Parameter File

Each instance has several elements or components whose characteristics are controlled by parameter settings. Some of these are database objects and others are resource-like components that facilitate inter-instance processing. You uniquely identify these instance components using the sid designator in the server parameter file or by using parameter settings in initsid.ora.

See Also:

Oracle9i Database Reference for more information on parameters  

For example, Table 2-2 shows the sids and instance names if the database name is db and the thread IDs for each instance are 1, 2, and 3 respectively:

Table 2-2 Example sids and Instance Names
thread id  sid  instance_name 







Instance-Specific Parameter Settings Using sid in the Server Parameter File

Use instance-specific parameter settings to improve performance. For example, you can create System Global Areas (SGAs) of different sizes for each instance. You would do this if you had all OLTP users on one instance and all data warehouse users on another. In this case, you might decrease the value for the SHARED_POOL_SIZE and use parallel execution on the Data Warehouse instance. In addition, the OLTP instance would likely require a larger setting for SHARED_POOL_SIZE.

In the server parameter file, you would use the sid designator to identify these instance-specific settings. Also use the sid designator in the server parameter file when you create instances that specify:

Types of Parameters in Real Application Clusters

There are three types of initialization parameters in Real Application Clusters environments as described in this section. There are:

Multi-Valued Parameters

You can configure some parameters to have different values for one or more instances. Parameters that can be multi-valued have a default value and a value for each instance that has modified the default setting.

Use the ALTER SYSTEM SET statement to set multiple values for such parameters. You can also use ALTER SYSTEM SET to define a global value that is effective for all instances. In addition, you can override these global values for specific instances.

Parameters That Must Be Identical Across All Instances

Certain initialization parameters that are critical at database creation or that affect certain database operations must have the same value for every instance in Real Application Clusters. Specify these parameter values in the common parameter file, or within each init_dbname.ora file on each instance. Table 2-3 lists the parameters that must be identical on every instance.

Table 2-3 Parameters That Must Be Identical on All Instances


DML_LOCKS (Only if set to zero)  


LOG_ARCHIVE_DEST_n (Optional) 











GC_FILES_TO_LOCKS (Optional: Oracle automatically controls resource assignments, so ore-release 1 (9.0.1) locks are not needed)  

Parameters That Must Be Unique Across All Instances

If you use the THREAD or ROLLBACK_SEGMENTS parameters, Oracle Corporation recommends setting unique values for them using the sid identifier in the the server parameter file. However, you must set a unique value for INSTANCE_NUMBER for each instance and you cannot use a default value.

Multiple Instance Issues for Parameters

Table 2-4 alphabetically summarizes issues for parameters in Real Application Clusters.

Table 2-4 Initialization Parameter Notes for Multiple Instances
Parameter  Description and Comments 


(Solaris environments only)  

This parameter is only valid for Solaris environments.

Oracle uses information from this parameter to distribute traffic among the various interfaces. You should set a value for CLUSTER_INTERCONNECTS when a single interconnect is insufficient to meet the bandwidth requirements of large Real Application Clusters databases.

The syntax of the parameter is:


Where ifn is an IP address in standard dotted-decimal format, for example, Subsequent platform implementations may specify interconnects with different syntaxes.

Warning: When you set CLUSTER_INTERCONNECTS in Solaris configurations, the interconnect high availability features are not available. In other words, an interconnect failure that is normally unnoticeable would instead cause an Oracle cluster failure.  


To enable a database to be started in Real Application Clusters mode, set this parameter to TRUE.  


Set this parameter to the number of instances in your Real Application Clusters environment. A proper setting for this parameter can improve memory use. 


If you set a value for DB_NAME in instance specific parameter files, it must be identical for all instances.  


To enable a shared server configuration, set the DISPATCHERS parameter. The DISPATCHERS parameter may contain many attributes.

Oracle Corporation recommends that you configure at least the PROTOCOL and LISTENER attributes. PROTOCOL specifies the network protocol for which the dispatcher generates a listening end point. LISTENER specifies an alias name for the listeners with which the PMON process registers dispatcher information. Set the alias to a name that is resolved through a naming method such as a tnsnames.ora file.

Oracle9i Net Services Administrator's Guide for complete information about configuring the DISPATCHER parameter and its attributes and for configuring the shared server 


Must be identical on all instances only if set to zero. The default value assumes an average of four tables referenced per transaction. For some systems, this value may not be enough. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE


If specified, this parameter must have unique values on all instances. In Real Application Clusters environments, multiple instances can be associated with a single database service. Clients can override connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance. Oracle Corporation recommends that you set INSTANCE_NAME equivalent to the sid.  


This parameter is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST_n parameter. You must include the thread number.

The following variables can be used in the format:

  • %s: log sequence number

  • %S: log sequence number, zero filled

  • %t: thread number

  • %T: thread number, zero filled

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format is:




This is a Real Application Clusters-specific parameter. However, you should not change it except under a limited set of circumstances.

This parameter specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). It determines whether the local SCN should be refreshed from the lock value when getting the snapshot SCN for a query. Units are in hundredths of seconds. Under unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value (700 hundredths of a second, or seven seconds) is an upper bound that allows the preferred existing high performance mechanism to remain in place.

If you want commits to be seen immediately on remote instances, you may need to change the value of this parameter.  

NLS_* parameters

For Oracle Globalization Support  

There are several Globalization Support parameters as described in Oracle9i Database Reference and Oracle9i Globalization and National Language Support Guide. You can set different values for different instances.  


Defaults for the SESSIONS and TRANSACTIONS parameters are derived directly or indirectly from the value of the PROCESSES parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters. If you do not use defaults, you may want to increase the values for some of the above parameters to allow for additional background processes.  


To speed up roll forward or cache recovery processing, you may want to set this parameter to specify the number of processes to participate in instance or crash recovery. A value of zero or one indicates that recovery is to be performed serially by one process. 


(Use only in Rollback Managed Undo Mode)

Note: Oracle Corporation strongly recommends that you use automatic undo management, not Rollback Managed Undo.  

Use this parameter in manual rollback managed undo mode only to specify the private rollback segments for each instance by allocating one or more rollback segments by name to an instance. If you set this parameter, the instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance, calculated from the ratio of:



Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.  


The value of SPFILE is the name of the current server parameter file in use. You can define the SPFILE parameter in a client-side PFILE to indicate the name of the server parameter file to use.

When the server uses the default server parameter file, the server internally sets the value of SPFILE.  


If specified, this parameter must have unique values on all instances. THREAD is a Real Application Clusters parameter that specifies the number of the redo thread to be used by this instance.

In Real Application Clusters, you can specify any available redo thread number, as long as that thread number is enabled and is not in use by another instance.

A value of zero specifies that an instance can use any available, enabled public thread.  

See Also:

Oracle9i Database Reference for more information about these parameters and Oracle9i Real Application Clusters Deployment and Performance for a discussion of additional parameters for parallel execution in Real Application Clusters environments  

The Startup Process and Parameters in Real Application Clusters

In Real Application Clusters, the first instance to start mounts the database. In addition, entries in the alert.log file of the first instance to start identifies that instance as the first one to start.


To find your alert log file, use the search string alert*.log. You can usually find alert.log in the background_dump_dest directory.  

See Also:

Chapter 4, Administering Real Application Clusters Databases with Server Control, SQL, and SQL*Plus for more information about starting instances  

Special Startup Considerations for Traditional Parameter File Use

If you use the traditional parameter files and a file for an instance contains a global parameter, its value must match the value set in other instances for that parameter. Otherwise, the instance cannot mount the database.

Starting Two Instances on Remote Nodes

Oracle Corporation recommends using the Server Control (SRVCTL) utility to start instances. You can also use SRVCTL for other administrative tasks as described under the heading "Using SRVCTL to Administer Real Application Clusters Instances". The rest of this section describes using SQL*Plus to start instances.

To start multiple instances from a SQL*Plus session on one node by way of Oracle Net. For example, you can use a SQL*Plus session on a local node to start two instances on remote nodes using individual parameter files named init_db1.ora and init_db2.ora. Before connecting to the database, in SQL*Plus direct your commands to the first instance by entering:


Connect to the first instance, start it, and disconnect from it by entering:

   STARTUP PFILE=$ORACLE_HOME/dbs/initsid.ora

Where the file initsid.ora contains an entry for an spfle.ora file's location on a raw device.

Re-direct commands to the second instance using the following syntax:


Connect to and start the second instance by entering:


Here, DB1 and DB2 are sids for the two instances. These sids are defined with the sid entry in tnsnames.ora. An SPFILE parameter entry in the initsid.ora file specifies a location on the remote instance.

IFILE Use in Traditional Parameter File Scenarios

In the previous example, both parameter files can use the IFILE parameter to include values from an init_dbname.ora file.

Setting Instance Numbers

You must explicitly specify an instance number by using the INSTANCE_NUMBER parameter when you start it. You can do this with Real Application Clusters enabled or disabled. Set INSTANCE_NUMBER equal to the value of the instance's THREAD_ID. The SQL*Plus command:


Shows the current number for each instance.


All instance numbers must be unique.  

If you start an instance merely to perform administrative operations with Real Application Clusters disabled, you can omit the INSTANCE_NUMBER parameter from the parameter file. An instance starting with Real Application Clusters disabled can also specify a thread other than 1 to use the online redo log files associated with that thread.

See Also:


Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index