Features and configuration of Oracle Automatic UNDO Management AUM

advertisements

_____________________________________________________________________________________________________________________

Overview

Oracle 8i and below, the rollback segments were used for maintaining the read consistency and for the transaction rollback. Oracle 9i onwards the UNDO replaced the rollback segments with some automatic features. This feature includes like creation, monitoring, performance, sizing of the rollback segments. It is easier for the DBAs to minimize the issues with UNDO when using the automatic undo management.
If you set the UNDO_RETENTION high enough with properly sized undo tablespace you shouldn’t have as many issues with UNDO. There are few parameters needed for the UNDO configuration.

1. UNDO_TABLESPACE=<Name of the undo tablespace> : Name of the undo tablespace need to be mention in the parameter file and this tablespace needs to be created in the database with autoextend on.
2. UNDO_MANAGEMENT=[MANUAL or AUTO]: To enable the automatic undo management the parameters needs to be set as AUTO. The default value for this parameter is MANUAL in Oracle 8i & 9i. Oracle 10g onwards it is AUTO by default. Database needs a bounce if you make any change in this parameter value.
3. UNDO_RETENTION=<seconds>: This parameter assures the undo image will keep in the undo segments up to these many seconds. This parameter is more relevant in solving the ORA-01555 error. UNDO_RENTENTION parameter can be modified to higher value depends upon the transaction duration. The size of the UNDO_TABLESPACE is matters if you give a high value on UNDO_RETENTION. You can use Undo advisor to find out how large tablespace should be given for a desired UNDO_RETENTION.
4. UNDO_SUPRESS=[TRUE/FALSE]: If you set the parameter value as TRUE it will suppress the errors when the UNDO was handled by manual(example: SET TRANSACTION USE ROLLBACK SEGMENT).

Below mentioned parameters can be changed dynamically.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDO_02;

ALTER SYSTEM SET UNDO_RETENTION=3200;

ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

And the UNDO_MANAGEMENT is a static parameter

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Data Dictionary Views

V$UNDOSTAT

V$ROLLSTAT

V$TRANSACTION

DBA_UNDO_EXTENTS

_____________________________________________________________________________________________________________________

Website Stats