Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)

Part Number A76970-01





Go to previous page Go to next page

Parameter Files and Oracle Parallel Server-Specific Parameters

This chapter describes the initialization parameter files and Oracle Parallel Server-specific parameters. It includes the following sections:

Chapter 2 discusses additional parameters for parallel execution in Oracle Parallel Server environments.

Managing Parameter Files for Oracle Parallel Server

In addition to the parameters used in single instance environments, there are several Oracle Parallel Server-specific parameters. Some of these parameters must have identical values across all instances.

You can specify settings for these parameters using one or more parameter files that you edit with any text editor. Oracle reads these settings from the parameter files and writes the values to the control files.

You can implement parameter files in Oracle Parallel Server in several ways using:

Parameter File Naming Conventions

Oracle recommends using these naming conventions for parameter files:

Using these naming conventions simplifies Oracle Parallel Server administration.

One Common Parameter File

If you use all the default parameter settings with Oracle Parallel Server, place one common parameter file on the shared disk. This simplifies administration because you use one file to globally manage parameter settings. If your clustering system does not share files, copy the common file onto each node.

See Also:

Oracle8i Parallel Server Setup and Configuration Guide for details about init_dbname.ora initialization parameter file entries.  

Instance-Specific Parameter Files

For some configurations, you may want to use instance-specific parameter settings to improve performance. For example, you can create System Global Areas (SGAs) of different sizes for each instance. If you do this on a shared file system, also use the common parameter file for parameters that must have identical settings across all instances. Oracle recommends that you identify the common file from within the instance-specific parameter file by setting the IFILE (include file) parameter.


The Database Configuration Assistant does this by default.  

Conditions Under Which You Must Use Instance-Specific Files

You must use instance-specific parameter files when you create instances that:

Instances that use only public rollback segments can share a common parameter file.

See Also:

"Parameters for Common Parameter Files".  

Placement and Use of IFILE Parameters within Instance-Specific Files

If you duplicate parameter entries in a parameter file, the last value specified in the file for the parameter overrides earlier 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 Database Configuration Assistant places the IFILE parameter at the top of the parameter file.  

Using Multiple IFILEs

You can specify IFILE more than once in a parameter file to include multiple common parameter files. If you do not reset a parameter value in each subsequent common parameter file designated with your multiple IFILE entries, then each IFILE does not override previous values. For example, an instance-specific parameter file might include an init_dbname.ora file and separate parameter files for the LOG_* and GC_* parameters as in this example:


In this example, the value of LOG_ARCHIVE_START overrides any value specified in init_log.ora for this parameter because the IFILE parameter appears before the LOG_ARCHIVE_START parameter.

See Also:


Non-Default Parameter Files For Particular Sessions

Specify a non-default parameter file for a particular session by using the PFILE option of the STARTUP command. Do this, for example, to use special parameter settings to optimize parallel execution for off-peak, batch operations.

The parameter file you specify with PFILE must be on a disk accessible to the local node, even if you specify a parameter file for an instance on a remote node. You can have multiple non-default parameter files and use them on demand.

Location of Initialization Files

The database for which the instance is started must have access to the initialization parameter files. Oracle Parallel Server uses the database initialization files located in:

The Startup Process and Parameters in Parallel Server Environments

As mentioned, Oracle writes the parameter values from all parameter files to the control files when the first instance in your environment starts up. The alert log of the first instance identifies that instance as the first one to start and mounts the database. The startup process is described in more detail in Chapter 4.

If the parameter file for a subsequent instance contains a parameter that must be the same for all instances and its value does not match the value already set for that parameter in the control file, the instance cannot mount the database.


To locate your alert log file, use the search string alert*.log.  

Starting Two Instances on Remote Nodes

You can start multiple nodes from a SQL*Plus session on one node. 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_ops1.ora and init_ops2.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:


Redirect commands to the second instance:


Connect to and start the second instance by entering:


Here, OPS1 and OPS2 are Net8 net service names for the two instances. These net service names are defined in TNSNAMES.ORA.

Both individual parameter files can use the IFILE parameter to include parameter values from the init_dbname.ora file.

Instance Numbers and Startup Sequence

You can explicitly specify an instance number by using the initialization parameter INSTANCE_NUMBER when you start it with Oracle Parallel Server enabled or disabled. You should set INSTANCE_NUMBER equal to the value of THREAD_ID. If you do not specify an instance number, the instance automatically acquires the lowest available number.

Always use the INSTANCE_NUMBER parameter if you need consistent allocation of extents to instances for inserts and updates. This allows you to maintain data partitioning among instances. You must specify unique instance numbers for each instance when using the INSTANCE_NUMBER parameter.

When an instance starts up, it acquires an instance number that maps the instance to one group of free lists for each table created with the FREELIST GROUPS storage option.

Startup Order Determines Instance Number by Default

The startup order determines the instance number if you have not specified a value for INSTANCE_NUMBER. Note that default startup numbers are difficult to control if instances start up in parallel. Moreover, instance numbers can change after you shut down and restart instances. The SQL*Plus command:


Shows the current number for each instance. This command displays a null value if Oracle assigned an instance number based on startup order.

After you shut down an instance, its instance number is available for re-use. If a second instance starts up before the first instance restarts, the second instance can acquire the instance number previously used by the first instance.

Instance numbers based on startup order are independent of instance numbers specified with the INSTANCE_NUMBER parameter. After an instance acquires an instance number by one of these methods, either with or without INSTANCE_NUMBER, another instance cannot acquire the same number by another method. All instance numbers must be unique, regardless of the method by which they are acquired.

An instance starting with Oracle Parallel Server disabled can specify an instance number with the INSTANCE_NUMBER parameter. This is only necessary if the instance performs inserts and updates and if the tables in your database use the FREELIST GROUPS storage option to allocate free space to instances.

If you start an instance merely to perform administrative operations with Oracle Parallel Server disabled, you can omit the INSTANCE_NUMBER parameter from the parameter file.

An instance starting with Oracle Parallel Server disabled can also specify a thread other than 1 to use the online redo log files associated with that thread.

See Also:


Setting Initialization Parameters for Multiple Instances

This section discusses Oracle Parallel Server initialization parameters for multiple instances and covers the following topics:

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 Oracle Parallel Server. Specify these parameter values in the common parameter file, or within each init_dbname.ora file on each instance. Table 1-1 lists the parameters that must be identical on every instance.

Table 1-1 Parameters That Must Be Identical on All Instances


LM_LOCKS and LM_RESS (automatically calculated by Oracle, but identical values recommended)  


LOG_ARCHIVE_DEST (optional) 












DML_LOCKS (only if set to zero)  

Parameters That Must Be Unique Across All Instances

If you use the parameters INSTANCE_NUMBER, THREAD, or ROLLBACK_SEGMENTS, Oracle recommends setting unique values for them using instance-specific parameter files.

Parameters for Common Parameter Files

This section on common parameter files includes the following topics:

DB_NAME Parameter

Make sure you include the DB_NAME parameter in the common parameter file. If you do not set a value for DB_NAME in the common file, you must set a value for this parameter in the instance or non-default parameter files. The value you set for this parameter must be identical for all instances.

If you specify parameters with identical values in a common parameter file referred to by IFILE, you can omit parameters for which you are using the default values.

GC_* Global Cache Parameters

Initialization parameters with the prefix GC (Global Cache) are relevant only to Oracle Parallel Server. The settings of these parameters determine the size of the collection of global locks that protect the database buffers on all instances. The settings you choose also affect the use of certain operating system resources. Specify these parameters in the init_dbname.ora file.

The first instance to start up in shared mode determines the values of the global cache parameters for all instances. The control file records the values of the GC_* parameters when the first instance starts up.

When another instance attempts to start up in shared mode, Oracle compares the values of the global cache parameters in its parameter file with those already in use and issues a message if any values are incompatible. The instance cannot mount the database unless it has the correct values for its global cache parameters.

The global cache parameters for Oracle Parallel Server are:

Parameter:   Description:  


Controls the ratio of data block locks to data blocks. (must be set identically on all instances).  


Controls the number of undo block locks. (must be set identically on all instances).  


Controls the number of releasable locks. 


Specifies the amount of time in hundredths of seconds that Oracle waits before responding to forced-write requests from other instances for hot blocks.  

See Also:

Part Three of this book, "Oracle Parallel Server Design and Deployment" for a thorough discussion of setting global cache parameters.  

Multiple Instance Issues for Initialization Parameters

Table 1-2 summarizes multi-instance issues concerning initialization parameters.

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


Must be identical on all instances only if set to zero. The value should equal the total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.

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. Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter. 


If specified, this parameter must have unique values on all instances. In Oracle Parallel Server 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. In a single-instance database system, the instance name is usually the same as the database name.  


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 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 an Oracle Parallel Server-specific parameter. However, you should not change it except under a limited set of circumstances specific to the Oracle Parallel Server.

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 

There are several NLS parameters as described in Oracle8i Reference . You can set different values for different instances.  


To enable a database to be started in Oracle Parallel Server mode, set this parameter to TRUE in the instance initialization file (init_sid.ora).  


Set this parameter equal to the number of instances in your Oracle Parallel Server environment. Oracle uses the value of this parameter to size memory structures to optimize performance. PARALLEL_SERVER_INSTANCES is an Oracle Parallel Server parameter that specifies the number of instances currently configured. You must set this parameter for every instance.

Normally you should set this parameter to the number of instances in your Oracle Parallel Server environment. Oracle uses the value of this parameter to compute the default value of the LARGE_POOL_SIZE parameter when the PARALLEL_AUTOMATIC_TUNING parameter is set to TRUE. A proper setting for this parameter can improve memory use. 


This parameter must have a value large enough to accommodate all background and user processes. Some operating systems can have additional DBWR processes. Defaults for the SESSIONS and TRANSACTIONS parameters are derived directly or indirectly from the value of the PROCESSES parameter. PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes. The default values of the SESSIONS and TRANSACTIONS parameters are derived from this 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 LCKn and other optional background processes.  

For the eight parameters just described in this table, if you do not use defaults, you may want to increase the values for some of these parameters. This allows Oracle to create additional LCKn processes and other background processes to improve performance.  


To speed up the roll forward or cache recovery phase, 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 this 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:


You cannot change the value of this parameter dynamically, but you can change its value and then restart the instance. Although this parameter usually specifies private rollback segments, it can also specify public rollback segments if they are not already in use. To find the name, segment ID number, and status of each rollback segment in the database, query the data dictionary view DBA_ROLLBACK_SEGS. 


If specified, this parameter must have unique values on all instances. THREAD is an Oracle Parallel Server parameter that specifies the number of the redo thread to be used by this instance. When you create a database, Oracle creates and enables thread 1 as a public thread (one that can be used by any instance). You must create and enable subsequent threads using the ADD LOGFILE THREAD clause and ENABLE THREAD clause of the ALTER DATABASE statement.

The number of threads you create is limited by the MAXINSTANCES parameter specified in the CREATE DATABASE statement. In exclusive mode, thread 1 is the default thread. However, you can specify THREAD for an instance running in exclusive mode if you want to use the redo log files in a thread other than thread 1. In parallel mode, 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 this instance can use any available, enabled public thread.  


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 MTS_DISPATCHER Parameter and Oracle Parallel Server

To enable a Multi-Threaded Server configuration, set the MTS_DISPATCHERS parameter in the common file. The MTS_DISPATCHERS parameter may contain many attributes.

Oracle 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 will register dispatcher information. The alias should be set to a name that is resolved through a naming method such as a tnsnames.ora file.

See Also:

Oracle8i Parallel Server Setup and Configuration Guide and the Net8 Administrator's Guide for complete information about configuring the MTS_DISPATCHER parameter and its attributes and for configuring the Multi-Threaded Server.  

LM_* Initialization Parameters

Distributed Lock Manager capacity is determined by the values Oracle sets for the LM_RESS and LM_LOCKS parameters. Table 1-3 describes these parameters. The Distributed Lock Manager automatically calculates values for LM_RESS and LM_LOCKS.

If your shared pool runs out of space, or if the maximum utilization shown in the V$RESOURCE_LIMIT view is greater than the values Oracle sets for these parameters, adjust LM_RESS and LM_LOCKS as described in Chapter 10. Otherwise, you do not need to set these parameters. If you adjust the settings, Oracle recommends that you set the values for them identically across all instances to simplify administration.

Table 1-3 LM_* Initialization Parameters
Parameter  Description 


This parameter controls the number of resources that can be locked by the Distributed Lock Manager (DLM). This parameter includes non-PCM resources such as the number of lock resources allocated for DML, DDL, data dictionary cache locks, and file and log management locks.  


This parameter controls the number of locks. Where N is the total number of nodes:

LM_LOCKS = LM_RESS + (LM_RESS * (N - 1))/N  

Use increased values for LM_RESS and LM_LOCKS if you plan to use parallel DML or DML performed on partitioned objects.

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

All Rights Reserved.