This article presents new feature of 11g – Flashback Data Archive.

This functionality allows to save all transactions executed on a table for longer period than just UNDO_RETENTION parameter and UNDO tablespace. In 11g you can create dedicated space to keep changed data(DML operations, DDL operations) for longer specified retention period.

Historical records can be reviewed using standard flashback sql queries feature.

Setup Flashback Data Archive

NOTE – all commands are executed as user TOMASZ with role DBA. Only dedicated commands are executed as user SYS.

Setup consists of following steps:

1. Create Flashback Data Archive – you can create many flashback data archives – as user TOMASZ

create flashback archive fda1
tablespace users quota 100m 
retention 1 year;

flashback ARCHIVE created.

create flashback archive fda2
tablespace users quota 100m 
retention 1 year;

flashback ARCHIVE created.

2. Specify default Flashback Data Archive if you have defined many – as user SYS

Setting default Flashback Data Archive can be done only by sysdba using:

• ALTER FLASHBACK ARCHIVE .. SET DEFAULT
• CREATE FLASHBACK ARCHIVE DEFAULT …

create flashback archive default fda3
tablespace users quota 100m
retention 1 year;

Flashback archive altered.

alter flashback archive fda2 set default;

Flashback archive altered.

NOTE – if default is already defined command CREATE FLASHBACK ARCHIVE DEFAULT can fail. In such case create such flashback without DEFAULT and use ALTER FLASHBACK … SET DEFAULT

SQL> create flashback archive default fda4
  2  tablespace users quota 100m
  3  retention 1 year;
tablespace users quota 100m
           *
ERROR at line 2:
ORA-55609: Attempt to create duplicate default Flashback Archive

3. Enable Flashback Data Archive on a table – as user TOMASZ

create table test_tbl
(id number);

table TEST_TBL created.

--enable flashback archive default is assigned to table
alter table test_tbl flashback archive;

table TEST_TBL altered.

--disable flashback archive
alter table test_tbl no flashback archive;

table TEST_TBL altered.

--enable flashback archive with specific name
alter table test_tbl flashback archive fda2;

table TEST_TBL altered.

4. View Flashback data archive details

Useful views:

• *_FLASHBACK_ARCHIVE – information about created flashback data archives in database
• *_FLASHBACK_ARCHIVE_TS – information about space allocated in tablespaces by flashback data archive
• *_FLASHBACK_ARCHIVE_TABLES – information about tables that are using flashback archive

select owner_name, flashback_archive_name, status 
from dba_flashback_archive;

OWNER_NAME   FLASHBACK_ARCHIVE_NAME     STATUS
------------ -------------------------- -------
TOMASZ       FDA1                       
TOMASZ       FDA2                       DEFAULT
SYS          FDA3

select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------- ------------------ --------------- -----------
FDA1                   1                  USERS           100        
FDA2                   2                  USERS           100        
FDA3                   3                  USERS           100

select * from dba_flashback_archive_ts;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ---------------------- ------------------ ------
TEST_TBL   TOMASZ     FDA2                   SYS_FBA_HIST_81365 ENABLED

5. Review data

It works in the same way as flashback sql queries. You can notice data is preserved even for TRUNCATE and DROP COLUMN operations.

insert into test_tbl values(10);

commit;

truncate table test_tbl;

alter table test_tbl add(id1 number);

alter table test_tbl drop column id;

insert into test_tbl values(20);

commit;

--old data survives truncate, drop column
select * from test_tbl 
versions between scn
      minvalue
  AND maxvalue;

       ID1 D_1975947_ID
---------- ------------
                     10 
        20

Flashback archive restrictions

If flashback archive is enabled some DDL operations can cause ORA-55610 – Invalid DDL statement on history-tracked table

• ALTER TABLE that includes UPGRADE TABLE
• ALTER TABLE that moves or exchanges a partition or subpartition operation
• DROP TABLE
• More complex DDL can be performed using DISASSOCIATE_FBA and REASSOCIATE_FBA procedures of the DBMS_FLASHBACK_ARCHIVE package

drop table test_tbl;

ORA-55610: Invalid DDL statement on history-tracked table

You can’t enable Flashback Data Archive for

• nested, clustered, temporary, remote or external tables
• tables that contain LONG or nested columns

Useful commands

Privileges

--grant privileges on a flashback archive to a user
grant flashback archive on fda1 to tomasz;

--grant privilege to manage flashback archive like create/drop
grant flashback archive administer to tomasz;

Modify retention

--modify retention for flashback archive
alter flashback archive fda1 
modify retention 2 year;

alter flashback archive fda1 
modify retention 2 month;

alter flashback archive fda1 
modify retention 2 day;

Remove old data

-- remove old data from flashback archive
alter flashback archive fda1 
purge all;

alter flashback archive fda1
purge before timestamp (systimestamp - interval '2' month);

alter flashback archive fda1 
purge before scn 256745;

Drop flashback archive

drop flashback archive fda1;

Have a fun 🙂

Tomasz