Oracle 11g Flashback Data Archive (FDA) Know-how－DanBrother的部落格

Oracle 11g Flashback Data Archive (FDA) Know-how

FDA is a database object that stores historical information in one or more tablespaces and
keeps track of history of one or more tables.

A background process named "FBDA" starts with the database;

SQL> select paddr,pserial#,name,description,error
from v$bgprocess
where name = 'FBDA';
>>
PADDR PSERIAL# NAME DESCRIPTION ERROR
------------------------ ----------- ------- ------------------------------------ ----------
000000017768D230 134 FBDA Flashback Data Archiver Process 0

1. Test Data Preparation

SQL> create table fda_test_tab
(id number(10),
balance number(10),
lastmodified timestamp
) compress;

SQL> insert into fda_test_tab(id,balance,lastmodified)
values(100,0,systimestamp);

SQL> insert into fda_test_tab(id,balance,lastmodified)
values(200,0,systimestamp);

SQL> COMMIT;

SQL> col lastmodified format a30
SQL> select * from fda_test_tab;
>>
ID BALANCE LASTMODIFIED
---------- ---------- ------------------------------
100 0 13-JAN-14 05.39.24.065001 PM
200 0 13-JAN-14 05.39.24.066529 PM

2. Create a tablespace to store FDA
-- conn as SYS
-- Create a Bigfile tablespace for flashback data archive:

SQL> create bigfile tablespace LOG_ARCHIVE_TBS
datafile size 1G autoextend on next 100M;

3. Create a Flasback data archive object
SQL> create flashback archive DATA_FBA
tablespace LOG_ARCHIVE_TBS
quota 1G
retention 3 MONTH;

4. alter table to allow flashback archive:
-- conn as SYS
SQL> grant flashback archive on DATA_FBA to testuser;

-- conn as TESTUSER
SQL> col FLASHBACK_ARCHIVE_NAME format a22
SQL> col TABLESPACE_NAME format a20
SQL> col QUOTA_IN_MB format a20
SQL> select * from dba_flashback_archive_ts;
>>
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------- ------------------ -------------------- --------------------
DATA_FBA 1 LOG_ARCHIVE_TBS 1024

SQL> alter table fda_test_tab flashback archive DATA_FBA;

SQL> col TABLE_NAME format a12
SQL> col OWNER_NAME format a12
SQL> col FLASHBACK_ARCHIVE_NAME format a20
SQL> col ARCHIVE_TABLE_NAME format a20
SQL> col STATUS format a10
SQL> select * from dba_flashback_archive_tables;
>>
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------ ------------ ---------------------- -------------------- ----------
FDA_TEST_TAB TESTUSER DATA_FBA SYS_FBA_HIST_91526 ENABLED

select owner_name,flashback_archive_name,retention_in_days
from dba_flashback_archive;
OWNER_NAME   FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
-----------------   ----------------------------------- ----------------------------
SYS                             DATA_FBA                                    90

5. Perform Testing

begin
for i in 1..10 loop
update fda_test_tab
set balance=balance+10,lastmodified=systimestamp
where id = 100;
dbms_lock.sleep(1); -- suspend for 1 second
COMMIT;
end loop;
end;
/

SQL> select * from fda_test_tab;

ID BALANCE LASTMODIFIED
---------- -------------- -------------------------------------
100 100 13-JAN-14 05.40.34.951210 PM
200 0 13-JAN-14 05.39.24.066529 PM

SQL> select id,balance,lastmodified from fda_test_tab
VERSIONS BETWEEN TIMESTAMP
to_timestamp('2014-01-13 17:39:24','yyyy-mm-dd hh24:mi:ss') and
to_timestamp('2014-01-13 17:40:36','yyyy-mm-dd hh24:mi:ss')
where id = 100
order by 3 asc;
>>
ID BALANCE LASTMODIFIED
---------- ---------- ------------------------------------
100 0 13-JAN-14 05.39.24.065001 PM
100 10 13-JAN-14 05.40.25.937514 PM
100 20 13-JAN-14 05.40.26.938685 PM
100 30 13-JAN-14 05.40.27.940716 PM
100 40 13-JAN-14 05.40.28.942853 PM
100 50 13-JAN-14 05.40.29.944081 PM
100 60 13-JAN-14 05.40.30.945295 PM
100 70 13-JAN-14 05.40.31.946534 PM
100 80 13-JAN-14 05.40.32.947736 PM
100 90 13-JAN-14 05.40.33.950055 PM
100 100 13-JAN-14 05.40.34.951210 PM

SQL> select startscn,endscn,id,balance,lastmodified from SYS_FBA_HIST_91526
order by 5 asc;
>>
STARTSCN ENDSCN ID BALANCE LASTMODIFIED
---------- ---------- ---------- ---- ------ ------------------------------
21906901 100 0 13-JAN-14 05.39.24.065001 PM
21906901 21906903 100 10 13-JAN-14 05.40.25.937514 PM
21906903 21906906 100 20 13-JAN-14 05.40.26.938685 PM
21906906 21906909 100 30 13-JAN-14 05.40.27.940716 PM
21906909 21906912 100 40 13-JAN-14 05.40.28.942853 PM
21906912 21906915 100 50 13-JAN-14 05.40.29.944081 PM
21906915 21906918 100 60 13-JAN-14 05.40.30.945295 PM
21906918 21906924 100 70 13-JAN-14 05.40.31.946534 PM
21906924 21906930 100 80 13-JAN-14 05.40.32.947736 PM
21906930 21906933 100 90 13-JAN-14 05.40.33.950055 PM

** [Note] **

To disable flashback archive for particular table, using the follow SQL statement:
(Be caution, once disabled, the archived history data is gone as well)

Connect as SYS or Archive Administrator (who has been granted FLASHBACK ARCHIVE ADMINISTER system privilege)

SQL> alter table testuser.fda_test_tab NO FLASHBACK ARCHIVE;
SQL> select * from dba_flashback_archive_tables;

no rows selected

SQL> alter table testuser.fda_test_tab FLASHBACK ARCHIVE DATA_FBA;

TABLE_NAME OWNER_ NAME FLASHBACK_ARCHIVE_TABLE_NAME STATUS
----------------- ------------ -------------------- ----------------------------------------- ----------
FDA_TEST_TAB TESTUSER DATA_FBA SYS_FBA_HIST_91526 ENABLED

SQL> select count(*) from SYS_FBA_HIST_91526;
ERROR at line 1:
ORA-00942: table or view does not exist

=> Because once disabled, the archived history data were lost