|Oracle9i Real Application Clusters Administration
Release 2 (9.2)
Part Number A96596-01
This chapter describes how to administer storage components in Real Application Clusters. It includes the following topics:
Oracle9i Real Application Clusters Deployment and Performance for information about optimizing the use of storage components to improve performance
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.
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:
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:
autoin your server parameter file. If you use client-side parameter files, then the setting for
UNDO_MANAGEMENTmust be identical in all the files.
UNDO_TABLESPACEparameter 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
TABLESPACE to a specific instance to control their use.
Oracle Corporation recommends that you do not use the
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.
You can dynamically re-direct undo tablespace use by executing the
ALTER SYSTEM SET UNDO_TABLESPACE statement. For example, assume you have instances
db2 accessing undo tablespaces
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
Each instance can only use one undo tablespace at a time. In addition, instances cannot share undo tablespaces.
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,
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.
Refer to the Oracle9i Database Administrator's Guide for more information about the
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.
The Oracle9i Database Administrator's Guide for information about the remaining administrative operations you can perform on undo tablespaces, such as setting the undo retention period and dropping undo tablespaces
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
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
In addition, the following rules also apply:
ALTER ROLLBACK SEGMENTcommand.
SYSTEMrollback segment online, then you need at least one more rollback segment to start the instance.
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.
Appendix B, "Associating Instances and Users with Free Lists and Free List Groups (Optional)" for more information on manual undo management and free lists
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.
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.
Oracle9i Database Administrator's Guide for a full description of multiplexed redo log files
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.
"Using Trace Files" for more information about trace files and alert logs
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 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.
Oracle9i SQL Reference for more information about 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.
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.
Real Application Clusters databases do not support the