Skip Headers
Oracle® Real Application Clusters Administration and Deployment Guide
11g Release 1 (11.1)

Part Number B28254-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Administering Database Instances and Cluster Databases

This chapter describes how to administer Oracle Real Application Clusters (Oracle RAC) database instances and Oracle RAC databases.

The topics in this chapter include:

See Also:

Oracle Enterprise Manager Concepts and the Enterprise Manager online help for more information about Enterprise Manager

Tools for Administering Oracle Real Application Clusters

The following sections introduce Oracle RAC administration using the three tools that you will most likely use to manage Oracle RAC databases and instances: 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:

Administering Oracle Real Application Clusters with Enterprise Manager

Oracle Enterprise Manager provides a central point of control for the Oracle RAC environment, allowing you to perform administrative tasks simultaneously on multiple cluster databases. It has both the Database Control and Grid Control graphical user interfaces (GUIs) for managing single-instance and Oracle RAC environments. Because there is one Enterprise Manager Agent on each node of an Oracle RAC database, for Database Control you can use any URL for that database to administer it with Enterprise Manager.

In Enterprise Manager, Oracle RAC-specific administrative tasks generally focus on two levels: tasks that affect an entire cluster database and tasks that affect specific instances. For example, you can use Enterprise Manager 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. Or you can perform instance-specific commands such as setting parameters or creating resource plans. You can also use the Console to manage schemas, security, and cluster database storage features.

See Also:

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 Oracle 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 and specify a remote instance net service name, as shown in the following example:

CONNECT user_name/password@net_service_name

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.

Note:

Use the SYSASM privilege instead of the SYSDBA privilege to connect to and administer an ASM instance. If you use the SYSDBA privilege to connect to an ASM instance, then Oracle Database writes warnings to the alert log files because commands that run using the SYSDBA privilege on an ASM instance are deprecated.

See the Oracle Database Storage Administrator's Guide for more information.

See Also:

Changing the SQL*Plus Prompt

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:

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

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:

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

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

How SQL*Plus Commands Affect Instances

Most SQL statements affect the current instance. You can use SQL*Plus to start and stop instances in the Oracle RAC database. You do not need to run SQL*Plus commands as root on Linux and UNIX systems or as Administrator on Windows systems. You need only the proper database account with the privileges that you normally use for a single-instance Oracle database. Some examples of how SQL*Plus commands affect instances are:

  • ALTER SYSTEM CHECKPOINT LOCAL affects only the instance to which you are currently connected, rather than the default instance or all instances.

  • ALTER SYSTEM CHECKPOINT or ALTER SYSTEM CHECKPOINT GLOBAL affects all instances in the cluster database.

  • ALTER SYSTEM SWITCH LOGFILE affects only the current instance.

    • To force a global log switch, use the ALTER SYSTEM ARCHIVE LOG CURRENT statement.

    • The INSTANCE option of ALTER SYSTEM ARCHIVE LOG enables you to archive each online redo log file for a specific instance.

Table 3-1 describes how SQL*Plus commands affect instances.

Table 3-1 How SQL*Plus Commands Affect Instances

SQL*Plus Command Associated Instance

ARCHIVE LOG

Always affects the current instance.

CONNECT

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

HOST

Affects the node running the SQL*Plus session, regardless of the location of the current and default instances.

RECOVER

Does not affect any particular instance, but rather the database.

SHOW INSTANCE

Displays information about the current instance, which can be different from the default local instance if you have redirected your commands to a remote instance.

SHOW PARAMETER and SHOW SGA

Displays parameter and SGA information from the current instance.

STARTUP and SHUTDOWN

Always affects the current instance. These are privileged SQL*Plus commands.


Administering Oracle Real Application Clusters with SRVCTL

The Server Control (SRVCTL) tool is a command-line interface that you can use to manage an Oracle RAC database from a single point. You can use SRVCTL to start and stop the database and instances, and to delete or move instances and services. You can also use SRVCTL to add services and manage configuration information. You use SRVCTL to start and stop a group of programs that includes virtual IP addresses, listeners, Oracle Notification Services, node-level applications.

The SRVCTL tool also 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 the following sections assume that you are using a server parameter file (SPFILE):

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.

Note:

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

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

Starting and Stopping with Enterprise Manager

See the Oracle Database 2 Day + Real Application Clusters Guide for step-by-step instructions to start up or shut down a cluster database instance or a cluster database.

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 in your SQL*Plus session:

CONNECT / AS SYSDBA 
   STARTUP MOUNT

Note:

If you use ASM disk groups, use the SYSASM privilege instead of the SYSDBA privilege to connect to and administer the ASM instances. See the Oracle Database Storage Administrator's Guide for more information.

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.

Note:

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:

CONNECT /@db1 AS SYSDBA
   SHUTDOWN TRANSACTIONAL

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

CONNECT /@db2 AS SYSDBA 
   SHUTDOWN TRANSACTIONAL

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 want to create a script that will connect to each instance in turn and start it up and shut it down. However, you must maintain this script manually if you add or drop instances.

See Also:

SQL*Plus User's Guide and Referencefor information about other startup and shut down keywords, such as NOMOUNT, MOUNT, IMMEDIATE, and so on

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]

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]

This command also stops 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

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]
srvctl stop database -d name [-o stop_options]

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 A, " Server Control Utility Reference" for information about SRVCTL options and information about other administrative tasks that you can perform with SRVCTL

Verifying That Instances are Running

To verify that instances are running, on any node from a SQL*Plus prompt enter:

CONNECT SYS/password as SYSDBA
SELECT * FROM V$ACTIVE_INSTANCES;

This query returns output similar to the following:

INST_NUMBER INST_NAME          
-----------  ----------------- 
           1 db1-sun:db1  
           2 db2-sun:db2  
           3 db3-sun:db3  

The output columns for this example are shown in Table 3-2.

Table 3-2 Descriptions of V$ACTIVE_INSTANCES Columns

Column Description

INST_NUMBER

Identifies the instance number.

INST_NAME

Identifies the host name and instance name as host_name:instance_name.


Terminating Sessions On a Specific Cluster Instance

You can use the ALTER SYSTEM KILL SESSION statement to terminate a session on a specific instance. When a session is terminated, any active transactions of the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

Using this statement enables you to maintain strict application service-level agreements in Oracle RAC environments. Often, the goal of a service-level agreement is to execute a transaction in a specified time limit. In an Oracle RAC environment, this may require terminating a transaction on an instance and retrying the transaction on another instance within a specified time frame.

To terminate sessions, follow these steps:

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

The following examples provide a three scenarios in which a user identifies and terminates a specific session.


Example 1   Identify and terminate the session on an busy instance

In this example, assume that the executing session is SYSDBA on the instance INST_ID=1. The SYSDBA first queries the GV$SESSION view for the SCOTT user's session to identify the session to terminate, and then issues the ALTER SYSTEM KILL SESSION statement to terminate the session on the instance INST_ID=2. The ORA-00031 message is returned because some activity must be completed before the session can be terminated.

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';
 
       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          4          2
 
SQL> ALTER SYSTEM KILL SESSION '80, 4, @2';
alter system kill session '80, 4, @2'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>

Example 2   Identify and terminate the session on an idle instance

In this example, assume that the executing session is SYSDBA on the instance INST_ID=1. The session on instance INST_ID=2 is terminated immediately when Oracle Database executes the statement within 60 seconds.

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';
 
       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          6          2
 
SQL> ALTER SYSTEM KILL SESSION '80, 6, @2';
 
System altered.
 
SQL> 

Example 3   Using the IMMEDIATE parameter

The following example include the optional IMMEDIATE clause to immediately terminate the session without waiting for outstanding activity to complete.

SQL> SELECT SID, SERIAL#, INST_ID FROM GV$SESSION WHERE USERNAME='SCOTT';
 
       SID    SERIAL#    INST_ID
---------- ---------- ----------
        80          8          2
 
SQL> ALTER SYSTEM KILL SESSION '80, 8, @2' IMMEDIATE;
 
System altered.
 
SQL> 

See Also:

Oracle Database Administrator's Guide for more information about terminating sessions

Overview of Initialization Parameter Files in Oracle Real Application Clusters

When you create the database, Oracle Database creates an SPFILE in the file location that you specify. This location can be an ASM disk group, a cluster file system, 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).

Note:

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

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.

If you include the FROM MEMORY clause (for example, CREATE PFILE FROM MEMORY or CREATE SPFILE FROM MEMORY), the CREATE statement creates a PFILE or SPFILE using the current systemwide parameter settings. In an Oracle RAC environment, the created file contains the parameter settings from each instance. Because the FROM MEMORY clause requires all other instances to send their parameter settings to the instance that is trying to create the parameter file, the total execution time depends on the number of instances, the number of parameter settings on each instance, and the amount of data for these settings.

Setting SPFILE 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.

Note:

Modifying the SPFILE using tools other than Enterprise Manager or SQL*Plus can corrupt the file and prevent database startup. To repair the file, you might need to create a PFILE and then regenerate the SPFILE.

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:

*.OPEN_CURSORS=500
prod1.OPEN_CURSORS=1000

Note:

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:

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

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

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

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

prod1.OPEN_CURSORS=1000
*.OPEN_CURSORS=500

Issue the following statement to make Oracle disregard the first entry from the server parameter file:

ALTER SYSTEM RESET SCOPE=SPFILE;

Issue the following statement to reset a parameter to its default value for instance prod1 only:

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE SID='prod1';

Parameter File Search Order in Oracle Real Application Clusters

Oracle searches for your parameter file in a particular order depending on your platform.

On Linux and UNIX platforms, the search order is as follows:

  1. $ORACLE_HOME/dbs/spfilesid.ora

  2. $ORACLE_HOME/dbs/spfile.ora

  3. $ORACLE_HOME/dbs/initsid.ora

On Windows platforms, the search order is as follows:

  1. %ORACLE_HOME%\database\spfilesid.ora

  2. %ORACLE_HOME%\database\spfile.ora

  3. %ORACLE_HOME%\database\initsid.ora

Backing Up the Server Parameter File

Oracle recommends that you regularly back up the server parameter file for recovery purposes. Do this using Enterprise Manager (as described in the Oracle Database 2 Day + Real Application Clusters Guide) or use the CREATE PFILE statement. For example:

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

You can use Recovery Manager (RMAN) 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:

Oracle Database 2 Day + Real Application Clusters Guide to perform backup jobs using Enterprise Manager, and the Oracle Database SQL Language Reference for more information about the CREATE SPFILE statement

Initialization Parameter Use in Real Application Clusters

By default, most parameters are set 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 Table 3-3. Other parameters must either be unique or identical as described in the following sections.

Table 3-3 summarizes the initialization parameters used specifically for Oracle RAC databases. See Oracle Database Reference for additional information about these and other initialization parameters.

Table 3-3 Initialization Parameters Specific to Oracle Real Application Clusters

Parameter Description

ASM_PREFERRED_READ_FAILURE_GROUPS

Specifies a set of disks to be the preferred disks from which to read mirror data copies. The values you set for this parameter are instance specific and need not be the same on all instances.

CLUSTER_DATABASE

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

CLUSTER_DATABASE_INSTANCES

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.

Note: 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.

CLUSTER_INTERCONNECTS

Specifies an alternative cluster interconnect for the private network when there is more than one interconnect. The CLUSTER_INTERCONNECTS initialization parameter is used only in Linux and UNIX environments where UDP IPC is enabled.

Notes:

  • Oracle recommends that all Oracle databases and Oracle Clusterware use the same interconnect network. If an Oracle database runs on a separate network from Oracle Clusterware, then the timeout on error is 900s.

  • Oracle does not recommend setting the CLUSTER_INTERCONNECTS parameter except in certain situations. See "Administering Multiple Cluster Interconnects on Linux and UNIX Platforms" for more details.

DB_NAME

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

DISPATCHERS

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. See Oracle Database Net Services Administrator's Guide for complete information about configuring the DISPATCHERS parameter and its attributes and for configuring the shared server.

GCS_SERVER_PROCESSES

This static parameter specifies the initial number of server processes for an Oracle RAC instance's Global Cache Service (GCS). The GCS processes manage the routing of interinstance traffic among Oracle RAC instances. The default number of GCS server processes is calculated based on system resources with a minimum setting of 2. For systems with one CPU, there is one GCS server process. For systems with two to eight CPUs, there are two GCS server processes. For systems with more than eight CPUs, the number of GCS server processes will be equal to the number of CPUs divided by 4, dropping any fractions. For example, if you have 10 CPUs, then 10 divided by 4 means that your system has 2 GCS processes. You can set this parameter to different values on different instances.

INSTANCE_NAME

Specifies the number of the redo log file groups to be used by an instance. See Redo Log File Storage in Oracle Real Application Clusters for more information.

RESULT_CACHE_MAX_SIZE

In a clustered database, you can either set RESULT_CACHE_MAX_SIZE=0 on every instance to disable the result cache, or use a nonzero value on every instance to enable the result cache. To switch between enabled and disabled result cache requires that you restart every instance:

  • Enabling the result cache: Set RESULT_CACHE_MAX_SIZE to a value greater than 0, or leave the parameter unset. You can size the cache differently on individual instances.

  • Disabling the result cache: Set RESULT_CACHE_MAX_SIZE=0 on all instances to disable the result cache. If you set RESULT_CACHE_MAX_SIZE=0 upon startup of any one instance, then you must set the parameter to zero on all instance startups because disabling the result cache must done clusterwide. Disabling the result cache on some instances may lead to incorrect results.

If you do not set the RESULT_CACHE_MAX_SIZE parameter, the parameter resolves to a default, nonzero value.

SERVICE_NAMES

When you use services, Oracle recommends that you do not set a value for the SERVICE_NAMES parameter but instead you should create cluster managed services through the Cluster Managed Services page in Enterprise Manager Database Control. This is because Oracle Clusterware controls the setting for this parameter for the services that you create and for the default database service. The service features described in Chapter 4, "Introduction to Automatic Workload Management" are not directly related to the features that Oracle provides when you set SERVICE_NAMES. In addition, setting a value for this parameter may override some of the benefits of using services.

Note: 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.

SESSIONS_PER_USER

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.

SPFILE

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

THREAD

Specifies the number of the redo threads 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. If specified, this parameter must have unique values on all instances. The best practice is to use the INSTANCE_NAME parameter to specify redo log groups.


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 in the individual PFILEs for each instance. The following list contains the parameters that must be identical on every instance:


ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT

The following parameters must be identical on every instance only if the parameter value is set to zero:

  • DML_LOCKS

  • RESULT_CACHE_MAX_SIZE

Parameters That Have Unique Settings on All Instances

If you use the ROLLBACK_SEGMENTS parameters, then Oracle recommends setting unique values for it 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 INSTANCE_NAME parameter to assign redo log groups to specific instances. To simplify administration, use the same number for both the INSTANCE_NAME and INSTANCE_NUMBER parameters.

Specify the ORACLE_SID environment variable, which comprises the database name and the number of the INSTANCE_NAME 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.

Using the ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter, you can specify a list of preferred read failure group names. The disks in those failure groups become the preferred read disks. Thus, every node can read from its local disks. This results in higher efficiency and performance and reduced network traffic. The setting for this parameter is instance-specific, and the values need not be the same on all instances.

Parameters That Should Have Identical Settings on All Instances

Oracle recommends that you set the values for the parameters in Table 3-4 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.

Table 3-4 Parameters That Should Have Identical Settings on All Instances

Parameter Description

ARCHIVE_LAG_TARGET

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.

LICENSE_MAX_USERS

Because this parameter determines a database-wide limit on the number of users defined in the database, 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 Oracle to generate additional warning messages during instance startup, or cause commands related to database user management to fail on some instances.

LOG_ARCHIVE_FORMAT

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 archived redo log files, must use the same value of LOG_ARCHIVE_FORMAT for all instances.

SPFILE

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, and during 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.

TRACE_ENABLED

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.

UNDO_RETENTION

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.


Quiescing Oracle Real Application Clusters Databases

The procedure for quiescing Oracle RAC databases is identical to quiescing a single-instance database. You use the ALTER SYSTEM QUIESCE RESTRICTED statement from one instance. You cannot open the database from any instance while the database is in the process of being quiesced. Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement finishes, and the database is considered as in a quiesced state. In an Oracle RAC environment, this statement affects all instances, not just the one from which the statement is issued.

To successfully issue the ALTER SYSTEM QUIESCE RESTRICTED statement in an Oracle RAC environment, you must have the Database Resource Manager feature activated, and it must have been activated since instance startup for all instances in the cluster database. It is through the facilities of the Database Resource Manager that non-DBA sessions are prevented from becoming active. Also, while this statement is in effect, any attempt to change the current resource plan will be queued until after the system is unquiesced.

These conditions apply to Oracle RAC:

Note:

You cannot use the quiesced state to take a cold backup. This is because Oracle background processes may still perform updates for Oracle internal purposes even while the database is in quiesced state. In addition, the file headers of online datafiles continue to look like they are being accessed. They do not look the same as if a clean shutdown were done. You can still take online backups while the database is in a quiesced state.

See Also:

See the Oracle Database Administrator's Guide for details on the quiesce database feature and the Oracle Database SQL Language Reference for more information about the ALTER SYSTEM QUIESCE RESTRICTED syntax

Administering Multiple Cluster Interconnects on Linux and UNIX Platforms

In Oracle RAC environments that run on Linux and UNIX platforms where UDP IPC is enabled, you can use the CLUSTER_INTERCONNECTS initialization parameter to specify an alternative interconnect for the private network.

Oracle does not recommend setting the CLUSTER_INTERCONNECTS parameter, which overrides the default interconnect settings at the operating system level. Instead, the best practice is to use operating system bonding techniques (also referred to as NIC bonding). See your platform-specific Oracle Real Application Clusters installation guide for information about setting up NIC bonding at the operating system level.

This section contains the following topics:

Recommendations for Setting the CLUSTER_INTERCONNECTS Parameter

Oracle does not recommend setting the CLUSTER_INTERCONNECTS parameter.

Note:

Oracle recommends that all databases and Oracle Clusterware use the same interconnect network.

Typically, you might need to set this the CLUSTER_INTERCONNECTS parameter only in the following situations:

  • Due to operating system limitations, you cannot use Network Interface Card (NIC) bonding to provide increased bandwidth using multiple network interfaces.

  • The cluster is running multiple databases and you need the interconnect traffic to be separated.

  • You have a single IP address that is made highly available by the operating system, and it does not have a stable interface name (for example, the name can change when you restart).

Do not set the CLUSTER_INTERCONNECTS 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.

Consider the following important points when specifying the CLUSTER_INTERCONNECTS initialization parameter:

  • The CLUSTER_INTERCONNECTS initialization parameter is useful only in Linux and UNIX environments where UDP IPC is enabled.

  • Specify a different value for each instance of the Oracle RAC database when setting the CLUSTER_INTERCONNECTS initialization parameter in the parameter file.

  • The IP addresses you specify for the different instances of the same database on different nodes must belong to network adapters that connect to the same interconnect network.

  • The CLUSTER_INTERCONNECTS initialization parameter requires an IP address. It enables you to specify multiple IP addresses, separated by colons. Oracle RAC network traffic is distributed between the specified IP addresses.

  • If you specify multiple IP addresses for this parameter, then list them in the same order for all instances of the same database. For example, if the parameter for instance 1 on node 1 lists the IP addresses of the alt0:, fta0:, and ics0: devices in that order, then the parameter for instance 2 on node 2 must list the IP addresses of the equivalent network adapters in the same order. See the examples in "Usage Examples for the CLUSTER_INTERCONNECTS Parameter" for more information about setting multiple interconnects with this parameter.

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

See Also:

Oracle Database Reference for more information about the CLUSTER_INTERCONNECTS initialization parameter

Usage Examples for the CLUSTER_INTERCONNECTS Parameter

This section provides two examples for setting the CLUSTER_INTERCONNECTS parameter.


Example 1   

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: 144.25.16.214:

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

Example 2   

To use the network interface whose IP address is 129.34.137.212 for all GCS, GES, and IPQ IPC traffic, set the CLUSTER_INTERCONNECTS parameter as follows:

CLUSTER_INTERCONNECTS=129.34.137.212

Use the ifconfig or netstat command to display the IP address of a device. This command provides a map between device names and IP addresses. For example, to determine the IP address of a device, run the following command as the root user:

# /usr/sbin/ifconfig -a 
fta0: flags=c63<UP,BROADCAST,NOTRAILERS,RUNNING,MULTICAST,SIMPLEX> 
      inet 129.34.137.212 netmask fffffc00 broadcast 129.34.139.255 ipmtu 1500

lo0:  flags=100c89<UP,LOOPBACK,NOARP,MULTICAST,SIMPLEX,NOCHECKSUM> 
      inet 127.0.0.1 netmask ff000000 ipmtu 4096 

ics0:  flags=1100063<UP,BROADCAST,NOTRAILERS,RUNNING,NOCHECKSUM,CLUIF> 
      inet 10.0.0.1 netmask ffffff00 broadcast 10.0.0.255 ipmtu 7000 

sl0:  flags=10<POINTOPOINT>

tun0: flags=80<NOARP>

In the preceding example, the interface fta0: has an IP address of 129.34.137.212 and the interface ics0: has an IP address of 10.0.0.1.

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 upgrades or routine maintenance.

Note:

When using third-party clusterware, Oracle recommends that you allow Oracle Database to manage the Oracle RAC instances. If you set the instance to manual and start it with third-party clusterware, do not use the third-party clusterware to monitor and restart database instances, Oracle Clusterware must do that.

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. The manual policy minimizes the database instance protection level and overrides the automatic policy.

These policies enable 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.

Use SRVCTL commands to display and change the Oracle Clusterware policies, as shown in the following examples:


Example 1   Display the Current Policy

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

Example 2   Change the Current Policy to Another Policy

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 was identified by database_name in Example1):

srvctl modify database d management_policy -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.

Example 3   Specify a Policy for a New Database

When you add a new database using the SRVCTL command, you can use the -y option to specify the management policy, as shown in the following example where database_name is the name of the database and management_policy is the name of the policy:

srvctl add database -d database_name -y management_policy

This command syntax places the new database under the control of Oracle Clusterware. If you do not provide a new management policy 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:

Appendix A, " Server Control Utility Reference" for more information about SRVCTL commands

Advanced Oracle Enterprise Manager Administration

You can install, configure, and monitor an Oracle RAC database from a single location using either Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control.

This section provides advanced administration tasks that are not covered in Oracle Database 2 Day + Real Application Clusters Guide or in "Overview of Monitoring and Tuning Oracle Real Application Clusters Databases".

See Also:

Oracle Database 2 Day + Real Application Clusters Guide for a task-oriented guide that explains how to use Enterprise Manager to perform routine Oracle RAC database administrative tasks

This section includes the following topics:

Using Enterprise Manager Grid Control to Discover Nodes and Instances

Discovering Oracle RAC database and instance targets in Enterprise Manager enables monitoring and administration from the console:

  • Database Control does not require discovery because DBCA performs any necessary configuration while creating the database.

  • Grid Control enables you to use the Enterprise Manager console interface to discover Oracle RAC database and instance targets.

    If the Grid Control agents are installed on a cluster that already has an Oracle RAC database, Oracle RAC database targets are discovered at install time. You can use the console interface to discover targets if a database is created after agents are installed or if a database is not automatically discovered at agent install time.

To discover nodes and instances, use Enterprise Manager Grid Control as follows:

  1. Log in to Enterprise Manager and click the Targets tab.

  2. Click the Database tab to view all of the available targets. The column labeled Types shows the Oracle RAC databases using the entry "Cluster Database."

  3. Add the database target by selecting the target name, then clicking Add. The Add Database Target: Specify Host page appears, which enables you to add databases, listeners, and Automatic Storage Management (ASM) as monitored targets.

  4. Click the flashlight icon to display the available host names, select a host, then click Continue. The Add Database: Specify Source page appears.

  5. Either request Enterprise Manager to discover only single-instance databases and listeners, or to discover all cluster databases, single-instance databases, and listeners on the cluster, then click Continue.

    Enterprise Manager performs discovery to locate and display the cluster database and its associated instances. The Targets Discovered on Cluster page appears. If this procedure did not discover your reconfigured cluster database and all of its instances, you can use this page to manually configure your cluster databases and single-instance databases.

Administering Jobs and Alerts in Oracle Real Application Clusters

The Cluster Database Home page shows all of the instances in the Oracle RAC database and provides an aggregate collection of several Oracle RAC-specific statistics that are collected by the Automatic Workload Repository (AWR) for server manageability.

You do not need to navigate to an instance-specific page to see these details. However, on the Cluster Database Home page, if an instance is down that should be operating, or if an instance has a high number of alerts, then you can drill down to the instance-specific page for each alert.

To perform specific administrative tasks as described in the remainder of this section, log in to the target Oracle RAC database, navigate to the Cluster Database Home page, and click the Administration tab.

Administering Jobs in Oracle Real Application Clusters

You can administer Enterprise Manager jobs at both the database and instance levels. For example, you can create a job at the cluster database level and the job will run on any active instance of the target Oracle RAC database. Or you can create a job at the instance level and the job will only run on the specific instance for which you created it. In the event of a failure, recurring jobs can run on a surviving instance.

Because you can create jobs at the instance level, cluster level, or cluster database level, jobs can run on any available host in the cluster database. This applies to scheduled jobs as well. Enterprise Manager also displays job activity in several categories, including, Active, History, and Library.

Use the Jobs tab to submit operating system scripts and SQL scripts and to examine scheduled jobs. For example, to create a backup job for a specific Oracle RAC database:

  1. Click Targets and click the database for which you want to create the job.

  2. Log in to the target database.

  3. When Enterprise Manager displays the Database Home page, click Maintenance.

  4. Complete the Enterprise Manage Job Wizard panels to create the job.

Administering Alerts in Oracle Real Application Clusters with Enterprise Manager

You can use Enterprise Manager to configure Oracle RAC environment alerts. You can also configure special Oracle RAC database tests, such as global cache converts, consistent read requests, and so on.

Enterprise Manager distinguishes between database- and instance-level alerts in Oracle RAC environments. Alert thresholds for instance level alerts, such as archive log alerts, can be set at the instance target level. This enables you to receive alerts for the specific instance if performance exceeds your threshold. You can also configure alerts at the database level, such as setting alerts for tablespaces. This enables you to avoid receiving duplicate alerts at each instance.

See Also:

Oracle Technology Network for an example of configuring alerts in Oracle RAC and the Oracle Database PL/SQL Packages and Types Reference for information about using packages to configure thresholds

Performing Scheduled Maintenance Using Defined Blackouts in Enterprise Manager

You can define blackouts for all managed targets of an Oracle RAC database to prevent alerts from occurring while performing maintenance. You can define blackouts for an entire cluster database or for specific cluster database instances.