Oracle Total Recall Tips

Oracle Database Tips by Burleson Consulting

April 11, 2015

Question: I
understand how Oracle rollback segments evolved into UNDO, and how a
large UNDO is used for Oracle flashback database, but I don't
understand the "total recall" Oracle feature. What is total
recall and how does it differ from flashback?

Answer: First, "total
recall" is an extra-cost feature of Oracle enterprise edition.
The total recall feature allows you to perform "time travel" and
query the Oracle database is it existed in a previous point in time.

The primary difference between total recall
and flashback is that total recall has the UNDO kept inside a
permanent tablespace:

Unlike flashback which relies on the size of
the UNDO logs, total recall allows you to assign individual tables
to the total recall
flashback
data archive tablespace:

alter table customerflashback
archive flashme;

Total Recall Features

At this point, all DML operations against
the customer table are logged into the total recall
flashback archive disk area for semi-permanent storage, in this
case, one year. In general
total recall is superior to flashback in these situations:

Highly inept end-users who make serious
mistakes with their data.

Table-level control of total recall.

No reliance on the size of your UNDO
tablespace.

No more ORA-1555
snapshot too old errors when the required before image
cannot be read any more. ORA-1555 occurs because it as already
been overwritten in the undo tablespace.

Easy access to historical values with the "select as of
timestamp" syntax.

Downsides of total recall

There are several downsides to using Oracle
total recall:

No internal consistency: It is
tempting to retrieve historical data ans then re-store it as of
the current time. However, total recall only keeps changes
at the table level and referential integrity may be lost if you
use total recall for restoring lost data.

The use of total recall can alleviate
the cumbersome LogMiner interface for auditing sensitive
information, but total recall has the downside of requiring a
huge amount of disk space when used with highly volatile tables
(tables with lots of updates).

RMAN

Oracle Flashback Data Archive (Total Recall)

Most of the previously explained flashback technologies rely on
undo data. This means that if undo data is overwritten, you cannot
get the before image of any table and perform flashback.

Starting from the Oracle 11g version, you can keep every
transaction made to the table and keep it as long as you want.
Before 11g, in order to get the before image of any row, either
you were getting it from archived redo log files (if they are
kept) using Log Miner, or were writing a trigger to save the data
in another log table. But now by using the flashback data archivefeature, you do not need to use Log Miner or a trigger
to track changes made to the table.

The new background process, FBDA (Flashback Data Archive),
tracks all changes made to the table and stores it in a file in a
compressed and partitioned format. However, you cannot use this
feature with clustered, temporary, nested, remote or external
tables and long or nested columns. It tracks all transactional
changes made to specific tables for the specific time interval. In
the following scenario, you will see the configuration and usage
of this feature in detailed examples.

Scenario 7: Bob got a call from the manager: Hi Bob. You
know we are currently working on a new project and we need to
keep all changes made to all tables for one year. We do not want
to use trigger and auditing because of performance degradation.
We cannot use Log Miner because we do not keep archived redo log
files for a long time. Please find another solution!?

As Bob's company uses Oracle 11g, Bob automatically decides to
use Oracle's flashback data archive technology to implement this
task. Now see the steps of creation of the transactional history
of the table using flashback data archive. For this, the user
should have the flashback archive administer system privilege to
create a new flashback data archive. Moreover, the flashback
archive object privilege should be granted to the user to enable
historical data tracking.

With the above command, a flashback archive named fl_arch is
created which resides in the tablespace tbs_arch and holds
information for one year. It means that you can use any flashback
query which contains one year of historical information regarding
the table that is assigned to this flashback archive.

Now, create a table, insert one row and assign it to the
flashback archive:

The historical change on the table tbl_fl_archive will now be
written to the flashback archive named fl_archive. To test it,
delete all rows from the table and use flashback queryon that table. Remember, it will not look for the undo
data; it will look to the flashback archive file for the changes:

In order to show and prove that it does not look to the undo
tablespace for the historical information on the rows for the
specific time, create a new undo tablespace and make it default by
dropping the old one. Then use flashback query on that table:

As can be seen, you are currently using the different undo
tablespace that does not have any information about before images
of data blocks of the tbl_fl_archive. Now use flashback query
against that table:

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.