The Oracle database saves the old value (undo data) when a process
changes data in a database. It stores the data as it exists before
modifications. Capturing undo data enables you to roll back your uncommitted
data. Undo supports read-consistent and flashback queries. Undo can also be
used to “rewind” (flash back) transactions and tables.
Read-consistent queries
provide results that are consistent with the data as of the time a query
started. For a read-consistent query to succeed, the original information must
still exist as undo information. If the original data is no longer available,
you receive a “Snapshot too old” error. As long as the undo information is
retained, the Oracle database can reconstruct data to satisfy read-consistent
queries.
Flashback
queries purposely ask for a version of the data as it existed at some
time in the past. As long as undo information for that past time still exists,
flashback queries can complete successfully. Flashback Transaction uses undo to
create compensating transactions, to back out a transaction and its dependent
transactions. With Flashback Table, you can recover a table to a specific point
in time.
Undo
data is also used to recover from failed transactions. A failed transaction
occurs when a user session ends abnormally (possibly because of network errors
or a failure on the client computer) before the user decides to commit or roll
back the transaction. Failed transactions may also occur when the instance
crashes or you issue the SHUTDOWN ABORT command.
In case of
a failed transaction, the safest behavior is chosen, and the Oracle database
reverses all changes made by a user, thereby restoring the original data.
Undo information
is retained for all transactions, at least until the transaction is ended by
one of the following:
•
User
undoes a transaction (transaction rolls back).
•
User
ends a transaction (transaction commits).
•
User
executes a DDL statement, such as a CREATE, DROP, RENAME or ALTER statement. If
the current transaction contains any DML statements, the database first commits
the transaction and then executes and commits the DDL as a new transaction.
•
User
session terminates abnormally (transaction rolls back).
•
User
session terminates normally with an exit (transaction commits).
The amount
of undo data that is retained and the time for which it is retained depend on
the amount of database activity and the database configuration.
Transactions
and Undo Data
When a
transaction starts, it is assigned to an undo segment. Throughout the life of
the transaction, when data is changed, the original (before the change) values
are copied into the undo segment. You can see which transactions are assigned
to which undo segments by checking the V$TRANSACTION dynamic performance view.
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, consist 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.
Storing
Undo Information
Undo
segments can exist only in a specialized form of tablespace called an undo
tablespace. (You cannot create other segment types, such as tables, in the
undo tablespace.)
The
installation process automatically creates a “smallfile” undo tablespace. You
can also create a “bigfile” undo tablespace. However, in a high-volume online
transaction processing (OLTP) environment with many short concurrent
transactions, contention could occur on the file header. An undo tablespace,
stored in multiple data files, can resolve this potential issue.
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.
Undo
segments are automatically created and always owned by SYS. Because the undo
segments act as a circular buffer, each segment has a minimum of two extents.
The default maximum number of extents depends on the database block size but is
very high (32,765 for an 8 KB block size).
Undo
tablespaces are permanent, locally managed tablespaces with automatic extent
allocation. They are automatically managed by the database.
Because
undo data is required to recover from failed transactions (such as those that
may occur when an instance crashes), undo tablespaces can be recovered only
while the instance is in the MOUNT state.
Managing
Undo
The Oracle
database provides automatic undo management, which is a fully automated
mechanism for managing undo information and space in a dedicated undo
tablespace for all sessions. The system automatically tunes itself to provide
the best possible retention of undo information . More precisely, the undo
retention period for autoextending tablespaces is tuned to be slightly longer
than the longest-running active query. For fixed-size undo tablespaces, the
database dynamically tunes for best possible retention.
Automatic
undo management is the default for Oracle Database 11g (and later
releases). Manual undo management is supported for backward compatibility with
Oracle8i and earlier releases but requires more DBA interaction. In
manual undo management mode, undo space is managed through rollback segments
(not through undo tablespace).
Note: Oracle strongly recommends that you
use automatic undo management.
Although by
default the Oracle database manages undo data and space automatically, you may
need to perform some tasks if your database is using Flashback operations. The
administration of undo should prevent space errors, the use of too much space,
and “Snapshot too old” errors.
Configuring
Undo Retention
The
UNDO_RETENTION initialization parameter specifies (in seconds) the low
threshold value of undo retention. Set the minimum undo retention period for
the autoextending undo tablespace to be as long as the longest expected
Flashback operation. For autoextending undo tablespaces, the system retains
undo for at least the time specified in this parameter, and automatically tunes
the undo retention period to meet the undo requirements of the queries. But
this autotuned retention period may be insufficient for your Flashback
operations.
For
fixed-size undo tablespaces, the system automatically tunes for the best
possible undo retention period on the basis of undo tablespace size and usage
history; it ignores UNDO_RETENTION unless retention guarantee is enabled. So
for automatic undo management, the UNDO_RETENTION setting is used for the three
cases.
In cases other than these three, this parameter is ignored.
In cases other than these three, this parameter is ignored.
Undo
information is divided into three categories:
•
Uncommitted
undo information:
Supports a currently running transaction; is required if a user wants to roll
back or if the transaction has failed. Uncommitted undo information is never
overwritten.
•
Committed
undo information:
Is no longer needed to support a running transaction but is still needed to
meet the undo retention interval. It is also known as “unexpired” undo
information. Committed undo information is retained when possible without
causing an active transaction to fail because of lack of space.
•
Expired
undo information:
Is no longer needed to support a running transaction. Expired undo information
is overwritten when space is required by an active transaction.
Guaranteeing
Undo Retention
•
The
default undo behavior is to overwrite committed transactions that have not yet
expired rather than to allow an active transaction to fail because of lack of
undo space.
•
This
behavior can be changed by guaranteeing retention. With guaranteed retention,
undo retention settings are enforced even if they cause transactions to fail.
•
RETENTION
GUARANTEE is a tablespace attribute rather than an initialization parameter.
This attribute can be changed only with SQL command-line statements. The syntax
to change an undo tablespace to guarantee retention is:
•
SQL> ALTER
TABLESPACE undotbs1 RETENTION GUARANTEE;
•
To
return a guaranteed undo tablespace to its normal setting, use the following
command:
•
SQL> ALTER
TABLESPACE undotbs1 RETENTION NOGUARANTEE;
•
The
retention guarantee applies only to undo tablespaces. Attempts to set it on a
non-undo tablespace result in the following error:
•
SQL> ALTER
TABLESPACE example RETENTION GUARANTEE;
•
ERROR
at line 1:
•
ORA-30044:
'Retention' can only specified for undo tablespace
Changing
an Undo Tablespace to a Fixed Size
You might
have two reasons for changing the undo tablespace to a fixed size: to support
Flashback operations (where you expect future use of the undo) or to prevent
the tablespace from growing too large.
If you
decide to change the undo tablespace to a fixed size, you must choose a large
enough size to avoid the following two errors:
•
DML
failures (because there is not enough space to the undo for new transactions)
•
“Snapshot
too old” errors (because there was insufficient undo data for read consistency)
Oracle recommends
that you run a regular, full workload, allowing the undo tablespace to grow to
its minimum required size. The automatically gathered statistics include the
duration of the longest-running query and the undo generation rate. Computing
the minimum undo tablespace size based on these statistics is advisable for a
system without Flashback operations, and for a system for which you do not
expect longer-running queries in the future.
You can use
the Undo Advisor to enter your desired duration for the undo period for
longer-running queries and flashback.
