Flashback Data Archive 11g


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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s