Undo Management and Undo Tablespace | Undo Retention

When a query tries to modify database, original data entries are kept in separate location and then changes are applied to datafiles. The data kept in separate location is called Undo data. Undo data is used to maintain data integrity and data consistency.Oracle uses Undo data in following situations

1-After query execution, user may commit or rollback transaction. If user issues a rollback, original data is copied from Undo data entries.

2-Suppose a user execute query which take 20 minutes to execute. Other users may change the data during these 20 minutes. This may cause read inconsistency. To insure read consistency, oracle database uses original data from undo data if data is changed during the execution of a query.

3-Flashback features including Oracle Flashback Query, Oracle Flashback version, Flashback transaction query and Oracle Flashback Table make use of undo data.

Undo Management and Undo Retention Period

Oracle supports two types of Undo management, Manual Undo Management and Auto Undo Management. In manual management, undo data is kept in rollback segments. When new database is created, oracle creates one rollback segments in system tablespace. Additional rollback segments can be created on requirement. Memory management for rollback segments is really complex. By default database is configured to use Manual Undo Management but it is not recommended by oracle.

Oracle database 9i and latest releases including 10g provide auto management of undo data. In auto management, undo data is stored in undo tablespace. Oracle creates one undo tablespace when a new database is created. Undo tablespace can also be created using following SQL statement.

CREATE UNDO TABLESPACE undotbs_02
DATAFILE ‘/oracle/oradata/orcl/undo0201.dbf’ SIZE 100M REUSE;

Use UNDO_MANAGEMENT initialization parameter to enable auto undo management. Set the value of this initialization parameter to Auto to start undo management as shown below

UNDO_MANAGEMENT=Auto

When oracle instance start, it select first undo tablespace to keep undo data. UNDO_TABLESPACE initialization parameter can be used to utilize a specific undo tablespace if you have more than one undo tablespaces.

The size of undo tablespace can be fixed or autoextend. Even with outoextend option enabled, maximum size of undo tablespace can be controlled by MAXSIZE clause. If undo tablespace has fixed size or MAXSIZE defined then old undo entries can be overwritten by new entries. The size of undo table space should be large enough to insure the undo data is available when required. This is done by setting Undo Retention period. Oracle database tries to retain undo data for time given by undo Retention period. Oracle database tries best to ensure this retention period. To insure read consistency, Undo retention period should be at least equal to time taken by your longest running query. If query is running and oracle finds that the data required by query is overwritten in undo tablespace, query fails with snapshot too old error message. If an SQL statement has not been committed or rolled back, its undo entries are said to be Active. Oracle ensures that these entries retain until corresponding entries are committed. Undo entries are marked expired which are old than current retention period. Undo retention period can be set by using UNDO_RETENTION initialization parameter. Value for this parameter can be set in parameter file as shows below

UNDO_RETENTION=3000

The value is in seconds

Use following statements to set Retention period at runtime

ALTER SYSTEM SET UNDO_RETENTION = 3000;

If you are using Logical level flashback features, make sure that retention period is set according to requirements as flash back features use undo data to get past information.

By default retention period in not guaranteed, oracle database may overwrite undo data if space for new undo entries is not available. You can use RETENTION GUARANTEE clause while creating undo tablespace to guarantee retention period.

1 thought on “Undo Management and Undo Tablespace | Undo Retention”

Leave a Reply

Your email address will not be published. Required fields are marked *