Total Recall or Flashback data archive:
A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.
Flashback data archive is stored in one or more tablespaces and can have multiple flashback data archives at the same time in the database.
Flashback data archive is configured using retention time i.e you can define the flashback data archive to live as long as you want..
SQL> show user
USER is "SYS"
SQL> create tablespace tbs1 datafile '+DATA2' size 10m autoextend on next 1m;
Tablespace created.
SQL> create tablespace tbs2 datafile '+DATA2' size 10m autoextend on next 1m;
Tablespace created.
SQL> create user archive identified by archive default tablespace tbs1;
User created.
SQL> alter user archive quota unlimited on tbs1;
User altered.
SQL> alter user archive quota unlimited on tbs2;
User altered.
SQL> grant create session,resource, create table,create database link to archive;
Grant succeeded.
SQL> grant create procedure to archive;
Grant succeeded.
SQL> grant flashback archive administer to archive;
Grant succeeded.
create flashback data archive:
SQL> show user
USER is "ARCHIVE"
SQL> create flashback archive fda tablespace tbs1 retention 100 year;
Flashback archive created.
Once enabled only a user with DBA privileges or user with FLASHBACK ARCHIVE ADMINISTER” privilege can disable this flashback data archive on a table.
SQL> alter flashback archive fda add tablespace tbs2 quota 5g;
Flashback archive altered.
SQL> grant flashback archive on fda to hr;
Grant succeeded.
SQL> show user
USER is "HR"
SQL> alter table employees flashback archive fda;
Table altered.
Lets make changes to the employees table:
SQL> update employees set first_name='SAMEER' where first_name ='Peter';
3 rows updated.
SQL> commit;
Commit complete.
SQL> select first_name,salary from employees where first_name='SAMEER';
FIRST_NAME SALARY
-------------------- ----------
SAMEER 2500
SAMEER 10000
SAMEER 9000
SQL> select employee_id ,salary from employees where first_name='SAMEER';
EMPLOYEE_ID SALARY
----------- ----------
144 2500
150 10000
152 9000
SQL> update employees set salary=90000 where first_name='SAMEER';
3 rows updated.
SQL> commit;
Commit complete.
SQL> select employee_id ,salary from employees where first_name='SAMEER';
EMPLOYEE_ID SALARY
----------- ----------
144 90000
150 90000
152 90000
Horaaaa ….. Now i can draw three paychecks… Shhhh…is there a HR person around?
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
select * from dba_flashback_Archive;
OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
ARCHIVE
FDA
1 36500 14-AUG-15 02.17.33.000000000 PM 14-AUG-15 02.17.33.000000000 PM
SQL> select table_name,flashback_archive_name,status from dba_flashback_archive_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NAME STATUS
----------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEES FDA ENABLED
Mr Peter went to the HR and complained he didn’t receive the paycheck for the previous week? Hmm what happened?
Lets do the audit:
SQL> select employee_id,first_name,salary from employees where upper(first_name)='PETER';
no rows selected
Where did Peter go?
SQL> select employee_id,first_name,salary from employees as of timestamp (systimestamp -interval '20' minute) where upper(first_name)='PETER';
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
152 Peter 9000
144 Peter 2500
150 Peter 10000
SQL> select employee_id,first_name,salary from employees where employee_id in (152,144,150);
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
144 SAMEER 90000
150 SAMEER 90000
152 SAMEER 90000
Gotcha… Mr SAMEER is the culprit here …. Lets fix the things up..
SQL> update employees set first_name =(select first_name from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=144) where employee_id=144;
1 row updated.
SQL> update employees set first_name =(select first_name from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=144) where employee_id=150;
1 row updated.
SQL> update employees set first_name =(select first_name from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=152) where employee_id=152;
1 row updated.
SQL> commit;
Commit complete.
SQL> select employee_id,first_name,salary from employees where employee_id in (152,144,150);
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
144 Peter 90000
150 Peter 90000
152 Peter 90000
SQL> update employees set salary = (select salary from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=152) where employee_id=152;
1 row updated.
SQL> select employee_id,first_name,salary from employees where employee_id in (152,144,150);
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
144 Peter 90000
150 Peter 90000
152 Peter 9000
SQL> update employees set salary = (select salary from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=150)where employee_id=150;
1 row updated.
SQL> update employees set salary = (select salary from employees as of timestamp (systimestamp -interval '40' minute ) where employee_id=144)where employee_id=144;
1 row updated.
SQL> select employee_id,first_name,salary from employees where employee_id in (152,144,150);
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
144 Peter 2500
150 Peter 10000
152 Peter 9000
SQL> commit;
Commit complete.
SQL> show user
USER is "ARCHIVE"
SQL> drop flashback archive fda;
Flashback archive dropped.
SQL>
SQL>
No comments:
Post a Comment