Oracle Flashback Data Archive (Oracle Total Recall)

It is new feature with 11g.

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

By default, flashback archiving is off for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table. After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

While flashback archiving is enabled for a table, following DDL statements are not allowed on that table.
· ALTER TABLE statement that does any of the following:
o Drops, renames, or modifies a column
o Performs partition or subpartition operations
o Converts a LONG column to a LOB column
o Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
· DROP TABLE statement
· RENAME TABLE statement
· TRUNCATE TABLE statement

Creating / Alerting / Dropping Flashback Data Archive

We must have SYSDBA or Flashback archive administrator privilege.
Creating a flashback data archive with the CREATE FLASHBACK ARCHIVE statement

1. Name of the flashback data archive
2. Name of the first tablespace of flashback data archive
3. Quota on tablespace default is UNLIMITED
4. Retention time (In number of days that flashback data archive data for the table is guaranteed to be stored)

For example:
--Create separate tablespace for FLASHBACK option
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create tablespace ORCLFLASHDB
2 datafile '+DGRP1' size 50m;
Tablespace created.
SQL> --Create flashback data archive
SQL> create flashback archive DEFAULT fla1 tablespace ORCLFLASHDB
2 RETENTION 1 YEAR;
Flashback archive created.

Note: DEFAULT keyword is fla1 is default database flashback data archive.

Altering a flashback data archive
We can do following changes with altering flashback data archive

1. change the retention time of flashback data archive

SQL> alter flashback archive FLA1 modify retention 2 year;
Flashback archive altered.

2. Purge some or all its data

SQL> alter flashback archive FLA1 purge all;
Flashback archive altered.

3. add / modify / remove tablespace

SQL> alter flashback archive FLA1 add tablespace tbsname;

Dropping a Flashback Data Archive

SQL> drop flashback archive FLA1;
Flashback archive dropped.

Specifying the default flashback data archive

SQL> alter flashback archive FLA1 set default;
Flashback archive altered.

Enabling and Disabling flashback data archive

By default flashback data archive is disabled for any database table.
We can enable flashback data archive for any table if we have FLASHBACK ARCHIVE object privilege

After enable flashback data archive on table if we want to disable then we must have FLASHBACK ARCHIVE ADMINISTRATOR or SYSDBA privileges
We can enable flashback data archive on table through adding FLASHBACK ARHICIVE keyword with ALTER or CREATE TABLE statement.

For example:
SQL> grant FLASHBACK ARCHIVE on FLA1 to scott;
Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> create table flasheg ( no number) FLASHBACK ARCHIVE;
Table created.

SQL> alter table EMP flashback archive;
Table altered.

SQL> alter table flasheg NO FLASHBACK ARCHIVE;
alter table flasheg NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

NOTE: Because disable flashback archive required FLASHBACK ARCHIVE ADMINISTRATOR or SYSDBA privileges.

SQL> conn system/oracle
Connected.

SQL> alter table scott.flasheg NO FLASHBACK ARCHIVE;
Table altered.

NOTE: NO FLASHBACK ARCHIVE keyword requried to disable flashback archive data for the table.

Viewing information about FLASHBACK ARCHIVE DATA

*_FLASHBACK_ARCHIVE display information about flashback data archive
*_FLASHBACK_ARCHIVE_TS display tablespaces of flashback data archive
*_FLASHBACK_ARCHIVE_TABLES display information about tables that are enabled for flashback archiving.
NOTE: * means DBA or USER or ALL

Example:

User Scott Create following two tables

13:07:18 SQL> create table empcopy1 as select * from empcopy;
Table created.
13:07:55 SQL> create table empcopy2 as select * from empcopy;
Table created.

Enable Flashback archive on only one tables

13:07:59 SQL> alter table empcopy1 flashback archive;
Table altered.

After that delete both table empcopy1,empcopy2 respectively and commit.

13:08:06 SQL> delete empcopy1;
13 rows deleted.
13:08:13 SQL> delete empcopy2;
13 rows deleted.
13:08:16 SQL> commit;
Commit complete.

13:08:18 SQL> select * from empcopy1;
no rows selected
13:08:26 SQL> select * from empcopy2;

Timing we perform above operation is "17 may 08 01:08 pm"

After Three and half hours SCOTT found his made incorrect mistake and delete incorrect tables data now he want to recover both tables.

As you know on one table (empcopy1) flashback archive is enable for 1 year retention period so scott can able to recover table empcopy1 without any problem
but about empcopy2 there flashback archive is disable so this data depands on undo_retention period which by deafult set is 900 seconds (15 minutes) only.
Means scott can able to recover data only for TABLE EMPCOYP1 and he have to perform incomplete recovery for TABLE EMPCOPY2.

Current time: 04:43:00 pm 17may06

SQL> select count(*) from empcopy1
2 as of timestamp
3 to_timestamp ('2008-05-17 13:08:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
13

SQL> select count(*) from empcopy2
2 as of timestamp
3 to_timestamp ('2008-05-17 13:09:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)
----------
0