Oracle Flashback Transaction Query is a useful tool to diagnose and repair changes made to the database at the transaction level. With it you can analyze transactions and recover from user or application errors. Before this capability existed, you would have to use Log Miner to get the information necessary to reconstruct and reverse historic transactions. Using Oracle Flashback for this is considerably faster and easier.
An Oracle Flashback Transaction Query retrieves its data from the static data dictionary view FLASHBACK_TRANSACTION_QUERY. Queries against this view can retrieve metadata and historical data for a single transaction or for all transactions in a supplied interval. The interval can be specified either by a timestamp value or by SCN values. The structure of the FLASHBACK_TRANSACTION_QUERY view is:
XID RAW(8 BYTE)
When a database has been configured for a Flashback Transaction Query, the UNDO_SQL column contains SQL text that is the logical opposite of the data manipulation language (DML) operation performed by the given transaction. Executing the SQL command in this field can usually reverse the original transaction within reason. Certain operations will not be exactly reversed. For example, a SQL_UNDO INSERT operation would be unlikely to insert a row back at the same ROWID from which it had been deleted.
It is very common for the Oracle Flashback Transaction Query to be used in conjunction with an Oracle Flashback Version Query. A Flashback Version Query can be used to provide a transaction ID that locates the specific transaction to be reversed.
SELECT operation, start_scn, commit_scn, logon_user
WHERE xid = HEXTORAW('09000900A9010000');
OPERATION START_SCN COMMIT_SCN LOGON_USER
------------ --------- ---------- ------------
UNKNOWN 393394 393463 OCPGURU
BEGIN 393394 393463 OCPGURU
The following statement uses Oracle Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change.
SELECT xid, logon_user
WHERE xid IN (
FROM employees VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('07-SEP-13 04.40.00 PM',
'DD-MON-YY HH:MI:SS AM') AND
TO_TIMESTAMP('07-SEP-13 04.49.00 PM',
'DD-MON-YY HH:MI:SS AM')
You can use the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to roll back a transaction and its dependent transactions while the database remains online. Transaction backout uses undo data to create and execute the compensating transactions to return the affected data to its original state. TRANSACTION_BACKOUT does not commit the DML operations that it performs as part of transaction backout. However, it does hold all the required locks on rows and tables in the right form to prevent other dependencies from entering the system. To make the transaction backout permanent, you must explicitly commit the transaction.
In order to configure a database for the Oracle Flashback Transaction Query feature, the database must be running in ARCHIVELOG mode. In addition, the database administrator must enable supplemental logging. If supplemental logging is not enabled, the FLASHBACK_TRANSACTION_QUERY view is still visible and can be queried, but it will not contain the information required to back out transactions. The following command enables supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
In addition, to perform Oracle Flashback Query operations, the administrator must grant appropriate privileges to the user who will be performing them. For Oracle Flashback Query, the administrator can do either of the following:
- To allow access to specific objects during queries, grant FLASHBACK and SELECT privileges on those objects.
- To allow queries on all tables, grant the FLASHBACK ANY TABLE privilege.
For Oracle Flashback Transaction Query, the administrator will need to grant the SELECT ANY TRANSACTION privilege. To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction, the administrator will need to grant SELECT, UPDATE, DELETE and INSERT privileges for the appropriate tables. Finally, the administrator must grant the user EXECUTE privileges on the DBMS_FLASHBACK Package.
When you are rolling back a given transaction, it is possible that there may be one or more dependent transactions. A dependent transaction can be related by a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction. Alternately, the dependent transaction can be related by a Primary Key Constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Either relationship constrains Oracle's ability to back out a transaction.
There are four options to the TRANSACTION_BACKOUT procedure. They determine how the backout operation will handle any transactions that are dependent on the one being backed out:
- CASCADE -- backs out specified transactions and all dependent transactions in reverse order (children are backed out before parents are backed out).
- NOCASCADE -- This is the default option. It assumes there are no dependent transactions. If a dependent transaction exists, it will cause an error.
- NOCASCADE_FORCE -- backs out specified transactions. If there are any dependent transactions, they are ignored. The server executes undo SQL statements for specified transactions in the reverse order of commit times.
- NONCONFLICT_ONLY -- backs out changes to nonconflicting rows of the specified transactions.
Oracle Database administrators can always use tools that help in resolving data problems with the minimum amount of time and trouble and the smallest possible footprint. Oracle Flashback Transaction Query is one that allows for highly targeted reversal of unintended data changes.
About the author:
Matthew Morris is a database engineer for Computer Sciences Corp. in Orlando, Fla. For over 17 years he has worked with the Oracle Database as a support engineer, database administrator, developer and architect.
This was first published in October 2013