Flashback Data Archive (Oracle Total Recall) 11g

Flashback Data Archive (Oracle Total Recall) 11g.

Starting from 11g Oracle has added another tool to its bundle of flashback technologies called Flashback
Data Archive(Oracle Total Recall).

With this feature previous state of a table can be viewed. But Unlike Flashback query
and or Flashback transaction query this feature does not depend on the undo_retention
of the database.


Flashback Data Archive requires  one or more tablespaces where it can store historical data
for one or several tables.

whenever the data in the table is modified its original unmodified value is written to the undo
tablespace. From there the Flashback Data Archiver Process(FBDA) will collect it and write
the data to flashback data archive.

#Create a Tablespace for the Flashback Data Archive


SQL> create tablespace fda datafile  '/u01/fda01.dbf' size 500M
     autoextend on next 100M
     extent management local
     segment space management auto;



SQL> select * from dba_sys_privs where privilege like '%FLASH%';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          FLASHBACK ARCHIVE ADMINISTER             NO
SYS                            FLASHBACK ANY TABLE                      NO
DBA                            FLASHBACK ANY TABLE                      YES
SYS                            FLASHBACK ARCHIVE ADMINISTER             NO
DBA                            FLASHBACK ARCHIVE ADMINISTER             YES
MDSYS                          FLASHBACK ANY TABLE                      NO

6 rows selected.


SQL> grant flashback archive administer to scott;

Grant succeeded.


SQL> create flashback archive flash_data
     tablespace fda
     quota 4g
     retention 5 year;

Flashback archive created.

#Quota is optional.It means that FDA can only use upto 4Gb space in the tablespace.

SQL> drop flashback archive flash_data;

Flashback archive dropped.


Modify commands

#Make it the default archive

SQL> alter flashback archive flash_data set default;

Flashback archive altered.

#Add space to it.

SQL> alter flashback archive flash_data add tablespace abc;

Flashback archive altered.

#Change its quota

SQL> alter flashback archive flash_data modify tablespace fda quota 1G;

Flashback archive altered.

#Change retention

SQL> alter flashback archive flash_data modify retention 1 year;

Flashback archive altered.

#Remove tablespace from flashback data archive

SQL> alter flashback archive flash_data
  2  remove tablespace abc;

Flashback archive altered.



#Purge data from flashback data archive


SQL> alter flashback archive flash_data purge all;

Flashback archive altered.

#Purge using interval clause

SQL> alter flashback archive flash_data purge before
  2  timestamp(systimestamp - interval '5' day);

Flashback archive altered.

#Purge using SCN Clause

SQL> alter flashback archive flash_data purge before scn 799684;

Flashback archive altered.



Playing with it.

SQL> create table t1
  2  (id int,name varchar2(30))
  3  flashback archive flash_data;

Table created.


SQL> alter table emp flashback archive;

Table altered.

#Below in the column ARCHIVE_TABLE_NAME tables which have been created in the FBA tablespace can be seen.

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME             STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ 

--------
T1                             SCOTT                          FLASH_DATA                     SYS_FBA_HIST_74560             

ENABLED
EMP                            SCOTT                          FLASH_DATA                     SYS_FBA_HIST_73181             

ENABLED


SQL> select flashback_archive_name, status from dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME         STATUS
------------------------------ -------
FLASH_DATA                     DEFAULT


SQL> alter table emp no flashback archive;

Table altered.

SQL> alter table emp flashback archive flash_data;

Table altered.


SQL> select * from dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                         LAST_PURGE_TIME         

           STATUS
---------- ---------------------- ------------------ ----------------- ----------------------------------- 

---------------------------------- -------
SYS        FLASH_DATA                              1               365 21-JAN-14 04.09.51.000000000 PM     21-JAN-14 

04.23.59.000000000 PM    DEFAULT



Values usable with systimestamp

systimestamp - interval '60' second/minute/day/month

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
---------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_DATA                              1 FDA                            1024


SQL> select sal from emp where empno=7934;

       SAL
----------
      1300

SQL> update emp set sal=2000 where empno=7934;

1 row updated.

SQL> commit;

Commit complete.

SQL> select sal from emp where empno=7934;

       SAL
----------
      2000

#After modifying the  we can see the previous state with the as of clause.

SQL> select * from emp as of timestamp(systimestamp - interval '10' minute) where empno=7934;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10