5 Administering Database Instances and Cluster Databases

This chapter describes how to administer Oracle Real Application Clusters (Oracle RAC) database instances and Oracle RAC databases. This chapter explains the startup and shutdown tasks for database components and well as how to administer parameters and parameter files in Oracle RAC. The topics in this chapter are:

Overview of Oracle Real Application Clusters Management Tools

The following section introduces the three tools that you will most likely use to manage Oracle RAC databases and Oracle RAC instances, namely, Oracle Enterprise Manager, SQL*Plus, and the SRVCTL utility. In many cases, you use these tools the same way to manage Oracle RAC environments as you would use them manage single-instance Oracle databases. The following sections identify the differences when managing an Oracle RAC database:

Overview of Administering Oracle Real Application Clusters with Enterprise Manager

Use the Web-based Enterprise Manager Database Control to manage a single Oracle RAC database. The Enterprise Manager Console provides a central point of control for the Oracle environment through a graphical user interface (GUI). You can use the Enterprise Manager Console to initiate a variety of cluster database management tasks. Use Enterprise Manager Grid Control to administer multiple Oracle RAC databases.

Enterprise Manager enables you to start, stop, and monitor databases, cluster database instances, and their Listeners, as well as to schedule jobs or set up alert thresholds for metrics. You can perform these tasks simultaneously on multiple cluster databases. You can also use the Console to manage schemas, security, and cluster database storage features.

See Also:

Oracle Enterprise Manager Concepts for more information about administering Oracle RAC with Enterprise Manager

Overview of Administering Oracle Real Application Clusters with SQL*Plus

SQL*Plus commands operate on the current instance. The current instance can be either the local default instance on which you initiated your SQL*Plus session, or it can be a remote instance to which you connect with Net Services.

Because, by default, the SQL*Plus prompt does not identify the current instance, you should direct your commands to the correct instance. Starting a SQL*Plus session and connecting to the database without specifying an instance directs all SQL*Plus commands to the local instance. In this case, the default instance is also the current instance.

To connect to a different instance in SQL*Plus, issue a new CONNECT command specify a remote instance net service name as in the following example:

CONNECT user name/password@net_service_name


You may wish to change the SQL*Plus prompt so that it includes the name of the current instance. To do this you can issue a SQL*Plus command such as the following:

This command replaces the "SQL" string in front of the greater than symbol (>) with the user variable _CONNECT_IDENTIFIER that will display the current instance name for the duration of your current session.

To change the prompt for all sessions automatically, add an entry similar to the following entry in your glogin.sql file, found in the SQL*Plus administrative directory:


You may include any other required text or SQL*Plus user variable between the single quotes in the command.

Connecting as SYSOPER or SYSDBA enables you to perform privileged operations, such as instance startup and shutdown. Multiple SQL*Plus sessions can connect to the same instance at the same time. SQL*Plus automatically disconnects you from the first instance whenever you connect to another one.

See Also:

Overview of Administering Oracle Real Application Clusters with SRVCTL

The SRVCTL tool manages configuration information that is used by several other Oracle tools. For example, Enterprise Manager uses the configuration information that SRVCTL generates to discover and monitor nodes in your cluster.

When you use SRVCTL to perform configuration operations on your cluster, SRVCTL stores configuration data in the Oracle Cluster Registry (OCR). SRVCTL performs other operations, such as starting and stopping instances, by calling SQL*Plus on each node.

Starting and Stopping Instances and Oracle Real Application Clusters Databases

You can start up and shut down instances with Enterprise Manager, SQL*Plus or SRVCTL as described in the following sections. Both Enterprise Manager and SRVCTL provide options to startup and shutdown all of the instances in an Oracle RAC database with a single step.

You can only perform certain operations when the database is in a NOMOUNT or MOUNT state. Performing other operations requires that the database be OPEN. In addition, some operations require that only one instance be in the required state, while other operations require that all of the instances be in an identical state.

The procedures in this section assume that you are using a server parameter file (SPFILE) and are described in the following topics:

Before you can start an Oracle RAC instance, your clusterware and any required operating system-specific processes must be running. For more information about these processes, see your operating system documentation.

The procedure for shutting down Oracle RAC instances is identical to shutting down instances in single-instance Oracle, with the exceptions described here. Refer to the Oracle Database Administrator's Guide for more information about shutting down Oracle databases.

  • In Oracle RAC, shutting down one instance does not interfere with the operation of other running instances.

  • To shut down an Oracle RAC database completely, shut down every instance that has the database open or mounted.

  • After a NORMAL or IMMEDIATE shutdown, instance recovery is not required. Recovery is required, however, after you issue the SHUTDOWN ABORT command or after an instance terminates abnormally. The 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 needing it.

  • The SHUTDOWN TRANSACTIONAL command with the LOCAL option is useful to shutdown an instance after all active transactions on the instance have either committed or rolled back. This is in addition to what this command does for SHUTDOWN IMMEDIATE. Transactions on other instances do not block this operation. If you omit the LOCAL option, then this operation waits until transactions on all other instances that started before the shutdown was issued either commit or rollback.

Starting Up and Shutting Down with Enterprise Manager

You can shut down all instances or any identified instance from the Cluster Database Startup and Shutdown page. To start up or shut down a cluster database instance:

  1. Go to the Cluster Database Home page.

  2. Click the appropriate Startup or Shutdown button for your situation. If the system is down, a Startup button appears. If the system is up, a Shutdown button appears. A Specify Host and Target Database Credentials page appears.

  3. Enter valid user names and passwords for host and database credentials to change the status of the database, then click OK. To change the status of the database, you must log in to the database as either SYSDBA or SYSOPER.

To start or shut down a cluster database, that is, all of the instances known to Enterprise Manager:

  1. Go to the Cluster Database Home page.

  2. Click Startup/Shutdown. The Specify Credentials page appears.

  3. Enter a valid User Name and Password for the cluster that hosts the cluster database, then click Continue. You must be a member of the OSDBA group.

Starting Up and Shutting Down with SQL*Plus

If you want to start or stop just one instance and you are connected to your local node, you should first ensure that your current environment includes the SID for the local instance. Note that any subsequent commands in your session, whether inside or outside a SQL*Plus session, will be associated with that same SID.

To start or shutdown your local instance, initiate a SQL*Plus session and connect with the SYSDBA or SYSOPER privilege and then issue the required command. For example to start and mount an instance on your local node, run the following commands within your SQL*Plus session:


You can start more than one instance from a single SQL*Plus session on one node by way of Oracle Net Services. To achieve this, you must connect to each instance in turn by using a Net Services connection string, typically an instance-specific alias from your TNSNAMES.ORA file.


To ensure that you connect to the correct instance, you must use an alias in the connect string that is associated with just one instance. If you use an alias to a service or with multiple addresses, you may not be connected to your intended instance.

For example, you can use a SQL*Plus session on a local node to perform a transactional shutdown for two instances on remote nodes by connecting to each in turn using the instance's individual alias name. Assume the alias name for the first instance is db1 and that the alias for the second instance is db2. Connect to the first instance and shut it down as follows:


Then connect to and shutdown the second instance by entering the following from you SQL*Plus session:


Other startup and shut down keywords, such as NOMOUNT, MOUNT, IMMEDIATE, and so on, are described in the SQL*Plus User's Guide and Reference

It is not possible to start up or shut down more than one instance at a time in SQL*Plus, so you cannot start or stop all of the instances for a cluster database with a single SQL*Plus command. You may wish to create a script that will connect to each instance in turn and start it up and shut it down. However, you will need to maintain this script manually if you add or drop instances.

Intermittent Windows Shutdown Issue in Oracle RAC Environments

In an Oracle Real Application Clusters release environment on Windows, a normal Windows shutdown may cause errors that prevent the Windows shutdown from completing. As a result, you may need to perform a power reset. The following steps are recommended to avoid this during Windows shutdowns. Before shutting down or restarting any Oracle cluster node, perform a graceful shutdown of all registered Oracle Clusterware resources on the affected cluster node. Do this by using SRVCTL commands to shutdown:

  • All services on the node.

  • All database instances on the node.

  • All ASM instances on the node.

  • All node applications on the node.

Lengthy Startup of OracleDBConsole and OracleCRService on Windows

After a cluster node restart, the node may not be fully responsive for some period of time. During this time, Oracle is attempting to restart the process OracleDBConsolesid and the OracleCRService resource. Eventually, all of the resource startup operations will complete and the computer will operate normally.

Starting Up and Shutting Down with SRVCTL

Enter the following SRVCTL syntax from the command line, providing the required database name and instance name, or include more than one instance name to start more than one specific instance:

srvctl start instance -d db_name -i "inst_name_list" [-o start_options] [-c connect_str | -q]

Note that this command will also start all enabled and non-running services that have the listed instances either as preferred or available instances.

To stop one or more instances, enter the following SRVCTL syntax from the command line:

srvctl stop instance -d name -i "inst_name_list" [-o stop_options] [-c connect_str | -q]

This command will also stop the services related to the terminated instances on the nodes where the instances were running. As an example, the following command provides its own connection information to shut down the two instances, orcl3 and orcl4, using the IMMEDIATE option:

srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediate -c "sysback/oracle as sysoper"

To start or stop your entire cluster database, that is, all of the instances and its enabled services, enter the following SRVCTL commands:

srvctl start database -d name [-o start_options] [-c connect_str | -q]
srvctl stop database -d name [-o stop_options] [-c connect_str | -q]

The following SRVCTL command, for example, mounts all of the non-running instances of an Oracle RAC database using the default connection information:

srvctl start database -d orcl -o mount

See Also:

Appendix E, " Server Control Utility Reference" for information about SRVCTL options and information about other administrative tasks that you can perform with SRVCTL

Customizing How Oracle Clusterware Manages Oracle RAC Databases

By default, Oracle Clusterware controls database restarts in Oracle RAC environments. In some cases, you may need to minimize the level of control that Oracle Clusterware has over your Oracle RAC database. You may need to do this, for example, during database migration or maintenance.

To prevent Oracle Clusterware from restarting your Oracle RAC database when you restart your system, or to avoid restarting failed instances more than once, configure a policy to define the degree of control. There are two policies, automatic, which is the default, and manual.

To use this feature, configure one of the pre-defined policies to define the degree of control. There are two policy types: automatic, which is the default, and manual. The manual policy, which minimizes the database instance protection level, is an override of the automatic policy.

This procedure enables you to configure your system so that either Oracle Clusterware automatically restarts your Oracle RAC database when you restart your system, or you manually restart your Oracle RAC database. You can also use this procedure to configure your system to prevent Oracle Clusterware from auto-restarting failed database instances more than once.

Switching Between the Automatic and Manual Policies

Use SRVCTL commands to display and change these Oracle Clusterware policies. For example, use the following command syntax to display the current policy where database_name is the name of the database for which you want to change policies:

srvctl config database -d database_name -a

Use the following SRVCTL command syntax to change the current policy to another policy where policy_name is the name of the new policy for the database that is identified by database_name:

srvctl modify database d database_name -y policy_name

This command syntax changes the resource profile values for each applicable resource and sets the Current Policy OCR key to the new value. When you add a new database using the srvctl command, you can use the -y option to specify the policy as in the following example where database_name is the name of the database and policy_name is the name of the policy:

srvctl add database -d database_name -y policy_name

This command syntax places the new database under the control of Oracle Clusterware. If you do not provide a new option, then Oracle uses the default value of automatic. After you change the policy, the OCR records the new value for the affected database.

See Also:

Chapter 14, "Making Applications Highly Available Using Oracle Clusterware" for more information about using Oracle Clusterware and Appendix E, " Server Control Utility Reference" for more information about SRVCTL commands

Overview of Initialization Parameter Files in Oracle Real Application Clusters

When you create the database, Oracle creates an SPFILE in the file location that you specify. This location can be an ASM disk group, cluster file system file, or a shared raw device. If you manually create your database, then Oracle recommends that you create an SPFILE from an initialization parameter file (PFILE).

All instances in the cluster database use the same SPFILE at startup. Because the SPFILE is a binary file, do not directly edit the SPFILE with an editor. Instead, change SPFILE parameter settings using Enterprise Manager or ALTER SYSTEM SQL statements.

Oracle RAC uses a traditional PFILE only if an SPFILE does not exist or if you specify PFILE in your STARTUP command. Oracle recommends that you use SPFILE file to simplify administration, maintain parameter setting consistency, and to guarantee parameter setting persistence across database shutdown and startup events. In addition, you can configure RMAN to back up your SPFILE.

Setting Server Parameter File Parameter Values for Oracle Real Application Clusters

You can alter SPFILE settings with Enterprise Manager or by using the SET clause of the ALTER SYSTEM statement. The examples in this section appear in ASCII text although the SPFILE is a binary file. Assume that you start an instance with an SPFILE containing the following entries:



The value before the dot in an SPFILE entry identifies the instance to which the particular parameter value belongs. When an asterisk precedes the dot, the value is applied to all instances that do not have a subsequent, individual value listed in the SPFILE.

For the instance with the Oracle system identifier (sid) prod1, the OPEN_CURSORS parameter is set to 1000 even though it has a database-wide setting of 500. Parameter file entries that have the asterisk (*) wildcard character only affect the instances without an instance-specific entry. This gives you control over parameter settings for instance prod1. These two types of settings can appear in any order in the parameter file.

If another DBA runs the following statement, then Oracle updates the setting on all instances except the instance with sid prod1:


Then if you run the following statement on another instance, the instance with sid prod1 also assumes the new setting of 2000:


In the following example, the server parameter file contains these entries:


Running the following statement 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 statement:

alter system reset open_cursors scope=spfile sid='prod1';


Modifying SPFILEs with anything except Enterprise Manager or SQL*Plus can corrupt the file and prevent database startup. To repair the file, you might need to create the PFILE and regenerate the SPFILE.

Parameter File Search Order in Oracle Real Application Clusters

Oracle searches for your parameter file in a particular order depending on your platform. On UNIX-based platforms, Oracle examines directories in the following order:

  1. $ORACLE_HOME/dbs/spfilesid.ora

  2. $ORACLE_HOME/dbs/spfile.ora

  3. $ORACLE_HOME/dbs/initsid.ora

The search order on Windows-based platforms is:

  1. %ORACLE_HOME%\database\spfilesid.ora

  2. %ORACLE_HOME%\database\spfile.ora

  3. %ORACLE_HOME%\database\initsid.ora

Initialization Parameter Use in Oracle Real Application Clusters

By default, Oracle sets most parameters to a default value and this value is the same across all instances. However, many initialization parameters can also have different values on different instances as described in the Oracle Database Reference. Other parameters must either be unique or identical as described in the following sections.

Parameters that Must Have Identical Settings 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 an Oracle RAC database. Specify these parameter values in the SPFILE, or within the individual PFILEs for each instance. The following list contains the parameters must be identical on every instance:














The setting for DML_LOCKS must be identical on every instance only if set to zero.

Parameters That Must Have Unique Settings on All Instances

If you use the THREAD or ROLLBACK_SEGMENTS parameters, then Oracle recommends setting unique values for them by using the sid identifier in the SPFILE. 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 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. When you specify UNDO_TABLESPACE with automatic undo management enabled, then set this parameter to a unique undo tablespace name for each instance.

Parameters that Should Have Identical Settings on All Instances

Oracle recommends that you set the values for the following parameters to the same value on all instances. Although you can have different settings for these parameters on different instances, setting each parameter to the same value on all instances simplifies administration:


    Different values for instances in your Oracle RAC database are likely to increase overhead because of additional automatic synchronization performed by the database processing.

    When using Streams with your Oracle RAC database, the value should be greater than zero.


    This parameter determines a database-wide limit on the number of users defined in the database and it is useful to have the same value on all instances of your database so you can see the current value no matter which instance you are using. Setting different values may cause additional warning messages to be generated during instance startup or cause commands related to database user management to fail on some instances.


    If you do not use the same value for all your instances, then you unnecessarily complicate media recovery. The recovering instance expects the required archive log file names to have the format defined by its own value of LOG_ARCHIVE_FORMAT, regardless of which instance created the archive log files.

    Databases that support Data Guard, either to send or receive archive log files, must use the same value of LOG_ARCHIVE_FORMAT for all instances.


    If this parameter does not identify the same file to all instances, then each instance may behave differently and unpredictably in fail over, load-balancing, as well as normal operations. Additionally, a change you make to the SPFILE with an ALTER SYSTEM SET or ALTER SYSTEM RESET command is saved only in the SPFILE used by the instance where you run the command. Your change will not be reflected in instances using different SPFILEs.

    If the SPFILE values are different in instances for which the values were set by the server, then you should restart the instances that are not using the default SPFILE.


    If you want diagnostic trace information to be always available for your Oracle RAC database, you must set TRACE_ENABLED to TRUE on all of your database instances. If you trace on only some of your instances, then diagnostic information might not be available when required should the only accessible instances be those with TRACE_ENABLED set to FALSE.


    By setting different values for UNDO_RETENTION in each instance, you are likely to reduce scalability and encounter unpredictable behavior following a fail over. Therefore, you should carefully consider whether you will accrue any benefits before you assign different values for this parameter to the instances in your Oracle RAC database.

Summary of Parameter Use in Oracle Real Application Clusters Databases

This section summarizes considerations for using parameters in Oracle RAC databases.


    Enables a database to be started in cluster mode. Set this parameter to TRUE.


    Sets the number of instances in your Oracle RAC environment. A proper setting for this parameter can improve memory use. Set the CLUSTER_DATABASE_INSTANCES parameter to the same value on all instances. Otherwise, instance startup can fail. Normally, set this parameter to be equal to the number of instances in your Oracle RAC database. Alternatively, you can set this parameter to a value that is greater than the current number of instances if you are planning to add instances.


    The value for this parameter determines the maximum number of instances that you can have in your Oracle RAC database environment. If you add instances, then you may need to reset the value for this parameter to accommodate the increased number of instances.

    Specifies the cluster interconnect when there is more than one interconnect. Refer to your Oracle platform-specific documentation for the use, syntax, and behavior of this parameter.

    You typically do not need to set the CLUSTER_INTERCONNECTS parameter. For example, do not set this parameter for the following common configurations:

    • If you have only one cluster interconnect.

    • If the default cluster interconnect meets the bandwidth requirements of your Oracle RAC database, which is typically the case.

    • If your database is running on a Sun Solaris cluster. If you do set the parameter, its value must be the same on all instances.

    Oracle uses information from CLUSTER_INTERCONNECTS to distribute interconnect traffic among the various network interfaces if you specify more than one interconnect with this parameter. Note that the specified configuration inherits any limitations of the listed interconnects and the associated operating system IPC services, such as availability. Consider setting CLUSTER_INTERCONNECTS when a single cluster interconnect cannot meet your bandwidth requirements. You may need to set this parameter in data warehouse environments with high interconnect bandwidth demands from one or more databases as described here.

    For example, if you have two databases with high interconnect bandwidth requirements, then you can override the default interconnect provided by your operating system and nominate a different interconnect for each database using the following syntax in each server parameter file where ipn is an IP address in standard dot-decimal format, for example:

    Database One: CLUSTER_INTERCONNECTS = ip1
    Database Two: CLUSTER_INTERCONNECTS = ip2

    If you have one database with high bandwidth demands, then you can nominate multiple interconnects using the following syntax:

    CLUSTER_INTERCONNECTS = ip1:ip2:...:ipn

    If you set multiple values for CLUSTER_INTERCONNECTS as in the preceding example, then Oracle uses all of the interconnects that you specify. This provides load balancing as long as all of the listed interconnects remain operational. You must use identical values, including the order in which the interconnects are listed, on all instances of your database when defining multiple interconnects with this parameter.

    If an operating system error occurs while Oracle is writing to the interconnect that you specify with CLUSTER_INTERCONNECTS, then Oracle returns an error even if some other interfaces are available. This is because the communication protocols between Oracle and the interconnect can vary greatly depending on your platform. Refer to your Oracle platform-specific documentation for more information.


    Setting this parameter does not provide interconnect failover. Because interconnect failover is operating-system dependent, refer to your operating system documentation for information about configuring interconnect failover.

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


    Set the DISPATCHERS parameter to enable a shared server configuration, that is a server that is configured to enable many user processes to share very few server processes. With shared server configurations, many user processes connect to a dispatcher. The 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 process generates a listening end point. LISTENER specifies an alias name for the Oracle Net Services Listeners. Set the alias to a name that is resolved through a naming method such as a tnsnames.ora file. The tnsnames.ora file contains net service names. Clients, nodes, and the Oracle Performance Manager node need this file. Enterprise manager does not require tnsnames.ora entries on the client for Database Control or Grid Control. Refer to Oracle Database Net Services Administrator's Guide for complete information about configuring the DISPATCHERS parameter and its attributes and for configuring the shared server.


    This parameter is normally set to a different value for each instance to provide a simple method for clients to connect directly to a specific instance. However, you may use the same value for multiple instances. The value of the INSTANCE_NAME parameter is usually the same as the value of the SID for the instance, to which it defaults, and this association of the two names may help reduce confusion when managing complex systems.


    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.


    When you use an SPFILE, all Oracle RAC database instances must use the SPFILE and the file must be on shared storage. Entries in the SERVICE_NAMES parameter may be used by client connections rather than the INSTANCE_NAME parameter value. The SERVICE_NAMES parameter may include one or more names and different instances may share one or more names with other instances. This enables a client to connect to either a specific instance or to any one of a set of instances, depending on the service name chosen in the connection string.


    If you use services as described in Chapter 6, "Introduction to Workload Management", then do not set values for the SERVICE_NAMES parameter. The tools that create and manage services for workload management automatically set values this parameter.

    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.


    When you use an SPFILE, all Oracle RAC database instances must use the SPFILE and the file must be on shared storage.


    If specified, this parameter must have unique values on all instances. The THREAD parameter specifies the number of the redo thread to be used by an instance. You can specify any available redo thread number as long as that thread number is enabled and is not used.


    TRANSACTIONS specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.

Backing Up the Server Parameter File

Oracle recommends that you regularly back up the server parameter file for recovery purposes. Do this using the CREATE PFILE statement. For example:

CREATE PFILE='?/dbs/initdbname.ora'
FROM SPFILE='/dev/vx/rdsk/oracle_dg/dbspfile'

You can use RMAN (Recovery Manager) to create backups of the server parameter file. You can also recover an SPFILE by starting an instance using a client-side initialization parameter file. Then re-create the server parameter file using the CREATE SPFILE statement. Note that if the parameter file that you use for this operation was for a single instance, then the parameter file will not contain instance-specific values, even those that must be unique in Oracle RAC instances. Therefore, ensure that your parameter file contains the appropriate settings as described earlier in this chapter.

To ensure that your SPFILE (and control files) are automatically backed up by RMAN during typical backup operations, use Enterprise Manager or the RMAN CONTROLFILE AUTOBACKUP statement to enable the RMAN autobackup feature

See Also: