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 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:
And then some potential problems began to cloud my vision. What would happen, I asked myself, if someone:
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:
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:
Setting Up Flashback Data Archive
Preparing an Oracle 11g database to use FBDA is extremely straightforward, involving a few simple steps:
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.
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:
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.
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:
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:
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):
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:
Best Practices. Finally, here are some recommended best practices for using Flashback Data Archive features effectively in Oracle 11g:
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