Oracle9i Real Application Clusters Administration
Release 1 (9.0.1)

Part Number A89869-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Administering Storage Components in Real Application Clusters

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

File Management Issues in Real Application Clusters

This section discusses the following file management issues that are specific to Real Application Clusters:

Adding Data Files

If you add data file while your Real Application Clusters database is running, Oracle automatically controls resource assignments to the new files, 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 rollback segment undo 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 rolback segment undo 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

To use automatic undo management, set the following parameters:

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, each instance uses the first available undo tablespace. If undo tablespaces are not available, 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.


Using the SYSTEM rollback segment is not recommended. When you use the SYSTEM rollback segment for undo, Oracle writes a message to the alert files to warn that your database is running without undo tablespaces.  

When you are 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 to undotbs03:



Each instance can only use one undo tablespace at a time. In other words, 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, undotbs01 or undotbs02 remain owned by the instance until the instance's last active transaction has committed.

Each undo tablespace can only be used by one instance for undo at any one time. However, 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.

See Also:

Refer to the Oracle9i Database Administrator's Guide for more information about the ALTER SYSTEM SET UNDO_TABLESPACE statement 

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 at CREATE DATABASE time.

In Real Application Cluster environments, 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.

See Also:

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  

Overriding Automatic Undo Management by Using Rollback Segment Undo

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, Oracle starts the instance in automatic undo management mode.

Manually Creating Rollback Segments

Real Application Clusters databases need at least as many rollback segments as the maximum number of concurrent instances plus one; the extra one is for the SYSTEM rollback segment. An instance cannot start up shared without exclusive access to at least one rollback segment, whether it is public or private.

You can create new rollback segments in any tablespace except for the temporary tablespace. To reduce contention between rollback data and table data, partition your rollback segments in a separate tablespace. This also facilitates taking tablespaces offline because a tablespace cannot be taken offline if it contains active rollback segments.

In general, make all rollback segment extent the same size by specifying identical values for the storage parameters INITIAL and NEXT. To ensure you have correctly created the rollback segments, examine the data dictionary view DBA_ROLLBACK_SEGS. This view shows each rollback segment's name, segment ID number, and owner (PUBLIC or other).

See Also:

Oracle9i Database Administrator's Guide for information about contention for a rollback segment and for information on the performance implications of adding rollback segments 

Public and Private Rollback Segments

Public and private rollback segments do not have performance differences. 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 reduce disk contention in high-performance systems.

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 instances are shut down and started up at the same time. For example, instance X shuts down and releases public rollback segments. Instance Y starts up and acquires the released rollback segments. Finally, instance X starts up and cannot acquire its original rollback segments. 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 utilization. If you create only one large public rollback segment for long-running transactions that run on different instances each month, 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 workloads.

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 parameter ROLLBACK_SEGMENTS.

In addition, the following rules also apply:

Using Redo Log Files

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 sps81036.gif follows
Text description of the illustration sps81036.gif

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.


MAXLOGHISTORY is useful for sites with very demanding availability requirements. This option can help you administer recovery, especially when there are many instances and many log files. 

See Also:

Oracle9i Database Administrator's Guide for a full description of multiplexed redo log files 

Managing Trace Files and the Alert 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.

Monitor these files frequently and regularly make copies of them for all instances of your Real Application Clusters environment. This preserves their content and avoids accidentally overwriting the files.

See Also:

"Using Trace Files" for more information about trace files and alert logs 

Associating Instances, Users, and Resources with Free List Groups

This section describes:

In general, all tables should have the same number of free list groups, but the number of free lists within a group may vary, depending on the type and amount of activity of each table.

Partitioning free space can particularly improve the performance of applications that have a high volume of concurrent inserts, or updates requiring new space, from multiple instances. Performance improvements also depend, of course, on your operating system, hardware, data block size, and so on.


In multi-instance environments, Oracle does not preserve information about multiple free lists and free list groups upon import. If you use Export and Import to back up and restore your data, the imported data is not partitioned. Oracle recommends that you maintain scripts to pre-create all objects before import to preserve your free list configuration and then import with IGNORE = Y.  

Associating Instances with Free Lists

Although it is not required for Real Application Clusters, sometimes data partitioning can reduce contention for data blocks. The Cache Fusion resources that often cover blocks in one free list group tend to be held primarily by the instance using that free list group. This is because an instance that modifies data is usually more likely to reuse that data than other instances. However, if multiple instances take free space from the same extent, they are more likely to contend for blocks in that extent if they subsequently modify the data that they inserted.

Assignment of New Instances to Existing Free List Groups

If MAXINSTANCES is greater than the number of free list groups in the table or cluster, then an instance number maps to the free list group associated with:

instance_number modulo number_of_free_list_groups

Modulo (or rem for remainder) is a formula for determining which free list group should be used by calculating a remainder value. In the following example there are 2 free list groups and 10 instances. To determine which free list group instance 6 will use, the formula would read 6 modulo 2 = 0. Six divided by 2 is 3 with zero remainder, so instance 6 will use free list group 0. Similarly, instance 5 would use free list group 1 because 5 modulo 2 = 1. Five is divisible by 2 with a remainder of 1.

If there are more free list groups than MAXINSTANCES, then a different hashing mechanism is used. If multiple instances share one free list group, they share access to every extent specifically allocated to any instance sharing that free list group.


In a system with relatively few nodes, the FREELIST GROUPS option for a table should generally have the same value as the MAXINSTANCES option of CREATE DATABASE, which limits the number of instances that can access a database concurrently. In a Massively Parallel Processing system, however, MAXINSTANCES could be many times larger than FREELIST GROUPS so that many instances share one group of free lists.

See Also:

Oracle9i Real Application Clusters Deployment and Performance for more information on associating instances and users with free list groups 

Associating User Processes with Free Lists

User processes associate with free lists based on their Oracle process IDs. Each user process has access to only one free list in the free list group for the instance on which it is running. Every user process also has access to the master free list of free blocks. If a table has multiple free lists but does not have multiple free list groups, or has fewer free list groups than the number of instances, then free lists and free list groups would be shared among user processes from different instances.

Using the statement ALTER SESSION INSTANCE_NUMBER, you can increase the instance number value beyond the value of MAXINSTANCES. You can also dynamically alter a table's free list assignment with the ALTER TABLE statement. However, you cannot use this statement for free list groups.

SQL Options for Managing Free Space

Several SQL options enable you to create free lists and free list groups for tables, clusters, and indexes. You can explicitly specify that new space for an object be taken from a specific data file. You can also associate free space with particular free list groups that you can then associate with particular instances.

The SQL statements include:


You can use these SQL options with the INSTANCE_NUMBER parameter to associate data blocks with instances.

See Also:

Oracle9i SQL Reference for complete syntax of these statements 

Preallocating Extents to Free List Groups

Preallocating extents is a static approach to the problem of preventing automatic allocation of extents by Oracle. You can preallocate extents to tables that have free list groups. This means that all free blocks are formatted into free lists that reside in the free list group of the instance to which you are preallocating the extent. This approach is useful if you need to accommodate objects that you expect will grow in size.

The Sequence Number Generator

Real Application Clusters allows users on multiple instances to generate unique sequence numbers with minimal synchronization. The sequence number generator allows 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 allows 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.

See Also:

Oracle9i SQL Reference for more information about the CREATE SEQUENCE and CYCLE options 

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.


Real Application Clusters databases do not support the CACHE option with the ORDER option of CREATE SEQUENCE when the database is mounted in cluster mode. Oracle cannot guarantee an order if each instance has some sequence values cached. Therefore, if you should create sequences with both the CACHE and ORDER options, they will be ordered but not cached. 

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index