| 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_TABLESSQL> 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
