Oracle Database 11g: Flashback Data Archives

November 26, 2008

Synopsis. Oracle Database 10g offered five new Flashback features: Flashback Versions Query, Flashback Transaction Query, Flashback Drop, Flashback Table, and Flashback Database. Oracle Database 11gR1 offers an intriguing new Flashback feature – Flashback Data Archives – that allows an Oracle DBA to preserve a record of all changes to any table for a definite period of time.

Many years ago – in fact, in the last millennium - I found myself working as a contractor for the Human Resources division of a large Midwestern beverage supplier. One day the Director of Human Resources asked me, “How hard would it be to set up a ‘delta’ table for our employee data so that we can track all changes to any employee’s data?” The answer appeared in my head much like the much-maligned “magic map” that CNN had used during the recent U.S. presidential election cycle to display and analyze voting results:

• I’d create a table named EMPLOYEES_HISTORY that exactly mirrored the columns in the EMPLOYEES table, except for the addition of a time stamp corresponding to the date and time of the change.
• I’d place the new table in a separate tablespace that I’d reserve just for this purpose, and for possible future objects like it.
• I’d apply a foreign key referential integrity constraint on the EMPLOYEES_HISTORY table to ensure data consistency between the two tables.
• Finally, I’d create an AFTER ... FOR EACH ROW trigger that would populate the EMPLOYEES_HISTORY table with the “delta” records whenever an INSERT, UPDATE, or DELETE transaction fired on the EMPLOYEES table.

And then some potential problems began to cloud my vision. What would happen, I asked myself, if someone:

• ... disabled the trigger that populates the EMPLOYEES_HISTORY table, and then forgot to re-enable it?
• ... needed to add a new column, or modify a column’s size or datatype in the EMPLOYEES table, but forgot to modify the EMPLOYEES_HISTORY table to accommodate that change?
• ... made the appropriate changes to both the EMPLOYEES and EMPLOYEES_HISTORY tables, but forgot to modify the corresponding trigger to populate the historical changes properly?
• ... needed to drop a column in the EMPLOYEES table, or just mark it UNUSED?
• ... accidentally TRUNCATEd the EMPLOYEES_HISTORY table?
• ... accidentally dropped the EMPLOYEES_HISTORY table? Or, even worse, dropped the EMPLOYEES table?

The simple answer to these questions is that if any of these situations occurred, I would have to explain to my client why I lost some portion of the delta data in EMPLOYEES_HISTORY, as well as why I couldn’t continue to treat the EMPLOYEES table as if it were just a normal table. My client must’ve seen my initial flash of triumph turn to sudden despair, but she soldiered on with her next set of requirements: “We’ll also need to be able to create a report for any of our external auditors that shows the state of any customer at any time in the past, as well as the changes to each individual employee record.”

As I began to think of the various SQL statements and views that I’d need to construct to accomplish this, she dropped her final bomb: “Oh, and by the way ... we’ll need to make sure that any data that’s older than seven years is purged automatically so that any external auditor simply doesn’t have access to the data. Legal requirements and the statute of limitations, you know.” At this point, my initial vision of an elegant solution collapsed completely, and I replied as any good hourly consultant would do in a similar moment of crisis: “Well, given enough time or money, I guess anything is possible.”

Implementing Oracle “Total Recall” With Flashback Data Archives

If I’d had the power of Oracle Database 11g back then, I’d have been able to answer my client directly: “Yes, and it’ll only take a matter of minutes to set it up.” Oracle 11gR1’s new Flashback Data Archive (FBDA) makes short work of historical data tracking by leveraging many of the Flashback features introduced in Oracle 10g to create a self-contained repository that retains historical changes to a table’s data well beyond the point in time that such data would be flushed out of the UNDO tablespace (in theory, essentially indefinitely).

FBDA information is stored in a separate set of objects that track a targeted table’s transactional history. These objects are stored in one or more tablespaces that are distinctly separated from the table for which historical data is being tracked. (The names for the FBDA tables are system generated, but they can be accessed by querying the DBA_FLASHBACK_ARCHIVE_TABLES view. More on this later.) Once FBDA is enabled for a table, all retained transaction history can be viewed, and this eliminates the cumbersome task of creating corresponding history tracking tables for critical transactional tables, and then writing complex triggers to insure historical data is accurately tracked.

The capture of this historical information is also extremely efficient because Oracle 11g implements special kernel modifications to minimize the performance overhead of retaining the historical data; in addition, these historical data are stored in compressed form to minimize the amount of storage needed. In addition, once FBDA is enabled for a table, Oracle 11g will not permit any operation that would either invalidate historical data (e.g. dropping or truncating the target table) or prevent its capture (e.g. dropping a targeted table’s column).

Here’s how FBDA actually works:

• A new background process, Flashback Database Archiver (FBDA), is directly responsible for tracking and archiving historical data for any table for which FBDA has been enabled. This new background process automatically collects and writes original data to the designated Flashback Data Archive via asynchronous processing.
• When any data for an FBDA-enabled table has been modified, FBDA first interrogates the UNDO data that’s stored in the database buffer cache. If the data is still there, then FBDA uses it; otherwise, if that UNDO data has already been aged out of the database buffer cache, FBDA will attempt to get the changes from the UNDO segments themselves in the UNDO tablespace.
• Once FBDA has captured the appropriate change data, it consolidates the rows of the FBDA-enabled tables, and then writes those rows to the appropriate history tables in the FBDA. These table data are compressed as well as internally partitioned.

Interestingly, FBDA never includes the original indexes for the captured data. Though this might not seem to make sense, it’s actually a boon in disguise. For example, the pattern of how the last two years’ worth of data is retrieved may significantly differ from the way in which the most recent three months’ worth of data is retrieved. Note that it is possible (and even commendable!) to create at least some secondary indexes that can be used just for reporting against historical data in the FBDAs.

Automatic Retention Policies. Historical table data can also be grouped together in an FBDA object based on similar retention requirements. Oracle 11g also provides methods to automatically purge data retained in the FBDA once a specified retention period has been exceeded. Multiple tables can share the same policies for data retention and purging. Moreover, since an FBDA consists of one or more tablespaces (or subparts thereof), multiple FBDAs can be constructed, each with a different but specific retention period. This means it’s possible to construct FBDAs that support different retention needs. Here are a few common examples:

• 90 days for common short-term historical inquiries
• One full year for common longer-term historical inquiries
• Seven years for U.S. Federal tax purposes
• 20 years for legal purposes

Setting Up Flashback Data Archive

Preparing an Oracle 11g database to use FBDA is extremely straightforward, involving a few simple steps:

• Create (or designate) one or more tablespaces for FBDA historical retention
• Optionally designate one FBDA as the default for the database
• Designate a user account that will function as the FBDA administrator by granting it the FLASHBACK ARCHIVE ADMINISTER system privilege
• Grant FBDA system privileges to appropriate user accounts
• Grant FLASHBACK and SELECT privileges to appropriate FBDA table users
• Grant EXECUTE privileges for DBMS_FLASHBACK procedures for all appropriate FBDA users

The code in Listing 1 illustrates how easy it is to set up Flashback Data Archive features. As it demonstrates, I’ve set up a new tablespace named FBDA and created within it three Flashback Data Archives, FBDA_1, FBDA_2, and FBDA_3, with initial retention periods of 5 days, one year, and seven years, respectively. I’ve also created a new user account, FBDA_ADMIN, and granted it the FLASHBACK ARCHIVE ADMINISTER system privilege. Finally, I granted the appropriate system and object privileges to HR, OE, and SH user accounts from the standard “sample” schema so that they can participate in FBDA operations.

Enabling and Disabling Historical Retention for a Table. Issuing the ALTER TABLE <table_name> FLASHBACK ARCHIVE [fda_name]; command enables FBDA retention for an existing table.

• If no FBDA is specified, the default FBDA will be used.
• Otherwise, the DBA can specify the desired FBDA for the table.
• If there is no current default FBDA, then an FBDA name must be specified.

Likewise, issuing the ALTER TABLE <table_name> NO FLASHBACK ARCHIVE; command disables FBDA retention for an existing table.

The code In Listing 2 illustrates these concepts:

• I then designated a Flashback Data Archive as the default FBDA. Initially I chose FBDA_2 as the default, and then later switched the default status to FBDA_1 using the ALTER FLASHBACK ARCHIVE <FBDA_name> SET DEFAULT; command.
• Next, I enabled table HR.APPLICANTS for Flashback Data Archive tracking in the default FBDA. (Note that the code to create and populate the HR.APPLICANTS table is presented in CreateFDBATables.sql.)
• Then I enabled tables HR.DEPARTMENTS, HR.JOB_HISTORY, and OE.CUSTOMERS for Flashback Data Archive tracking, choosing different FBDAs for retention of their historical data.
• Finally, to show how to discontinue FDBA tracking for a table, I issued the ALTER TABLE <table_name> NO FLASHBACK ARCHIVE; command against the OE.CUSTOMERS table.

Flashback Data Archives: Historical Data and Metadata

Oracle Database 11g hasn’t “re-engineered the wheel” to store data within a FBDA. Each table for which Flashback Data Archives is enabled uses three simple table structures; each named in the format of <source table owner>.SYS_FBA_<purpose>_<FBDA_object_identifier>, as shown in Table 1 below. These tables’ data can be queried directly for an interesting look at how Oracle 11gR1 is actually managing the FBDA infrastructure.

To illustrate, I first applied some changes to HR.APPLICANTS via some DML statements, and I then flushed the database buffer cache and switched UNDO processing to point a different tablespace via the ALTER SYSTEM SET UNDO_TABLESPACE=... SCOPE=BOTH; command as shown in Listing 3. Once the UNDO tablespace successfully switched, I queried table SYS_FBA_HIST_73218 to obtain the most recent set of UNDO transaction values, and the results are shown below:

                    Sample Data from Flashback Data Archives
                           (From SYS_FBA_HIST_73218)
                                     G
                                     N
  Appl   Ending                      D      Salary Job
    ID      SCN Last Name            R     Desired Desired
------ -------- -------------------- -- ---------- ----------
     3  1203058 Brown                M    70113.04 IT_CNTR2
     6  1203058 Chandler             M    55511.77 IT_CNTR1
     9  1203058 Chestnut             M    73042.53 IT_CNTR3

Contrast this with the same selection from the table itself for this time frame:

                         Sample Data from HR.APPLICANTS
                             (Between Time Periods)
                                     G
                                     N
  Appl   Ending                      D      Salary Job
    ID      SCN Last Name            R     Desired Desired
------ -------- -------------------- -- ---------- ----------
     1          Aniston              M    88017.94 IT_CNTR2
     2          Niven                M    82553.39 IT_CNTR1

    3  1203058 Brown                M    70113.04 IT_CNTR2
    3          Brown                M   70100.00 IT_CNTR2
     4          Murdock              M    70389.16 IT_CNTR2
     5  1202273 Bedelia              M    38720.86 IT_CNTR3
     5          Bedelia              M    39000.00 IT_CNTR3

    6  1203058 Chandler             M    55511.77 IT_CNTR1
    6          Chandler             M   55500.00 IT_CNTR1
     7          Lerner               M    80587.46 IT_CNTR2
     8          Robinson             M    49516.37 IT_CNTR3

    9  1203058 Chestnut             M    73042.53 IT_CNTR3
    9          Chestnut             M   73000.00 IT_CNTR3

FBDA Metadata. In addition, several new Oracle 11gR1 data dictionary views (see Table 2) provide metadata about the FBDAs that already exist, including which tablespace(s) support the extended historical data storage and which tables are participating in FBDA retention:

I’ve supplied sample SQL*Plus formatted queries that return metadata from these views in MonitorFBDA.sql; here’s a sample of the information these queries would return at the current point in this demonstration:

Sun Nov 23                                                             page    1
                        Current Flashback Data Archives
                          (From DBA_FLASHBACK_ARCHIVE)
Flashback               Retention
Archive      Status     (in Days) Created On  Last Purged
------------ ---------- --------- ----------- -----------
FBDA_1      DEFAULT           1 11-21-2008  11-24-2008
                                  22:06:12    22:32:31
FBDA_2                        365 11-21-2008  11-21-2008
                                  22:08:33    22:08:33
FBDA_3                       2555 11-21-2008  11-21-2008
                                  22:09:33    22:09:33

Sun Nov 23                                                             page    1
             Corresponding Tablespaces for Flashback Data Archives
                        (From DBA_FLASHBACK_ARCHIVE_TS)
Flashback    Corresponding   Tablespace
Archive      Tablespace      Quota (MB)
------------ --------------- ------------
FBDA_1       FBDA            1
FBDA_2       FBDA            4
FBDA_3       FBDA            20

Sun Nov 23                                                             page    1
         Tables Using Flashback Data Archives For Historical Retention
                          (From DBA_FLASHBACK_ARCHIVE)
Flashback    Table
Archive      Owner        Table Name           FBDA Object Name
------------ ------------ -------------------- ------------------------------
FBDA_1      HR           APPLICANTS          SYS_FBA_HIST_73218
FBDA_2       HR           JOB_HISTORY          SYS_FBA_HIST_70279
FBDA_3       OE           CUSTOMERS            SYS_FBA_HIST_70286

Utilizing Flashback Data Archives: Auditing and Data Repair

So now that I’ve spent all this time setting up Flashback Data Archives ... just what is it good for? Here are just a few scenarios that prove its value:

Auditing Historical Transactions. Now that I’ve established FBDA tracking on the HR.APPLICANTS table, all data will be retained automatically depending, of course, on the duration policy set up for the Flashback Data Archive in which the table’s transactions are retained. This could be extremely valuable when Equal Employment Opportunity Commission (EEOC) reporting is mandated for an employer because the company recently established a contract with the U.S. Federal Government, as EEOC reporting often requires proof that there has been no discrimination in hiring or promotion practices over lengthy historical periods. I’ve illustrated this scenario and its resolution in the prior listing.

“Digital Shredding.” While most companies certainly realize the value of retaining historical data to fulfill legal and procedural requirements, in many cases the old saw “Less is more” may apply. While good accounting principles require retention of crucial financial data for a minimum of seven years to facilitate United States Internal Revenue Service (IRS) tax auditing purposes, common business sense also dictates that it might be advantageous to not retain transactions beyond the legal statute of limitations. (Please note that I’m discussing corporate accounting rather than corporate accountability right now.) Oracle 11g will automatically delete all data that’s dated prior to the FBDA’s retention period plus one day. As its name implies, during this digital shredding process, only the historical data – not the corresponding FBDAs themselves – are purged in this fashion.

Repairing Lost or Erroneously Changed Data. In my nearly thirty years of IT experience, I’ve often encountered “panic room” situations when users, application developers, and yes, even DBAs have inadvertently updated crucial data incorrectly, or (even worse) physically deleted rows from a critical table. Of course, these mistakes may not be discovered until days, weeks, or even months have elapsed, at which time I’ve been expected to magically reconstruct the data. In the past, I’d probably have to perform a limited or full incomplete recovery of the database or tablespace in question to obtain the data. Flashback Database can certainly allow me to perform incomplete recovery, but its granularity is database and SCN specific (oh, and Flashback Logging must have been enabled prior to the mistake). Flashback Table, while granular enough for most repairs, is still limited by the amount of UNDO retained in the current UNDO tablespace. In this case, however, historical data retained inside a FBDA could be used to reconstruct the original data. See Listing 4 for an example of how to use Flashback Data Archive data and Flashback Query to resolve this scenario.

Maintaining Flashback Data Archives

Flashback Data Archives are generally self-sufficient. However, minor but limited maintenance against FBDA objects occasionally may be required. For example, an Oracle DBA could:

• Expand the size of an existing FBDA (as long as the new FBDA size isn’t larger than the tablespace quota granted to the FBDA admin user)
• Change the retention period of the data stored on an existing FBDA
• Manually purge data that’s older than a specific date and time in an existing FBDA
• Drop an existing FBDA (but note that dropping an FBDA does not drop its corresponding tablespace)

Listing 5 provides several examples of how an Oracle DBA might take care of these maintenance tasks.

FBDA Space Management. When an FBDA runs out of available space, the session and operation that were modifying the table that is supported by the FBDA will receive one of two possible errors (using Flashback Data Archive fbda_1 to illustrate the errors):

• ORA-55617: Flashback Archive fbda_1 runs out of space and tracking on fda1 is suspended. This error message indicates that the specified FBDA is nearly out of space; it will be issued when the FBDA reaches 90% or more capacity.
• ORA-55623: Flashback Archive fbda_1 is blocking and tracking on all tables is suspended. In this case, the specified FBDA has run out of space completely.

In either of these cases, the DBA can either increase the FBDA’s quota on the underlying tablespace or increase the size of the underlying tablespace to resolve the out-of-space condition. Note that these errors will also be logged to the database’s alert log.

Flashback Data Archives: Restrictions and Recommendations

Of course, you may have guessed that all this power does require some restraint:

DDL Limitations. Be aware that if a DBA attempts to apply any of the following DDL commands against a table that’s currently enabled for historical retention via FBDA, Oracle 11g will raise an exception:

• Any variation of ALTER TABLE <table_name> that:
• Drops a column
• Renames a column
• Modifies a column
• Performs a PARTITION or SUBPARTITION operation
• Converts a column from datatype LONG to datatype LOB
• Invokes an UPGRADE TABLE operation, regardless of whether the INCLUDING DATA directive is specified
• DROP TABLE <table_name>;
• RENAME TABLE <table_name>;
• TRUNCATE TABLE <table_name>;

Best Practices. Finally, here are some recommended best practices for using Flashback Data Archive features effectively in Oracle 11g:

• It’s a good idea to perform a COMMIT or ROLLBACK operation before querying past data; this tends to insure database consistency.
• Flashback Data Archive processing always utilizes the current session settings, including NLS settings like NLS_LANGUAGE and NLS_CHARACTERSET. However, remember that the settings for these variables may not match those in effect when the historical data was retained.
• Oracle recommends using the various INTERVAL and TIMESTAMP translation functions to compute a time in the past -- for example, specifying SYSTIMESTAMP – INTERVAL ‘20’ DAYS -- to obtain past results from a table with data stored in a FBDA
• To query data stored in an FBDA most precisely, Oracle recommends using an SCN. Remember that the TIMESTAMP_TO_SCN function can be used to obtain a relatively accurate SCN value directly from a TIMESTAMP value, but that its precision is still limited to a boundary of three seconds.

Conclusion

Oracle Database 11g’s new Flashback Data Archive features dramatically expand an Oracle DBA’s capabilities to retain historical transactions for an exceedingly long period of time, limited only by the amount of tablespace storage allocated to the corresponding historical objects. Since Flashback Query, Flashback Versions Query, and Flashback Transaction Query features are supported as well, an Oracle DBA can also utilize FBDA features to correct erroneous DML at extremely high granularity over almost unlimited time spans. Finally, since FBDA is easy to set up, simple to monitor, and mostly self-maintaining, it’s a welcome addition to any Oracle DBA’s “toolbelt.”

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article:

B28279-02 Oracle Database 11g New Features Guide

B28320-01 Oracle Database 11g Reference Guide

B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference

B28424-03 Oracle Database 11g Advanced Application Developer’s Guide

Also, the following MetaLink documentation helps clarify this feature set:

470199.1 11g Feature: Flashback Data Archive Guide

» See All Articles by Columnist Jim Czuprynski