Automatic Undo Management (AUM)

articles: 

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.

To start using Automatic Undo Management one must create an UNDO-type tablespace and set some initialisation parameters.

Create an Undo Tablespace:

Oracle can only create undo segments in special UNDO-type tablespaces. One can create an undo tablespace with database creation or by creating separate UNDO tablespaces:

-- Using the create undo tablespace statement
CREATE UNDO TABLESPACE undots1 
   DATAFILE 'C:\Oracle\oradata\ORCL\orcl_undots101.dbf' SIZE 100M REUSE AUTOEXTEND ON;

-- As part of database creation
CREATE DATABASE orcl ...
UNDO TABLESPACE undots2
   DATAFILE 'C:\Oracle\oradata\ORCL\orcl_undots201.dbf' SIZE 100M AUTOEXTEND ON;

Enable Undo Management:

The following initialisation parameters can be used to enable and control AUM (do a "show parameters undo" to see your settings):

UNDO_MANAGEMENT (MANUAL or AUTO)
- One MUST set this parameter to AUTO to enable Automatic Undo Management. The default is MANUAL for both Oracle 8i and Oracle 9i databases. One must restart the database to switch between MANUAL and AUTO mode.

UNDO_TABLESPACE (valid UNDO-type tablespace name)
- Specifies which undo tablespace to use.

UNDO_RETENTION (time in seconds)
- Specifies the amount of undo the instance should attempt to retain. The default is 900 seconds or 15 minutes. If the UNDO tablespace is too small compared to the retention time, one can still get the famous ORA-1555 errors.

UNDO_SUPPRESS_ERRORS (TRUE or FALSE)
- Specifies whether or not to return an exception when DDL and "SET TRANSACTION USE ROLLBACK SEGMENT" statements are issued. The default is FALSE.

Example:

SQL> alter system set undo_management=auto scope=spfile;
SQL> create UNDO tablespace UNDOTS1 
  2     datafile 'C:\Oracle\oradata\ORCL\orcl_undots101.dbf' size 100M;
SQL> alter system set undo_tablespace=UNDOTS1 scope=spfile;
SQL> startup force

The following views can be used to monitor Undo Segments:

The traditional views like DBA_ROLLBACK_SEGS, V$ROLLSTAT, V$ROLLNAME, and V$TRANSACTION are still available.

DBA_UNDO_EXTENTS - shows when each extent in the undo tablespace was committed.

V$UNDOSTAT - shows the undo usage for the last 24 hours. Each row records a ten minute interval defined by START_TIME and END_TIME. The key field is UNDO_BLOCKS. Note that one can size the UNDO tablespace according to this info: UNDO TS SIZE = UNDO_RETENTION * UNDO_BLOCKS * BLOCK_SIZE.

Additional Notes:

One can have multiple UNDO tablespaces per database, but only one can be active at a given time (per instance). Oracle attempts to assign each transaction to its own undo segment. When it cannot, it will create additional undo segments. When space within the undo tablespace is depleted, Oracle will start sharing undo segments between transactions.

At any time the DBA can switch to a different undo tablespace by changing the UNDO_TABLESPACE parameter on-line (ALTER SYSTEM). However, if one wants to switch between AUTOMATIC and MANUAL undo mode, one must restart the database.

One can perform DDL on undo tablespaces (create, alter, drop), however, one cannot perform DDL on undo segments. One can instruct the database or database session to ignore these errors (handy for old scripts). Example:

SQL> alter rollback segment "_SYSSMU11$" offline;
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

SQL> alter session set undo_suppress_errors=TRUE;
Session altered.

SQL> alter rollback segment "_SYSSMU11$" offline;
Rollback segment altered.

If you create a database without specifying an UNDO TABLESPACE clause, an undo tablespace with name SYS_UNDOTBS will be created.

Comments

very helpful; however, i'm looking for advice on alertlog extensive messages "Created Undo Segment" that seem to correspond with database hangs. Any hints would be warmly accepted!

Thanks.

Article is good. Everything at one place.