You can use
Flashback technology when a logical corruption occurs in the Oracle database,
and you need to recover data quickly and easily. As with human errors, it is
difficult to identify the objects and rows that are affected by an erroneous
transaction. With Flashback technology, you can diagnose how errors are
introduced into the database, and then you can repair the damage. You can view
the transactions that have contributed to specific row modifications, view the
entire set of versions of a given row during some time period, or just view
data as it appeared at a specific time in the past. Flashback Database depends
on the flashback logs to perform flashback. Flashback Drop uses the recycle
bin. All other techniques use undo data.
Not all
flashback features modify the database. Some are simply methods to query other
versions of data. Those are tools for you to use to investigate a problem and
aid in recovery. The results of those flashback queries can help you do one of
these two things:
•
Determine
which type of database-modifying flashback operation to perform to fix the
problem.
•
Feed
the result set of these queries into an INSERT, UPDATE, or DELETE statement
that enables you to easily repair the erroneous data.
Flashback
Data Archive enables you to use the preceding logical flashback features to
access data from far back in the past.
Transactions
and Undo
When a
transaction starts, it is assigned to an undo segment. Throughout the life of
the transaction, when data is changed, the original “old” 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.
Note: Parallel DML operations can
actually cause a transaction to use more than one undo segment.
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. In case of conflict, transactions have precedence over queries.
This
behavior can be changed by guaranteeing retention. With guaranteed retention,
undo retention settings are enforced even if they cause transactions to fail.
(So in case of conflict, queries have precedence over transactions.)
RETENTION
GUARANTEE is a tablespace attribute rather than an initialization parameter.
This attribute can be changed using either SQL command-line statements or
Enterprise Manager. 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;
You can set
Undo Retention Guarantee in Enterprise Manager. Navigate to the Automatic Undo
Management page. Click the current setting for Retention Guarantee
(General/Undo Retention Settings) to modify it.
Preparing
Your Database for Flashback
To enable
flashback features for an application, you must perform these tasks:
•
Create
an undo tablespace with enough space to keep the required data for flashback
operations.
The
more often users update the data, the more space is required. The database
administrator usually calculates the space requirement. If you are uncertain about
your space requirements, you can start with an automatically extensible undo
tablespace, observe it through one business cycle (for example, 1 or 2 days),
collect undo block information with the V$UNDO_STAT view, calculate your space
requirements, and use them to create an appropriately sized fixed undo
tablespace. By default, Automatic Undo Management is enabled.
•
For
a fixed-size undo tablespace, the Oracle database automatically tunes the
system to give the undo tablespace the best possible undo retention.
•
For
an automatically extensible undo tablespace (default), the Oracle database
retains undo data to satisfy at a minimum, the retention periods needed by
the longest-running query and the threshold of undo retention, specified by the
UNDO_RETENTION parameter.
Default
database initialization parameters:
UNDO_MANAGEMENT='AUTO'
UNDO_TABLESPACE='UNDOTBS1'
UNDO_RETENTION=900
You can
query V$UNDOSTAT.TUNED_UNDORETENTION to determine the amount of time for which
undo is retained for the current undo tablespace. Setting the UNDO_RETENTION
parameter does not guarantee, that unexpired undo data is not overwritten. If
the system needs more space, the Oracle database can overwrite unexpired undo
with more recently generated undo data.
•
Specify
the RETENTION GUARANTEE clause for the undo tablespace to ensure that unexpired
undo data is not discarded.
•
Grant
flashback privileges to users, roles, or applications that need to use
flashback features.
To satisfy
long retention requirements, create a flashback data archive.
