Oracle8i Parallel Server Concepts and Administration
Release 8.1.5






Prev Next

Administering Multiple Instances

Justice is a machine that, when someone has once given it the starting push, rolls on of itself.

John Galsworthy: Justice. Act II.

This chapter describes how to administer instances of a parallel server. It includes the following topics:


This chapter explains how to configure and start up instances for OPS using the following procedure:

  1. Define multiple instances by setting up parameter files.

  2. Set initialization parameters for multiple instances.

  3. Determine how many PCM and non-PCM lock you require and set the LM_* parameters.

  4. Create database objects for multiple instances.

  5. Starting instances.

The details of this procedure appear after a brief explanation of Oracle Parallel Server Management.

See Also:

"Starting Up and Shutting Down" in the Oracle8i Administrator's Guide.  

Oracle Parallel Server Management

Oracle Parallel Server Management (OPSM) is a comprehensive and integrated system management solution for OPS. OPSM allows you to configure and manage multi-instance databases running in heterogeneous environments through an open client-server architecture.

In addition to managing parallel databases, OPSM allows you to schedule jobs, perform event management, monitor performance, and obtain statistics to tune parallel databases.

For more information about OPSM, refer to the Oracle Parallel Server Management Configuration Guide for UNIX and the Oracle Parallel Server Setup and Configuration Guide. For installation instructions, please refer to your platform-specific installation guide.

Defining Multiple Instances with Parameter Files

When an instance starts, Oracle uses values in an initialization parameter file to create the System Global Area (SGA) for that instance. You use parameter files in various ways to define multiple instances:

Using a Common Parameter File for Multiple Instances

A common parameter file for all instances, as shown in Figure 18-1, can simplify administration. If file systems are shared among nodes, you can update all instances by making a change in only one place.

Figure 18-1 Instances with a Common Parameter File

Most clustering systems, however, do not share file systems. In such cases, make a separate physical copy of the common file for each node.

Using Individual Parameter Files for Multiple Instances

Individual parameter files are useful when many parameters differ from instance to instance. For example, initialization parameters to create different sized SGAs for different sized machines may improve performance dramatically.

Figure 18-2 Instances with Individual Parameter Files

Embedding a Parameter File Using IFILE

By setting the IFILE parameter, each individual parameter file can embed an additional parameter file containing common values. This approach is illustrated in Figure 18-3.

Figure 18-3 Instances with Individual Parameter Files and IFILE

In Oracle Parallel Server (OPS), some initialization parameters must have the same values for every instance, whether individual or common parameter files are used. By referencing the same file using the IFILE parameter, instances can use their unique parameter files and also ensure they have the correct values for parameters that must be identical across all instances.

Instances must use individual parameter files in the following cases:


For example, a Server Manager session on the local node can start two instances on remote nodes using individual parameter files named INIT_OPS1.ORA and INIT_OPS2.ORA:


Here, "INSTANCE1" and "INSTANCE2" are Net8 aliases for the two respective instances. These aliases are defined in TNSNAMES.ORA.

Both individual parameter files can use the IFILE parameter to include parameter values from the file INIT_COMMON.ORA. They can reference this file as follows:






Oracle recommends making INSTANCE NAME identical to SID.  

The INIT_COMMON.ORA file can contain the following parameter settings that must be identical on both instances.



Oracle recommends that you set SERVICE_NAMES to be identical to DBNAME.  

Each parameter file must contain identical values for the CONTROL_FILES parameter, for example, because all instances share the control files.

To change the value of a common initialization parameter, modify the INIT_COMMON.ORA file rather than changing both individual parameter files.


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

If you use multiple Server Manager sessions on separate nodes to start up the instances, each node must have its own copy of the common parameter file unless the file systems are shared.

If a parameter is duplicated in an instance-specific file and in the common file, or within one file, the last value specified overrides earlier values. You can therefore ensure the use of common parameter values by placing the IFILE parameter at the end of an individual parameter file. Placing IFILE at the beginning of the individual file allows you to override the common values.

You can specify IFILE more than once in a parameter file to include multiple common parameter files. Unlike the other initialization parameters, IFILE does not override previous values. For example, an individual parameter file might include an INIT_COMMON.ORA file and separate command files for the LOG_* and GC_* parameters:


The individual value of LOG_ARCHIVE_START overrides the value specified in INIT_LOG.ORA, because the statement IFILE = INIT_LOG.ORA appears before the LOG_ARCHIVE_START parameter specification. The individual GC_* values specified in INIT_GC.ORA override values specified in INIT_COMMON.ORA because IFILE = INIT_GC.ORA comes after IFILE = INIT_COMMON.ORA.

See Also:

"Instance Numbers and Startup Sequence", "Redo Log Files", and "Parameters that Must Be Identical on All Instances".  

Specifying a Non-default Parameter File with PFILE

Use the PFILE option of the STARTUP command to specify a parameter file other than the default file when you start up an instance. The parameter file specified by PFILE must be on a disk accessible to the local node, even for an instance on a remote node.

Setting Initialization Parameters for Multiple Instances

This section discusses important OPS initialization parameters for multiple instances.

GC_* Global Cache Parameters

Initialization parameters with the prefix GC (Global Cache) are relevant only for OPS. 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 affect use of certain operating system resources.

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 correct values for its global cache parameters.

The global cache parameters for OPS are:


Controls data block locks.  


Controls undo block locks.  


Controls the number of locks.  

See Also:

Chapter 15, "Allocating PCM Instance Locks".  

Parameter Notes for Multiple Instances

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

Table 18-1 Initialization Parameter Notes for Multiple Instances
Parameter   Parallel Server Notes  


In OPS, your database may have more datafiles than in a single-instance environment. To speed up checkpoints, enable the CHECKPOINT_PROCESS parameter.  


The default value TRUE reduces pinging between instances.  


Must be identical on all instances only if set to zero.  


If specified, this parameter must have unique values for different instances.  


You must include thread number.  


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

NLS_* parameters  

Can have different values for different instances.  


To enable OPS, this parameter must be set to TRUE in the initialization file. It defaults to FALSE.  


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.

If you do not use defaults, you may want to increase the values for some of the above parameters to allow for LCKn and other optional background processes.  


To speed up the roll forward or cache recovery phase, you may want to set this parameter.  


Specify the private rollback segments for each instance.  


If specified, this parameter must have unique values for different instances.  

See Also:

Oracle8i Reference for details about each parameter.  

Parameters that Must Be Identical on 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 OPS. For example, the values of DB_BLOCK_SIZE and CONTROL_FILES must be identical for every instance. Other parameters can have different values for different instances. The following initialization parameters must have identical values for every instance in a parallel server:

DML_LOCKS (must be identical only if set to zero)
LM_LOCKS (identical values recommended)
LM_PROCS (identical values recommended)
LM_RESS (identical values recommended)

See Also:

Oracle8i Reference for details about each parameter.  

Determining the Amount of Locks Needed and Setting LM_* Parameters

Set values for the LM_* initialization parameters. Resources, locks and process configurations are per OPS instance. For ease of administration, these parameters should be consistent for all instances.


This parameter controls the number of resources that can be locked by the Integrated Distributed Lock Manager (IDLM). This parameter includes non-PCM resources such as the number of lock resources allocated for DML, DDL (data dictionary locks), and data dictionary cache locks plus file and log management locks. Derive a value for LM_RESS by adding the number of PCM and non-PCM resources as calculated in Chapter 15, "Allocating PCM Instance Locks" and Chapter 16, "Ensuring IDLM Capacity for Resources and Locks" respectively.  


Number of locks. Where N is the total number of nodes:

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


Number of processes. The value of the PROCESSES initialization parameter multiplied by the number of nodes.  

Used increased values if you plan to use parallel DML or DML performed on partitioned objects.

Creating Database Objects for Multiple Instances

Creating a database automatically starts a single instance with OPS disabled. Before you can start multiple instances, however, you must perform certain administrative operations. These tasks may include:

You can perform these operations with a single instance in either exclusive or shared mode.

See Also:

"Creating Additional Rollback Segments", "Redo Log Files", and "What Is the Total Number of PCM Locks and Resources Needed?".  

Starting Instances

An Oracle instance can start with OPS enabled or disabled. This section explains the procedures on how to do this:

Enabling Parallel Server and Starting Instances


In Oracle8 the keywords SHARED, EXCLUSIVE, and PARALLEL are obsolete in the STARTUP and ALTER DATABASE MOUNT statements.  

Starting an Instance Using SQL

  1. To enable OPS in Oracle8, set the PARALLEL_SERVER parameter to TRUE in the initialization file. It defaults to FALSE.

  2. Start any required operating system specific processes.

    For more information about these, please see your Oracle system-specific documentation.

  3. Ensure your Cluster Manager software is running.

    See "The Cluster Manager" for more information.

  4. Connect with SYSDBA or SYSOPER privileges.

       CONNECT username/password AS SYSDBA
  5. Make sure the PARALLEL_SERVER initialization parameter is set to TRUE if you wish to run with OPS enabled, or to FALSE to run with OPS disabled.

  6. Start an instance.

  7. Mount a database.

       ALTER DATABASE database_name MOUNT
  8. Open the database.


Starting an Instance Using Server Manager


The Server Manager command STARTUP with the OPEN option performs steps 4, 5, and 6 of the procedure given above.  

  1. Start any required operating system specific processes.

    For more information, please see your Oracle system-specific documentation.

  2. Set the PARALLEL_SERVER initialization parameter to TRUE to run with OPS enabled, or to FALSE to run with OPS disabled.

  3. Ensure your Cluster Manager software is running.

    See "The Cluster Manager" for more information.

  4. Start Server Manager.

  5. Start up an instance with the OPEN option:

    STARTUP OPEN database_name 

Starting with OPS Disabled

OPS must be disabled whenever you change the archiving mode (ARCHIVELOG or NOARCHIVELOG). To change the archiving mode, the database must be mounted but not open.

If an instance mounts a database with PARALLEL_SERVER set to FALSE, no other instance can mount the database.

Before you can start an instance in exclusive mode, shut down all instances running in shared mode. A single instance running in shared mode is not the same as an instance running in exclusive mode, and the last instance running in shared mode does not automatically revert to exclusive mode.

An instance starting with OPS 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 just to perform administrative operations with OPS disabled, omit the INSTANCE_NUMBER parameter from the parameter file.

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

See Also:

Chapter 17, "Using Free List Groups to Partition Data".  

Starting in Shared Mode

In OPS, each instance must mount the database in shared mode. Each initialization parameter file for each instance must have the SINGLE_PROCESS parameter set to FALSE and the PARALLEL_SERVER parameter set to TRUE. Before you start up multiple instances in shared mode, create at least one rollback segment for each instance sharing the same database and enable a thread containing at least two groups of redo log files for each additional instance.

If one instance mounts a database in shared mode, other instances can also mount the database in shared mode, but not in exclusive mode.

If PARALLEL_SERVER is set to FALSE, the instance tries to start with OPS disabled by default.

Retrying to Mount a Database in Shared Mode

If you attempt to start an instance and mount a database in shared mode while another instance is currently recovering the same database, your new instance cannot mount the database until the recovery is complete.

Rather than repeatedly attempting to start the instance, you can use the STARTUP RETRY statement. This causes the new instance to retry every five seconds to mount the database until it succeeds or has reached the retry limit. For example:


To set the maximum number of times the instance attempts to mount the database, use the Server Manager SET command with the RETRY option; you can specify either an integer (such as 10) or the keyword INFINITE.

If the database can only be opened by being recovered by another instance, then the RETRY will not repeat. For example, if the database was mounted in exclusive mode by one instance, then trying the STARTUP RETRY command in shared mode does not work for another instance.

Instance Numbers and Startup Sequence

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.

An instance can specify its instance number explicitly by using the initialization parameter INSTANCE_NUMBER when it starts up with OPS enabled or disabled. If an instance does not specify the INSTANCE_NUMBER parameter, it automatically acquires the lowest available number.

Startup order determines the instance numbers for instances that do not specify the INSTANCE_NUMBER parameter. Startup numbers are difficult to control if instances start up in parallel, and they can change after instances shut down and restart.

Instances using the INSTANCE_NUMBER parameter must specify different numbers. The Server Manager command SHOW PARAMETERS INSTANCE_NUMBER shows the current instance number each instance is using. This command displays a null value if an instance number was assigned based on startup order.

After an instance shuts down, its instance number becomes available again. 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 the other method. All numbers are unique, regardless of the method by which they are acquired.

Always use the INSTANCE_NUMBER parameter if you need a consistent allocation of extents to instances for inserts and updates. This allows you to maintain data partitioning among instances.

See Also:

"Rollback Segments", "Creating Additional Rollback Segments", "Redo Log Files", and Chapter 17, "Using Free List Groups to Partition Data" for information about allocating free space for inserts and updates.  

Specifying Instances

When performing administrative operations in a multi-instance environment, be sure you specify the correct instance. This section includes the following topics related to instance-specific administration:

Differentiating Between Current and Default Instance

Some Server Manager commands apply to the instance to which Server Manager is currently connected and others apply to the default instance.

Default instance.  

The default instance is on the machine where you initiate Server Manager. Server Manager commands that cannot be used while you are connected to an instance, such as executing a host command, apply to the default instance.  

Current instance.  

The current instance is determined by the CONNECT command. Server Manager commands that can be used while you are connected to an instance apply to the current instance.  

The current instance can be different from the default instance if you specify a connect string in the CONNECT command.

Net8 must be installed to use the SET INSTANCE or CONNECT command for an instance running on a remote node.

See Also:

Your platform-specific Oracle documentation, for more information about installing Net8 and the exact format required for the connect string used in the SET INSTANCE and CONNECT commands.  

How SQL Statements Apply to Instances

Instance-specific SQL statements apply to the current instance. For example, the statement ALTER DATABASE ADD LOGFILE only applies to the instance to which you are currently connected, rather than the default instance or all instances.

ALTER SYSTEM CHECKPOINT LOCAL applies to the current instance. By contrast, ALTER SYSTEM CHECKPOINT or ALTER SYSTEM CHECKPOINT GLOBAL applies to all instances.

ALTER SYSTEM SWITCH LOGFILE applies only to the current instance. To force a global log switch, you can use ALTER SYSTEM ARCHIVE LOG CURRENT. The THREAD option of ALTER SYSTEM ARCHIVE LOG allows you to archive online redo log files for a specific instance.

How Server Manager Commands Apply to Instances

When you initiate Server Manager, the commands you enter are relevant to the default instance, which is also the current instance.

This is true until you use the SET INSTANCE command to set the current instance. From that point, all Server Manager commands operate on the current instance.

Table 18-2 describes how these commands relate to instances.

Table 18-2 How Server Manager Commands Apply to Instances
Server Manager Command   Associated Instance  


Always applies to the current instance.  


Uses the default instance if no instance is specified in the CONNECT command.  


Applies to the node running the Server Manager session, regardless of the location of the current and default instances.  


MONITOR display screens identify the current instance, not the default instance, in the upper left corner.  


Does not apply to any particular instance, but rather to the database.  


Displays information about the default instance, which can be different from the current instance.  


Displays information about the current instance.  


Displays information about the current instance.  


Always applies to the current instance. A privileged Server Manager command.  


Always applies to the current instance. A privileged Server Manager command.  


The security mechanism invoked when you use privileged Server Manager commands depends on your operating system. Most operating systems have a secure authentication mechanism when logging onto the operating system. On these systems, your default operating system privileges usually determine whether you can use STARTUP and SHUTDOWN. For more information, see your Oracle system-specific documentation.  


You can change the default instance with the Server Manager statement:

   SET INSTANCE instance_path

where instance_path is a valid Net8 connect string without a user ID/password. If you are connected to an instance, you must disconnect before using SET INSTANCE. Alternatively, if you do not wish to disconnect from the current instance, you may use the CONNECT command with instance_path.

You can use the SET INSTANCE command to specify an instance on a remote node for the commands STARTUP and SHUTDOWN. The parameter file for a remote instance must be on the local node.

The SHOW INSTANCE command displays the connect string for the default instance. SHOW INSTANCE returns the value local if you have not used SET INSTANCE during the Server Manager session.

To reset to the default instance, use SET INSTANCE without specifying a connect string or specify LOCAL (but not DEFAULT, which would indicate a connect string for an instance named "DEFAULT").

The following Server Manager line mode examples illustrate the relationship between SHOW INSTANCE and SET INSTANCE:

   INSTANCE                      LOCAL 

   SET INSTANCE node1 
   Oracle8 Server Release 8.1 - Production
   With the distributed, parallel query and Parallel Server options
   PL/SQL V8.1 - Production

   Instance                      node2 

   ORACLE8 Server Release 8.1 - Production
   With the procedural, distributed, and Parallel Server options 
   PL/SQL V8.1 - Production

   INSTANCE                      LOCAL 

   ORA-06030: NETDNT: connect failed, unrecognized node name 

The CONNECT Command

The CONNECT command associates Server Manager with either the default instance or an instance you explicitly specify. The instance to which Server Manager connects becomes the current instance.

The CONNECT command has the following syntax:

where instance-path is a valid Net8 connect string. CONNECT without the argument @instance-path connects to the default instance (which may have been set previously with SET INSTANCE).

Connecting as SYSOPER or SYSDBA allows you to perform privileged operations, such as instance startup and shutdown.

Multiple Server Manager sessions can connect to the same instance at the same time. When you are connected to one instance, you can connect to a different instance without using the DISCONNECT command. Server Manager disconnects you from the first instance automatically whenever you connect to another one.

The CONNECT @instance-path command allows you to specify an instance before using the Server Manager commands MONITOR, STARTUP, SHUTDOWN, SHOW SGA, and SHOW PARAMETERS.

See Also:

Oracle Server Manager User's Guide for syntax of Server Manager commands, the Net8 Administrator's Guide for the proper specification of instance_path, and the Oracle8i Administrator's Guide for information on connecting with SYSDBA or SYSOPER privileges.  

The Cluster Manager

To achieve high availability, OPS cooperates with a platform-specific software component known as the Cluster Manager (CM). Hardware vendors usually provide this component.

CM monitors the status of various resources in a cluster including nodes, interconnect hardware and software, shared disks, and Oracle instances. CM automatically starts and stops when the instance starts and stops.

The CM informs clients and the Oracle server when the statuses of resources change. The Oracle server must know when another database instance registers with the CM or disconnects from it. Database instances register with the CM during the mount phase of startup.

A CM disconnect occurs for any of three reasons: the client disconnects voluntarily, the client's process terminates, or the client's node shuts down or crashes. An important feature of CM is that it provides a global view of the cluster, even during failures. This ensures the integrity of OPS databases, as each instance must be aware of all other instances to coordinate access to shared disks.

Oracle accesses CM through an interface called the "Node Monitor API". This interface provides an abstract link to process groups whose members may be arbitrarily distributed throughout the cluster. When an OPS instance is mounted, the LMON process joins one of these groups. Any instances of the same database that were already running are informed of the new OPS instance. All instances then synchronize to ensure they have the same view of active instances. The CM then informs the IDLM layer about any new instances; the CM also initiates an IDLM reconfiguration. If an instance shuts down or terminates abnormally, CM informs the remaining instances. Again, the CM synchronizes the instances informs the IDLM.

OPS Cluster Administration

When starting an OPS instance, first ensure your CM software is running. Detailed instructions on CM administration appear in platform-specific documentation. If the CM is not available or if Oracle has a problem communicating with it, Oracle displays error ORA-29701: "Unable to connect to Cluster Manager".

Multiple Version Compatibility on Clusters

As long as your Oracle version numbers are greater than 8.1, they can co-exist on the same cluster. This also means you cannot have different versions of Oracle older than 8.1 on the same cluster. For example, an 8.0 and an 8.1 OPS database are not compatible on the same cluster.

Specifying Instance Groups

For ease of administration, logically group different instances and perform parallel operations on all associated instances at once. You can define an instance group as a set of instances used for a specific purpose, such as resource allocation, parallel query or other parallel operations. They thus enable you to partition your resources effectively.

Sometimes, for example, a DBA may wish to prevent users or query processes from obtaining resources on all instances. The DBA may want to keep certain instances available only for users running OLTP processes, and restrict users running parallel queries only to a particular set of instances.

For example, you might create instance groups such that between 9 AM and 5 PM users can use group B, but after 5 PM they can use group D. Or, you might use group C for normal OLTP inserts and updates but use group D for large parallel tasks to avoid interfering with OLTP performance.

If you simply set the degree of parallelism, the system chooses which specific instances to use given disk affinity, and the number of instances actually running. By specifying instance groups, you can directly specify the instances for parallel operations.

The instance from which you initiate a query need not be a member of the group of instances that perform the query. The parallel coordinator does run on the current instance.

How to Specify Instance Groups

To specify instance groups, set the INSTANCE_GROUPS initialization parameter within the parameter file of each instance you wish to associate to the group. This parameter at once defines a group and adds the current instance to the group.

For example, instance 1 could set the parameter as follows:


Instance 3 could set it as follows:


As a result, instances 1 and 3 would both belong to instance group D, but would also belong to other groups as well.

You cannot dynamically change INSTANCE_GROUPS.

How to Use Instance Groups

You can use instance groups to identify a group to be used for a parallel operation with PARALLEL_INSTANCE_GROUP

The default for PARALLEL_INSTANCE_GROUP is a group consisting of all currently running instances.

To use a particular instance group for a given parallel operation, specify the following parameter in the initialization parameter file:


All parallel operations initiated from that instance spawn processes only within that group, using the same algorithm as before either randomly or with disk affinity.

PARALLEL_INSTANCE_GROUP is a dynamic parameter that you change using an ALTER SESSION or ALTER SYSTEM statement. You can use it to refer to only one instance group; by default it is set to a default group that includes all currently active instances. The instance upon which you are running need not be a part of the instance group you are going to use for a particular operation.

See Also:

The Oracle8i Reference for complete information about initialization parameters and views.  

How to List Members of Instance Groups

To see the members of different instance groups, query the GV$ global dynamic performance view GV$PARAMETER. Look at all entries for the INSTANCE_GROUPS parameter name.

Instance Group Example

In this example, instance 1 has the following settings in its initialization parameter file:


Instance 2 has the following settings in its initialization parameter file:


On instance 1, if you enter the following statements, the instances in Gb is used. Two server processes spawn on instance 1, and 2 server processes on instance 2.


If you enter the following statements on instance 1, Gc will be used. Two server processes will be spawned on instance 2 only.


If you enter the following statements on instance 1, the default instance group (all currently running instances) is used. Two server processes spawn on instance 1, and 2 server processes on instance 2.


Using a Password File to Authenticate Users on Multiple Instances

You can use a password file to authenticate users performing database administration when running multiple instances on OPS. In this case, the environment variable for each instance must point to the same password file. Similarly, the REMOTE_LOGIN_PASSWORDFILE initialization parameter for each instance must be set to the appropriate, identical value.

See Also:

The Oracle8i Administrator's Guide for information about the REMOTE_LOGIN_PASSWORDFILE parameter. For more information on the exact name of the password file, or for the name of the environment variable used to specify this name for your operating system, see your Oracle system-specific documentation.  

Shutting Down Instances

Use the following procedure to shut down an instance:

  1. Connect with SYSDBA.

    CONNECT username/password AS SYSDBA
  2. Close the database.

    ALTER DATABASE database_name CLOSE
  3. Dismount the database.


Alternatively, you can use the Server Manager command SHUTDOWN, which performs all three of these steps for the current instance.

In OPS, shutting down one instance does not interfere with the operations of any instances still running.

To shut down a database mounted in shared mode, shut down every instance in the parallel server. OPS allows you to shut down instances in parallel from different nodes. When an instance shuts down abnormally, Oracle forces all user processes running in that instance to log off the database. If a user process is currently accessing the database, Oracle terminates that access and returns the message "ORA-1092: Oracle instance terminated. Disconnection forced". If a user process is not currently accessing the database when the instance shuts down, Oracle returns the message "ORA-1012: Not logged on" upon the next call or request made to Oracle.

After a NORMAL or IMMEDIATE shutdown, instance recovery is not required. Recovery is required, however, after the SHUTDOWN ABORT command or after an instance terminates abnormally. The SMON process of an instance that is still running performs instance recovery for the instance that shut down. If no other instances are running, the next instance to open the database performs instance recovery for any instances that need it.

If multiple Server Manager sessions are connected to the same instance simultaneously, all but one must disconnect before the instance can be shut down normally. You can use the IMMEDIATE or ABORT option of the SHUTDOWN command to shut down an instance when multiple Server Manager sessions (or any other sessions) are connected to it.

See Also:

"Starting Up and Shutting Down" in Oracle8i Administrator's Guide for options of the SHUTDOWN command.  

Limiting Instances for Parallel Query

Although the parallel query feature does not require OPS, some aspects of parallel query apply only to a parallel server.

The INSTANCES keyword of the PARALLEL clause of the CREATE TABLE, ALTER TABLE, CREATE CLUSTER, and ALTER CLUSTER commands allows you to specify that a table or cluster is split up among the buffer caches of all available instances of OPS when the table is scanned in a parallel query.

If you do not want tables to be dynamically partitioned among all available instances, specify the number of instances that can participate in scanning or caching with the ALTER SYSTEM command.

To specify the number of instances to participate in parallel query processing at startup time, specify a value for the initialization parameter PARALLEL_MAX_INSTANCES.

To dynamically limit the number of instances available for parallel query processing, use the ALTER SYSTEM command. For example, if your parallel server has ten instances running but you want only eight to be involved in parallel query processing, while the remaining two instances are dedicated for other use issue the following command:


Thereafter, if a table's definition has a value of ten specified for the INSTANCES keyword, the table is scanned by query servers on only eight of the ten instances. Oracle selects the first eight instances in this example. You can set the PARALLEL_MAX_SERVERS initialization parameter to zero on instances that you do not want to have participating in parallel query processing.

If you wish to limit the number of instances that cache a table, issue the following command:


Thereafter, if a table definition has 10 specified for the INSTANCES keyword and the CACHE keyword was specified, the table is divided evenly among eight of the ten available instances' buffer caches.

See Also:

"Specifying Instance Groups" and the Oracle8i Reference for more information about parameters. For more information on parallel query or parallel execution, please refer to Oracle8i Tuning.  


The parameter PARALLEL_SERVER_INSTANCES specifies the number of instances configured in an OPS environment. Use this parameter to provide information to Oracle to manage the size of SGA structures that depend on the number of instances. When you set this parameter, the system makes better use of available memory.

Instance Registration and Client/Service Connections

Instance registration involves three separate processes. These processes are registration of:

PMON performs service handler registration by registering MTS dispatchers with TNS listeners. PMON also registers dedicated server handlers by dynamically communicating with the TNS listener. PMON can also dynamically register dedicated server handlers by telling the TNS listener how to start new dedicated processes.

Registering dedicated server handlers is where TNS listeners of an instance only create dedicated servers if the instance has been registered. This is part of the process of connect time failover. The processes spawned are then available to handle dedicated client server connections.

Instance registration refers to the recording of instance-specific information among all related listener processes. Most importantly, information about the load for a given instance is recorded among other listener processes within the same service.

How Clients Access Services

Clients connect to services by contacting the listeners as specified in the TNSNAMES.ORA file. The client passes the CONNECT_DATA from the TNSNAMES.ORA to the listener. This information tells the listener the service that the client wants to connect to. The listener then redirects the client to an appropriate handler or the listener creates a new dedicated server for the client.

Figure 18-4 Client-Service Connections

Configuring Client-to-service Connections

To configure client-to-service connections, or "client-to-instance" connections, make INIT.ORA entries as described in this section. This section also describes entries to TNSNAMES.ORA. For more information about configuring TNSNAMES.ORA, please refer to the Net8 Administrator's Guide.

This section explains how entries in these files influence the following OPS features:

Database Instance Registration

Database instance registration is the process by which an instance "registers" with a listener. You can configure instances to register with local and remote listeners of the same service. To set up registration, configure the following INIT.ORA parameters:

   LOCAL_LISTENER = protocol address
   INSTANCE_NAME = instance name

For SERVICE_NAMES, enter a fully qualified service name as in this example. The value you enter for SERVICE_NAMES in INIT.ORA should be the same as the entry in TNSNAMES.ORA.


There are alternate settings you can use instead of setting the SERVICE_NAMES parameter. For more information on these, please refer to the Net8 Administrator's Guide.  

Connect Time Failover

Connect time failover refers to a client attempting to connect to a second listener when the attempt to connect to the first listener fails. You control how the client executes these connection attempts by the way you enter listener addresses in the address list within TNSNAMES.ORA.

To do this, enter listener addresses in TNSNAMES.ORA in the order in which you want the client to attempt to make client-to-service connections. Also set the TNSNAMES.ORA parameter FAILOVER to ON.

Connect time failover continues until the client successfully connects to a listener.


Implementing connect time failover does not allow use of static service configuration parameters. Therefore, you cannot simultaneously implement Oracle Enterprise Manager (OEM) and connect time failover. OEM's Dynamic Discovery feature searches within a service for active connections using static configuration parameters.  

See Also:

For more information on Transparent Application Failover, please refer to Oracle8i Tuning.  

Client Load Balancing

Client load balancing refers to the balancing of client connections among all listeners servicing a database. Enable this feature by setting the LOAD_BALANCE parameter in TNSNAMES.ORA to ON.

Client Load Balancing is not the same as "Parallel Query Load Balancing" which refers to dispersing server processes across instances to balance processing loads. Parallel query load balancing is described in more detail under the heading "Parallel Execution Load Balancing".

Connection Load Balancing

Connection load balancing attempts to evenly distribute client connections among all available nodes, instances, and their dispatchers. The distribution of connections is based on each node's processing load and the number of active connections on each instance and on each dispatcher. Connection load balancing is automatically enabled when you configure the multi-threaded server.


This feature is only available when you enable the Multi-threaded Server.  

Parallel Execution Load Balancing

Parallel execution load balancing feature spreads server processes across instances to balance loads. This improves load balances for parallel execution and PDML operations on multiple instances.

Although you cannot tune this particular aspect of the automated degree of parallelism, you can adjust the database scheduler values to influence the load balancing algorithm of automated parallel query.

On MPP systems, Oracle first populates affinity nodes before populating non-affinity nodes. Generally, affinity nodes have loads that are about 10-15 percent higher than non-affinity nodes.

The load balancing feature uses your vendor-specific cluster manager software to communicate among instances.

See Also:

For more information about degree of parallelism and its use, refer to Oracle8i Tuning.  

Managed Standby and Standby Databases

You can protect OPS systems against disasters by using standby databases. To simplify the adminstration of standby databases, consider using the Managed Standby feature as described in the Oracle8i Backup and Recovery Guide.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.