Oracle Database 11g: Flashback Transaction Backout
December 29, 2008
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 teams offshore resources were attempting to implement. I got a request today from one of our developers to approve the execution of some DML that hed 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 hed 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 hed 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 couldnt explain to her how Oracle 10g offered several features that would have made her job much simpler:
However, both of these tools are limited by the amount of undo thats currently retained within the databases UNDO tablespace, because thats 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
Oracles LogMiner tool set finally gets the recognition its due as a power player in Oracle 11g its the perfect addendum to the Flashback Versions Query and Flashback Transaction Query capabilities that were introduced in Oracle 10g. For starters, Oracle 11gs 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:
The wizard interface is not the only new LogMiner feature in Oracle 11g, however:
Ill illustrate these extended LogMiner query capabilities a bit later in this article.
Reversing Transactions Accurately Via Flashback Transaction Backout
Oracle Database 11gs 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:
Performing FTB Using Enterprise Manager Database Control
To illustrate the flexibility and power of Flashback Transaction Backout, Ive set up the following simple scenario:
Now Im ready to use the power of FTB to roll back just the last two update transactions. As shown in Figure 1, Ill select the Perform Recovery link from the Availability tab of Oracle 11g Enterprise Manager Database Control, and then Ill start FTB by selecting the Transactions option for the scope of my recovery operation:
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 thatll need to be read to find the corresponding change vectors. As Figure 2 shows below, Ive also restricted the search for possible transactions to only those that affect table AP.VENDORS:
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:
After a few minutes, the Recovery Wizard then presents me with a list of possible transactions against AP.VENDORS that Id be most interested in reversing, as shown in Figure 4.
If Im 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 x05000F00D050000:
After selecting the desired transaction(s) for FTB, Oracle provides a tracking screen to notify me that its begun to process them for reversal, as Figure 6 shows.
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.
I have several options at this point:
Once Ive 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.
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 Orders 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:
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:
Flashback Transaction Backout: Metadata
Two new data dictionary views allow the DBA to monitor the progress of ongoing FTB operations:
Ive presented some SQL*Plus queries against these views in Listing 4, and Ive 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:
Finally, Oracle recommends the following best practices regarding FTB:
Oracle Database 11gs 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 DBAs tool belt.
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
In addition, the following MetaLink documentation helps clarify this feature set:
737332.1 Flashback Transactions Using DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure