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.

Table 1. Flashback Data
Archives: Historical Data Storage

View

Description

SYS_FBA_DDL_COLMAP_<object_id>

Maps
out the current state of all columns
for an existing FBDA-enabled table

SYS_FBA_HIST_<object_id>

Contains
the actual “delta” values for
all recent (i.e. non-purged) transactions for an existing FBDA-enabled table

SYS_FBA_TCRV_<object_id>

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_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:

Table 2. Flashback Data
Archives: Metadata

View

Description

DBA_FLASHBACK_ARCHIVE

Lists
all current Flashback Data Archives

DBA_FLASHBACK_ARCHIVE_TS

Shows
the corresponding tablespaces
that house all current Flashback Data Archives

DBA_FLASHBACK_ARCHIVE_TABLES

Displays
all tables that are using a
Flashback Data Archive to retain historical data

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

Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles