Oracle Flashback Data Archive - Oracle Total Recall

Flashback Data archive is the new feature introduced in Oracle 11g. It provides the ability to track and store all transitional changes to a table over its lifetime. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive. It allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time.

The database can have multiple flashback data archives, but only a single default archive. The individual flashback archive consists of one or more tablespaces or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace.

When a DML transaction commits an operation on a flashback archive enabled table, the FBDA (Flashback Data Archiver) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

To use this feature user should have below privileges
1.FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table
2.FLASHBACK ARCHIVE ADMINISTER system privilege to enable or disable Flashback data archive.

Creating a Flashback Data Archive
Create Flashback Data archive using below command
SQL> CREATE FLASHBACK ARCHIVE flashback_archive TABLESPACE flashback_archive_tbs QUOTA 15G RETENTION 5 YEARS;

Altering a Flashback Data Archive
You can add, modify and remove tablespaces, and change the retention, purge some or all of its data.

Add up to 2G of tablespace “flashback_archive_tbs1” to Flashback Data Archive flashback_archive1

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 ADD TABLESPACE flashback_archive_tbs1 QUOTA 2G;

Change the maximum space that Flashback Data Archive “flashback_archive1” can use in tablespace flashback_archive_tbs1 to 5G

SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 MODIFY TABLESPACE flashback_archive_tbs1QUOTA 5G;

Remove tablespace “flashback_archive_tbs1” from Flashback Data Archive
SQL> ALTER FLASHBACK ARCHIVE flashback_archive1 REMOVE TABLESPACE flashback_archive_tbs1;

Dropping a Flashback Data Archive
Remove Flashback Data Archive flashback_archive1 and all its historical data
SQL> DROP FLASHBACK ARCHIVE flashback_archive1;

Enabling and Disabling Flashback Data Archive
By default, flashback archiving is disabled for any table and you need FLASHBACK ARCHIVE privilege to enable for any table. After flashback archiving is enabled for a table, you can disable it only if you either have SYSDBA or FLASHBACK ARCHIVE ADMINISTER privilege.

Create table EMP and store the historical data in the DEFAULT Flashback Data Archive
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE;

Create table EMP and store the historical data in the Flashback_Archive1
SQL> CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ENAME VARCHAR2(20)) FLASHBACK ARCHIVE Flashback_Archive1;

Enable flashback archiving for the table EMP and store data in the default Flashback Data Archive
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE;

Enable flashback archiving for the table EMP and store data in Flashback Data Archive Flashback_Archive1
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE Flashback_Archive1;

Disable flashback archiving for the table employee:
SQL> ALTER TABLE EMP NO FLASHBACK ARCHIVE;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/