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
