2
Parameter Management in Real Application Clusters Environments

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
• Using Client-Side Parameter Files
• Parameter Settings in Real Application Clusters
• Types of Parameters in Real Application Clusters
• Considerations for Parameters in Clusters
• The Startup Process and Parameters in Real Application Clusters

  See Also: Oracle9i Real Application Clusters Setup and Configuration for information on creating and configuring the server parameter file in Real Application Clusters

Administering the Server Parameter File in Real Application Clusters Databases

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. This section describes administering the server parameter file and includes the following topics:

• Backing Up the Server Parameter File
• Setting the Server Parameter File Parameter Values for Real Application Clusters
• Exporting the Server Parameter File For Backward Compatibility

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.

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 a server parameter file containing the following entries:

*.OPEN_CURSORS=500
proddb1.OPEN_CURSORS=1000

For the instance with sid proddb1, the parameter remains set to 1000 even though it is preceded by a database-wide setting of 500. The instance-specific parameter setting in the parameter file prevents database-wide alterations of the setting. This gives the Database Administrator (DBA)of instance proddb1 complete control over parameter settings for that instance. These two types of settings can appear in any order in the parameter file.

If another DBA executes the following:

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

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:

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=MEMORY sid='proddb1';

Then execute the following on another instance and the instance with sid proddb1 also assumes the new setting of 2000:

ALTER SYSTEM SET OPEN_CURSORS=2000 sid='*' SCOPE=MEMORY;

If the server parameter file contains the entries:

proddb1.OPEN_CURSORS=1000
*.OPEN_CURSORS=500

Executing:

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='proddb1';

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

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

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='*';

Exporting the Server Parameter File For Backward Compatibility

You can revert to a pre-Real Application Clusters release of Oracle cluster software 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 prerelease 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 the server parameter file into a prerelease 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:

• Create backups of the server parameter file. Note that Recovery Manager (RMAN) does not backup the server parameter file.
• Obtain a list of all parameter values currently used by an instance for diagnostic purposes. Do this using the SHOW PARAMETER command or by querying the V$PARAMETER table.
• Modify the server parameter file by exporting it, editing the output file, and then re-creating it.

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 the parameter when the instance is started up with sid proddb2.

The parameter file entry:

*.DB_FILE_MULTIBLOCK_READ_COUNT=16

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 global and the value specified in the parameter setting is applied to all the sids.

Note that the server parameter file supports the prerelease 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:

OPEN_CURSORS = 1000 
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.

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, initdbname.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

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

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:

• Unique instance name
• Unique thread number and instance number
• Private rollback segments or undo tablespaces
• Local listener if not using default TCP/IP port address for the listener

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 number. 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

ifile='C:\OracleSW\admin\db\pfile\initdb.ora'
thread=1
instance_name=db1
instance_number=1

Example 2-2 initdb2.ora

ifile='C:\OracleSW\admin\db\pfile\initdb.ora'
thread=2
instance_name=db2
instance_number=2

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

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_name="db"
db_domain=us.acme.com
cluster_database=true
service_names=db.us.acme.com
db_files=1024  # INITIAL
control_files=("\\.\db_control1", "\\.\db_control2")
open_cursors=100
db_file_multiblock_read_count=8  # INITIAL
db_block_buffers=13816  # INITIAL
shared_pool_size=19125248  # INITIAL
large_pool_size=18087936
java_pool_size=2097152
log_checkpoint_interval=10000
log_checkpoint_timeout=1800
processes=50  # INITIAL
parallel_max_servers=5  # SMALL
log_buffer=32768  # INITIAL
max_dump_file_size=10240  # limit trace file size to 5M each
global_names=true
oracle_trace_collection_name=""
background_dump_dest=C:\OracleSW\admin\db\bdump
user_dump_dest=C:\OracleSW\admin\db\udump
db_block_size=4096
remote_login_passwordfile=exclusive
os_authent_prefix=""
dispatchers="(protocol=TCP)(lis=listeners_db)"
compatible=9.2
sort_area_size=65536
sort_area_retained_size=65536

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.

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:

   IFILE=INIT_CLUSTER.ORA
   IFILE=INIT_LOG.ORA
   IFILE=INIT_GC.ORA
   LOG_ARCHIVE_START=FALSE
   THREAD=3
   UNDO_MANAGEMENT=AUTO

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.

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.

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

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

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

• INSTANCE_NAME
• THREAD

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
• Parameters That Must Be Identical Across All Instances
• Parameters That Must Be Unique Across All Instances

  See Also: Oracle9i Database Reference for details about other Oracle initialization parameters

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. The following list shows the parameters that must be identical on every instance.

• CONTROL_FILES
• DB_BLOCK_SIZE
• DB_FILES
• DB_NAME
• DB_DOMAIN
• ARCHIVE_LOG_TARGET
• ROW_LOCKING
• DML_LOCKS (Only if set to zero)
• LOG_ARCHIVE_DEST_n (Optional)
• MAX_COMMIT_PROPAGATION_DELAY
• SERVICE_NAMES
• ACTIVE_INSTANCE_COUNT
• TRACE_ENABLED
• GC_FILES_TO_LOCKS (Optional: You do not need to set this parameter because Oracle automatically controls resource assignments.)

  See Also: Oracle9i Real Application Clusters Deployment and Performance for more information about setting GC_FILES_TO_LOCKS

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 by using the sid identifier in the server parameter file. However, you must set a unique value for INSTANCE_NUMBER for each instance and you cannot use a default value.

• Oracle uses the INSTANCE_NUMBER parameter to distinguish among instances at startup. Oracle also uses INSTANCE_NUMBER to assign free space to instances using the INSTANCE option of the ALLOCATE EXTENT clause in the ALTER TABLE or ALTER CLUSTER statements.
• Specify the THREAD parameter so instances avoid the overhead of acquiring different thread numbers during startup and shutdown. Oracle uses the THREAD number to assign redo log groups to specific instances. To simplify administration, use the same number for both the THREAD and INSTANCE_NUMBER parameters.
• Specify the ORACLE_SID environment variable which comprises the database name and the number of the THREAD assigned to the instance.
• Specify INSTANCE_NAME to uniquely identify the instance. The default is the instance's sid and Oracle Corporation recommends that you use this for INSTANCE_NAME.
• Oracle acquires private rollback segments upon instance startup based on the rollback segment names you identify with the ROLLBACK_SEGMENTS initialization parameter. If you do not declare rollback segment names with this parameter for an instance, Oracle acquires public rollback segments for the instance.
• If you specify UNDO_TABLESPACE with automatic undo management enabled, set this parameter to a unique value for each instance.

Considerations for Parameters in Clusters

Table 2-3 summarizes the considerations for using certain parameters in Real Application Clusters databases. Table 2-3 lists the parameters in alphabetical order.

Table 2-3  Initialization Parameter Notes for Instances in Clusters

CLUSTER_INTERCONNECTS = ip1:ip2:...:ipn

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.

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 utility to start instances. You can also use SRVCTL for other administrative tasks as described under the heading "Administering Real Application Clusters Environments with SRVCTL". 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:

   SET INSTANCE DB1;

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

   CONNECT / AS SYSDBA; 
   STARTUP PFILE=$ORACLE_HOME/dbs/initsid.ora
   DISCONNECT;

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

redirect commands to the second instance using the following syntax:

   SET INSTANCE DB2; 

Connect to and start the second instance by entering:

   CONNECT / AS SYSDBA; 
   STARTUP PFILE=full pathINIT_DB2.ORA;

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 upon startup. You can do this with Real Application Clusters enabled or disabled. Oracle Corporation recommends that you set INSTANCE_NUMBER equal to the value you set to identify the instance's THREAD. The SQL*Plus command:

SHOW PARAMETER INSTANCE_NUMBER

Shows the current number for each instance.

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.