|Oracle9i Real Application Clusters Concepts
Release 1 (9.0.1)
Part Number A89867-02
This chapter describes storage considerations for Real Application Clusters applications. Topics in this chapter include:
This section describes storage issues specific to Real Application Clusters. This section discusses:
All Real Application Clusters instances access the same datafiles. The composition of database files is the same for Oracle in parallel mode and exclusive mode. You do not have to alter the datafiles to start Oracle in parallel mode.
To improve performance, you can control the physical placement of data so that the instances use separate sets of data blocks. Free list groups, for example, enable you to allocate space for inserts to particular instances.
Whenever an instance starts up, it verifies access to all online datafiles. The first Real Application Clusters instance to start must verify access to all online files so it can determine if media recovery is required. Additional instances can operate without access to all of the online datafiles, but any attempt to use an unverified file fails and an error message is generated.
You do not need to recover entire files in the event of data loss. You can recover individual blocks.
Oracle9i Recovery Manager User's Guide for a description of Block Media Recovery (BMR).
When an instance adds a datafile or brings a datafile online, all instances verify access to the file. If an instance adds a new datafile on a disk that other instances cannot access, verification fails, but the instances continue running. Verification can also fail if instances access different copies of the same datafile.
If verification fails for any instance, then diagnose and fix the problem, and use the ALTER SYSTEM CHECK DATAFILES statement to verify access.
Real Application Clusters records parameter settings for your database in a server initialization text file that resides on the server. The Database Configuration Assistant (DBCA) creates this file as a binary server parameter file when a database is created. The server parameter file records values for both global and instance-specific parameter settings.
Oracle uses parameter settings in parameter files to determine how to control various database resources. You can use two types of files for parameter administration: the server-side parameter file or one or more traditional client-side parameter files.
Oracle Corporation recommends that you administer parameters using server parameter files. If you use the traditional client-side parameter files, parameter changes that Oracle makes as a result of self-tuning are not preserved after shutdown.
Oracle9i Real Application Clusters Administration for more information on using client-side parameter files
By default, the Oracle creates server parameter files based on one parameter file (PFILE). You can only change parameter settings in server parameter files using Oracle Enterprise Manager or
ALTER SYSTEM SET SQL statements. These are binary files that you cannot edit directly.
The default location of the server parameter file is inappropriate for Real Application Clusters. This is because all instances must use the same server parameter file.
The server parameter file uses the Oracle system identifier (SID) designator to indicate global parameter settings. Although you can still use client-side parameter files, Oracle strongly recommends that you use server parameter files
By using server parameter files, you can take advantage of Oracle's advanced self-tuning features. When Oracle self-tunes, it can only do so by automatically modifying parameter settings in server parameter files. Using server parameter files also greatly simplifies parameter administration within Real Application Clusters environments.
Oracle9i Real Application Clusters Installation and Configuration for details on server parameter files
In Real Application Clusters, each instance writes to its own set of online redo log files. The redo written by a single instance is called a thread of redo. Each online redo log file is associated with a particular thread number. When an online redo log is archived, Oracle records its thread number to identify it during recovery.
A private thread is a redo log created by using the
ALTER DATABASE ADD LOGFILE statement with the THREAD clause. A public thread is a redo log created using the
ALTER DATABASE ADD LOGFILE statement but without specifying a
If the THREAD initialization parameter is specified, then the instance starting up acquires the thread identified by that value as a private thread. If THREAD has the default value
0, then the instance acquires a public thread. Once acquired, the acquiring instance uses the redo thread exclusively.
Online redo log files can be multiplexed, or mirrored.
Rollback segments are records of old values of data that were changed by each transaction (whether or not committed). Oracle requires rollback segments to maintain read consistency, to undo changes made by transactions that roll back or abort, and to recover the database.
Each instance must have at least two rollback segments. For private rollback segments, you must always specify the SID when using the R
Each instance in Real Application Clusters shares use of the
SYSTEM rollback segment and requires at least two dedicated rollback segments for each instance. You do not have to manually create an undo tablespace or rollback segment unless you manually create your database.
If you use the recommended automatic undo management instead of Rollback Segment Undo, you do not need to do anything more than monitor the use of rollback segments. It is highly recommended that you use automatic undo management instead of Rollback Segment Undo.
Oracle9i Real Application Clusters Installation and Configuration for greater detail on the use of automatic undo management and Rollback Segment Undo
Both private and public rollback segments can be acquired at instance startup and used exclusively by the acquiring instance. They are used until taken offline or when the acquiring instance is shut down as specified in the rollback segment parameter.
Private rollback segments are unique to a particular instance; other instances cannot use them. A public rollback segment is offline and not used by any instance until an instance that needs an extra rollback segment starts up, acquires it, and brings it online. Once online, the acquiring instance uses the public rollback segment exclusively.
Only one instance writes to a given rollback segment (except for the SYSTEM rollback segment). However, other instances can read from it to create read-consistent snapshots or to perform instance recovery.
Oracle9i Database Administrator's Guide for information about contention for a rollback segment and for information on the performance implications of adding rollback segments. This manual also contains details on automatic undo management, undo tablespaces, and managing rollback segments
This section explains space management and free list group concepts by covering the following topics:
Real Application Clusters enables transactions running on separate instances to insert and update data in the same table concurrently. This occurs without contention to locate free space for new records. However, to take advantage of this capability, you must accurately manage free space in your database by using structures such as free list groups, which are described later in this chapter.
Oracle keeps track of blocks with space available for transactions that could cause rows to exceed the space available in their original block. Oracle does this for each database object, such as a table, cluster, or index. A transaction requiring free space can examine the free list of blocks. If the free list does not contain a block with enough space to accommodate it, then Oracle allocates a new extent.
When Oracle allocates new extents to a table, Oracle adds the new extent's blocks to the master free list. (The master free list is a list of blocks containing available space drawn from any extent in a table). This can eventually result in contention for free space among multiple instances on Real Application Clusters because the free space contained in newly allocated extents cannot be reallocated to any group of free lists. You can have more control over free space if you specifically allocate extents to instances; in this way you minimize free space contention.
Single instance Oracle uses multiple free lists to reduce block contention. Every tablespace has a free list that identifies data blocks with free space. Oracle uses blocks with free space when inserts or updates are made to a database object such as a table.
Blocks in free lists contain free space greater than shown with the PCTFREE parameter. The PCTFREE is the percentage of a block reserved for updates to existing rows. In general, blocks included in process free lists for a database object must satisfy the PCTFREE and PCTUSED (percentage used) constraints.
You can specify the number of free lists by setting the FREELISTS parameter when you create a table, index or cluster. The maximum value of the FREELISTS parameter depends on the Oracle block size on your system. In addition, for each free list you need to store a certain number of bytes in a block to accommodate overhead.
Within free list groups there are two subsets of free lists:
Because Real Application Clusters has multiple instances, free lists alone cannot solve contention problems. Free list groups, however, effectively reduce forced writes (pinging) between instances.
A free list group is a set of free lists for use by one or more instances. Each free list group provides free data blocks to accommodate inserts or updates on tables and clusters and is associated with one or more instances at startup. By default, only one free list group is available. This means all free lists for an object reside in the segment header block. Free list groups are supported on all database objects.
If multiple free lists reside in a single block in a Real Application Clusters environment, then the block with the free lists could thus experience block writes, or a forced read/write among all the instances. Avoid this by grouping the free lists into separate groups and assigning each group to an instance. Each instance then has its own block containing free lists. Since each instance uses its own free lists, there is no contention among instances to access the same block containing free lists.
Interinstance contention occurs when different instances' transactions insert data into the same table. This occurs because all free lists are held in the segment header if you do not define free list groups. The free list may be from a common pool of blocks, or you can partition multiple free lists so specific extents in files are allocated to objects.
A highly concurrent environment has potential contention for the segment header, that contains the free list.
In multiinstance environments, as illustrated in Figure 8-1, free lists provide free data blocks from available extents to different instances. You can partition multiple free lists so that extents are allocated to specific database instances. Each instance hashes to one or more free list groups, and each group's header block points to free lists. Without free list groups, every instance must read the segment header block to access the free lists.
Figure 8-2 shows the blocks of a file where the master free list is stored in the segment header block. Three instances are forced to read this block to obtain free space. Because there is only one free list, there is only one insertion point. Free list groups help reduce contention by spreading this insertion point over multiple blocks. With free list groups each block is accessed less frequently.
Locally managed tablespaces are also useful because they help avoid data dictionary contention. This can occur if Real Application Clusters performs a lot of space management for sorting segments in tablespaces, creating or dropping tables, and so on. Locally managed tablespaces eliminate this contention and the benefits of Real Application Clusters in this case easily outweigh the benefits of single instance Oracle.
This section describes two free list group examples:
Figure 8-3 illustrates the division of free space for a table into a master free list and two free list groups. Each contains three free lists. This example involves a well-partitioned application where deletes occur. The master free list pictured is the master free list for this particular free list group.
The table shown in Figure 8-3 shows one initial extent. After this, extents 2 and 5 are allocated to instance X, extents 3 and 4 are allocated to instance Y. Extent 6 is allocated automatically, but not to any particular instance. Notice the following:
Each user process running on instance X uses one of the free lists in group X. Meanwhile, each user process on instance Y uses one of the free lists in group Y. If more instances start up, their user processes share free lists with instance X or Y.
The simple case in Figure 8-3 becomes more complicated when you consider that extents are not permanently allocated to instances, and that space allocated to one instance cannot be used by another instance. Each free list group has its own master free list. After allocation, some blocks go onto the master free list for the group, some go to a process free list, and some do not belong to a free list at all. If the application is fully partitioned, then once blocks are allocated to a given instance, they stay with that instance. However, blocks can move from one instance to another if the application is not fully partitioned.
Consider a situation where instance Y fills a block, takes it off the free list, and then instance X frees the block. The block then goes to the free list of instance X, the instance that freed it. If instance Y needs space, then it cannot reclaim this block. Instance Y can only obtain free space from its own free list group.
When a row is inserted into a table and new extents need to be allocated, a certain number of contiguous blocks are automatically allocated by Cache Fusion to the free list group associated with an instance. This extent allocation occurs when the table or cluster is first created and new extents that are automatically allocated. These extents add their blocks to the master free list or to the space above the high water mark.
When you explicitly allocate an extent without specifying an instance, or when an extent is automatically allocated to a segment because the system is running out of space (the high water mark cannot be advanced any more), the new extent becomes part of the unused space. It is placed at the end of the extent map. This means that the current high water mark is now in an extent to the left of the new one. The new extent is thus added above the high water mark.