Oracle Flashback Data Archive

Oracle Flashback Data Archive 

At this Article I would like to share new feature of Oracle 11g version Flashback Data Archive/ Total Recall

Today I tested this to keep transaction, I think at oracle 10g version we used to Log Miner or Developed Trigger to keep the datas. After I used FBDA I saw incredibly to made datas in the compressed file.

My user have to have  FLASHBACK ARCHIVE ADMINISTER privilege.

So, let me start

Frist I give a privilege to my user

SQL> grant flashback archive administer to veysi;

Grant succeeded.

Created a tablespace for testing Flasback data archive

SQL> CREATE TABLESPACE tblspce_arch DATAFILE ‘/u01/app/oradata/orcl/flshbck_arch.dbf’ size 10m;

Tablespace created.

Lets tested it by creating table and using transactions

SQL> create table vey_flsh_arch (id number, name varchar2(15));

Table created.

SQL> insert into vey_flsh_arch values(1,’Veysi’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vey_flsh_arch;

        ID NAME

———- ——————–

         1 Veysi

Create A flashback archive on this tablespace and give for your retention time.

SQL> create flashback archive FLSHBACK_ARCH tablespace tblspce_arch retention 5 year;

Flashback archive created.

Now we can use the flashback query through 5 years.

So register flashback archive on apllication

SQL> begin

     dbms_flashback_archive.register_application

     (application_name=>'veyAp',

     flashback_archive_name=>' FLSHBACK_ARCH);

     end;

     /  

PL/SQL procedure successfully completed.

Now Add our table on it

SQL> begin

     dbms_flashback_archive.add_table_to_application

     (application_name=>'veyAp',

     table_name=>'vey_flsh_arch',

     schema_name=>'VEYSI');

     end;

     /  

PL/SQL procedure successfully completed.

Or use the following command.

SQL> alter table vey_flsh_arch flashback archive FLSHBACK_ARCH;

Table altered.

So, let look at our table

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Upps! No rows, it means we have to enabled our table on flashback archive tables.

SQL> select table_name,status from user_flashback_archive_tables;

SQL> exec dbms_flashback_archive.enable_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS

---------- ----------

VEY_FLSH_ARCH         ENABLED

Also we can lock down our table no one can change any transactions

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL> delete from vey_flsh_arch;

delete from vey_flsh_arch

            *

ERROR at line 1:

ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

Unlock by using following command

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'veyAp')

PL/SQL procedure successfully completed.

SQL>

Now it will be on the Flashback archive and every transaction we can use it on it.

SQL> set time on;

SQL> insert into vey_flsh_arch values (2,’Gunay’)

1 row inserted.

SQL>delete from vey_flsh_arch;

2 row deleted.

SQL> commit;

Commit complete.

SQL> select * from vey_flsh_arch;

no rows selected

SQL> select * from tbl_fl_archive as of timestamp to_timestamp(’08082013 15:08:30′,’ddmmyyyy hh24:mi:ss’);

        ID NAME

———- ——————–

         1 Veysi