Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, August 14, 2015

Oracle Total Recall or Flashback data archive:

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: