Basics of Oracle 12c’s Flashback Data Archive

By Chris Ruel, Michael Wessler

Oracle 12c’s Flashback Data Archive is a database mechanism that allows you to periodically or indefinitely store all row versions in a table over its lifetime. You can then choose a time to view the data as it existed at a specific point in time.

Be aware that Flashback Data Archive is a licensed feature. The good news: It is included with advanced compression, which makes the advanced compression cost an even better value.

You don’t need to code complex triggers to move rows to history tables. You also don’t need to code complex application logic to retrieve the data. The archiving is completely transparent to developers and end users. Oracle has sometimes referred to this feature as Oracle Total Recall. (No, that’s not a reference to some cheesy 1990s movie.)

When you enable Flashback Data Archive, the row versions are automatically compressed to conserve space. You can also specify the retention period.

You can’t do certain operations (such as DROP or TRUNCATE) on tables where you’ve enabled Flashback Data Archive. Furthermore, you can’t modify historical data; this ensures the validity and consistency of the archive data.

Flashback Data Archive is a totally online operation. No downtime is required to enable or use this feature. It’s enabled on a table-by-table basis. You can also group objects according to retention periods for easier management. Indexes aren’t maintained, but you can create your own index to facilitate searching.

After the specified retention period expires, data is automatically purged to conserve space. If space is a concern, you can set quotas to limit archive growth. Also, to best organize your Flashback data, create tablespaces to store Flashback data for specific retention periods.

If an archive quota is exceeded, new transactions are blocked. Keep an eye on space usage and periodically check the alert log for space warnings.

Here’s how you might use a Flashback Data Archive:

  1. Create a tablespace that holds data for a one-year retention period:

    <create tablespace fbda_1yr datafile
    '/opt/oracle/oradata/dev12c/fdba_1yr_01.dbf' size 100M
    Autoextend on next 100M maxsize 10g;>

    The tablespace in this example is named for documentation purposes. You see this:

    Tablespace created.
  2. Create a Flashback Data Archive object in your tablespace with a one-year retention and a 10GB space limit:

    <create flashback archive FBDA1
    Tablespace fbda_1yr quota 10G retention 1 year;>
  3. Enable Flashback data archiving on the table to keep row history:

    <alter table emp flashback archive FBDA1;>

    You see this:

    Table altered.
  4. Query the table to see what it looked like:

    <select *
    From emp
    As of timestamp sysdate – 180;>

    In this case, you’re searching for emp 6 months prior. You see the row images as they existed 180 days ago.

You can’t drop, truncate, or modify any historical rows in this table as long as Flashback Data Archive is enabled.

To remove the Flashback Data Archive status, deleting all historical data, type this:

<alter table EMP no flashback archive;>

You see this:

Table altered.