How to configure Flashback Data Archive on Table level

Flashback Data Archive (FDA)

Points to Remember

1) A FDA can be configured to guarantee the ability to flash back a table to any time(perhaps to a time years ago).

2) Easy to configure – apply to all tables, one or a group of tables with simple “enable archive” command.

3) Secure – complete protection from accidental or malicious updates and deletes.

4) However, you cannot use this feature with clustered, temporary, nested, remote or external tables and
long or nested columns.


Create a new user and grant him the required privileges

SQL> create user adeeb identified by adeeb
Grant succeeded.


SQL> grant connect, resource, flashback archive administer to adeeb;

Grant succeeded.


Create a new separate tablespace for data archive

SQL> create tablespace fda_tsb datafile '/u01/panclone/fda_tsb.db' size 30m;

Tablespace created.



Create flashback archive on this tablespace using the create flashback archive commands follows

SQL>
create flashback archive fl_arch tablespace fda_tsb retention 1 year;
Flashback archive created.

With the above command, a flashback archive named fl_arch is created which resides in the 
tablespace fda_tsb 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> conn adeeb/adeeb
Connected.
SQL> create
table tbl_fl_archive (id number, name varchar2(20));  2

Table created.

SQL> insert into
tbl_fl_archive values(1,'Flashback Archive');  2

1 row created.

SQL> commit
  2  ;

Commit complete.

SQL>

SQL> select * from
tbl_fl_archive;  2

        ID NAME
---------- --------------------
         1 Flashback Archive

SQL>

SQL>  alter table  tbl_fl_archive flashback archive fl_arch;

Table altered.


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;  2    3    4

DDATE
-----------------
28072017 19:07:33

SQL> delete
from tbl_fl_archive;  2

1 row deleted.

SQL> commit
  2  ;

Commit complete.

SQL> select * from
tbl_fl_archive;  2

no rows selected


SQL> select * from
tbl_fl_archive as of timestamp to_timestamp('28072017
19:07:33','ddmmyyyy hh24:mi:ss');
  2    3
        ID NAME
---------- --------------------
         1 Flashback Archive

SQL>


How to recover Data using Flashback Data Archive.

SQL> insert into tbl_fl_archive select * from tbl_fl_archive as of timestamp to_timestamp('28072017
19:07:33','ddmmyyyy hh24:mi:ss');  2

1 row created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_FBA_DDL_COLMAP_88630       TABLE
SYS_FBA_HIST_88630             TABLE
SYS_FBA_TCRV_88630             TABLE
TBL_FL_ARCHIVE                 TABLE

SQL> show user;
USER is "ADEEB"
SQL>