Oracle Database 11g: Flashback Transaction Backout

Synopsis.
Oracle Database 10g offered two new Flashback features- Flashback Versions
Query and Flashback Transaction Query – that allowed an authorized user to see
all versions of any changes made to one or more rows in a table, as well as the
capability to view the entire transaction associated with any row change, based
on the transaction ID of the transaction. Database 11gR1 builds upon these 10g
flashback features to their logical destination: the ability to back out one or
more independent or dependent transactions with Flashback Transaction Backout.

An IT
project manager for a large multinational insurance firm and one of my closest
colleagues recently told me of her frustration with a production fix that their
team’s offshore resources were attempting to implement. “I got a request today
from one of our developers to approve the execution of some ‘DML that he’d
built against several production database tables,” she said. When I asked why
she allowed her developers to submit what I like to call “naked” DML against
their production database, she replied, “We really had no choice. It was actually
the third time this week he’d requested this type of approval, because on
Monday he submitted some DML to fix a problem in some transactions. On Tuesday,
he submitted a fix to his original
fix, and Wednesday he found out he’d broken several other transactions with his
Tuesday ‘fix.’”

I
grimaced in sympathy, as this had happened to me before as well. “Let me guess,”
I said. “He forgot to look at what triggers would fire against the tables in
his SQL statement as a result of his original Monday ‘repairs,’ right?”
“Exactly!” she replied. “And when he made the second fix, he forgot to look at
what other triggers would fire as
a result of that fix. I made him
go back and take a look at all the objects affected by his third attempt to
repair things, and we found that even more triggers would have fired had he not
checked first. What a nightmare.”

Unfortunately
for my friend, her client was not using an Oracle database on the back end of
their application, so I couldn’t explain to her how Oracle 10g offered several
features that would have made her job much simpler:

  • Flashback
    Versions Query
    allows an authorized session to query against all versions of one or more rows. Any DML
    statement issued against a table is therefore viewable, and the developer can
    use that information to decide how to best reverse errant transactions.
  • Flashback
    Transaction Query
    , on the other hand, works at “right angles” to
    Flashback Versions Query because it can utilize the transaction ID that’s
    stored alongside each row version in a Flashback Versions Query to display
    every “atom” of the transaction that changed the row – including any that may have
    fired via a table-level DML trigger.

However,
both of these tools are limited by the amount of undo that’s currently retained
within the database’s UNDO tablespace, because that’s from where their
transaction information is retrieved. What if I need to delve into a
transaction whose reciprocal change vectors have been aged out of the UNDO
tablespace?

Delving Deeply
Into Transaction Detail with LogMiner

Oracle’s
LogMiner tool set finally gets the recognition it’s due as a “power player” in
Oracle 11g – it’s the perfect addendum to the Flashback Versions Query and
Flashback Transaction Query capabilities that were introduced in Oracle 10g. For
starters, Oracle 11g’s Enterprise Manager Database Control finally offers an
intuitive, wizard-based interface for this powerful tool, a major improvement
over prior releases’ “thick” Java Console GUI. The new LogMiner wizard
interface is activated by selecting the Availability
tab and then choosing the View and Manage Transactions
link as shown below:

LogMiner wizard interface

The
wizard interface is not the only new LogMiner feature in Oracle 11g, however:

  • LogMiner query
    parameters
    are easily specified at either the time stamp or SCN granularity
  • A long-running LogMiner
    query can now be halted so that
    any corresponding partial results
    can be displayed; equally impressive, it’s now possible to show the remaining estimated time to complete the query if
    execution was resumed.
  • The original LogMiner query can be used as a target
    of a “re-mining” operation based
    on the initial results retrieved, and the original LogMiner query results can
    now be saved for future evaluations
  • Finally, the detailed
    results
    of a LogMiner query – including all transaction details, dependencies, and
    the compensating undo SQL scripts
    – can be easily obtained, and this feature set facilitates a brand-new feature
    set in Oracle 11g: the ability to quickly and accurately reverse individual
    committed transactions via Flashback
    Transaction Backout
    .

I’ll
illustrate these extended LogMiner query capabilities a bit later in this
article.

Reversing
Transactions Accurately Via Flashback Transaction Backout

Oracle
Database 11g’s new Flashback
Transaction Backout
(FTB) feature
allows an authorized user session to back out a single transaction — as well
as any corresponding dependent
transactions — by applying appropriate compensating
statements
for the affected transaction(s). FTB uses undo data, redo
data generated for the UNDO blocks, and supplemental logging to accomplish
this.

Flashback Transaction Backout: Prerequisites

Before a Flashback Transaction Backout operation can be activated, the DBA must
make sure the following prerequisites have been addressed:

  • The database must be in ARCHIVELOG mode.
  • Supplemental
    logging
    must have been enabled at the database level; in addition,
    at a minimum, primary key level supplemental
    logging must be enabled on the tables for which FTB is desired.
  • Any user accounts other than SYS
    that will be participating in an FTB operation must have been granted the EXECUTE
    object privilege on package DBMS_FLASHBACK,
    and the SELECT
    ANY TRANSACTION
    system
    privilege.
  • Finally, any user account other than SYS must
    be granted FLASHBACK
    privileges on the table(s) on which FTB will be performed. Likewise, any user
    account that will be performing FTB must also either own the tables on which FTB will be performed, or have
    complete (SELECT,
    INSERT, UPDATE,
    and DELETE) DML privileges on those
    tables

Performing FTB Using
Enterprise Manager Database Control

To
illustrate the flexibility and power of Flashback Transaction Backout, I’ve set
up the following simple scenario:

  • First, I’ve recreated my Accounts Payable (AP)
    schema and added just one table (AP.VENDORS) as shown in the code
    in Listing
    1
    . I’ve also activated supplemental logging and appropriate
    permissions for the AP schema so that it can perform FTB.
  • Via the code in Listing
    2
    , I’ve populated that table with just a few rows of sample data
    and then updated two of the five rows in the table, capturing the SCNs and
    timestamps of these transactions. The updated transactions will be the target
    of my FTB operation.
  • Listing
    3
    shows the queries I executed against V$LOGMNR_CONTENTS
    to view corresponding change vectors that LogMiner will provide to FTB for
    making intelligent decisions about which transactions may be backed out, as
    well as the corresponding output from those queries.

Now I’m
ready to use the power of FTB to roll back just the last two update
transactions. As shown in Figure 1,
I’ll select the Perform
Recovery
link from the Availability
tab of Oracle 11g Enterprise Manager Database Control, and then I’ll start FTB by
selecting the Transactions
option for the scope of my recovery operation:

Invoking Database Recovery Wizard

Figure 1. Invoking Database Recovery Wizard.

Now
that the FTB recovery operation is underway, Oracle 11g allows me to supply
either a range of SCNs or a range of timestamps to limit the amount of archived
or online redo logs that’ll need to be read to find the corresponding change
vectors. As Figure 2 shows below,
I’ve also restricted the search for possible transactions to only those that
affect table AP.VENDORS:

Specifying SCN / Timestamp Ranges

Figure 2. Specifying SCN / Timestamp Ranges.

Oracle
11g next searches for any matching transaction information, and it provides a
concise summary of how the search is progressing, as Figure 3 below shows:

Tracking FTB Transaction Retrieval Processing

Figure 3. Tracking FTB Transaction Retrieval Processing.

After
a few minutes, the Recovery Wizard then presents me with a list of possible
transactions against AP.VENDORS that I’d be most
interested in reversing, as shown in Figure 4.

Reviewing Candidate FTB Transactions

Figure 4. Reviewing Candidate FTB Transactions.

If I’m
interested in reviewing the individual atomic transactions before making a
decision on which one(s) to back out, I can simply select one of them by
clicking on the Transaction ID “breadcrumb.” Figure
5
shows the results of selecting the link to my proposed target
transaction, the two UPDATE statements in Transaction ID x’05000F00D050000’:

Reviewing FTB Transaction Details

Figure 5. Reviewing FTB Transaction Details

After
selecting the desired transaction(s) for FTB, Oracle provides a tracking screen
to notify me that it’s begun to process them for reversal, as Figure 6 shows.

Monitoring FTB Progress

Figure 6. Monitoring FTB Progress.

Almost
done! Once Oracle verifies that the transactions can be rolled back without
causing any disruption of the atomicity of the transaction – also known as NOCASCADE
mode – it acknowledges that the FTB reversal transactions are ready for
execution, as Figure 7 below
shows.

Final Review before FTB Application

Figure 7. Final Review before FTB Application.

I
have several options at this point:

  • I can simply execute
    the FTB transactions.
  • I can formulate
    and execute
    a custom query against the target table to view its
    contents immediately after the
    FTB compensating transactions have been applied.
  • Finally, I can view
    the actual compensating statements
    themselves by clicking the Show Undo SQL Script button. Figure 8 shows the results of doing just
    that:

Viewing Undo SQL Statements

Figure 8. Viewing Undo SQL Statements.

Once I’ve
chosen to continue, FTB will apply the compensating transactions it has already
prepared to the table, and returns an acknowledgment that the operation has
completed successfully, as shown in Figure 9.

Acknowledgement of Successful FTB Processing

Figure 9. Acknowledgement of Successful FTB Processing.

Handling Special
FTB Situations

This
was an extremely straightforward example of using FTB to reverse a simple set
of UPDATE statements; but what if the transactions that I want to reverse are
much more complex? For example, what if the transaction that I want to back out
has changed data that another transaction depends upon? Or what if I want to
reverse a transaction – say, delete an Order from OE.ORDERS
– that one or more rows in another table depend upon in a parent-child
relationship, or otherwise use that Order’s information to enforce referential
integrity via a foreign key constraint?

These
two examples represent the different types of dependent transaction conditions
that (thankfully!) FTB can detect and act upon:

  • Write-After-Write (WAW). In this case, another
    transaction needs to change the same data
    that was originally changed by the target
    transaction
    .
  • Primary/Unique Constraint Relationship. In this situation, an FTB transaction
    might even need to reinsert the
    same primary key value that was deleted by the original target transaction.

The
good news is that FTB can detect these situations, and if one is detected, it
provides three different additional options that can be specified to allow a
more complex FTB transaction to continue:

Table 1. Flashback Transaction
Backout: Backout Options

Option

Description

NOCASCADE

The
default value; it indicates that the executing session expects no dependent
transactions, but if any are found, an exception will be raised, and the
first dependent transaction will be listed.

CASCADE

Tells
Oracle to simply remove all transactions and their dependents in a posted-order fashion, i.e. in the
reverse order in which they were committed.

NOCONFLICT_ONLY

Tells
Oracle to back out only the
non-conflicting rows
for the transaction. This directly implies
that database consistency will be maintained, even though the atomicity of
the transaction has been broken while completing its repair.

NOCASCADE_FORCE

Tells
Oracle to back out all transactions regardless of any of the dependent
transactions. In other words, all compensating transactions will be executed
in reverse order of the time that each transaction was originally COMMITted.

Flashback
Transaction Backout: Metadata

Two
new data dictionary views allow the DBA to monitor the progress of ongoing FTB operations:

Table 2. Flashback
Transaction Backout: Metadata Views

View

Description

DBA_FLASHBACK_TXN_STATE

Shows
a transaction’s current state,
i.e. whether it has been backed out successfully from the target database, or
if it is still a “live” transaction. The set of compensating transaction(s)
may contain multiple rows because of the dependency between the transactions
that have been “compensated” by the compensating transaction(s).

DBA_FLASHBACK_TXN_REPORT

Contains
detailed metadata in XML format for the actual transactions that have been recently backed out of the
database, including every compensating transaction, that has been committed
in the database. One row is presented for each compensating transaction.

I’ve
presented some SQL*Plus queries against these views in Listing
4
, and I’ve included the results of running these queries against
the FTB operation I performed via EM earlier.

Flashback Transaction
Backout: Caveats and Best Practices

Of course,
you may have guessed that all this power does require some restraint. There are
a few additional restrictions on FTB at the table level:

  • Just as with Flashback Versions Query and Flashback
    Transaction Query, a FTB operation cannot be performed on transactions that
    have been punctuated by DDL changes to the table(s) being flashed back.
  • Also, if a table’s columns include data types other
    than those supported by LogMiner in Oracle 11g (e.g. BFILEs, VARRAYs),
    FTB may not be performed against that table.

Finally,
Oracle recommends the following best practices regarding FTB:

  • Once the need for FTB is discovered, it’s generally
    best to start the FTB operation as soon as possible. Since FTB needs to
    traverse transactional change vectors that may be stored in both archived and
    current online redo logs, it performs best when huge redo logs don’t have to be
    interrogated to find the change vectors necessary to create the compensating
    transactions.
  • To make later FTB auditing simpler, the DBA should
    assign a transaction name to the
    FTB operation. (If no transaction name is provided, FTB will automatically
    generate one.)
  • Don’t forget that function DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
    provides an excellent means to capture and return an SCN from within PL/SQL
    code blocks.

Conclusion

Oracle
Database 11g’s new LogMiner and Flashback
Transaction Backout features offer excellent capabilities for perusing as well
as correcting erroneous data on a transaction-by-transaction basis. The
fine-grained control over which transactions should be reversed, the ability to
preview the compensating transactions before deciding to reverse them, and the
capability to break the atomicity of transactions (only if desired, of course!)
are welcome additions to any DBA’s tool belt.

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

In
addition, the following MetaLink documentation helps clarify this feature set:

737332.1 Flashback Transactions Using DBMS_FLASHBACK.TRANSACTION_BACKOUT
procedure

»


See All Articles by Columnist
Jim Czuprynski

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