Undo Data

Undo data provides read consistency, Oracle provides two ways to allocate and manage undo(rollback) space among transactions. If you use the manual approach you will be using traditional rollback segments but is easier to let oracle automatically control the rollback segments which is called AUM (automatic undo management), the only part on the DBA side is to size the undo tablespace, then oracle will automatically create the undo segments within the tablespace.

Using AUM you can take advantage of flashback recovery, flashback query, flashback versions query, flashback transaction query and flashback table - see flashback for further details.

There are three parameters associated with AUM

UNDO_MANAGEMENT (default manual)

This is the only mandatory parameter and can be set to either auto or manual.

UNDO_TABLESPACE
(default undo tablespace)

This specifies the tablespace to be used, of course the tablespace needs to be a undo tablespace. If you do not set this value oracle will automatically pick the one available. If no undo tablespace exists then oracle will use the system tablespace which is not a good idea (always create one).
UNDO_RETENTION
(seconds)

Once a transaction commits the undo data for that transaction stays in the undo tablespace until space is required in which case it will be over written.

When a transaction commits the undo data is not required anymore, the undo data however will stay in the undo tablespace unless space is required then newer transactions will overwrite it. During a long running query that need to retain older undo data for consistency purposes , there might be a possibility that some data it needs has been over written by other new transactions, this would produce the "snapshot too old" error message, which indicates that the before image has been overwritten. To prevent this oracle uses the undo_retention system parameter which try's and keeps the data in the undo tablespace for as long a possible meeting the undo_retention target, however this is not guaranteed.

Undo data can be in 3 states

State   When is undo data over written
uncommitted undo information undo data that supports active transactions and required in the event of rollback never
committed undo information (unexpired) also known as unexpired undo, required to support undo_retention interval after undo_retention period or undo tablespace space pressure unless guaranteed option is set (see below)
expired undo information undo information that is no longer needed always

There are times when you want to guarantee the undo retention at any cost even if it means transactions fail, the option retention guarantee will guarantee that the data will stay in the undo tablespace until the interval has expired, even if there are space pressure problems in the undo tablespace, the default is not to set the guarantee retention period.

I have have noticed on my travels that once undo is expired it is no longer available even if the undo tablespace is not under any space pressure, the only way to keep it is to increase the undo_retention parameter. You can prove this by checking the oldest undo data avilable via the dba_hist_undostat view, the oldest data will match the undo_retention period you set via the undo_retention parameter.

Undo Sizing

Depending on how much undo data you want to keep will determine the size of the undo tablespace, a simple formula is used when calculating the undo tablespace size

undo tablespace size

UR  *  UPS  *  DB_BLOCK_SIZE

UR = undo retention (system parameter undo_retention)
UPS = maximum undo blocks used/sec (obtain from v$undostat)
DB_BLOCK_SIZE = the default block size (obtained from dba_tablespaces)

The Oracle Enterprise Manager uses the desired time period for undo retention and analyses the impact of the desired undo retention setting.

Undo Commands

Undo System Management
Management alter system set undo_management=auto;
Setting alter system set undo_tablespace = 'undotbs02';
Retention alter system set undo_retention = 43200; (it's in seconds)
Undo Control
Creating create undo tablespace undotbs2 datafile 'c:\oracle\undo02.dbf' size 2G;
Removing drop undo tablespace undotbs02;

guarantee

 alter tablespace undotbs02 retention guarantee;
alter tablespace undotbs02 retention noguarantee;
See current undo blocks

select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from v$undostat;

Contains snapshots of v$undostat (use obtain the oldest undo available)

select begin_time, undotsn, undoblks, activeblks, unexpiredblks, expiredblks from dba_hist_undostat;

NOTE: If your current undo_retention period is 6 days then the oldest undo data in dba_hist_undo should be 6 days old.

Useful Views
DBA_ROLLBACK_SEGS describes rollback segments
DBA_TABLESPACES describes all tablespaces in the database
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database
DBA_HIST_UNDOSTAT displays the history of histograms of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. This view contains snapshots of V$UNDOSTAT.
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode.
V$ROLLNAME lists the names of all online rollback segments. It can only be accessed when the database is open.
V$ROLLSTAT contains rollback segment statistics
V$TRANSACTION lists the active transactions in the system

 

Flashback

Flashback recovery, flashback query, flashback versions query, flashback transaction query and flashback table all use undo data for more details see flashback.