3
Administering Storage Components in Real Application Clusters

This chapter describes how to administer storage components in Real Application Clusters. It includes the following topics:

• Adding Datafiles in Real Application Clusters
• Automatic Undo Management In Real Application Clusters
• Using Redo Log Files in Real Application Clusters
• Managing Trace Files and Alert Files in Real Application Clusters
• The Sequence Number Generator in Real Application Clusters

  See Also: Oracle9i Real Application Clusters Deployment and Performance for information about optimizing the use of storage components to improve performance

Adding Datafiles in Real Application Clusters

You can add a datafile while your Real Application Clusters database is running. When you do this, Oracle automatically controls resource assignments to the new file just as it controls resource assignments for existing files.

Automatic Undo Management In Real Application Clusters

This section explains how to use automatic undo management and manual undo management in Real Application Clusters. Oracle offers undo space management features in Real Application Clusters that augment the undo space management features available in single-instance environments.

You can use either automatic undo management or manual undo management to manage undo space. However, Oracle Corporation strongly recommends that you use the more transparent automatic undo management method. The undo space management topics in this section are:

• Using Automatic Undo Management
• Overriding Automatic Undo Management by Using Manual Undo Management

  Note: You cannot simultaneously use automatic undo management and manual undo management mode. In other words, all instances within Real Application Cluster environments must operate in the same undo mode.

Using Automatic Undo Management

With automatic segment-space management, Oracle manages the in-segment free and used space with bitmaps, as opposed to free lists. Automatic segment-space management is simpler to administer than free lists and it provides improved space utilization. This is especially true for objects with highly varying size rows. Automatic segment-space management also improves run-time adjustment to variations in concurrent access. Moreover, it provides improved cluster performance in terms of performance/space utilization.

To use automatic undo management, set the following parameters:

• Set the global parameter UNDO_MANAGEMENT to auto in your server parameter file. If you use client-side parameter files, then the setting for UNDO_MANAGEMENT must be identical in all the files.
• Set the UNDO_TABLESPACE parameter to assign undo tablespaces to instances.

You must have already created any undo tablespaces you use with the UNDO_TABLESPACE parameters. Otherwise, the STARTUP command fails.

If you do not set the UNDO_TABLESPACE parameter, then each instance uses the first available undo tablespace. If undo tablespaces are not available, then the instances use the SYSTEM rollback segment. Therefore, Oracle Corporation recommends that you assign an UNDO TABLESPACE to a specific instance to control their use.

When using automatic undo management, Oracle ignores settings for the TRANSACTIONS parameter. This is because Oracle dynamically allocates transaction objects from the System Global Area (SGA) for automatic undo management.

Switching Undo Tablespaces

You can dynamically re-direct undo tablespace use by executing the ALTER SYSTEM SET UNDO_TABLESPACE statement. For example, assume you have instances db1 and db2 accessing undo tablespaces undotbs01 and undotbs02 respectively. If you have an idle undo tablespace, for example, undotbs03, you can execute the following statement from either instance to re-direct undo processing from that instance to undotbs03:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs3;

User transactions proceed normally while Oracle executes this operation. In some circumstances, an instance can temporarily access two undo tablespaces at the same time. This only happens while transition processing occurs during the tablespace switching operation.

This process also does not wait for all user transactions to commit. Instead, it places the previous undo tablespace in a pending-offline state if there are active transactions in that tablespace. This means that the pending offline tablespace may be unavailable for other instances until all transactions against that tablespace are committed.

In the last code example, the previously-used undo tablespaces, undotbs01 or undotbs02 remain owned by the instance until the instance's last active transaction has committed.

Although each undo tablespace can only be used by one instance at any one time, all instances can read undo blocks for consistent read purposes at any time. Also, any instance is allowed to update any undo tablespace during transaction recovery, as long as that undo tablespace is not currently used by another instance for undo generation or transaction recovery.

System Rollback Segment

When you use automatic undo management, the only external rollback segment Oracle uses is the SYSTEM rollback segment. There is only one SYSTEM rollback segment for each database. The SYSTEM rollback segment resides in the SYSTEM tablespace and Oracle automatically creates it during database creation.

In Real Application Clusters databases, all instances use the same SYSTEM rollback segment. Under normal circumstances, the SYSTEM rollback segment is only used for performing system transactions, such as the creation of transaction tables. You normally do not have to perform any operations to manage the SYSTEM rollback segment.

Public and Private Rollback Segments

Public and private rollback segments provide the same level of performance. However, private rollback segments provide more control over the matching of instances with rollback segments. This enables you to locate the rollback segments for different instances on different disks to improve performance. Therefore, use private rollback segments to improve performance.

Public rollback segments form a pool of rollback segments that can be acquired by any instance needing an additional rollback segment. Using public rollback segments can be disadvantageous, however, when you simultaneously shut down and start up instances.

For example, assume you shut down instance X and it releases public rollback segments. Then you start instance Y and it acquires the released rollback segments from instance X. Finally, you start instance and it cannot acquire its original rollback segments. Thus, instances are forced to acquire public rollback segments at startup if you do not properly set the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENTS parameters.

You can use public rollback segments to improve space usage. For example, if you create only one large public rollback segment for long-running transactions that run on different instances each month, then you can take the rollback segment offline and bring it back online or move it from one instance to another to better serve instances with the heavier processing demands.

By default, a rollback segment is private and is used by the instance specifying it in the parameter file. Specify private rollback segments using the ROLLBACK_SEGMENTS parameter.

In addition, the following rules also apply:

• Once a public rollback segment is acquired by an instance, it is then used exclusively by that instance.
• Once created, you can bring both public and private rollback segments online using the ALTER ROLLBACK SEGMENT command.
• If an instance has more than the SYSTEM rollback segment online, then you need at least one more rollback segment to start the instance.

  See Also: Oracle9i Database Administrator's Guide for more information about how instances acquire rollback segments and how to monitor rollback segments Oracle9i Database Reference for a description of DBA_ROLLBACK_SEGS and DBA_SEGMENTS

Overriding Automatic Undo Management by Using Manual Undo Management

Although not recommended, you can override the default automatic undo management by setting the UNDO_MANAGEMENT parameter to manual. In addition, follow the recommendations in the rest of this section. If you do not specify the UNDO_MANAGEMENT parameter, then Oracle starts the instance in automatic undo management mode.

Using Redo Log Files in Real Application Clusters

Each instance has its own online redo log groups which are called an instance's thread of online redo. Create these online redo log groups and establish group members as described in the Oracle9i Database Administrator's Guide. Figure 3-1 shows the threads of redo for three Real Application Clusters instances.

Figure 3-1 Threads of Redo

Text description of the illustration sps81036.gif

• Instance X uses thread 1 that contains three groups of online redo log groups. Thread 1 is multiplexed, that is, each group has two copies, or members, of the redo log file.
• Instance Y uses thread 2 that contains two groups of online redo log files. Thread 2 is multiplexed and each group of redo logs has three members.
• Instance Z uses thread 3 that contains three groups of online redo log files, but these redo log files are not multiplexed; there is only one member for each group.

  Note: The Database Configuration Assistant (DBCA) creates two log files, one member for each group and one thread for each instance.

Group numbers must be unique within the database. However, the order of assigning groups to threads and threads to instances is arbitrary.

For example, although in Figure 3-1 thread 1 contains groups 1, 2, and 3, while thread 2 contains groups 4 and 5, you could instead assign groups 2, 4, and 5 to thread 1 while assigning groups 1 and 3 to thread 2. The V$LOGFILE view displays the group number associated with each redo log file.

Although it is possible to have different numbers of groups and members for each thread, Oracle Corporation recommends that you configure all threads using a standard that facilitates administration. That is, if possible configure all of your threads like those shown for thread X or Y. Oracle Corporation recommends against using non-mirrored redo log groups as shown for thread Z. Non-standard redo log configurations can be useful, however, for performance reasons.

Different degrees of mirroring may be required for some instances that perform better with less mirroring overhead. For example, one instance could have three groups with two members for each group, a second instance could have four non-multiplexed log files, and a third instance could have two groups with four members for each group.

In Real Application Clusters, each instance must have at least two groups of online redo log files. When the current group fills, an instance begins writing to the next log file group. At a redo log switch, Oracle writes information to the control file that identifies the filled group and its thread number after it has been archived.

Managing Trace Files and Alert Files in Real Application Clusters

Oracle records information about important events that occur in your Real Application Clusters environment in trace files and alert files. The trace files and alert files for Real Application Clusters are the same as those in single-instance Oracle databases.

Monitor these files frequently and regularly copy of them for all instances. This preserves their content and avoids accidentally overwriting the files.

The Sequence Number Generator in Real Application Clusters

Real Application Clusters enables users on multiple instances to generate unique sequence numbers with minimal synchronization. The sequence number generator enables multiple instances to access and increment a sequence without contention among instances for sequence numbers and without waiting for transactions to commit.

Each instance can have its own sequence cache for faster access to sequence numbers. Oracle uses Global Cache Service (GCS) resources to coordinate sequences across instances in Real Application Clusters.

This section describes the CREATE SEQUENCE statement and its options.

• The CREATE SEQUENCE Statement
• The CACHE Option
• The ORDER Option

The CREATE SEQUENCE Statement

The SQL statement CREATE SEQUENCE establishes a database object from which multiple users can generate unique integers without waiting for other users to commit transactions to access the same sequence number generator.

Real Application Clusters enables users on multiple instances to generate unique sequence numbers with minimal cooperation or contention among instances.

Sequence numbers are always unique, unless you use the CYCLE option. However, you can assign sequence numbers out of order if you use the CACHE option without the ORDER option, as described in the following section.

The CACHE Option

The CACHE option of CREATE SEQUENCE preallocates sequence numbers and retains them in an instance's SGA for faster access. You can specify the number of sequence numbers cached as an argument to the CACHE option. The default value is 20.

Caching sequence numbers significantly improves performance but can cause the loss of some numbers in the sequence. In other words, the sequence numbers will not be in chronological order. Losing sequence numbers is unimportant in some applications, such as when sequences are used to generate unique numbers for primary keys.

A cache for a given sequence is populated at the first request for a number from that sequence. After the last number in that cached set of numbers is assigned, the cache is repopulated with another set of numbers.

Each instance keeps its own cache of sequence numbers in memory. When an instance shuts down, cached sequence values that have not been used in committed DML statements can be lost. The potential number of lost values can be as great as the value of the CACHE option multiplied by the number of instances shutting down. Cached sequence numbers can be lost even when an instance shuts down normally.

The ORDER Option

The ORDER option of CREATE SEQUENCE guarantees that sequence numbers are generated in the order of the requests. You can use the ORDER option for time-
stamp numbers and other sequences that must indicate the request order across multiple processes and instances.

If you do not need Oracle to issue sequence numbers in order, the NOORDER option of CREATE SEQUENCE can significantly reduce overhead in a Real Application Clusters environment.