Flashback Data Archive

Flashback Data archive is one of the Oracle 11g feature (Oracle Total Recall).

A Flashback Data Archive (Oracle Total Recall) 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.

 

How to Enable Flashback Data Archive;

Step 1:  Conn AS sys user & create tablespace

SQL> select flashback_on from v$database;

FLASHBACK_ON

SQL> create tablespace flashbacktbs datafile 'D:\backup\flbtbs01.dbf' size 50m autoextend on;

Tablespace created.
------------------
YES

Step 2: Create Flashback Data Archive with retention period

SQL> create flashback archive fla1 tablespace flashbacktbs retention 1 year;

Flashback archive created.

Step 3:

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive

So Need to give privilege for scott user

Step 4: Grant privilege

SQL> conn sys/Admin123 as sysdba
Connected.

SQL> grant flashback archive on fla1 to scott;

Grant succeeded.

Step 5:

SQL> conn scott/tiger;
Connected.
SQL> alter table emp flashback archive fla1;

Table altered.

Step 6:

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
18-DEC-10 10.53.41.521000 AM +03:00

SQL>  update emp set sal=sal+100 where empno=7788;

1 row updated.

SQL>  select sal from emp as of timestamp to_timestamp('18-DEC-10 10:53:41','DD-MM-YY HH24:MI:SS')  WHERE EMPNO=7788;

       SAL
----------
      3000

SQL> select empno,ename,sal from emp where empno=7788;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3100

Some Other operations:

Modify Retention Period:

SQL>  alter flashback archive FLA1 modify retention 2 year;

Flashback archive altered.

Purge all data

SQL> alter flashback archive FLA1 purge all;

Flashback archive altered.

Disable Falshback data archive for table

SQL> alter table scott.emp no flashback archive;

Table altered.

ADD/MODIFY/REMOVE tablespace

SQL> alter flashback archive fla1 add tablespace testdb;

Flashback archive altered.

Ref :

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFIEEII