3 Administering Database Instances and Cluster Databases

Oracle RAC Database Administration

Prior to Oracle RAC 11g release 2 (11.2), DBAs had to hard code parameters, such as database instance number and redo threads, to allocate specific Oracle RAC database instances to nodes within the cluster. If a node in the cluster does not start, then the database instance does not start. In Oracle RAC 11g release 2 (11.2), this method of managing your Oracle RAC database continues to be available. An Oracle RAC database managed in this manner is referred to as an administrator-managed database. Administrator-managed databases include pre-11g release 2 (11.2) Oracle databases and upgraded Oracle databases. You can manage these databases using the same commands or methods (such as DBCA or Oracle Enterprise Manager) you used with previous releases of Oracle Database. All commands and utilities maintain backward compatibility.

In Oracle RAC 11g release 2 (11.2), Oracle introduces policy-managed databases to move away from any hard coding of parameters, to make it easier to replace nodes in a cluster, or expand the cluster as requirements change. Policy-managed databases must be 11g release 2 (11.2) and cannot coexist on the same servers as administrator-managed databases.

A policy-managed database is defined by cardinality, which is the number of database instances you want running during normal operations. A policy-managed database runs in one or more database server pools that are created in the cluster by the cluster administrator, and it can run on different servers at different times. A database instance starts on all servers that are in the server pools defined for the database. If you are using Oracle Automatic Storage Management (Oracle ASM) with Oracle Managed Files (OMF) for your database storage, then, when an instance starts and there is no redo thread available, Oracle RAC automatically enables one and creates the required redo log files and undo tablespace. Clients can connect to a policy-managed database using the same SCAN-based connect string no matter which servers they happen to be running on at the time.

You can convert an administrator-managed database to a policy-managed database using the srvctl modify database -d db_unique_name -g server_pool command. Conversely, you cannot directly convert a policy-managed database to an administrator-managed database. Instead, you can remove the policy-managed configuration using the srvctl remove database and srvctl remove service commands, and then create a new administrator-managed database with the srvctl add database command.

With Oracle RAC 11g release 2 (11.2), you define a database as a resource in Oracle Clusterware. The resource is automatically created when you create your database with DBCA or the resource can be manually created by adding your database with srvctl. This resource contains the Oracle home, the spfile, one or more server pools, and one or more Oracle ASM disk groups required for the database. The database resource also has a weak start dependency on the VIP, which means that the resource tries to start the VIP for the node when the database instance starts. If the VIP does not start successfully, then the instance still starts. When you review the database resource for an administrator-managed database, you will see a server pool defined with the same name as the Oracle database. This server pool is part of a special Oracle-defined server pool called Generic. Oracle RAC manages the Generic server pool to support administrator-managed databases. When you add or remove an administrator-managed database using either SRVCTL or DBCA, Oracle RAC creates or removes the server pools that are members of Generic. You cannot use SRVCTL or CRSCTL commands to modify the Generic server pool.

Services for administrator-managed databases continue to be defined by the PREFERRED and AVAILABLE definitions. For policy-managed databases, a service is defined to a database server pool and can either be uniform (running on all instances in the server pool) or singleton (running on only one instance in the server pool).

Administering Oracle RAC with Oracle 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 Oracle 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 Oracle Enterprise Manager.

In Oracle 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 Oracle Enterprise Manager to start, stop, and monitor databases, cluster database instances, and their listeners, and 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.

Administering Oracle RAC 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, where password is the password:

CONNECT user_name@net_service_name
Enter password: password

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.

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

Administering Oracle RAC with SRVCTL

The Server Control Utility (SRVCTL) 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, in addition to other resources in the 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 configuring and managing Oracle Clusterware resources, that define agents that perform database startup and shutdown operations using Oracle Call Interface APIs.

Starting and Stopping with Oracle Enterprise Manager

See the Oracle Database 2 Day + Real Application Clusters Guide for step-by-step instructions to start or stop 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, are 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

You can start multiple instances from a single SQL*Plus session on one node using Oracle Net Services. Connect to each instance in turn by using a Net Services connection string, typically an instance-specific alias from your TNSNAMES.ORA file.

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 or stop multiple instances with 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 connects 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.

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 multiple instance names to start multiple specific instances:

For administrator-managed databases, enter a comma-delimited list of instance names:

$ srvctl start instance -d db_unique_name -i instance_name_list [-o start_options]

In Windows you must enclose a comma-delimited list in double quotation marks ("").

For policy-managed databases, enter a single node name:

$ srvctl start instance -d db_unique_name -n node_name [-o start_options]

Note that this command also starts all enabled and non-running services that have AUTOMATIC management policy, and for which the database role matches one of the service's roles.

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

$ srvctl stop instance -d db_unique_name [ -i "instance_name_list" | 
  -n node_name ] [ -o stop_options ]

You can enter either a comma-delimited list of instance names to stop several instances or you can enter a node name to stop one instance. In Windows you must enclose a comma-delimited list in double quotation marks ("").

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 shuts down the two instances, orcl3 and orcl4, on the orcl database using the immediate stop 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 db_unique_name [-o start_options]

$ srvctl stop database -d db_unique_name [-o stop_options]

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

$ srvctl start database -d orcl -o mount

Setting SPFILE Parameter Values for Oracle RAC

You can alter SPFILE settings with Oracle 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:

*.OPEN_CURSORS=500
prod1.OPEN_CURSORS=1000

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

Oracle Database 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 Oracle 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 does 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 Oracle Enterprise Manager or the RMAN CONTROLFILE AUTOBACKUP statement to enable the RMAN autobackup feature

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:

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

When it is necessary to set parameters that have unique settings on a policy-managed database, you can ensure that instances always use the same name on particular nodes by running the srvctl modify instance -n node_name -i instance_name command for each server that can be assigned to the database's server pool. Then a unique value of the parameter can be specified for instance_name that will be used whenever the database runs on node_name.

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.

Use the CLUSTER_INTERCONNECTS initialization parameter to specify an alternative interconnect to the one Oracle Clusterware is using for the private network. Each instance of the Oracle RAC database gets a unique value when setting the CLUSTER_INTERCONNECTS initialization parameter.

Oracle Database uses the INSTANCE_NUMBER parameter to distinguish among instances at startup. Oracle Database 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. With Oracle Database 11.2 using Grid Plug and Play, the instance name parameter is no longer required. If you do not specify the instance name, then instance name defaults to db_unique_name_number.

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.

Recommendations for Setting the CLUSTER_INTERCONNECTS Parameter

Typically, you set the CLUSTER_INTERCONNECTS parameter only in the following situations:

• Due to operating system limitations, you cannot use 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 Database 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.

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: crm1.CLUSTER_INTERCONNECTS = ip1
Database Two: ext1.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, providing load balancing if 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.

Using Oracle Enterprise Manager Grid Control to Discover Nodes and Instances

Discovering Oracle RAC database and instance targets in Oracle 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 Oracle Enterprise Manager console interface to discover Oracle RAC database and instance targets.

  If the Grid Control agents are installed on a cluster that 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 Oracle Enterprise Manager Grid Control as follows:

1. Log in to Oracle 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 Oracle 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 Oracle 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.

   Oracle 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 RAC

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.