Oracle Total Recall Tips

http://www.dba-oracle.com/t_total_recall.htm

Oracle Total Recall Tips Oracle Tips by Burleson Consulting April 11, 2012

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 archivetablespace:

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.