Flashback Data Archive: Recreating History, After The Fact

Leave a Comment / Backup and Recovery, Daily DBA Stuff / By /

Introduction

As an IT professional, especially one working closely with data, surely, almost definitely, you have been asked a question from the users along the lines of “Can we know what the value was of col1 in the table t1 at 25:00 on Thursday, the 30th of February 8000 years ago?” (Silly, but that’s really how the requests sound sometimes.. point being that data is gone!) and the users are sometimes shocked when you say no, you don’t have it(Mind you, they never specified this in the requirements). They can’t believe it! absolutely astounded they are to hear you say that. Most troubling is that sometimes there is actually a legal requirement to keep this data around for a certain period (depending on your industry), something you’d think should have been communicated to the application development team. Well, the fact is, unless you’re Doctor Brown or Sarah Connor, you can’t really “go back in the past” and find this out after the fact, unless you planned for this in advance. In which case, that will make you James Bond, the 007 of data😎(Keep reading, and you might turn out to be)

The flashback Data Archive (FDA) feature in Oracle is designed for exactly situations like this; it provides a mechanism for tracking changes to production databases that is secure, efficient, easy to use, and application-transparent. One of the main benefits of this feature is it’s very easy to set up and in recent releases (post 12c) does not require any additional licensing (previously required advanced compression) if you’re using enterprise edition. FDA can also track user contexts (as explored on Oracle Base) as well as changes to the data over time. It’s a comprehensive solution for data changes “built-in” to the database and readily available for implementation.

In this post, I will attempt to explain a bit about the FDA and provide an example setup.

What the FDA is all about.

A bit of background

To set up the FDA, one or more tablespaces must be dedicated to storing transactional changes to the specified tables. Note that FDA is not meant to be used for everything in the database; it is intended for specific tables only, as it incurs a storage and CPU hit. Various methods are attempted(to this day, trust me! I’ve seen it) to implement change tracking, including:

to name a few! All of these methods have performance and complexity drawbacks and are prone to errors, as they can require excessive developer code to implement.

Why not undo?

The undo mechanism in Oracle captures snapshots/before images of data whenever a DML statement is issued and stores them in the undo segments in the undo tablespace. However, these previous copies of data are purged from the undo tablespace as more recent changes require that space, depending on the undo retention parameters specified. In any case, the purpose of undo is not to keep a superhistorical record of table changes, but to help with the rolling back of statements and to maintain read consistency. Furthermore, when your instance is shut down, all of the undo is lost! Good luck depending on that.

Worry not, Oracle’s got your back!

Flashback Data Archive enables tracking and storing transactional changes to a table throughout its lifetime.

With flashback features, you can:

  • Perform queries that return past data
  • Perform queries that return metadata detailing the detailed history of changes to the database.
  • Recover tables or rows to a previous point in time
  • Automatically track and archive transactional data changes
  • Roll back a transaction and its dependent transactions while the database remains online

Restrictions and Limitations

FDA also has the following restrictions

  • Data archived in the Flashback Data Archive is retained for the retention time specified when the Flashback Data Archive was created.
  • FLASHBACK ARCHIVE object privilege is required on the Flashback Data Archive to use it for that table.
  • The table whose data needs to be archived cannot be any of the following: a nested table, a temporary table, an external table, a materialized view, an Advanced Query (AQ) table, or a non-table object.
  • The table contains neither LONG nor nested columns

Using the Flashback Data Archive imposes certain limitations on the type of DDL statements you can issue. You can’t issue the following types of DDL statements on a table that you enabled for a Flashback Data Archive:

  • An ALTER TABLE statement that drops, renames, or modifies a table column
  • An ALTER TABLE statement that performs partition operations
  • A DROP TABLE or a TRUNCATE TABLE statement
  • A RENAME TABLE statement

Now that we’ve got an idea of what this feature is and What’s it’s supposed to do, let us see it in action.

Setting up Flashback Data Archive

To set up and demonstrate how this feature works, let’s examine a fictional scenario :

A spy story?

Consider the following fictional Scenario.

Suppose you work for MI6 and have been tasked with monitoring the mission details of a certain James Bond. Mr. Bond is constantly on missions; he updates intel and occasionally blows things up. MI6 needs a way to track how Mr. Bond’s mission intel changes as it progresses, so there is a schema for tracking mission data.

Step 1: Sample Data

The commands below can be used to set up our sample fictional schema.

SQL> CREATE USER bond IDENTIFIED BY classified ;

User created.
SQL>
SQL> CREATE ROLE bond_role ;

Role created.

SQL> GRANT bond_role to bond ;

Grant succeeded.

SQL>

SQL>
SQL> GRANT connect TO bond;

Grant succeeded.

SQL> CREATE USER mi6_owner NO AUTHENTICATION ;

User created.

SQL>
SQL>
SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO mi6_owner;

Grant succeeded.

SQL> ALTER USER mi6_owner QUOTA UNLIMITED ON users;

User altered.

CREATE TABLE mi6_owner.mission_intel (
   intel_id           NUMBER PRIMARY KEY
  ,location           VARCHAR2(100 CHAR)
  ,threat_level       VARCHAR2(20 CHAR)
  ,notes              VARCHAR2(500 CHAR)
)
/

Table created.

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON mi6_owner.mission_intel TO bond_role ;

Grant succeeded.
SQL>
SQL> CREATE SYNONYM bond.mission_intel FOR mi6_owner.mission_intel ;

Synonym created.

SQL>
SQL> connect bond/classified
Connected.

SQL> INSERT INTO mission_intel
  2  VALUES (1, 'Monaco Casino', 'LOW', 'Target observed at poker table');

1 row created.

Notice the use of the Schema-only account feature.

We now have an MI6_OWNER schema that contains all the tables, and the BOND user will be the application user used to manipulate them.

Suppose MI6 needs a way to see

  • What Bond originally reported
  • What he changed
  • When he changed it
  • And what the intel looked like at any moment during any mission for the past year!

Step 2: FDA Tablespace

As discussed earlier, the FDA needs a dedicated tablespace. We can create this using the regular CREATE TABLESPACE statement:

SQL> CREATE TABLESPACE fda_ts DATAFILE '/u13/oradata/TESTDB/fda_tbs01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M ;

Tablespace created.

Now we have to create the flashback data archive on the fda_ts tablespace. You can specify the following parameters when you create the FDA ;

  • Name of the archive
  • Name of the first tablespace of the archive
  • quota for the archive in the tablespace
  • How far back must the database retain the data
  • (Optional) Whether to optimize the storage of data in the history tables maintained in the Flashback Data Archive, using [NO] OPTIMIZE DATA. The default is NO OPTIMIZE DATA

Of those four, only the name of the first tablespace and the retention period are necessary.

SQL> CREATE FLASHBACK ARCHIVE bond_archive   --archive name
  2  TABLESPACE fda_ts                       --default tablespace
  3  QUOTA 1M                                --quota
  4  RETENTION 1 YEAR                        --mandatory retention period
  5  ;

Flashback archive created.

The previous statement creates the new flashback archive bond_archive and ensures that any tables you enable for archiving will be tracked, and that transactional changes to their data will be saved for a year. After the year is up, the transactional changes will be automatically deleted, thus keeping only a year’s worth of changes at any given time.

Step 3: Grant FDA privileges

Now that a flashback data archive has been set up, we need to enable its use by the owning schema. We also need to allow the application user to read archived transactions. The FLASHBACK ARCHIVE on BOND_ARCHIVE is granted to the MI6_OWNER and the FLASHBACK object privilege on MI6_OWNER.MISSION_INTEL is granted to the BOND_ROLE.

SQL> GRANT FLASHBACK ARCHIVE ON bond_archive to mi6_owner ;

Grant succeeded.

SQL> GRANT FLASHBACK ON mi6_owner.mission_intel to bond_role

Grant succeeded.

The above privileges allow the owning schema to generate archive data and the application user to read it.

Step 4: Enable Archive On Table

Now we enable our table for archiving.
NB: A table can also be enabled for FLASHBACK ARCHIVE at creation time (see example)

SQL> ALTER TABLE mi6_owner.mission_intel FLASHBACK ARCHIVE bond_archive ;

Table altered.

That’s it! Flashback archive has been set up and is now enabled for our table.

Flashback Data Archive In Use

Let’s see Flashback Data Archive in action. We update our table mission_intel with some new values!

SQL> col notes for a40
SQL> col location for a20
SQL>
SQL> SELECT *
  2  FROM  mission_intel ;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        LOW                  Target observed at poker table

UPDATE mission_intel
SET threat_level = 'Medium'
    ,notes       = 'Target Armed'
WHERE  intel_id  = 1
/

1 row updated.

SQL> SELECT *
  2  FROM  mission_intel ;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        Medium               Target Armed

View The History

Using the AS OF or VERSIONS BETWEEN clause in a query, you can access the historical data stored in the Flashback Data Archive, as shown in the following example:

SQL> SELECT *
  2  FROM mission_intel
  3  AS OF TIMESTAMP to_timestamp ('2026-03-20 19:30:00', 'YYYY-MM-DD HH24:MI:SS') ;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        LOW                  Target observed at poker table

SQL> SELECT * FROM
  2  mission_intel
  3  VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        Medium               Target Armed
         1 Monaco Casino        LOW                  Target observed at poker table

SQL> SELECT * FROM mission_intel;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        Medium               Target Armed

As seen above, the AS OF clause and VERSIONS BETWEEN can “go back in time” and return the previous values for the same row—the VERSIONS BETWEEN shows all the different values. MI6 can now see Bond’s chaos and reconstruct the mission details step by step.

Recover From DML Errors

With the FDA, suppose you learn about an erroneous INSERT weeks after it happened, upon knowledge of this, you can UPDATE the current values using an UPDATE... SELECT statement to correct the values in the table as below ;

  1  update mission_intel
  2  set threat_level =
  3  (  SELECT threat_level
  4     FROM mission_intel
  5     AS OF TIMESTAMP    to_timestamp ('2026-03-20 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
  6* )
SQL> /

1 row updated.

SQL> SELECT *
  2  FROM mission_intel ;

  INTEL_ID LOCATION             THREAT_LEVEL         NOTES
---------- -------------------- -------------------- ----------------------------------------
         1 Monaco Casino        LOW                  Target Armed

View Metadata about Flashback Data Archive

You can use the following views to find out details about the data stored in the Flashback Data Archive:

  • DBA_FLASHBACK_ARCHIVE : shows details about the Flashback Data Archive
  • DBA_FLASHBACK_ARCHIVE_TS : Shows information about the tablespaces hosting the Flashback Data Archive
  • DBA_FLASHBACK_ARCHIVE_TABLES : Shows information about the tables that are enabled for flashback data archiving
SQL> l
  1  SELECT owner_name,
  2         flashback_archive_name,
  3         flashback_archive#,
  4         retention_in_days,
  5         TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
  6         TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
  7         status
  8  FROM   dba_flashback_archive
  9* ORDER BY owner_name, flashback_archive_name
SQL> /

OWNER_NAME           FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME      STATUS
-------------------- ---------------------- ------------------ ----------------- -------------------- -------------------- -------
SYS                  BOND_ARCHIVE                            1               365 20-MAR-2026 19:09:25 20-MAR-2026 19:09:25

SQL> l
  1  SELECT flashback_archive_name,
  2         flashback_archive#,
  3         tablespace_name,
  4         quota_in_mb
  5  FROM   dba_flashback_archive_ts
  6* ORDER BY flashback_archive_name
SQL> /

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_MB
---------------------- ------------------ -------------------- -----------
BOND_ARCHIVE                            1 FDA_TS               1

SQL>

Alter a Flashback Data Archive

You can use the ALTER FLASHBACK ARCHIVE statement to alter the properties of a Flashback Data Archive, such as the size of the archive and changes in the retention time for the archived data. Here are some examples:

  • Specify a Flashback Data Archive as the default archive for the database:
    SQL> ALTER FLASHBACK ARCHIVE bond_archive SET DEFAULT;
  • Add a tablespace to the archive:
    SQL> ALTER FLASHBACK ARCHIVE bond_archive ADD TABLESPACE fda_ts QUOTA 10G;
  • Change the retention period:
    SQL> ALTER FLASHBACK ARCHIVE bond_archive MODIFY RETENTION 5 YEAR;
  • Purge all data from an archive:
    SQL> ALTER FLASHBACK ARCHIVE bond_archive PURGE ALL;
  • Purge all data older than a week from an archive:
    SQL> ALTER FLASHBACK ARCHIVE bond_archive PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

Drop a Flashback Data Archive

You can remove a Flashback Data Archive with the following statement:

SQL> DROP FLASHBACK ARCHIVE bond_archive;

The previous statement will only remove the Flashback Data Archive. The tablespace that hosts the archive remains intact, since that tablespace may contain other objects besides the Flashback Data Archive.

Best Practices and More Information


Below are some best practices and considerations: (from the FDA Whitepaper)

  • Flashback Data Archive should be enabled on a few chosen tables and not all the tables in a schema, as tracking history consumes space and CPU resources, so a careful decision has to be made on the choice of the tables (and the tables tailored with the columns that need the history to be maintained)
  • FDA tables should ideally be created on tables with Primary Key as a best practice so that retrieval can be based on Primary Keys and/or time/SCN ranges
  • Create indexes on the History table for faster access. The FDA creates history tables, but does not create an index on any data column in the history table.
  • Try to keep the same schema for the table enabled for Flashback Archive (anticipate any future DDL changes)
  • Provide enough storage so the data does not have to be exported for the retention period.
  • Flashback Data Archive MOS Note: FDA – Flashback Data Archive Usage and Best Practices (a.k.a. Total Recall) (Doc ID 2370465.1)

Conclusion

As this post showed, there is a whole lot that can be done with Flashback data archive, and even more that is not covered in this blog. It is an extremely rich feature, as this example shows, and fairly easy to set up, providing many benefits over traditional application workarounds. FDA data is tamper-proof, meaning no database user can modify the data in the FDA. Also, the FDA can be used to satisfy many data retention requirements, including those under SOX and other regulatory requirements. Coupled with savings in CPU, storage, administration, and time, this reduces your compliance costs when you combine all the potential benefits, especially, I mean, “What business doesn’t love saving money! 💸 ” Then, FDA is an absolute no-brainer! Stay Compliant Folks.

cheers 🎊

References and Further Reading

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Posts