4.13 Maintaining read consistency in RAC
|
| < Day Day Up > |
|
In order to provide users with a consistent image of the rows while other users are modifying the rows and have not completed their operation, read consistent images of data records are required. A consistent image of the row provides users with access to the previous image before users have made an update or delete operation, but have not yet completed the operation by performing a commit or rollback. Read consistent images of rows can be provided in two different ways:
-
Rollback segments (traditional)
-
Undo management (new)
-
Rollback segment method
-
Undo tablespace method
-
| Oracle 9i | New Feature: Oracle 9i has introduced undo management, which replaces the functionality provided by the traditional method of rollback segments. While either of these options could be implemented at a time, rollback segments is a deprecated feature and is provided for backward compati bility only. Oracle has been encouraging the usage of undo tablespace. |
4.13.1 Traditional rollback segments
Rollback segments contain information for maintenance of read consistent images of data in an instance. When a user process modifies a record in the database, the rollback segment maintains the record's past image and current image. The past image is maintained to provide a consistent view of data to user processes that are accessing the row from the database. It is also maintained so that when the user decides that he or she does not require to make this change it could replace the current image with the original record by allowing an undo or rollback operation.
Rollback segments under a RAC configuration are a little different compared to a single instance implementation, as a RAC configuration allows two different types of rollback segment implementation, private and public. Because the rollback segments contain information pertaining to data modified by users connected to that specific instance, the reasons for such an instance-specific relationship are quite understandable.
As the name suggests, private rollback segments are specific to an instance which they were assigned to during creation and instance startup. Private rollback segments are created using the keyword PRIVATE clause with the CREATE ROLLBACK SEGMENT command. Private rollback segments are private to an instance and are not shareable in the sense that, without DBA intervention, other instances cannot gain access to these segments under any circumstances.
On the other hand, public rollback segments are created in a generic nature and are shareable across instances. Public rollback segments are created using the keyword PUBLIC clause with the CREATE ROLLBACK SEGMENT command. These segments are generic in nature and could be utilized by any instance, provided they are allocated to that instance during startup or allocated to it through the intervention of a DBA.
While implementing the rollback segment feature in a RAC implementation, it is advisable to create a larger number of private rollback segments and a smaller number of public rollback segments. This will guarantee a balanced number of rollback segments to all the instances. If sufficient private rollback segments are not present and there is a crash of all the members of the cluster, when the instances start up under recovery mode and find a number of public rollback segments, Oracle would automatically assign all rollback segments to this instance to complete the recovery operations more quickly. Consequently, when other instances start up, there would be no rollback segments left for use during startup and this could cause the instance to crash.
If, instead, there were a large number of private rollback segments, every instance would be guaranteed to get hold of those rollback segments.
An availability of private and public rollback segments would provide the best of both worlds. During instance startup, every instance is guaranteed the minimum of rollback segments allocated to it. If additional rollback segments are required (like in the case of a recovery operation) the instance will attempt to acquire the required number of rollback segments from the pool of available public rollback segments for the recovery operation. This allocation of public rollback segments is based on a first come, first served basis. Once allocated, it remains attached to that instance for the life of the instance or until intervention of a DBA.
Rollback segments are assigned using the parameter ROLLBACK_SEGMENTS. The number of rollback segments required by each instance is based on a formula and depends on the load of transactions on the system as defined by the two parameters TRANSACTIONS and TRANSACTION_PER_ROLLBACK_SEGMENT. Using the values in these parameters, Oracle arrives at the number of rollback segments using the formula below:
TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT
As an example, if the number of TRANSACTIONS defined in the parameter file is of the value 187 and the TRANSACTIONS_PER_ROLLBACK_SEGEMENT is defined as 5, then the number of rollback segments would be 187/5, which equals 37. So, if during instance startup, Oracle finds that the parameter ROLLBACK_SEGMENTS has not been set, it would use the above formula to allocate that many rollback segments from the pool of public rollback segments.
| Note | If the TRANSACTIONS parameter is not defined in the parameter file it is dynamically generated using the formula: 1.1 times the number of sessions. This should be the preferred approach of defining the number of transactions and would provide a balanced approach to the rollback segment requirements. |
The other parameter that affects the rollback segments is the MAX_ROLLBACK_SEGMENTS. This parameter defines the maximum number of rollback segments that could be allocated to an instance. This is also a user definable parameter. However, if no value is specified, the value equals the number of rollback segments defined under the ROLLBACK_SEGMENTS parameter or equals the value arrived at based on the formula defined earlier.
Figure 4.11 represents the rollback segment extent allocation. Rollback segment extents are used in a circular fashion with transactions moving from one extent to the next after the current extent is filled. To manage transactions, a ''head and tail'' mechanism is employed such that the head is the pointer location of the current uncommitted transaction record and the tail is the pointer location to the oldest uncommitted transaction record. The RBS mechanism will always try to reuse the next extent in the ring unless there is an active transaction. If an active transaction is found, a new extent is allocated and brought into the ring. Only after a rollback or commit operation will the transaction space be available for other transactions.
Figure 4.11: Rollback segment extent management.
| Note | The feature of rollback segments is a deprecated feature starting with Oracle 9i and is provided for backward compatibility; it is advisable, based on Oracle recommendations, to use the undo tablespace option instead. |
4.13.2 Undo management
This is a new feature introduced in Oracle 9i that replaces the existing rollback segment feature. Oracle has provided two methods of undo manage ment: the rollback segment, or the manual undo management method, and the undo tablespace, or the automatic undo management method.
Rollback segment method
This method is an extension of the traditional rollback segment approach available in previous versions of Oracle. Under this method, all the normal requirements of the traditional rollback segment approach discussed in the previous section apply. While the entire set of rollback segment related parameters could be used, an additional parameter called UNDO_MANAGEMENT is set to a value of MANUAL.
| Oracle 10g | New Feature: The rollback segment method is absolute with Oracle version 10g. The only undo management method available is the undo tablespace method. |
Undo tablespace method
This method is also referred to as the automatic undo management (AUM) approach. By internalizing the interface of these segments, this simplifies the process of managing undo segments (undo segments replace the rollback segments in prior versions). This also reduces the number of parameters traditionally required for the rollback segment management.
Advantages of the AUM feature are:
-
Eliminates DBA interface traditionally present with rollback segment implementations
-
Considerably lowers the number of parameters required to manage undo segments, thus making it easier for the DBA
-
Better manages the undo segments, reducing the occurrence of various ORA-15xx errors that generally appear with rollback segments (e.g., ORA-1555 and ORA-1561)
The undo tablespace method brings about changes to the architecture in which undo is being handled both physically and structurally. The undo segments are more transaction aware in that an undo segment of appropriate size is allocated based on the type and length of the transaction; the introduction of the concept of dynamic undo segments makes this possible. To the extent that they also contain transaction tables and undo blocks, dynamic undo segments are very much like rollback segments. However, the manner in which undo segments are managed is quite different from the previous versions.
The AUM feature is enabled by setting the following parameters:
*.UNDO_MANAGEMENT = AUTO RAC1.UNDO_TABLESPACE = (undo tablespace name)
The undo tablespace is created with the undo tablespace clause. This clause creates the tablespace as a locally managed tablespace and its space- extent is managed via bitmaps that reside in the file header. The advantage of locally managed tablespaces is that space transaction and management is performed using bitmaps versus performing expensive recursive calls to maintain these values in the data dictionary. This is supportive of information in previous chapters.
Under this method of undo management, the traditional rollback segment methods, or parameters, do not apply. However, under this method, the parameter UNDO_MANAGEMENT is set to a value of AUTO. When set to this value, all undo management activity is automatically performed by Oracle. With this method, Oracle will provide all the functionality provided by the traditional and rollback segment method, such as providing the opportunities for the user to perform rollback operations, and providing a read consistent image of the data, and data for recovery operations.
The management of undo segments is very similar to the rollback segment approach; when the instance starts it will bind an undo tablespace of its own. Therefore, as in the case of rollback segments (private and public), it is required that each instance maps to its own undo tablespace.
At instance startup each undo tablespace will contain 10 undo segments. The number of additional segments brought online during instance startup is based on the SESSIONS parameter. Oracle allocates approximately one undo segment for every five sessions. These are sized according to the auto-allocate algorithm for locally managed tablespaces. The basic algorithm is that the first 16 extents are 64 KB in size. During subsequent allocation the next 63 extents are 1 MB, the next 120 extents are 8 MB and all additional extents are 64 MB.
Under this approach the DBAs will notice entries in the alert log files during allocation and deallocation of undo segments:
Fri Jul 26 00:52:01 2002 Undo Segment 1 Onlined Undo Segment 2 Onlined Undo Segment 3 Onlined Undo Segment 4 Onlined Undo Segment 5 Onlined Undo Segment 6 Onlined Undo Segment 7 Onlined Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Fri Jul 26 00:52:01 2002
The above output is from the alert log file recorded on instance startup. This output indicates that for every undo tablespace at least 10 segments are allocated at instance startup.
This method of undo management provides quite a few new features or options. One such feature is to go back into history to reconstruct a transaction, and this is enabled by the parameter UNDO_RETENTION. Setting this parameter allows the DBAs to go back into history to retrieve any specific data as it appeared at that point in time. The parameter is set in seconds. Depending on how far back visibility of data is required, this parameter is set that many seconds backwards. For example, if a 24-hour visibility of the past information is required, the parameter is set to a value of 86,400.
The following query queries the undo statistics related information from the data dictionary view:
SQL> SELECT INST_ID, BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, MAXQUERYLEN FROM GV$UNDOSTAT WHERE MAXQUERYLEN >0; INST_ID BEGIN_TIME END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN ------ -------------------- -------------------- ------ ------- ----------- 2 26-JUL-2002 17:13:54 26-JUL-2002 17:23:54 5 0 3 2 26-JUL-2002 03:13:54 26-JUL-2002 03:23:54 5 0 2 2 26-JUL-2002 01:03:54 26-JUL-2002 01:13:54 5 3 2 2 26-JUL-2002 00:43:54 26-JUL-2002 00:53:54 5 5 13 1 28-JUL-2002 16:11:36 28-JUL-2002 16:21:36 1 0 3 1 28-JUL-2002 10:51:36 28-JUL-2002 11:01:36 1 2 2 1 26-JUL-2002 22:21:36 26-JUL-2002 22:31:36 1 0 3 1 26-JUL-2002 14:41:36 26-JUL-2002 14:51:36 1 0 2 1 26-JUL-2002 00:51:36 26-JUL-2002 01:01:36 1 1 10
While under the traditional and rollback segment method Oracle managed rollback segments using the parameters, TRANSACTIONS and TRANSACTION_PER_ROLLBACK_SEGMENT, when using automatic undo management (AUM), Oracle ignores settings for the TRANSACTIONS parameter. This is due to the fact that Oracle dynamically allocates transaction objects from the SGA for AUM. Also, when using the AUM, Oracle uses the SYSTEM rollback segment. There is only one SYSTEM rollback segment for each database and it resides in the SYSTEM tablespace. Oracle automatically creates it during database creation when the CREATE DATABASE statement is issued.
In RAC environments also, all instances use the same SYSTEM rollback segment and are used for performing system transactions such as creation of transaction tables. There is no maintenance required on this rollback segment.
The output below is a dump of the undo header block. As with any block header, this block header also contains the generic fields such as the Db ID and the database name that helps identify the database. The next few columns indicate the file characteristics such as the block size and the file type.
The next section is the extent control header and contains the extent header information. The undo segment header carries around the high water mark information, which helps efficient management when a user performs a rollback operation. Oracle will ensure that the high water mark is not exceeded.
FILE HEADER: Software vsn=153092096=0x9200000,Compatibility Vsn=134217728=0x8000000 Db ID=3598885999=0xd682a46f, Db Name='PRODDB' Activation ID=0=0x0 Control Seq=2536=0x9e8, File size=358400=0x57800 File Number=2, Blksiz=8192, File Type=3 DATA Dump all the blocks in range: buffer rdba: 0x00800099 (2/153) scn: 0x0000.0898b2d4 seq: 0x01 flg: 0x04 tail: 0xb2d42601 frmt: 0x02 chkval: 0x3639 type: 0x26=KTU SMU HEADER BLOCK Extent Control Header ------------------------------------------------------ Extent Header:: spare1: 0 spare2: 0 #extents: 6 #blocks: 527 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00800e7b ext#: 5 blk#: 114 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 5 Unlocked Map Header:: next 0x00000000 #extents: 6 obj#: 0 flag: 0x40000000 Extent Map ------------------------------------------------------ 0x0080009a length: 7 0x00800071 length: 8 0x00800189 length: 128 0x00800589 length: 128 0x00800309 length: 128 0x00800e09 length: 128 Retention Table ------------------------------------------------------ Extent Number:0 Commit Time: 1040025723 Extent Number:1 Commit Time: 1040025723 Extent Number:2 Commit Time: 1040025723 Extent Number:3 Commit Time: 1040031124 Extent Number:4 Commit Time: 1040046425 Extent Number:5 Commit Time: 1040046425 TRN CTL::seq:0x08fb chd:0x0026 ctl:0x000d inc:0x00000000 nfb:0x0002 mgc:0x8201 xts:0x0068 flg:0x0001 opt:2147483646 (0x7ffffffe) uba:0x00800e7a.08fb.27 scn:0x0000.08987c03 Version: 0x01 FREE BLOCK POOL:: uba: 0x00800e7a.08fb.27 ext: 0x5 spc: 0xddc uba: 0x00800e7b.08fb.01 ext: 0x5 spc: 0x1a3c uba: 0x00000000.08e5.22 ext: 0x4 spc: 0x10f2 uba: 0x00000000.063c.01 ext: 0x2 spc: 0x1fa0 uba: 0x00000000.063c.01 ext: 0x2 spc: 0x1fa0 TRN TBL:: index state cflags wrap# uel scn dba ----- ----- ------ ------ ------ -------------- ---------- 0x00 9 0x00 0x33df 0x0001 0x0000.08987c3c 0x00800e6d 0x01 9 0x00 0x33d6 0x0013 0x0000.08987c3f 0x00800e70 0x02 9 0x00 0x33e5 0x0005 0x0000.0898920f 0x00800e7a parent-xid nub stmt_num ------------------- ----------- ----------- 0x0000.000.00000000 0x00000003 0x00000000 0x0000.000.00000000 0x00000003 0x00000000 0x0000.000.00000000 0x00000001 0x00000000
The undo segment header also contains the transaction table. Oracle ensures that before creation of an undo record, the transaction is created in the transaction table. The transaction table contains slots (or rows), which keeps track of all active transactions within that rollback segment. The rollback segment header is similar in structure to a normal segment header (table, index, or cluster segments). Additional structures in the undo segment header include the free blocks pool, the transaction table, and new in 9i is the retention table.
The transaction table holds a pointer to the head and tail of the committed transaction table slots. Each inactive entry in the transaction table points to the next committed (inactive) slot. They are ordered by commit SCN, starting at the oldest transaction to have committed. If the committed list is empty after several attempts at traversing it, the transaction table remains full and an error is reported. This indicates that there are not enough undo segments for the amount of concurrent transactions in the database. However, since AUM tries to allocate each concurrent transaction to their own undo segment, this should be extremely rare to see. If this error does occur, the solution is to increase the size of the undo tablespace to allow more undo segments to be created. Each time the transaction table slot is reused, the slot wrap number is incremented. When the slot has been reused 4,294,967,295 (0xffffffff) times[2] an ORA-1558[3] is reported when trying to increase the wrap number one more time. This should also be extremely rare due to the AUM algorithm of undo segment allocation.
Figure 4.12 illustrates the process followed during allocation of slots in the transaction table.
Figure 4.12: Transaction table slot allocation.
The transaction table also contains a data block address of the last undo block that was used. Within the header of each undo block is a record index, similar to the way that a data block has a row index. The record index stores the byte offsets for each undo record stored in the block. There is also a structure that indicates the last record number in the block that was created for an active transaction. It is used in a multitude of ways:
-
Deferred block cleanout (also known as delayed logging block cleanout) occurs because on issuing a commit, the data blocks are not fully updated to show that the commit has occurred. The next process that accesses the block for consistent read (CR) or updates carries out the block updates for this. The transaction table is used during this process to determine when the commit originally occurred.
-
The transaction table is needed to determine if a current transaction that is locking a row has committed or is still active. If the transaction is committed, a block cleanout is carried out. Otherwise there is a wait for the active transaction to complete (commit or rollback).
-
During rollback the undo chain is traversed in reverse order to which it was created so that the original changes made to the data blocks can be undone. The last (most recent) undo block added to the undo chain is named in the transaction table entry, along with the number of undo blocks used.
-
During recovery the transaction tables from each rollback segment are read. The slots that contain active transactions are then used to undo the transaction. The transaction undo mechanism is the same as described in the previous option. Rollback of all transactions is not done at startup. The blocks needed for successful startup will be rolled back, and the rest will be left for SMON or for any process that accesses a block that needs to be rolled back. The processes will roll back the whole transaction and not just the data block.
Free block pool table
Within each undo segment header there is free block pool table. Blocks are stored in this pool for reuse (once the last transaction has committed) and to avoid wasted space in the undo segment. When a transaction commits, the last block that was used, if its free space is greater than or equal to 400 bytes, is placed in the pool. If all five slots are filled, current entries can be replaced if it has less free space or is from a non-current extent. When an undo segment fails during an extension, the last block of the last used extent will also be put into the free block pool, assuming it has enough free space.
Retention table
Undo retention allows for better control on the length of time undo information remains in the undo segments. This should help in reducing ORA-1555s. By setting the init<SID>.ora parameter UNDO_RETENTION to a number of seconds, Oracle attempts to not reuse each extent until that number of seconds has passed since the last transaction using each extent has committed. This is controlled by a new structure in the undo segment header and extent map blocks called the retention table. Each time a transaction commits, the retention table is updated with a time value (hundredths of seconds from a known starting point) for all extents that transaction used. During undo segment shrinkage or extent stealing, the retention times are compared with the current time (plus the UNDO_RETENTION time) to decide if the extents have expired.
When allocating an undo block, Oracle tries to use space available in the transaction's undo segment, and when it determines that no space is available, it searches for expired space. If no expired space is available, extents that have not yet expired will be reused. Under this situation the user could potentially encounter an ORA-1555 error.
[2]4,294,967,295 is the maximum value defined internally by Oracle.
[3]ORA-1558: "Out of transaction Ids in rollback segment %s."
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 174