4 Administering Database Instances and Cluster Databases

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 that must be identical on every instance:

• CLUSTER_DATABASE

• COMPATIBLE

• CONTROL_FILES

• DB_BLOCK_SIZE

• DB_DOMAIN

• DB_FILES

• DB_NAME

• DB_RECOVERY_FILE_DEST

• DB_RECOVERY_FILE_DEST_SIZE

• DB_UNIQUE_NAME

• DML_LOCKS (only if set to 0)

• INSTANCE_TYPE (RDBMS or ASM)

• PARALLEL_EXECUTION_MESSAGE_SIZE

• REMOTE_LOGIN_PASSWORDFILE

• RESULT_CACHE_MAX_SIZE (either enabled or disabled on all instances)

• UNDO_MANAGEMENT

Certain initialization parameters must have unique values for every instance in an Oracle RAC database.

For the following parameters, the parameter must be set on all instances to a value that is unique to that instance:

• ASM_PREFERRED_READ_FAILURE_GROUPS

  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.

• CLUSTER_INTERCONNECTS

  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.

• INSTANCE_NUMBER

  Oracle Database uses the INSTANCE_NUMBER parameter to distinguish among instances at startup and the INSTANCE_NAME parameter to assign redo log groups to specific instances. The instance name can take the form db_unique_name_instance_number and when it has this form of name and number separated by an underscore, the number after the underscore is used as the INSTANCE_NUMBER. With policy-managed databases, you no longer have to explicitly assign instance numbers and the instance name defaults to db_unique_name_instance_number, where Oracle Database assigns the instance number.

• ROLLBACK_SEGMENTS (if the UNDO_MANAGEMENT initialization parameter is not set to AUTO)

  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.

• UNDO_TABLESPACE (if UNDO_MANAGEMENT is set to AUTO)

  When you specify UNDO_TABLESPACE with automatic undo management enabled, then set this parameter to a unique undo tablespace name for each instance.

Oracle recommends that you set the values for these 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.

The SERVICE_NAMES parameter should not be modified in the same manner as other initialization parameters.

The SERVICE_NAMES initialization parameter specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.

In an Oracle RAC database, you should not modify this parameter directly. Instead, define services for your database and database instances using the Clustered Managed Database Services page in Oracle Enterprise Manager. If you must change a service, then you can use either Enterprise Manager or SRVCTL. When you use either Oracle Enterprise Manager or SRVCTL to create and start the service, the SERVICE_NAMES parameter is updated automatically once the service is active.

The Current subpage of the Initialization Parameters page contains a list of configuration parameters for that instance and database.

You can set the parameters on the Current subpage of the Initialization Parameters page to particular values to initialize many of the memory and process settings of an Oracle instance. When you modify initialization parameters using the Current tab, the changes are applied only to the running instances, not the SPFILE, unless the "Apply changes in current running instance(s) mode to SPFile" option is selected.

The Instance column shows the instances for which the parameter has the value listed in the table. An asterisk (*) indicates that the parameter has the same value for all remaining instances of the cluster database. For example, if open_cursors = 250 for node1 and open_cursors = 300 for node2, then the Instance column for open_cursors = 250 displays an asterisk, but the Instance column for open_cursors = 300 contains "node2". This shorthand notation saves space when the cluster database has many instances.

Figure 4-1 Modify Initialization Parameters: Current Tab

Description of "Figure 4-1 Modify Initialization Parameters: Current Tab"

You can filter the Initialization Parameters page to show only those parameters that meet the criteria of the filter you enter in the Filter by name field. Optionally, you can select Show All to display on one page all parameters currently used by the running instance(s).

1. Select a parameter from the Select column and do one of the following:

   • Click Add to add the selected parameter to a different instance.

     When the new row appears in the table, choose the instance name to which the new setting applies, and enter the new value for the parameter in the Value field. Optionally, you can put text in the Comment field to indicate why the instance has a different value from the other instances.

   • Click Reset to reset the value of the selected parameter. Note that using this option you can reset only those parameters that do not have an asterisk in the Instance column.

     When you click Reset, the value of the selected parameter is reset to the value of the remaining instances (that is, the row with the asterisk in the Instance column) and the row that has the local instance name in the Instance field is removed from the table.

   For example, select the parameter OPEN_CURSORS, then click Add. In the new entry for OPEN_CURSORS, select an Instance (for example sales1 or RACDB2), and change the Value field to 300.

2. After you make changes to one or more of the parameters, click Apply to accept and implement the changes.

You can add or reset parameters using the SPFile subpage of the Initialization Parameters page.

1. Choose a parameter using the Select column.

   For more information about each parameter, click the information icon in the Help column next to the parameter.

2. Click Add, then edit the entry to change the target instance or parameter value. You can optionally add a comment describing the reason for the change.
3. Click Apply to apply the changes to the SPFILE, or to the SPFILE and the currently running instance if you selected the "Apply changes in SPFile mode to the current running instance(s)" option.
4. On the SPFile subpage of the Initialization Parameters page, select a parameter from the Select column and do one of the following:
   1. Click Add to add the selected parameter to a different instance.

      When the new row appears in the table, choose the instance name to which the new setting applies, and then enter the new value for the parameter in the Value field. Optionally, you can put text in the Comment field describing the reason for the change.

   2. Click Reset to reset the value of the selected parameter.

      When you click Reset, one of the following actions is performed:

      • If the entry you selected was for a specific instance, then the value of the selected parameter for that instance is reset to the value of the remaining instances (indicated by an asterisk in the Instance column). The entry that has the local instance name in the Instance field is deleted.

      • If the entry you selected to reset was the default value for all instances (indicated by an asterisk in the Instance column), then the value of the selected parameter is unset for all instances, but any instance-specific parameter entries for the same parameter are not changed.

      • If you reset the only entry for a parameter, regardless of whether the entry applies to all instances or a single instance, then the parameter is unset for all instances in the cluster database.

5. After you make changes to one or more of the parameters, click Apply to accept and implement the changes.

This topic provides an example of modifying the OPEN_CURSORS parameter.

Suppose that the open_cursors parameter has two entries in the SPFILE:

*.open_cursors = 250 
RACDB2.open_cursors = 200

Figure 4-4 Initial Values for the OPEN_CURSORS Parameter

Description of "Figure 4-4 Initial Values for the OPEN_CURSORS Parameter"

Using the Initialization Parameters page with the SPFile tab selected, if you click Reset for *.open_cursors, then Enterprise Manager deletes that entry from both the SPFILE and the displayed list of parameters, leaving only RACDB2.open_cursors = 200 displayed.

Figure 4-5 Parameter Values After Deleting Default Entries

Description of "Figure 4-5 Parameter Values After Deleting Default Entries"

If you click Reset for RACDB2.open_cursors, then Enterprise Manager also deletes this parameter entry from both the SPFILE and the displayed list of parameters, but then a new entry, *.open_cursors = <NULL> is added to the displayed list of parameters for the reset parameter.

Figure 4-6 Parameter Values After Deleting Custom Entry and Default Entry

Description of "Figure 4-6 Parameter Values After Deleting Custom Entry and Default Entry"

An Oracle ASM instance can operate in several configurations in Oracle Flex ASM.

Oracle Flex ASM enables an Oracle ASM instance to run on a separate physical server from the database servers. With this deployment, larger clusters of Oracle ASM instances can support more database clients while reducing the Oracle ASM footprint for the overall system. When using Oracle Flex ASM, Oracle ASM clients are configured with direct access to storage.

With Oracle Flex ASM, you can consolidate all the storage requirements into a single set of disk groups. All these disk groups are mounted by and managed by a small set of Oracle ASM instances running in a single cluster. You can specify the number of Oracle ASM instances with a cardinality setting. The default is three instances.

Each Oracle ASM instance has either an SPFILE or PFILE type parameter file.

For the environment described in this guide, there is an Oracle ASM instance on each node and the Oracle ASM instances use SPFILEs.

When you create a disk group for a cluster, or add new disks to an existing clustered disk group, you must prepare only the underlying physical storage on shared disks.

The shared disk requirement is the only substantial difference between using Oracle ASM in an Oracle RAC database compared to using it in a single-instance Oracle database. Oracle ASM automatically rebalances the storage load after you add or delete a disk or disk group.

In a cluster, each Oracle ASM instance manages the metadata updates to the disk groups for the node on which it is running. In addition, each Oracle ASM instance coordinates disk group metadata with other nodes in the cluster.

As in single-instance Oracle databases, you can use Enterprise Manager, Oracle ASM configuration assistant (ASMCA), Oracle ASM command-line utility (ASMCMD), SQL*Plus, and SRVCTL to administer disk groups for Oracle ASM in an Oracle RAC environment.

If you choose not to configure Oracle ASM during installation, then you must manually configure Oracle ASM, or perform standalone Oracle ASM disk group management.

You can configure Oracle ASM during installation of Oracle Grid Infrastructure for a cluster. You do not have to create a database to modify Oracle ASM storage properties.

To manually configure Oracle ASM, you must first run ASMCA to create the Oracle ASM instances and create at least one disk group. After Oracle ASM is running, then you can use DBCA to create an Oracle RAC database that uses Oracle ASM for storage.

You can perform administrative operations on Oracle ASM disk groups using Enterprise Manager such as adding and deleting them.

You can also monitor Oracle ASM disk group performance and control disk group availability at the instance level. For example, Enterprise Manager provides some Oracle RAC-specific features for Oracle ASM, such as the following:

• When you add a disk group, the disk group definition includes a check box to indicate whether the disk group is automatically mounted to all the cluster database instances.

• The default Disk Group Performance page displays instance-level performance details when you click a performance characteristic such as Write Response Time or I/O Throughput.

• When you mount and dismount Oracle ASM disk groups, you can use a check box to indicate which instances should mount or dismount a particular Oracle ASM Disk Group.

Redo logs contain a record of changes that have been made to data files.

In a single-instance Oracle database, redo logs are stored in two or more redo log file groups. Each of these groups contains a redo log file and possibly one or more mirrored copies of that file. In an Oracle RAC database, each instance requires its own set of redo log groups, which is known as a redo thread. Mirrored copies of the redo log files provide your system with extra protection against data loss that is due to hardware failures or data corruption. If a redo log file is unreadable, then Oracle Database attempts to access its mirrored copy. The redo log file mirrors should be located on different disk devices from the primary redo log files.

Figure 4-7 Illustration of Redo Threads for an Oracle RAC Database

Description of "Figure 4-7 Illustration of Redo Threads for an Oracle RAC Database"

Each instance's redo thread must contain at least two redo log groups. Each redo log group should contain at least two members: a redo log and its mirrored copy. If you create your Oracle RAC database using DBCA, then your Oracle RAC database automatically implements a configuration that meets the Oracle recommendations.

You should create redo log groups only if you are using administrator-managed databases. For policy-managed databases, if an instance starts due to a change in server pool cardinality, then Oracle Database automatically creates redo log files, enables a redo thread for the instance if there is not a redo thread allocated to that instance, and creates the undo tablespace if there is not an undo tablespace allocated to that instance. The database must be using Oracle Managed Files and Oracle ASM in this situation.

In an Oracle RAC database, all the redo log files reside on shared storage. In addition, each instance must have access to the redo log files of all the other instances in the cluster. If your Oracle RAC database uses Oracle ASM, then Oracle ASM manages the shared storage for the redo log files and the access to those files.

In an Oracle RAC database, each instance writes and archives the redo log groups in its redo thread in the same manner that single-instance Oracle databases do.

However, in recovery mode, the instance performing the recovery can read and process all the redo threads for the database, regardless of which instance generated the redo thread. Being able to read all the redo threads enables a running instance to recover the work completed by one or more failed instances.

In case of instance failure, a surviving instance can read the redo logs of the failed instance. Users can continue to access and update the database without waiting for the failed instance to be restarted. For example, assume that you have an Oracle RAC database with two instances, instance A and instance B. If instance A is down, then instance B can read the redo log files for both instance A and B to ensure a successful recovery.

On the Redo Log Groups page, you can create additional redo log groups and add members to the redo log group. The Thread column identifies the instance, or redo thread, to which a redo log file belongs.

1. Access the Cluster Database Home page.

   See Oracle Database 2 Day DBA for details on logging in to Oracle Enterprise Manager.

2. From the Administration menu, select Storage, and then select Redo Log Groups.

   The Redo Log Groups page appears.

3. On the Redo Log Group page, perform any of the following tasks:

   • View the status, number of members, thread, file size, status, and archive status of each redo log group

   • Create or delete log groups

   • Edit a redo log group to add or remove members

   • Perform other redo log group management tasks, including clearing log files, duplicating a log group, generating sizing advice, and forcing a log switch