Oracle Flashback Technology (III) - Flashback Data Archive



Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:



Using Flashback Data Archive (Oracle Total Recall)
  • With Data Archive you can store and track transactional changes to a record over its lifetime.
  • It permanently stores undo information on flashback archives, allowing you to keep the transactional history of a object since its creation.
  • Flashback archives are enabled on individual tables and are located in tablespaces, and have a name, a specified retention period and a space quota on the tablespace.
  • A database can have multiple flashback archives.
  • when a DML transaction commits on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive.
  • FBDA also manages the data within the flashback archives (purging data beyond retention period).
  • Historical data can be queried using the Flashback Query AS OF clause.
  • Useful for compliance with record stage policies and audit requirements.


To enable flashback archiving for a table:
  • You need FLASHBACK ARCHIVE privilege on a flashback data archive
  • Table cannot be clustered, nested, temporary, remote or external
  • Table cannot have LONG nor nested columns


Create a Flashback Data Archive
(1) Create a new tablespace (you may also use an existing one)

SQL> create tablespace fda_ts
   datafile '/u01/app/oracle/oradata/test112/fda1_01.dbf'
   size 1m autoextend on next 1m;

SQL> select tablespace_name, status, contents, retention
  from dba_tablespaces
  where tablespace_name ='FDA_TS';

TABLESPACE_NAME                STATUS    CONTENTS  RETENTION   
------------------------------ --------- --------- ----------- 
FDA_TS                         ONLINE    PERMANENT NOT APPLY   

(2) Create Flashback archvies:

SQL> create flashback archive default fda_1m tablespace fda_ts   -- Must be SYSDBA to create DEFAULT FDA
  quota 1G retention 1 month;                                -- To change use ALTER FLASHBACK ARCHIVE...SET DEFAULT

SQL> create flashback archive fda_2yr tablespace fda_ts retention 2 year;
  
SQL> create flashback archive fda_10d tablespace fda_ts retention 10 day;

Managing Flashback Data Archives:
(1) Manage FDA tablespaces:

ALTER FLASHBACK ARCHIVE...
   ...SET DEFAULT;
   ... ADD TABLESPACE... QUOTA...;
   ... MODIFY TABLESPACE...
   ... REMOVE TABLESPACE...

(2) Manage retention period:

ALTER FLASHBACK ARCHIVE fda_name MODIFY RETENTION n [Year | Month | day ];

(3) Purge historical data

ALTER FLASHBACK ARCHIVE...
   ...PURGE ALL;                          -- Purge ALL historical data
   ...PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL 'n' DAY);
   ...PURGE BEFORE SCN scn_num;

(4) Drop FDA:

DROP FLASHBACK ARCHIVE fda_name;           -- Drops FDA. Keeps tablespace.

Enabling FDAs on objects:
  • FDA is disabled by default
  • User needs FLASHBACK ARCHIVE privilege to create enable flashback archive on object.
SQL> conn sys/pwd as sysdba;

SQL> grant flashback archive on fda_1m to userA;

SQL> conn userA/pwd;

SQL> Create table emp 
  (empno number primary key,
   ename varchar2(20),
   salary number) 
  flashback archive fda_1m;


-- To Disable Flashback archive on table
SQL> ALTER TABLE emp NO flashback archive;

Information about Flashback data Archives:
DBA_FLASHBACK_ARCHIVE, DBA_FLASHBACK_ARVHIE_TS and DBA_FLASHBACK_ARCHIVE_TABLES
SQL> select owner_name, flashback_archive_name, retention_in_days, status, 
       to_char(last_purge_time, 'dd-mon-yy hh24:mi:ss')
from dba_flashback_archive;

OWNER_NAME    FLASHBACK_ARCHIVE_NAME  RETENTION_IN_DAYS      STATUS  LAST_PURGE_TIME           
------------- ----------------------- ---------------------- ------- ------------------------- 
SYS           FDA_1M                  30                     DEFAULT 25-oct-11 13:34:14 
SYS           FDA_2YR                 730                            25-oct-11 13:34:54 
SYSTEM        FDA_10D                 10                             25-oct-11 13:38:05

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE#     TABLESPACE_NAME                QUOTA_IN_MB 
----------------------- ---------------------- ------------------------------ ------------
FDA_1M                  1                      FDA_TS                         1024        
FDA_2YR                 2                      FDA_TS                                     
FDA_10D                 3                      FDA_TS                                     


SQL> select * from dba_flashback_archive_tables;

TABLE_NAME  OWNER_NAME                     FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS   
----------- ------------------------------ ---------------------- ------------------- -------- 
EMP         SYSTEM                         FDA_1M                 SYS_FBA_HIST_75434  ENABLED  


Example: Viewing table history.
(1) Insert data on emp
(2) Keep record of some points in time
(3) Query the historical data on emp
SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
       current_scn start_scn
     from v$database;

START_TIME         START_SCN              
------------------ ---------------------- 
25-oct-11 14:22:25 1498655       

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 

-- PL/SQL block performs a number of DMLs on emp and prints timestamps
set serveroutput on
declare
 procedure get_timestamp
 is
   v_time varchar2(25);
   v_scn  integer;
 begin
   select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
        current_scn start_scn into v_time, v_scn
   from v$database;
   dbms_output.put_line('timestamp: ' || v_time);
   dbms_output.put_line('SCN:       ' || v_scn);
end;
 
begin
  insert into emp values (1, 'John', 2000);
  commit;
  dbms_lock.sleep(2);
  get_timestamp();
  for i in 1 .. 10 
  loop
   update emp set salary =salary*1.05 where empno=1;
   commit;
   dbms_lock.sleep(2);
   if i=5 then
     insert into emp values (2, 'Mary', 3000);
     update emp set salary = 2500 where empno =1;
     commit;
     dbms_lock.sleep(2);
     update emp set ename = initcap(ename);
     commit;
     insert into emp values (3, 'Gary', 1500);
     delete from emp where empno=2;
     commit;
     get_timestamp();   
   end if;
  end loop;
  dbms_lock.sleep(2);
  get_timestamp();
end;
/

anonymous block completed
timestamp: 25-oct-11 14:22:27
SCN:       1498659
timestamp: 25-oct-11 14:22:39
SCN:       1498683
timestamp: 25-oct-11 14:22:51
SCN:       1498700

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500         

SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') end_time, 
       current_scn end_scn
     from v$database;
END_TIME           END_SCN                
------------------ ---------------------- 
25-oct-11 14:22:51 1498701


(a) Select data at a point in time
SQL> select *  from emp as of scn 1498683;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 2500                   
3                      Gary                 1500       

SQL> select * 
     from emp as of timestamp to_timestamp('25-oct-11 14:22:51', 'dd-mon-yy hh24:mi:ss');
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500  


(b) select all versions of a row betwen two points in time

SQL> select *
     from emp
       versions between scn 1498659 and 1498700
     where empno =1;
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
1                      John                 2000                   
1                      John                 2100                   
1                      John                 2205                   
1                      John                 2315.25                
1                      John                 2431.0125              
1                      John                 2552.563125            
1                      John                 2500                   
1                      John                 2500                   
1                      John                 2625                   
1                      John                 2756.25                
1                      John                 2894.0625              
1                      John                 3038.765625            
1                      John                 3190.70390625          

 14 rows selected 


SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, ename, salary
     from emp
        versions between scn 1498659 and 1498700
     where empno =1;

XID              START_SCN   END_SCN                OPERATION EMPNO   ENAME                SALARY                 
---------------- ----------- ---------------------- --------- ------- -------------------- ---------------------- 
03000F008B040000 1498633     1498674                I         1       John                 3190.70390625          
05001F00AA040000 1498657     1498661                I         1       John                 2000                   
030003008B040000 1498661     1498664                U         1       John                 2100                   
02000A007E040000 1498664     1498667                U         1       John                 2205                   
01000D003A030000 1498667     1498670                U         1       John                 2315.25                
0400090075030000 1498670     1498672                U         1       John                 2431.0125              
06000B0094040000 1498672     1498674                U         1       John                 2552.563125            
0900080096040000 1498674     1498678                U         1       John                 2500                   
03001F008B040000 1498678     1498685                U         1       John                 2500                   
09001F0097040000 1498685     1498688                U         1       John                 2625                   
080010006C050000 1498688     1498691                U         1       John                 2756.25                
0700190078030000 1498691     1498694                U         1       John                 2894.0625              
03001A008B040000 1498694     1498697                U         1       John                 3038.765625            
05001E00AB040000 1498697                            U         1       John                 3190.70390625          

 14 rows selected 







No comments:

Post a Comment