Undo Data Introduction

What is Undo Data

When a transaction modifies data, Oracle Database copies the original data before modifying it. The original copy of the modified data is called undo data. Saving this information is necessary for the following reasons:

- To undo any uncommitted changes made to the database in the event that a rollback operation is necessary. A rollback operation can be the result of a user who wants to undo the changes of a misguided or unintentional transaction, or it can be part of a recovery operation.

- To provide read consistency, which means that each user can get a consistent view of data, even while other changes may be occurring against the data. For example, if a user issues a query at 10:00 a.m. and the query lasts for 15 minutes, then the query results should reflect the entire state of the data at 10:00 a.m., regardless of update or insert operations by other users during the query.

- To enable certain Oracle Flashback features, such as Oracle Flashback Query and Oracle Flashback Table, which enable you to view or recover data to a previous point in time.

Beginning with Oracle Database Release 11g, for a default installation, Oracle Database automatically manages the undo data. There is typically no need for DBA intervention. However, if your installation uses Oracle Flashback operations, you may need to perform some undo management tasks to ensure the success of these operations.


The amount of undo data that is retained and the time for which it is retained depend on the amount of the database activity and the database configuration. When a transaction starts, it is assigned to an undo segment. Throughout the life of the transaction when data is changed, the original values - that existed before the change are copied into the undo segment.

Undo segments are specialized segments that are automatically created by the instance as needed to support transactions. Like all segments, undo segments are made up of extents, which, in turn, consists of data blocks.

Undo segments automatically grow and shrink as needed, acting as a circular storage buffer for their assigned transactions. Transactions fill extents in their undo segments until a transaction is completed or all space is consumed. If an extent fills up and more space is needed, the transaction acquires that space from the next extent in the segment. After all extents have been consumed, the transaction either wraps around back into the first extent or requests a new extent to be allocated to the undo segment. (If you are performing parallel DML and DDL operations it can actually cause a transaction to use more than one undo segment).

Undo segments can exists only in specialized form of tablespace called an undo tablespace. Another types of segment (like table or index) cannot be store int this undo tablespace.

If you have high connection database you may create undo tablespace with many data files to prevent bottleneck in the data file header.

Although a database may have many undo tablespaces, only one of them at a time can be designated as the current undo tablespace for any instance in the database. By using following command

ALTER SYSTEM SET UNDO_TABLESPACE the_name_of_undo_tablespace;

For example if you have two, and you switch from the old one to the other new one for a brief period of time, both are ACTIVE even though all new transactions go to the new setting. This is important because if you want to move the undo tablespace from the mount point you need to wait until all the transactions are cleared in the tablespace.



Following is the explanation with example what is the undo data:

In this I have two sessions open top window is login with DBA privilege and bottom window is the Oracle database end user or application user)

The user name is TESTUSER, test user has a table my_table with only 1 column col. TESTUSER inserted 1 record a commit the transaction.

TESTUSER executing DML (Data Manipulation Language) to update the table set the column value to 2 (originally is 1). TESTUSER never commit the transaction.

While there is uncommited transaction, with DBA privilege you can query the database to find out the undo segment by combining v$transaction, v$rollstat, v$session, dba_data_files.

Following is the SQL command that I execute:

COL username FORM A15
COL tablespace_name FORM A20
COL filename FORM A40

SELECT ses.username username, txn.used_ublk blk_used,
df.tablespace_name tablespace_name, df.file_name filename
FROM v$rollstat rs,
v$transaction txn,
v$session ses,
dba_data_files df
WHERE rs.usn=txn.xidusn AND
txn.ses_addr = ses.saddr AND
df.file_id = txn.ubafil
/


The result show that
TESTUSER has 1 block of undo segment, in the UNDOTBS1 tablespace and the the datafile is /datafiles/edba01/edba01/undotbs01.dbf

Now I issue SHUTDOWN ABORT to stimulate the database is crash while TESTUSER still have uncommited transaction.

I startup the database.

The record in MY_TABLE is now restore to its original value.

The above scenario is also true, if TESTUSER exit the session (window) while there is uncommited transaction.

Undo information is retained for all transactions, at least until the transaction is ended in one of these ways:

- User undoes a transaction - transaction roll back.
- User ends a transactions - transaction commits.
- User executes a DDL (Data Definition Language) statement. If the current transaction contains any DML (Data Manipulation Language) statements, Oracle first commits the transaction and then executes and commits the DDL as a new transaction.
- User session terminates abnormally - transaction roll back. (Above example)
- User session terminates normally with an exit - transaction commits.


NOTE:
This script can be use to query the rollback, for example when you perform shutdown while there is big number of undo segment block need to be rollback, the number will count down until 0 then the database is continue to close the database, it is handy script to check you database in case some one or some software (e.g. Veritas Cluster) perform shutdown abort after it time out while waiting DB to perform rollback.

And you must connect as sysdba if shutdown in progress.

COL username FORM A15
COL tablespace_name FORM A20
COL filename FORM A40

SELECT ses.username username, txn.used_ublk blk_used,
df.tablespace_name tablespace_name, df.file_name filename
FROM v$rollstat rs,
v$transaction txn,
v$session ses,
dba_data_files df
WHERE rs.usn=txn.xidusn AND
txn.ses_addr = ses.saddr AND
df.file_id = txn.ubafil
/