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 our application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.
Prior to oracle 11g, Flashback technology to a large part was based on the availability of undo data or flashback logs and both the undo data as well as flashback logs are subject to recycling when out of space pressure exists. The UNDO tablespace in Oracle was primarily meant for transaction consistency and not data archival. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.Let’s look at an example :
Creating a Flashback Data Archive :
SQL>
create flashback archive near_term
tablespace users
retention 1 month ;
Flashback archive created.
The archive is created in the tablespace Users. Assume we have to record changes to a table called employees which is in "HR" schema . All we need to do is enable the Flashback Data Archive status of the table to start recording the changes in that archive .
SQL>
alter table hr.employees flashback archive near_term;
Table altered.
This puts the table into the Flashback Data Archive mode. All the changes to the rows of the table will be now tracked permanently.
SQL>
select salary,job_id from hr.employees where employee_id=121;
SALARY JOB_ID
--------- ----------
8200 ST_MAN
SQL>
update hr.employees set salary=50000 where employee_id=121;
1 row updated.
SQL>
commit;
Commit complete.
Now, if we select the row, it will always display 50000 in this column. To find out the older value as of a certain time, we can use the Flashback query as shown below :
SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2011 10:55:00','mm/dd/yyyy hh24:mi:ss') where employee_id =121;
SALARY
---------
8200
Now, after some time, when the undo data has been purged out of the undo segments, query the flashback data again:
SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2010 10:55:00','mm/dd/yyyy hh24:mi:ss') where employee_id =121 ;
SALARY
---------
8200
It comes back with the result :500000 The undo is gone, so where did the data come from .We can do that using autotrace and see the execution plan:
SQL>
set autotrace traceonly explain
SQL> select salary from hr.employees as of timestamp to_timestamp ('09/5/2010 10:55:00','mm/dd/yyyy hh24:mi:ss') where employee_id =121;
Check the explain Plan detail by clicking below link :
http://www.4shared.com/document/WXMMFOS8/fda_explain_tab.html
This output answers the riddle “Where did the data come from?”; it came from the table SYS_FBA_HIST_68909, which is a location in the Flashback Archive we defined earlier for that table. We can check the table but it’s not supported by Oracle to directly peek at that data there. Anyway, I don’t see a reason we would want to do that.
The data inside the archive is retained but until how long? This is where the retention period comes into play. It’s retained up to that period. After that, when new data comes in, the older data will be purged. We can also purge it ourself,
e.g.
SQL>
alter flashback archive near_term purge before scn xxxxxxxx;
Disable flashback : Disable flashback archiving for the table employee
:
SQL>
ALTER TABLE hr.employee NO FLASHBACK ARCHIVE;
Remove Flashback Data Archive and all its historical data, but not its tablespaces:
SQL>
DROP FLASHBACK ARCHIVE near_term ;
Use Cases :
Flashback Data Archive is handy for many purposes. Here are some ideas:
• To audit for recording how data changed
• To enable an application to undo changes (correct mistakes)
• To debug how data has been changed
• To comply with some regulations that require data must not be changed after some time. Flashback Data
Archives are not regular tables so they can’t be changed by typical users.
• Recording audit trails on cheaper storage thereby allowing more retention at less cost
For more detail visit
:
http://www.oracle.com/pls/db111/search?remark=quick_search&word=flashback+data+archive
Enjoy
:-)
