Using Automatic Undo Management

Oracle introduced automatic undo management in Oracle 9i. This feature has been significantly improved in Oracle Database 10g; in addition, the Undo Advisor tool has been introduced. To run your database in automatic undo management mode, you should set the UNDO_MANAGEMENT initialization parameter to AUTO.

Oracle makes a copy of the original data in the database before modifying it. This is helpful if you ever wish to roll back any database changes. Oracle writes these change vectors to undo the operation. The original copy of the data before any modification is called the undo data. This undo data is stored in an undo tablespace. The automatic undo management feature automatically determines the retention period for undo data after committing a transaction, based on the queries against the database. Within this retention or preservation period, the undo data is in an unexpired state. Oracle retains the undo data in the unexpired state for as long as needed by currently running queries or by the undo retention threshold (whichever is the higher-value duration of the two). When Oracle crosses this time limit, the undo data changes its status to the expired state.

Under normal operations, undo data can be overwritten only when it is in the expired state. When the undo tablespace has inadequate free space (or expired space) to keep active undo data from current transactions, Oracle will be forced to overwrite the unexpired undo data. This could cause your queries to fail. For this reason, Oracle recommends that you either enable AUTO EXTEND ON for the undo tablespace or have a sufficiently large tablespace for any kind of transactions.

AUTO EXTEND should be turned on for undo tablespace, per Oracle recommendations. This is the best choice for undo sizing compared to manual sizing of the undo tablespace.

Deprecated Oracle Initialization Parameters Related to Undo Management

Oracle 9i had a set of initialization parameters that are deprecated in the Oracle Database 10g. Some of these parameters related to undo (for example, MAX_ ROLLBACK_SEGMENT, ROW_LOCKING, UNDO_SUPPRESS_ERRORS, SERIALIZABLE, and TRANSACTION_AUDITING) have been eliminated in Oracle Database 10g because they are automatically calculated instead of manually sized.

If you are unable to use auto extension to confine the undo tablespace size to certain limits, use the Undo Advisor provided by Oracle to get the optimal size. This is discussed later in this chapter in the section "Making the Most of Undo Advisor." In Oracle Database 10g Release 2, Undo Advisor calculates the optimum undo tablespace size as the maximum of the auto-tuned value of undo retention and the low threshold value.

How Undo Data Helps the Database

When you install Oracle Database 10g, Oracle automatically enables auto-tuning of the undo-retention period along with the database-creation process. This will help you to start building the database without tuning undo.

Undo data keeps changes made to the database by transactions for a certain time period or as long as the undo data remains in an unexpired state. Undo is used to reverse any uncommitted changes made to the database in the event you ever have to roll back an operation. It also provides read consistency for the database user while uncommitted changes could be occurring against the database records. Undo also helps to enable database flashback features (such as flashback query) to view or recover data from a prior time period.

The undo data has to be saved at least until the database transaction has been committed. This time is determined by the UNDO_RETENTION parameter. The undo tablespace should be large enough to hold the active undo data generated by the longest-running transactions in your database. Otherwise, your long-running transactions could fail. After the transaction has been committed, the undo data should not be immediately overwritten. This is important because flashback features rely on this data to work.

The Significance of the UNDO_RETENTION Parameter

With autoundo tuning enabled, the UNDO_RETENTION parameter is set to 0. If the DBA wants to control this parameter and use a value other than 0, automatic-tuning functionality will not work.

Oracle Database 10g has an option to guarantee undo retention. When this option is enabled, the database never overwrites unexpired undo data (that is, undo data within the undo retention period). You can enable the guarantee option by specifying the RETENTION GUARANTEE clause for the undo tablespace when it is created (in the CREATE DATABASE or CREATE UNDO TABLESPACE statement) or at a later period using the ALTER TABLESPACE statement.

The UNDO_RETENTION parameter is valid only if the current undo tablespace has sufficient space. When an active transaction looks for undo space and cannot find enough space in the undo tablespace, the system will start reusing unexpired undo space as follows. It may even generate an error during SQL execution.

When more space is needed, Oracle allocates a new extent from the undo tablespace. If there are no free extents and the tablespace cannot autoextend, then Oracle tries to get an expired extent from another undo segment. If there are no extents with expired status, then Oracle will try to reuse an unexpired extent from the current undo segment. If that fails, it tries to get an unexpired extent from another undo segment. If all these attempts fail, Oracle reports an "out-of-space" error, causing queries to fail with ORA-01555.

Resumable Space Allocation

Oracle introduced a resumable space allocation feature in Oracle 9i to suspend and later restart the execution of large database operations during space-allocation failures. This enables you to take corrective action without the server returning an error to the database user. After the error condition is corrected, the suspended database operation automatically resumes. This feature can be used in tandem with automatic undo management.

Resumable space allocation was introduced in Oracle 9i for all database tablespaces at the session level. When the database encounters an out-of-space condition, all database operations are suspended. These suspended operations automatically resume after the error condition disappears or is cleared. In Oracle Database 10g, this feature can be enabled at the instance level. You can set up automatic alert notifications to be sent when an operation is suspended, as discussed in Chapter 6, "Monitoring with Automatic Statistics Collection," in the section "Using Database Alerts with Metrics."

When resumable space allocation mode is enabled, you can specify a timeout period using the RESUMABLE_TIMEOUT parameter, after which the suspended statement will error if no DBA intervention has taken place. The following code sets the timeout at 1,800 seconds, or 30 minutes.

 SQL> ALTER SYSTEM SET RESUMABLE_TIMEOUT = 1800; System altered. 

The RESUMABLE_TIMEOUT parameter retains the value until it is changed by another ALTER SESSION ENABLE RESUMABLE statement or until the session ends. The default timeout interval is 7,200 seconds.

Undo Management in Real Application Clusters

In Oracle RAC, Oracle manages undo segments within a specific undo tablespace allotted to the instance. An instance can use segments assigned to its undo tablespace only. But all RAC instances can read all undo blocks in the cluster environment for consistent read purposes. Similarly, any instance can update any undo tablespace during transaction recovery not used by any other instance. All instances of a RAC database must operate in the same undo mode. That is, you cannot deploy automatic undo management and manual undo management in a RAC database.