Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes how to manage undo space, either by using undo tablespaces or by using rollback segments. It contains the following topics:
See Also:
|
Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo.
Undo records are used to:
ROLLBACK statement is issuedWhen a rollback statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
Historically, Oracle has used rollback segments to store undo. Space management for these rollback segments has proven to be quite complex. Oracle now offers another method of storing undo that eliminates the complexities of managing rollback segment space, and enables DBAs to exert control over how long undo is retained before being overwritten. This method uses an undo tablespace. Both of these methods of managing undo space are discussed in this chapter.
You cannot use both methods in the same database instance, although for migration purposes it is possible, for example, to create undo tablespaces in a database that is using rollback segments, or to drop rollback segments in a database that is using undo tablespaces. However, you must shut down and restart your database in order to effect the switch to another method of managing undo.
| See Also:
Oracle9i Database Concepts for more information about undo and managing undo space |
If you use the rollback segment method of managing undo space, you are said to be operating in the manual undo management mode. If you use the undo tablespace method, you are operating in the automatic undo management mode. You determine the mode at instance startup using the UNDO_MANAGEMENT initialization parameter.
The following initialization parameter setting causes the STARTUP command to start an instance in automatic undo management mode:
UNDO_MANAGEMENT = AUTO
An undo tablespace must be available, into which Oracle will store undo records. The default undo tablespace is created at database creation, or an undo tablespace can be created explicitly. The methods of creating an undo tablespace are explained in "Creating an Undo Tablespace"
When the instance starts up, Oracle automatically selects for use the first available undo tablespace. If there is no undo tablespace available, the instance starts, but uses the SYSTEM rollback segment. This is not recommended in normal circumstances, and an alert message is written to the alert file to warn that the system is running without an undo tablespace.
You can optionally specify at startup that you want an Oracle instance to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter. For example:
UNDO_TABLESPACE = undotbs_01
In this case, if you have not already created the undo tablespace (in this example, undotbs_01), the STARTUP command will fail. The UNDO_TABLESPACE parameter can be used to assign a specific undo tablespace to an instance in an Oracle Real Application Clusters environment.
The following is a summary of the initialization parameters for automatic undo management mode:
If the initialization parameter file contains parameters relating to manual undo management, they are ignored.
To learn how to manage undo tablespaces, see "Managing Undo Tablespaces".
| See Also:
Oracle9i Database Reference for complete descriptions of initialization parameters used in automatic undo management mode |
The following initialization parameter setting causes the STARTUP command to start an instance in manual undo management mode:
UNDO_MANAGEMENT = MANUAL
If the UNDO_MANAGEMENT initialization parameter is not specified, the instance starts in manual undo management mode. If an UNDO_TABLESPACE initialization parameter is found, it is ignored. For DBAs who want to run their databases in manual undo management mode, their existing initialization parameter file can be used without any changes.
When the instance starts up, it brings online a number of rollback segments as determined by either of the following:
ROLLBACK_SEGMENTS initialization parameterTRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parametersThe following is a summary of initialization parameters that can be specified with manual undo management mode.
To learn how to manage rollback segments, see "Managing Rollback Segments".
| See Also:
Oracle9i Database Reference for complete descriptions of initialization parameters used in manual undo management mode |
Oracle strongly recommends operating in automatic undo management mode. The database server can manage undo more efficiently, and automatic undo management mode is less complex to implement and manage. The following sections guide you in the management of undo tablespaces:
| See Also:
Oracle9i SQL Reference for complete descriptions of the SQL statements discussed in the following sections |
There are two methods of creating an undo tablespace. The first method creates the undo tablespace when the CREATE DATABASE statement is issued. This occurs when you are creating a new database, and the instance is started in automatic undo management mode (UNDO_MANAGEMENT = AUTO). The second method is used with an existing database. It uses the CREATE UNDO TABLESPACE statement.
You cannot create database objects in an undo tablespace. It is reserved for system-managed undo data.
You can create a specific undo tablespace using the UNDO TABLESPACE clause of the CREATE DATABASE statement. But, this clause is not required.
If the UNDO TABLESPACE clause is not specified and the CREATE DATABASE statement is executed in automatic undo management mode, a default undo tablespace is created with the name SYS_UNDOTBS. This tablespace is allocated from the default set of files used by the CREATE DATABASE statement and its attributes are determined by Oracle. The initial size is 10M, and it is autoextensible. This method of creating an undo tablespace is only recommended to users who do not have any specific requirements for allocation of undo space.
The following statement illustrates using the UNDO TABLESPACE clause in a CREATE DATABASE statement. The undo tablespace is named undotbs_01 and one datafile, /u01/oracle/rbdb1/undo0101.dbf, is allocated for it.
CREATE DATABASE rbdb1 CONTROLFILE REUSE . . . UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';
If the undo tablespace cannot be created successfully during CREATE DATABASE, the entire CREATE DATABASE operation fails. You must clean up the database files, correct the error and retry the CREATE DATABASE operation.
The CREATE UNDO TABLESPACE statement is the same as the CREATE TABLESPACE statement, but the UNDO keyword is specified. Oracle determines most of the attributes of the undo tablespace, you can specify only the DATAFILE clause.
This example creates the undotbs_02 undo tablespace:
CREATE UNDO TABLESPACE undotbs_02 DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
Undo tablespaces are altered using the ALTER TABLESPACE statement. However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions:
These are also the only attributes you are permitted to alter.
If an undo tablespace runs out of space, or you want to prevent it from doing so, you can add more files to it or resize existing datafiles.
The following example adds another datafile to undo tablespace undotbs_01:
ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
You can use the ALTER DATABASE ... DATAFILE statement to resize or extend a datafile.
Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:
DROP TABLESPACE undotbs_01;
An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPACE drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo tablespace if undo information is needed by some existing queries.
DROP TABLESPACE for undo tablespaces behaves like DROP TABLESPACE ... INCLUDING CONTENTS. All contents of the undo tablespace are removed.
You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.
The following statement effectively switches to a new undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its undo tablespace.
If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:
The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).
If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This can be used, for example, to unassign an undo tablespace in the event that you want to revert to manual undo management mode.
The following example unassigns the current undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = '';
Oracle's Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive, UNDO_POOL, allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group).
You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the redo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.
When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.
Committed undo information normally is lost when its undo space is overwritten by a newer transaction. But for consistent read purposes, long running queries might require old undo information for undoing changes and producing older images of data blocks. The initialization parameter, UNDO_RETENTION, provides a means of explicitly specifying the amount of undo information to retain. With a proper setting, long running queries can complete without risk of receiving the "snapshot too old" error.
Retention is specified in units of seconds, for example 500 seconds. It is persistent and can survive system crashes. That is, undo generated before an instance crash, is retained until its retention time has expired even across restarting the instance. When the instance is recovered, undo information will be retained based on the current setting of the UNDO_RETENTION initialization parameter.
The UNDO_RETENTION parameter can be set initially in the initialization parameter file that is used by the STARTUP process:
UNDO_RETENTION = 10
The UNDO_RETENTION parameter value can be changed dynamically at any time using the ALTER SYSTEM command:
ALTER SYSTEM SET UNDO_RETENTION = 5;
The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space for the active transactions. If an active transaction requires undo space and the undo tablespace does not have available space, the system starts reusing unexpired undo space. Such action can potentially cause some queries to fail with the "snapshot too old" error.
If the UNDO_RETENTION initialization parameter is not specified, the default value is 900 seconds.
The retention period for undo information is an important factor in the execution of flashback queries. Oracle's flashback query feature enables you to see a consistent version of the database as of a specified time in the past. You can execute queries, or even applications, as of a previous time in the database. The Oracle supplied DBMS_FLASHBACK package implements this functionality at the session level. At the object level, flashback queries use the AS OF clause of the SELECT statement to specify the previous point in time for which you wish to view data.
The retention period determines how far back in time a database version can be established for flashback queries. Specifically, you must choose an undo retention interval that is long enough that it enables you to construct a snapshot of the database for the oldest version of the database that you are interested in. For example, if an application requires that a version of the database be available reflecting its content 12 hours previously, then UNDO_RETENTION must be set to 43200.
When using automatic undo management, the RETENTION value for LOB columns is set to the value of UNDO_RETENTION.
See Also:
|
Given a specific UNDO_RETENTION parameter setting and some system statistics, the amount of undo space required to satisfy the undo retention requirement can be estimated using the following formula:
UndoSpace = UR * UPS + overhead
where:
UNDO_RETENTION in secondsAs an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows:
(2 * 3600 * 200 * 4K) = 5.8GBs.
Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view.
This section lists views that are useful for viewing information about undo space in the automatic undo management mode. In addition to views listed here, you can obtain information from the views available for viewing tablespace and datafile information.
The following views are available for obtaining undo space information:
| See Also:
Oracle9i Database Reference for complete descriptions of the views used in automatic undo management mode |
The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, and length of queries in the instance.
Each row in the view contains statistics collected in the instance for a ten-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.
The following example shows the results of a query on the V$UNDOSTAT view.
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT;
The results are:
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON -------------------- -------------------- ------- -------- -------- ------ 07/28/2000 18:26:28 07/28/2000 18:32:13 2 709 55 2 07/28/2000 18:16:28 07/28/2000 18:26:28 2 448 12 2 07/28/2000 14:36:28 07/28/2000 18:16:28 1 0 0 0 07/28/2000 14:26:28 07/28/2000 14:36:28 1 1 1 1 07/28/2000 14:16:28 07/28/2000 14:26:28 1 10 1 1 ...
The above example shows how undo space is consumed in the system for the previous 24 hours from the time 18:32:13.
If you choose to use rollback segments to store undo, the following sections guide you in their management:
This section describes guidelines to consider before creating or managing the rollback segments of your databases, and contains the following topics:
| See Also:
Oracle9i Database Concepts for additional information about rollback segments |
Using multiple rollback segment distributes rollback segment contention across many segments and improves system performance. Oracle assigns transactions to rollback segments in round-robin fashion. This results in a fairly even distribution of the number of transactions for each rollback segment. It is also possible to assign a transaction to a specific rollback segment, but this is usually not done.
When a database is created, a single rollback segment named SYSTEM is created in the SYSTEM tablespace. This rollback segment is used in special ways by the Oracle database server, and is not intended for general use. Before you write to objects created in non-SYSTEM tablespaces, you must create and bring online at least one additional rollback segment in a non-SYSTEM tablespace.
At startup, an instance always acquires (brings online) the SYSTEM rollback segment in addition to any other rollback segments it needs or is directed to acquire. When there are multiple rollback segments, Oracle tries to use the SYSTEM rollback segment only for special system transactions and distributes user transactions among other rollback segments. If there are too many transactions for the non-SYSTEM rollback segments, Oracle uses the SYSTEM segment; plan your number of rollback segments to avoid this.
There are a couple of options for activating multiple rollback segments when you start up an instance:
TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameters in your initialization parameter fileROLLBACK_SEGMENTS initialization parameterThese options are discussed in other guidelines that follow.
There is a limit on the number of rollback segments that can be open simultaneously. This limit is set by the MAX_ROLLBACK_SEGMENTS initialization parameter. Ensure that this parameter is set to a value higher than the number of rollback segments specified in the ROLLBACK_SEGMENTS initialization parameter.
| See Also:
Oracle9i Database Reference for additional information about the |
A private rollback segment must be acquired explicitly by an instance. This can occur at database startup when the rollback segments name is included in the ROLLBACK_SEGMENTS parameter in the initialization parameter file. A private rollback segment can also be acquired by specifically bringing it online by manually issuing the statement to do so. In an Oracle Real Application Clusters environment, private rollback segments allow an instance to acquire specific rollback segments.
Public rollback segments form a pool of rollback segments that any instance requiring a rollback segment can use. An instance decides how many of these rollback segments to automatically acquire at instance startup based on the values of the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameters. Public rollback segments can be shared between Oracle Real Application Cluster instances.
If you are not using the Oracle9i Real Application Clusters feature, private and public rollback segments function similarly.
When many transactions are concurrently proceeding, they simultaneously generate rollback information. A way of specifying that an appropriate number of rollback segments be acquired automatically at instance startup is to include the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameters. You must also be using public rollback segments.
You can indicate the number of concurrent transactions you expect for the instance with the initialization parameter TRANSACTIONS, and the number of transactions you expect each rollback segment will need to handle with the initialization parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT. Then, when an instance opens a database, it attempts to acquire at least n rollback segments, where n=TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT. When creating your database, or subsequently, you should have created at least n public rollback segments.
If you choose to use private rollback segments, these rollback segments will be acquired automatically by an instance at startup if you specify the rollback segments by name in the ROLLBACK_SEGMENTS initialization parameter in the instance's parameter file.
If you use both private and public rollback segments the following might occur. An instance acquires all the rollback segments listed in the ROLLBACK_SEGMENTS initialization parameter, even if more than TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT segments are specified.
Total rollback segment size should be set based on the size of the most common transactions issued against a database. In general, short transactions experience better performance when the database has many smaller rollback segments, while long-running transactions, like batch jobs, perform better with larger rollback segments. Generally, rollback segments can handle transactions of any size easily. However, in extreme cases when a transaction is either very short or very long, a user might want to use an appropriately sized rollback segment.
If a system is running only short transactions, rollback segments should be small so that they are always cached in main memory. If the rollback segments are small enough, they are more likely to be cached in the SGA according to the LRU algorithm, and database performance is improved because less disk I/O is necessary. The main disadvantage of small rollback segments is the increased likelihood of the error "snapshot too old" when running a long query involving records that are frequently updated by other transactions. This error occurs because the rollback entries needed for read consistency are overwritten as other update entries wrap around the rollback segment. Consider this issue when designing an application's transactions, and make them short atomic units of work so that you can avoid this problem.
In contrast, long-running transactions work better with larger rollback segments, because the rollback entries for a long-running transaction can fit in preallocated extents of a large rollback segment.
When database systems applications concurrently issue a mix of very short and very long transactions, performance can be optimized if transactions are explicitly assigned to a rollback segment based on the transaction/rollback segment size. You can minimize dynamic extent allocation and truncation for rollback segments. This is not required for most systems and is intended for extremely large or small transactions.
To optimize performance when issuing a mix of extremely small and large transactions, make a number of rollback segments of appropriate size for each type of transaction (such as small, medium, and large). Most rollback segments should correspond to the typical transactions, with a fewer number of rollback segments for the atypical transactions. Then set OPTIMAL for each such rollback segment so that the rollback segment returns to its intended size if it has to grow.
You should tell users about the different sets of rollback segments that correspond to the different types of transactions. Often, it is not beneficial to assign a transaction explicitly to a specific rollback segment. However, you can assign an atypical transaction to an appropriate rollback segment created for such transactions. For example, you can assign a transaction that contains a large batch job to a large rollback segment.
When a mix of transactions is not prevalent, each rollback segment should be 10% of the size of the database's largest table because most SQL statements affect 10% or less of a table. A rollback segment of this size should be sufficient to store the actions performed by most SQL statements.
Generally speaking, you should set a high MAXEXTENTS for rollback segments. This allows a rollback segment to allocate subsequent extents as it needs them.
Each rollback segment's total allocated space should be divided among many equally sized extents. In general, optimal rollback I/O performance is observed if each rollback segment for an instance has 10 to 20 equally sized extents.
After determining the desired total initial size of a rollback segment and the number of initial extents for the segment, use the following formula to calculate the size (s) of each extent of the rollback segment:
s = T / n
where:
s = calculated size, in bytes, of each extent initially allocated
T = total initial rollback segment size, in bytes
n = number of extents initially allocated
After s is calculated, create the rollback segment and specify the storage parameters INITIAL and NEXT as s, and MINEXTENTS to n. PCTINCREASE cannot be specified for rollback segments and therefore defaults to 0. Also, if the size s of an extent is not an exact multiple of the data block size, it is rounded up to the next multiple.
You should carefully assess the kind of transactions the system runs when setting the OPTIMAL parameter for each rollback segment. For a system that executes long-running transactions frequently, OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently. Also, for a system that executes long queries on active data, OPTIMAL should be large to avoid "snapshot too old" errors. OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance.
The V$ROLLNAME and V$ROLLSTAT dynamic performance views can be monitored to collect statistics useful in determining appropriate settings for OPTIMAL. See "Monitoring Rollback Segment Statistics".
If possible, create one or more tablespaces specifically to hold all rollback segments. This way, all rollback segment data is stored separately from other types of data. Creating this "rollback segment" tablespace can provide the following benefits:
To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege. You use the CREATE ROLLBACK SEGMENT statement. The tablespace to contain the new rollback segments must be online. Rollback segments are usually created as part of the database creation script or process, but you may add more at a later time.
The following topics relating to creating rollback segments are contained in this section:
The following statement creates a rollback segment named rbs_02 in the rbsspace tablespace, using the default storage parameters of that tablespace. Since this is not an Oracle Real Application Clusters environment, it is not necessary to specify PRIVATE or PUBLIC. The default is PRIVATE.
CREATE ROLLBACK SEGMENT rbs_02 TABLESPACE rbsspace;
| See Also:
Oracle9i SQL Reference for exact syntax, restrictions, and authorization requirements for the SQL statements used in managing rollback segments |
New rollback segments are initially offline. You must issue an ALTER ROLLBACK SEGMENT statement to bring them online and make them available for use by transactions of an instance. This is described in "Changing the ONLINE/OFFLINE Status of Rollback Segments".
If you create a private rollback segment, add the name of this new rollback segment to the ROLLBACK_SEGMENTS initialization parameter in the initialization parameter file for the database. Doing so enables the private rollback segment to be acquired automatically by the instance at instance startup. For example, if two new private rollback segments are created and named rbs_01 and rbs_02, then the ROLLBACK_SEGMENTS initialization parameter can be specified as follows:
ROLLBACK_SEGMENTS = (rbs_01, rbs_02)
Suppose you wanted to create a rollback segment rbs_01 with storage parameters and optimal size set as follows:
The following statement creates a rollback segment with these characteristics:
CREATE ROLLBACK SEGMENT rbs_01 TABLESPACE rbsspace STORAGE ( INITIAL 100K NEXT 100K OPTIMAL 4M MINEXTENTS 20 MAXEXTENTS 100 );
You cannot set a value for the storage parameter PCTINCREASE. It is always 0 for rollback segments. The OPTIMAL storage parameter is unique to rollback segments. For a discussion of storage parameters see "Setting Storage Parameters".
Oracle Corporation makes the following recommendations:
INITIAL and NEXT to the same value to ensure that all extents are the same size.MINEXTENTS = 20 is a good value.MAXEXTENTS = UNLIMITED as this could cause unnecessary extension of a rollback segment and possibly of data files due to a programming error. If you do specify UNLIMITED, be aware that extents for that segment must have a minimum of four data blocks. Also, if you later want to convert a rollback segment whose MAXEXTENTS are limited to UNLIMITED, that rollback segment cannot be converted if it has less than four data blocks in any extent. If you want to convert from limited to UNLIMITED, and have less than four data blocks in an extent, your only choice is to drop and re-create the rollback segment.
| See Also:
Oracle9i SQL Reference for a detailed description of storage parameters |
This section discusses various actions you can take to maintain your rollback segments. All of these maintenance activities use the ALTER ROLLBACK SEGMENT statement. You must have the ALTER ROLLBACK SEGMENT system privilege to use this statement.
The following topics are discussed:
You can change some of a rollback segment's storage parameters after creating it. You may want to change the values of OPTIMAL or MAXEXTENTS. The following statement alters the maximum number of extents that the rbs_01 rollback segment can allocate:
ALTER ROLLBACK SEGMENT rbs_01 STORAGE (MAXEXTENTS 120);
You can alter the settings for the SYSTEM rollback segment, including the OPTIMAL parameter, just as you can alter those of any rollback segment.
You can manually decrease the size of a rollback segment using the ALTER ROLLBACK SEGMENT statement. The rollback segment you are trying to shrink must be online.
The following statement shrinks rollback segment rbs1 to 100K:
ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;
This statement attempts to reduce the size of the rollback segment to the specified size, but stops short if an extent cannot be deallocated because it is active.
ONLINE/OFFLINE Status of Rollback SegmentsThis section describes aspects of bringing rollback segments online and taking them offline, and contains the following topics:
A rollback segment is either online and available to transactions, or offline and unavailable to transactions. Generally, rollback segments are online and available for use by transactions.
You may want to take online rollback segments offline in the following situations:
You might later want to bring an offline rollback segment back online so that transactions can use it. When a rollback segment is created, it is initially offline, and you must explicitly bring a newly created rollback segment online before it can be used by an instance's transactions. You can bring an offline rollback segment online using any instance accessing the database that contains the rollback segment.
You can only bring a rollback segment online if its current status (as shown in the DBA_ROLLBACK_SEGS data dictionary view) is OFFLINE or PARTLY AVAILABLE. To bring an offline rollback segment online, use the ALTER ROLLBACK SEGMENT statement with the ONLINE option.
The following statement brings the rollback segment user_rs_2 online:
ALTER ROLLBACK SEGMENT user_rs_2 ONLINE;
After you bring a rollback segment online, its status in the data dictionary view DBA_ROLLBACK_SEGS is ONLINE. To see a query for checking rollback segment status, see "Displaying Rollback Segment Information".
A rollback segment in the PARTLY AVAILABLE state contains data for an in-doubt or recovered distributed transaction, or for yet to be recovered transactions. You can view its status in the data dictionary view DBA_ROLLBACK_SEGS as PARTLY AVAILABLE. The rollback segment usually remains in this state until the transaction is resolved either automatically by RECO, or manually by a DBA.
You might find that all rollback segments are PARTLY AVAILABLE. In this case, you can bring the PARTLY AVAILABLE segment online. Some resources used by the rollback segment for the in-doubt transaction remain inaccessible until the transaction is resolved. As a result, the rollback segment may have to grow if other transactions assigned to it need additional space.
As an alternative to bringing a PARTLY AVAILABLE segment online, you might find it more efficient to create a new rollback segment temporarily, until the in-doubt transaction is resolved.
If you would like a rollback segment to be automatically brought online whenever you start up the database, add the segment's name to the ROLLBACK_SEGMENTS parameter in the database's parameter file. Or, you can use public rollback segments and use the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameters.
These options are discussed in "Specify Rollback Segments to Acquire Automatically".
To take an online rollback segment offline, use the ALTER ROLLBACK SEGMENT statement with the OFFLINE option. The rollback segment's status in the DBA_ROLLBACK_SEGS data dictionary view must be ONLINE, and the rollback segment must be acquired by the current instance.
The following example takes the rollback segment user_rs_2 offline:
ALTER ROLLBACK SEGMENT user_rs_2 OFFLINE;
If you attempt to take a rollback segment that does not contain active rollback entries offline, Oracle immediately takes the segment offline and changes its status to OFFLINE.
In contrast, if you try to take a rollback segment that contains rollback data for active transactions (local, remote, or distributed) offline, Oracle makes the rollback segment unavailable to future transactions and takes it offline after all the active transactions using the rollback segment complete. Until the transactions complete, the rollback segment cannot be brought online by any instance other than the one that was trying to take it offline.
During this period that the rollback segment is waiting to go offline, the rollback segment's status in the view DBA_ROLLBACK_SEGS remains ONLINE. However, the rollback segment's status in the view V$ROLLSTAT is PENDING OFFLINE. For information on viewing rollback segment status, see "Displaying Rollback Segment Information".
The instance that tried to take a rollback segment offline and caused it to change to PENDING OFFLINE can bring it back online at any time. If the rollback segment is brought back online, it functions normally.
After you take a public or private rollback segment offline, it remains offline until you explicitly bring it back online or you restart the instance.
A transaction can be explicitly assigned to a specific rollback segment. Reasons for doing this include:
To assign a transaction to a rollback segment explicitly, use the SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause. The rollback segment must be online for the current instance, and the SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT clause is not the first statement in a transaction, an error is returned.
For example, if you are about to begin a transaction that contains a significant amount of work (more than most transactions), you can assign the transaction to a large rollback segment, as follows:
SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;
After the transaction is committed, Oracle automatically assigns the next transaction to any available rollback segment unless the new transaction is explicitly assigned to a specific rollback segment by the user.
You can drop rollback segments when the extents of a segment become too fragmented on disk, or the segment needs to be relocated in a different tablespace. Before dropping a rollback segment, make sure that the status of the rollback segment is OFFLINE. If the rollback segment that you want to drop is any other status, you cannot drop it. If the status is INVALID, the segment has already been dropped.
To drop a rollback segment, use the DROP ROLLBACK SEGMENT statement. You must have the DROP ROLLBACK SEGMENT system privilege. The following statement drops the rbs1 rollback segment:
DROP ROLLBACK SEGMENT rbs1;
After a rollback segment is dropped, its status changes to INVALID. The next time a rollback segment is created, it takes the row vacated by a dropped rollback segment, if one is available, and the dropped rollback segment's row no longer appears in the DBA_ROLLBACK_SEGS view.
This section presents views that can be used to obtain and monitor rollback segment information, and provides information and examples relating to their use.
The following topics are included:
| See Also:
Oracle9i Database Reference for more information about the data dictionary views discussed in this chapter |
The following views are useful for displaying information about rollback segments:
The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database:
SELECT SEGMENT_NAME, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS; SEGMENT_NAME TABLESPACE_NAME STATUS ------------- ---------------- ------ SYSTEM SYSTEM ONLINE PUBLIC_RS SYSTEM ONLINE USERS_RS USERS ONLINE
In addition, the following data dictionary views contain information about the segments of a database, including rollback segments:
The V$ROLLSTAT dynamic performance view can be queried to monitor rollback segment statistics. It must be joined with the V$ROLLNAME view to map its segment number to its name.
Some specific columns of interest in the V$ROLLSTAT view include:
These statistics are reset at system startup.
Ad hoc querying of this view can help in determining the most advantageous setting for the OPTIMAL parameter. Assuming that an instance has equally sized rollback segments with comparably sized extents, OPTIMAL for a given rollback segment should be set slightly higher than AVEACTIVE. The following chart provides additional information on how to interpret the statistics given in this view.
The following query returns the name of each rollback segment, the tablespace that contains it, and its size:
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'ROLLBACK'; SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS ------------ --------------- ------- ------ ------- SYSTEM SYSTEM 409600 200 8 RB_TEMP SYSTEM 1126400 550 11 RB1 RBS 614400 300 3 RB2 RBS 614400 300 3 RB3 RBS 614400 300 3 RB4 RBS 614400 300 3 RB5 RBS 614400 300 3 RB6 RBS 614400 300 3 RB7 RBS 614400 300 3 RB8 RBS 614400 300 3 10 rows selected.
When you take a rollback segment offline, it does not actually go offline until all active transactions in it have completed. Between the time when you attempt to take it offline and when it actually is offline, its status in V$ROLLSTAT is PENDING OFFLINE and it is not used for new transactions. To determine whether any rollback segments for an instance are in this state, use the following query:
SELECT NAME, XACTS "ACTIVE TRANSACTIONS" FROM V$ROLLNAME, V$ROLLSTAT WHERE STATUS = 'PENDING OFFLINE' AND V$ROLLNAME.USN = V$ROLLSTAT.USN; NAME ACTIVE TRANSACTIONS ---------- -------------------- RS2 3
If your instance is part of an Oracle Real Application Clusters configuration, this query displays information for rollback segments of the current instance only, not those of other instances.
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|