|Oracle8i Parallel Server Concepts and Administration
This chapter describes features of the Oracle database architecture pertaining to multiple instances of OPS.
The following sections describe the features of control files, datafiles, and redo log files that apply to OPS.
All OPS instances access the same control files. The control files hold values of global constant initialization parameters, such as GC_FILES_TO_LOCKS, some of which must be identical for all instances running concurrently. As each instance starts, Oracle compares the global constant initialization values in a common parameter file (or in parameter files for each instance) with those in the control file, and generates a message if the values are different.
For more information on parameters, please see "Parameters that Must Be Identical on All Instances", and "Initialization Parameters" in Appendix A. Also see Oracle8i Concepts.
All OPS instances access the same datafiles. Database files are the same for Oracle in parallel mode as in exclusive mode. You do not have to change the datafiles to start Oracle in exclusive or parallel mode.
To improve performance, you can control the physical placement of data so that the instances use separate sets of data blocks. Free lists, 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 OPS 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 a message is generated.
When an instance adds a datafile or brings an offline 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, diagnose and fix the problem, then use the ALTER SYSTEM CHECK DATAFILES statement to verify access. This statement has a GLOBAL option, which is the default, that makes all instances verify access to online datafiles. It also has a LOCAL option that makes the current instance verify access.
ALTER SYSTEM CHECK DATAFILES makes the online datafiles available to the instance or instances for which access is verified.
Oracle cannot recover from instance failure or media failure unless the instance that performs recovery can verify access to all required online datafiles.
Oracle automatically maps absolute file numbers to relative file numbers. Use of OPS does not affect these values. Query the V$DATAFILE view to see both numbers for your datafiles.
For more information, please see Chapter 17, "Using Free List Groups to Partition Data", "Access to Datafiles for Instance Recovery", and "Setting GC_FILES_TO_LOCKS: PCM Locks for Each Datafile". For more information about relative file numbers, see Oracle8i Concepts.
In OPS, 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 using the ALTER DATABASE ADD LOGFILE command with the THREAD clause. A public thread is a redo log created using the ALTER DATABASE ADD LOGFILE but without specifying a THREAD clause.
If the THREAD initialization parameter is specified, the instance starting up acquires the thread identified by that value as a private thread. If THREAD has the default of zero, 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". A multiplexed redo log consists of two or more groups of files and all members of a group are written to concurrently when that group is active. Figure 6-1 shows the threads of redo for three instances of OPS.
Group numbers must be unique within the database, therefore they are not unique within a thread. However, the order of assigning groups to threads, and threads to instances is arbitrary.
For example, although in Figure 6-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 per thread, we recommend that all threads be configured to a common standard to facilitate administration.
Different instances of OPS can have different degrees of mirroring, or different numbers of members per group. The different instances can also have different numbers of groups. For example, one instance could have three groups with two members per group, a second instance could have four non-multiplexed log files, and a third instance could have two groups with four members per group. While such a configuration may be inconvenient to administer, it may be necessary to achieve the full potential of the system.
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 log switch, information is written to the control file that can be used to identify the filled group and its thread number after it has been archived.
The number of redo log files about which the control file can keep information is limited by the value of the MAXLOGHISTORY option of the CREATE DATABASE statement. Only one member per group is needed. In OPS, set the value of MAXLOGHISTORY higher than you normally would in single instance Oracle. This is because in OPS, the history of multiple redo log files must be tracked.
Each instance of OPS has a dictionary cache, or row cache, containing data dictionary information in its SGA. The data dictionary structure is the same for Oracle instances in OPS as for instances in exclusive mode. Instance locks coordinate the data dictionary activity of multiple instances.
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.
OPS allows users on multiple instances to generate unique sequence numbers with minimal cooperation or contention among instances. Instance locks coordinate sequences across instances in OPS.
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.
For more information about the CREATE SEQUENCE and CYCLE options, please refer to the Oracle8i SQL Reference.
The CACHE option of CREATE SEQUENCE pre-allocates 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. 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 an OPS environment.
OPS does not support the CACHE option with the ORDER option of CREATE SEQUENCE when the database is mounted in parallel 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.
This section describes rollback segments as they relate to OPS.
Rollback segments contain information that Oracle requires to maintain read consistency and to be able to undo changes made by transactions that roll back or abort. Each instance in OPS shares use of the SYSTEM rollback segment and requires at least one dedicated rollback segment per instance.
Both private and public rollback segments can be acquired at instance startup and used exclusively by the acquiring instance until taken offline or when the acquiring instance is shutdown 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 in exclusive mode.
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.
OPS needs 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. 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 extents the same size by specifying identical values for the storage parameters INITIAL and NEXT.
The data dictionary view DBA_ROLLBACK_SEGS shows each rollback segment's name, segment ID number, and owner (PUBLIC or other).
These initialization parameters control rollback segment use:
specifies the names of rollback segments that the instance acquires at startup.
reserves additional instance locks to reduce contention for blocks containing rollback entries. In particular, it reserves instance locks for deferred rollback segments, that contain rollback entries for transactions in tablespaces that were taken offline.
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 allows 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 shutdown 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. Acquiring a public rollback segment can also be made at startup if TRANSACTIONS and TRANSACTIONS_PER_RBS are not properly set.
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, the rollback segment can be taken offline and brought back online or "moved" 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.
Once a public rollback segment is acquired by an instance, it is then used exclusively by that instance.
Once created, both public and private rollback segments can be brought online using the ALTER ROLLBACK SEGMENT command.
When an instance starts, it uses the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK initialization parameters to determine how many rollback segments to acquire as shown in the following equation:
The total_rollback_segments_required number is rounded up.
At startup, an instance attempts to acquire rollback segments by: