Enabling Flashback Data Archive Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

The first thing that needs to be done to enable the flashback data archive is to create a flashback archive.

LUTZ  @ orcl SQL> CREATE FLASHBACK ARCHIVE my_5_year_archive

  TABLESPACE fba_5_years_01
    QUOTA 10G
      RETENTION 5 YEARS;

This statement creates an object in the data dictionary, which is a logical container for the storage of history data.

SYS AS SYSDBA @ orcl SQL> SELECT * FROM dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME  STATUS

---------------------- -----------------  -----------------  ----------  ------------- --------------------------------------

MY_5_YEAR_ARCHIVE           1              1825             05-DEC-07    11.11.23.000000000 PM 05-DEC-07       11.11.23.000000000 PM

% There are a number of restrictions for flashback archives:

• The tablespaces used for a flashback archive must use local extent management and automatic segment space management.

• The database must use automatic undo management.

With the QUOTA clause, the amount of disk space consumed by data archive in a tablespace is limited.

SYS AS SYSDBA @ orcl SQL> SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE# TABLESPACE_NAME  QUOTA_IN_MB
------------------ ----------------------- ---------------  ------------
MY_5_YEAR_ARCHIVE             1             FBA_5_YEAR_01        50

SYS AS SYSDBA @ orcl SQL> ALTER FLASHBACK ARCHIVE my_5_year_archive
                            ADD TABLESPACE fba_5_year_02 QUOTA 20M; 

SYS AS SYSDBA @ orcl SQL> ALTER FLASHBACK ARCHIVE my_5_year_archive
                            MODIFY TABLESPACE fba_5_year_02 QUOTA 30M;

Here are the quotas in the data dictionary again:

SYS AS SYSDBA @ orcl SQL> SELECT * FROM dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME       QUOTA_IN_MB
---------------------- ------------------ ---------------      ----------------
Y_5_YEAR_ARCHIVE        1                FBA_5_YEAR_01                  50
 
MY_5_YEAR_ARCHIVE        1                FBA_5_YEAR_02                  30

LUTZ  @ orcl SQL> ALTER FLASHBACK DATA ARCHIVE my_5_year_archive

      MODIFY RETENTION 10 YEARS;

SYS AS SYSDBA @ orcl SQL> SELECT * FROM dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME  STATUS
---------------------- -----------------  -----------------  ----------  -------------
--------------------------------------

MY_5_YEAR_ARCHIVE           1              3650             05-DEC-07    11.11.23.000000000 PM 05-DEC-07       11.11.23.000000000 PM

To remove history data from the flashback archive ad hoc, and before the RETENTION time has exceeded,  the PURGE clause has to be used:

LUTZ  @ orcl SQL> ALTER FLASHBACK ARCHIVE my_5_year_archive
                   PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE); 

It is possible to define a DEFAULT FLASHBACK ARCHIVE for a database. However, note that there can only be one default flashback archive.

LUTZ  @ orcl SQL> ALTER FLASHBACK DATA ARCHIVE my_5_year_archive
      SET DEFAULT;

SYS AS SYSDBA @ orcl SQL> SELECT flashback_archive_name, status
                                FROM dba_flashback_archive;

FLASHBACK_ARCHIVE_NAME  STATUS
---------------------- --------
MY_5_YEAR_ARCHIVE       DEFAULT

The next step after creating the flashback archive is to associate it with a table. In other words, flashback archiving should be enabled for a table, and the flashback archive to be used must be enabled.

SYS AS SYSDBA @ orcl SQL> ALTER TABLE lutz.t_d
                            FLASHBACK ARCHIVE my_5_year_archive; 

Now the server will track DMLs on the table lutz.t_d.