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 employees 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:
- Id 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.
- Id place the new table in a separate tablespace that Id reserve just for this purpose, and for possible future objects like it.
- Id apply a foreign key referential integrity constraint on the EMPLOYEES_HISTORY table to ensure data consistency between the two tables.
- Finally, Id 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 columns 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 couldnt continue to treat the EMPLOYEES table as if it were just a normal table. My client mustve seen my initial flash of triumph turn to sudden despair, but she soldiered on with her next set of requirements: Well 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 Id need to construct to accomplish this, she dropped her final bomb: Oh, and by the way ... well need to make sure that any data thats older than seven years is purged automatically so that any external auditor simply doesnt 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 Id had the power of Oracle Database 11g back then, Id have been able to answer my client directly: Yes, and itll only take a matter of minutes to set it up. Oracle 11gR1s 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 tables 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 tables 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 tables column).
Heres 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 thats 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, its 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 its 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, Ive 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. Ive 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 hasnt 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.
Table 1. Flashback Data Archives: Historical Data Storage
Maps out the current state of all columns for an existing FBDA-enabled table
Contains the actual delta values for all recent (i.e. non-purged) transactions for an existing FBDA-enabled table
Maps the latest set of changes applied against an existing FBDA-enabled table to their corresponding UNDO transaction information
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_CNTR14 Murdock M 70389.16 IT_CNTR2 5 1202273 Bedelia M 38720.86 IT_CNTR3 5 Bedelia M 39000.00 IT_CNTR3 7 Lerner M 80587.46 IT_CNTR2 8 Robinson M 49516.37 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:
Table 2. Flashback Data Archives: Metadata
Lists all current Flashback Data Archives
Shows the corresponding tablespaces that house all current Flashback Data Archives
Displays all tables that are using a Flashback Data Archive to retain historical data
Ive supplied sample SQL*Plus formatted queries that return metadata from these views in MonitorFBDA.sql; heres 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 Ive 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 Ive 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 tables 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. Ive 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 Im discussing corporate accounting rather than corporate accountability right now.) Oracle 11g will automatically delete all data thats dated prior to the FBDAs 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, Ive 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 Ive been expected to magically reconstruct the data. In the past, Id 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 isnt 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 thats 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 FBDAs 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 databases 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 thats 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:
- Its 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.
Oracle Database 11gs new Flashback Data Archive features dramatically expand an Oracle DBAs 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, its a welcome addition to any Oracle DBAs toolbelt.
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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 Developers Guide
Also, the following MetaLink documentation helps clarify this feature set:
470199.1 11g Feature: Flashback Data Archive Guide