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!!!
WAW Dependency
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.
Setup
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.
User HR schema (user).
The transaction (referred as T1 includes 6 DMLs.
Also included is a transaction T2 (2 DMLs) that is not related to T1 but is dependent on T1.
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.
drop table ta;
CREATE table TA (ca int primary key, va varchar2(20));
-- Inserted 10 rows for set up.
insert into ta select level ca, 'AAAA - ' || to_char(to_date(level,'jsp')) from dual connect by level < 5;
commit;
Now with this setup completed, we can run the transactions and explore TRANSACTION BACKOUT feature.
Note:
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.
SQL> --drop table ta purge;
SQL> --CREATE table TA (ca int primary key, va varchar2(20));
SQL> delete from ta
9 rows deleted.
SQL> commit
Commit complete.
SQL> insert into ta select level ca, 'AAAA - ' || to_char(to_date(level,'jsp')) from dual connect by level < 5
4 rows created.
SQL> commit
Commit complete.
SQL> prompt After initial setup...
After initial setup...
SQL> select ora_rowscn, ta.* from ta
ORA_ROWSCN CA VA
---------- ---------- --------------------
31925488 1 AAAA - 01-JAN-12
31925488 2 AAAA - 02-JAN-12
31925488 3 AAAA - 03-JAN-12
31925488 4 AAAA - 04-JAN-12
4 rows selected.
SQL> insert into ta values( 10 , 'BBBB - Ins by T1')
1 row created.
SQL> insert into ta values( 11 , 'BBBB - Ins by T1')
1 row created.
SQL> insert into ta values( 12 , 'BBBB - Ins by T1')
1 row created.
SQL> insert into ta values( 13 , 'BBBB - Ins by T1')
1 row created.
SQL> update ta set va= 'UUUU - Upd by T1' where ca in (1,11)
2 rows updated.
SQL> delete from ta where ca in (2)
1 row deleted.
SQL> commit
Commit complete.
SQL> prompt After Transaction T1...
After Transaction T1...
SQL> select ora_rowscn, ta.* from ta
ORA_ROWSCN CA VA
---------- ---------- --------------------
31925493 1 UUUU - Upd by T1
31925493 3 AAAA - 03-JAN-12
31925493 4 AAAA - 04-JAN-12
31925493 10 BBBB - Ins by T1
31925493 11 UUUU - Upd by T1
31925493 12 BBBB - Ins by T1
31925493 13 BBBB - Ins by T1
7 rows selected.
SQL> insert into ta values( 21, 'BBBB - Ins by T2' )
1 row created.
SQL> insert into ta values( 22, 'BBBB - Ins by T2' )
1 row created.
SQL> update ta set va= 'UUUU - Upd by T2' where ca in (3, 10)
2 rows updated.
SQL> commit
Commit complete.
SQL> prompt After Transaction T2...
After Transaction T2...
SQL> select ora_rowscn, ta.* from ta
ORA_ROWSCN CA VA
---------- ---------- --------------------
31925495 1 UUUU - Upd by T1
31925495 3 UUUU - Upd by T2
31925495 4 AAAA - 04-JAN-12
31925495 10 UUUU - Upd by T2
31925495 11 UUUU - Upd by T1
31925495 12 BBBB - Ins by T1
31925495 13 BBBB - Ins by T1
31925495 21 BBBB - Ins by T2
31925495 22 BBBB - Ins by T2
9 rows selected.
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…
ORA_ROWSCN CA VA
---------- ---------- --------------------
31950709 11 UUUU - Upd by T1
31950709 1 UUUU - Upd by T1
31950709 12 BBBB - Ins by T1
31950709 13 BBBB - Ins by T1
31950709 21 BBBB - Ins by T2
31950709 22 BBBB - Ins by T2
31950709 3 UUUU - Upd by T2
31950709 4 AAAA - 04-JAN-12
31950709 10 UUUU - Upd by T2
9 rows selected.
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.
select * from dba_flashback_txn_state;
select * from dba_flashback_txn_report;
Exceptions:
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.
References:
Following references were used.
