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:
create flashback archive default
flashme
tablespace
flashback_arch_ts
quota
60g
retention
1 year;
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
customer
flashback
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.
Create a new user and grant him the required privileges:
SQL>
create
user usr
identified by
usr;
User created.
SQL>
grant
connect, resource, flashback archive administer to usr;
Grant succeeded.
SQL>
Create a new separate tablespace for data archive:
SQL>
create
tablespace tbs_arch datafile 'c:\flashback_archive.dbf' size
10m;
Tablespace created.
SQL>
Create flashback archive on this tablespace using the create
flashback archive commandas
follows:
SQL>
create
flashback archive fl_arch
2 tablespace tbs_arch retention 1 year;
Flashback archive created.
SQL>
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:
SQL>
create
table tbl_fl_archive (id number, name varchar2(20));
Table created.
SQL>
insert into
tbl_fl_archive values(1,'Flashback Archive');
1 row created.
SQL>
commit;
Commit complete.
SQL>
select * from
tbl_fl_archive;
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
alter
table tbl_fl_archive flashback archive fl_archive;
Table altered.
SQL>
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:
SQL>
select
to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate
from
dual;
DDATE
-----------------
13022010 12:46:49
SQL>
delete
from tbl_fl_archive;
1 row deleted.
SQL>
commit;
Commit complete.
SQL>
select * from
tbl_fl_archive;
no rows selected
SQL>
select * from
tbl_fl_archive as of timestamp to_timestamp('13022010
12:46:49','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
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:
SQL>
conn / as sysdba
Connected.
SQL>
show
parameter undo_tablespace;
NAME TYPE VALUE
--------------------- ----------- --------- ------------
undo_tablespace string UNDOTBS1
SQL>
select
a.name
from
v$datafile a, v$tablespace b
where
a.ts#=b.ts# and b.name='UNDOTBS1';
NAME
------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS01.DBF
SQL>
create
undo tablespace undotbs2 datafile
'c:\app\administrator\oradata\db2\undotbs02.dbf' size 10m;
Tablespace created.
SQL>
alter
system set undo_tablespace='undotbs2';
System altered.
SQL>
startup
force
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 251659860 bytes
Database Buffers 171966464 bytes
Redo Buffers 6078464 bytes
Database mounted.
Database opened.
SQL>
show
parameter undo_tablespace;
NAME TYPE VALUE
--------------------------- ----------- ------------
undo_tablespace string UNDOTBS2
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:
SQL>
conn
us1/us1
Connected.
SQL>
select * from
tbl_fl_archive as of timestamp to_timestamp('13022010
12:45:30','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
This query gets the data from flashback data archive.