Release 1 (9.0.1)
Part Number A89869-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Real Application Clusters Administration Release 1 (9.0.1) Part Number A89869-02 |
|
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
See Also:
This section discusses the following file management issues that are specific to Real Application Clusters:
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.
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:
To use automatic undo management, set the following parameters:
UNDO_MANAGEMENT to auto in your server parameter file. If you use client-side parameter files, the setting for UNDO_MANAGEMENT must be identical in all the files.
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, 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.
|
Warning:
Using the |
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.
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:
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.
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 |
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 |
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.
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 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:
ALTER ROLLBACK SEGMENT command.
See Also:
DBA_ROLLBACK_SEGS and DBA_SEGMENTS and for information about other dynamic performance views
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 Oracle Database Configuration Assistant (DBCA) creates two log files, one member for each group and one thread for each instance.
Note:
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.
|
See Also:
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.
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.
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.
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.
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 |
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.
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:
CREATE [TABLE | CLUSTER | INDEX] STORAGE FREELISTS FREELIST GROUPS ALTER [TABLE | CLUSTER | INDEX] ALLOCATE EXTENT SIZE DATAFILE INSTANCE
You can use these SQL options with the INSTANCE_NUMBER parameter to associate data blocks with instances.
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.
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.
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 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.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|