Automatic Undo Management

                                                                UNDO MANAGEMENT

Automatic Undo Management takes the entire issue of sizing and allocation of undo segments from the DBA’s hands and makes it Oracle’s responsibility. Oracle will dynamically create undo (rollback) segments and adjust their number to match the instance’s workload requirements. The database will automatically allocate and deallocate undo segments to match the transaction throughput in the instance.

Setting up Automatic Undo Management

To enable the automatic management of your undo space, you first need to specify the automatic undo mode in the init.ora file or your SPFILE. By default, the database uses AUM in Oracle Database 11g. Second, you need to create a dedicated tablespace to hold the undo information.

If you want to choose AUM when you create a new database, you need to configure the following three initialization parameters:

• undo_management

• undo_tablespace

• undo_retention

we can get this information by issuing the command:

SQL> show parameter undo

NAME                                 TYPE        VALUE

--------------------------- ----------- ------------------------------

undo_management        string      AUTO

undo_retention               integer     900

undo_tablespace              string      UNDOTBS1

The UNDO_MANAGEMENT Parameter:

You specify AUM in your initialization parameter file by adding the following line:

UNDO_MANAGEMENT = auto

The default for undo management in Oracle Database 11g is automatic, which means you can leave out the UNDO_MANAGEMENT initialization parameter if you want.

The UNDO_TABLESPACE Parameter:

The UNDO_TABLESPACE parameter isn’t mandatory—if you only have a single undo tablespace, you don’t have to specify this parameter in your initialization parameter file, because Oracle will use the one available undo tablespace automatically. If you specify AUM and don’t have an undo tablespace in your database at all, Oracle will be forced to use the System tablespace. You should avoid using the System tablespace for storing the undo data, since that tablespace also stores the data dictionary, and you don’t want to use up space there and cause problems such as fragmentation. Note that you can’t create application tables and indexes in an undo tablespace, since it’s exclusively reserved for undo data.

If you have multiple undo tablespaces in your database, however, you must specify which undo tablespace you want the database to use, by specifying the UNDO_TABLESPACE parameter in the initialization parameter file. You can have multiple tablespaces in your database, but only one of them can be active at any given time. You activate an undo tablespace by using

                the ALTER SYSTEM SET UNDO_TABLESPACE command

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

The UNDO_RETENTION Parameter:

When a transaction commits, the undo data for that transaction isn’t needed any more. That undo

data will stay in the undo tablespace, however, until space is needed to record undo data for newer

transactions. When the newer transactions’ undo data comes in, it may overwrite the old undo data

(from the committed transactions) if there isn’t enough free space left in the undo tablespace. For a

long-running query that needs to retain older undo data for consistency purposes, there is a possibility that some of the undo data it needs has been overwritten by other, newer transactions. In this case, you could get an error message (“snapshot too old”) from the database indicating that the before-image of the transaction has been overwritten.

To prevent this, Oracle provides the UNDO_RETENTION configuration parameter, which you can set

to the interval you wish. Note that in the older manual undo management mode, DBAs don’t have the option of determining how long Oracle retains undo information.

Let’s briefly review how undo information is managed in an undo segment. Undo information

can be classified in two broad types:

• If a transaction that has generated the undo data is still active, the undo data is said to be active (uncommitted). Oracle will always retain active undo data to support ongoing uncommitted

transactions.

• If the transaction that generated the undo data is inactive (committed), then the undo data is

said to be committed. The committed undo data can be either expired or unexpired. Expired data can be overwritten by new transactions. Oracle will try to save the unexpired data as long as it can, subject to undo space limitations. When there is no more room in the undo tablespace for newer transactions, Oracle will finally overwrite the unexpired data, depending on how you configure the UNDO_RETENTION parameter.

The V$UNDOSTAT table provides an indicator for helping figure out the undo retention

interval. Query the V$UNDOSTAT view as follows:

SQL> select max(MAXQUERYLEN) from v$undostat;

MAX(MAXQUERYLEN)

----------------

            1129

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

• UndoSpace is the number of undo blocks
• UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
• UPS is undo blocks for each second
• overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;

How to create UNDO tablespace:

SQL> CREATE UNDO TABLESPACE undotbs02 DATAFILE '/u02/oradata/mydb/undotbs02.dbf'

SIZE 50M AUTOEXTEND ON;

Tablespace created.

How to add datafile to exisiting undo tablespace:

SQL> alter tablespace UNDOTBS1 add datafile'/u01/app/oracle/oradata/mydb/undo3.dbf' size 50m;

Tablespace altered.

How to drop undo tablespace:

SQL> drop tablespace undotbs02 including contents and datafiles;

Tablespace dropped.

In Automatic Undo Management, the database is in charge of creating, allocating, and deallocating the undo segments as necessary. You can query the DBA_ROLLBACK_SEGS  view to find out which of your undo segments are online, as shown here:

SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME            TABLESPACE_NAME                STATUS

-------------------------- ------------------------------ ----------------

SYSTEM                                        SYSTEM                           ONLINE

_SYSSMU10_84651485$         UNDOTBS1                       ONLINE

_SYSSMU9_3163463872$       UNDOTBS1                       ONLINE

_SYSSMU8_625026625$          UNDOTBS1                       ONLINE

_SYSSMU7_2494227896$         UNDOTBS1                       ONLINE

Guaranteed Undo Retention

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

WARNING:

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASE or CREATE UNDO TABLESPACE  statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value of GUARANTEE, NOGUARANTEE, or NOT APPLY, where NOT APPLY is used for tablespaces other than the undo tablespace.

How to Manage undo retention:

SQL>SELECT tablespace_name, retention FROM dba_tablespaces;

SQL>ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SQL>select tablespace_name, retention from dba_tablespaces;

SQL>ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

SQL> select TABLESPACE_NAME,RETENTION from dba_tablespaces;

TABLESPACE_NAME      RETENTION

----------------------------- ----------------

SYSTEM                               NOT APPLY

SYSAUX                                NOT APPLY

UNDOTBS1                       NOGUARANTEE

TEMP                                    NOT APPLY

USERS                                   NOT APPLY

EXAMPLE                        NOT APPLY

The following data dictionary views are useful in managing undo space information:

• V$UNDOSTAT: This is the view Oracle uses to tune undo space allocation in the database.

This view can indicate whether your current allocation of space in the undo tablespace is enough. It also indicates whether you have set the UNDO_RETENTION parameter correctly. The TUNED_UNDORETENTION column in the V$UNDOSTAT view tells you the length of time undo is

retained in your undo tablespace.(check maximum query length)  

• DBA_ROLLBACK_SEGS: You can use this view to find out the undo segment name, initial,

next, and maximum extents, and other related information.(check status is online)

• DBA_TABLESPACES: This view will show whether the guaranteed undo retention feature is

enabled for a certain undo tablespace.(check for retention)

• V$TRANSACTION: You can get transaction information from this view.

• V$ROLLSTAT: You can join V$ROLLSTAT and V$ROLLNAME to get a lot of information on the

behavior of the undo segments.

• DBA_UNDO_EXTENTS: This view provides detailed information on the undo extents within

the undo tablespace.