Step by step test flashback data archives, a.k.a. Total Recall

–Flashback Data Archives, a.k.a. Total Recall
–  A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime.
–  Test version: 11.1.0.6

–create a system-wide default flashback archive on an existing tablespace.
conn / as sysdba
SYS@orcl11g>create flashback archive default fb tablespace example retention 1 year;
Flashback archive created.

–create test table t
conn t/t@orcl11g
create table t as select * from dba_objects;

T@orcl11g>alter table t flashback archive;
Table altered.

T@orcl11g>select systimestamp from dual;
SYSTIMESTAMP
———————————————————-
24-NOV-08 04.05.49.182000 PM +08:00

T@orcl11g>select timestamp_to_scn(systimestamp) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
1052630  –created point

–insert 1 time
T@orcl11g>insert into t select * from t;
68865 rows created.
T@orcl11g>commit;
Commit complete.
T@orcl11g>select current_scn from v$database;
CURRENT_SCN
———–
1052723  –test point A

–insert 2 times
T@orcl11g>insert into t select * from t;
137730 rows created.
T@orcl11g>insert into t select * from t;
275460 rows created.
T@orcl11g>commit;
Commit complete.
T@orcl11g>select current_scn from v$database;
CURRENT_SCN
———–
1054928  –test point B

–insert another time
T@orcl11g>insert into t select * from t;
550920 rows created.
T@orcl11g>commit;
Commit complete.
T@orcl11g>select current_scn from v$database;
CURRENT_SCN
———–
1056385  –test point C

–check count(*) of T at created point, point A, B and C
T@orcl11g>select count(*) from t as of scn 1052630;
COUNT(*)
———-
68865  –start count

–you can also use timestamp to check
T@orcl11g>select count(*) from t as of timestamp to_timestamp_tz(‘24-NOV-08 04.05.49.182000 PM +08:00′,’DD-Mon-RR HH:MI:SS.FF AM TZH:TZM’);
COUNT(*)
———-
68865  –start count

T@orcl11g>select count(*) from t as of scn 1052723;
COUNT(*)
———-
137730    –point A, doubled

T@orcl11g>select count(*) from t as of scn 1054928;
COUNT(*)
———-
550920  –point B, doubled 2 times comparing to A

T@orcl11g>select count(*) from t as of scn 1056385;

COUNT(*)
———-
1101840  –point C, doubled comparing to B

–it is exactly expected result! You may test it againt a long time period.

–Let’s check the under line view
–dba_flashback_archive includes all flashback archive defined in the db
T@orcl11g>select * from dba_flashback_archive;
FLASHBACK_ FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
———- —————— —————– ———————————–
———————–
LAST_PURGE_TIME                                                             STATUS
————————————————————————— ——-
FB                          1               365 24-NOV-08 04.03.14.000000000 PM
24-NOV-08 04.03.14.000000000 PM                                             DEFAULT

–dba_flashback_archive_ts includes tablespaces information related to falshback archive
–you can have more than 1 tablespace in a flashback archive
T@orcl11g>select * from dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_M
———- —————— ——————– ———-
FB                          1 EXAMPLE

–dba_flashback_archive_tables inculdes tables information
T@orcl11g>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME
———- ———- ————————- ——————-
T          T          FB                        SYS_FBA_HIST_70992

–let’s clean up
T@orcl11g>alter table t no flashback archive;
Table altered.
T@orcl11g>select * from dba_flashback_archive_tables;
no rows selected    –Table is removed

conn / as sysdba
SYS@orcl11g>drop flashback archive fb;
Flashback archive dropped.
SYS@orcl11g>select * from dba_flashback_archive;
no rows selected  –fb is dropped
SYS@orcl11g>select * from dba_flashback_archive_ts;
no rows selected  –related tablspace information is disappear, but the tablespace still exists

–Limitations:
–These DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive
–
**ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or subpartition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
**DROP TABLE statement
**RENAME TABLE statement
**TRUNCATE TABLE statement

–
–Sql statement reference
–
-Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data will be retained for one year:
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1 QUOTA 10G RETENTION 1 YEAR;

–Create a Flashback Data Archive named fla2 that uses tablespace tbs2, whose data will be retained for two years:
CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;

–Make Flashback Data Archive fla1 the default Flashback Data Archive:
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

–To Flashback Data Archive fla1, add up to 5 G of tablespace tbs3:
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;

–To Flashback Data Archive fla1, add as much of tablespace tbs4 as needed:
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs4;

–Change the maximum space that Flashback Data Archive fla1 can use in tablespace tbs3 to 20 G:
ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs3 QUOTA 20G;

–Allow Flashback Data Archive fla1 to use as much of tablespace tbs1 as needed:
ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1;

–Change the retention time for Flashback Data Archive fla1 to two years:
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;

–Remove tablespace tbs2 from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;

–Purge all historical data from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;

–Purge all historical data older than one day from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1
PURGE BEFORE TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1′ DAY);

–Purge all historical data older than SCN 728969 from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;

–Remove Flashback Data Archive fla1 and all its historical data, but not its tablespaces:
DROP FLASHBACK ARCHIVE fla1;

–Create table employee and store the historical data in the default Flashback Data Archive:
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10)) FLASHBACK ARCHIVE;

–Create table employee and store the historical data in the Flashback Data Archive fla1:
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10)) FLASHBACK ARCHIVE fla1;

–Enable flashback archiving for the table employee and store the historical data in the default Flashback Data Archive:
ALTER TABLE employee FLASHBACK ARCHIVE;

–Enable flashback archiving for the table employee and store the historical data in the Flashback Data Archive fla1:
ALTER TABLE employee FLASHBACK ARCHIVE fla1;

–Disable flashback archiving for the table employee:
ALTER TABLE employee NO FLASHBACK ARCHIVE;