Oracle Database 11g: Flashback Data Archives

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