
Oracle Flashback technology | ||
---|---|---|
Logical Flashback (do not depend on RMAN, rely on undo data)
|
![]() | 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