Oracle introduced Flashback Technology many years back (9i), and with version 11g TRANSACTION_BACKOUT capability was introduced.
This is a very powerful feature, which allows a developer or DBA to BACKOUT specific committed transactions. This feature leverages UNDO and supplemental log data to identify the DML changes involved in a specific transaction and the other dependent transactions to BACKOUT the transaction.
Please Note: This feature will allow you to violate Database consistency and Atomicity of Transactions. So use it with care!!!
However, in development and testing environment, I find this feature useful for performing repeated end-to-end testing for applications involving of complex applications.
Specifically in testing of transactions involving
Complex setup of test data.
Test data setup with workflow/different users/roles involvement.
Transaction testing involving external system interfaces, large batch processes or time sensitive transactions.
Transaction changes the state of data in such a way that process to reverse the state is complex.
In these situations, the effort for setting up the test data is significantly more than test step itself. We can use TRANSACTION_BACKOUT feature to revert back the transaction and repeat the test many times.
Let us explore the details of FLASHBACK transactions, specifically the BACKOUT feature.
What is Flashback Transaction BACKOUT?
Flashback Transaction Back-Out is a logical recovery option to roll back a target transaction and its dependent transactions while the database remains online. A dependent transaction is related by either a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction, or a primary key constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Flashback Transaction utilizes undo and the redo generated for undo blocks, to create and execute a compensating transaction for reverting the affected data back to its original state.
For Flashback Transactions to work, Oracle has to capture sufficient information in UNDO and supplemental logs.
For Oracle Flashback Transaction to work following are the requirements.
Database must be in ARCHIVELOG mode. (How to switch to ARCHIVELOG mode)
Supplemental Log (with primary key) must be turned on. (How to turn on Supplemental Logging)
User must have following privilege.
EXECUTE privilege on DBMS_FLASHBACK package
GRANT EXECUTE on DBMS_FLASHBACK to userid;
SELECT ANY TRANSACTION privilege.
GRANT SELECT ANY TRANSACTION to userid;
Please note this is a very powerful privilege and should be granted only after care evaluation of security/audit requirements.
In addition the user must have CRUD privilege on the tables that FBT BACKOUT is working on!!!
A transaction can have a write-after-write (WAW) dependency, meaning that a transaction updates or deletes row data, that has been inserted or updated by a previous transaction. In these situations, Oracle FBT BACKOUT feature provides options to back out all dependent transactions (keeping DB transaction-ally consistent) or allow you to break the transaction consistency.
The FBT BACKOUT can be used using command line or OEM. The rest of this posting will walk through an example of FBT BACKOUT using command line interface. The OEM interface is lot more intuitive but may be cumbersome when working with complex and large transactions.
The setup requirements and sample of FBT BACKOUT is well described at Oracle Tutorials. I’ll walk through an example in the context of repeatable testing.
Our goal is to repeatedly test the T1 after reverting T1 after each test.
In our test script, we’ll perform following steps.
Create the initially populate the table
Perform two transactions on the table.
Verify the data changes.
Identify the transactions (using XID) that’ll be
BACKOUT the transactions.
Verify that data changes are backed out and original values are restored.
Reapply the transaction by running the same script.
Verify the data changes.
Table’s definition and script to populate them are as follows.
Now with this setup completed, we can run the transactions and explore TRANSACTION BACKOUT feature.
Both update and delete in T1 are dependent on the insert of T1. Of course this is not transaction dependency.
Note transaction T2 (a different transaction from T1) inserts additional rows and however updates some rows created by transaction T1. Thus T2 transaction is dependent on T1: WAW dependency. In order to BACKOUT transaction T1 without compromising data integrity we must revert transaction T2.
Following script shows the transactions.
Now that transactions have been run, we can go to OEM, and look and manage transactions. Though transactions can be managed by command line environment (described here), OEM provides a robust interface that eases the tasks considerably.
After the transaction BACKOUT…
At this point we can re-run the transaction that was BACKED-OUT again.
There are views that describe the status of the transactions that were BACKED-OUT.
This way logical transactions can be rolled-back and reapplied for repeated testing.
When performing TRANSACTION BACKOUT there are several errors/ exceptions one can encounter. Some of the common ones are described below.
Transaction back-out is not supported over DDLs on the object. If a transaction is picked that is before a DDL issued on the DB object OEM will not allow the transactions to be backed-out.
Also if the DB is not in ARCHIVELOG mode this feature will not be available.
Hope this helps.
Following references were used.