Tuesday, June 30, 2015

Oracle 11g Transaction Backout

In some times there is a need to undo a whole transaction that changes a lot of tables and their dependence. From Oracle 11g and onward this can be done using Transaction Backout feature of Flashback features.

Here is an example of a session that connected , changed data in lots of tables, commited and disconnected.

DB Username for the example is MYUSER

1. Login as sys dba and start a logminer session using the time that the transaction happened

BEGIN
  dbms_logmnr.start_logmnr ( starttime => to_timestamp('30/06/15 11:10',
  'DD/MM/YY HH24:MI:SS'), endtime => to_timestamp('30/06/15 11:15',
  'DD/MM/YY HH24:MI:SS'), OPTIONS => dbms_logmnr.dict_from_online_catalog +
  dbms_logmnr.continuous_mine                                             +
  dbms_logmnr.no_sql_delimiter                                            +
  dbms_logmnr.print_pretty_sql );
END;
/

2. Find all the transaction of the MYUSER for the logminer session  oppened. You ma need to spool the output to a file if there are a lot of transactions:

select username , xid , operation , sql_redo
from v$logmnr_contents
WHERE username = 'MYUSER';

3. Since you identified the XID end the logminer session:

BEGIN
  DBMS_LOGMNR.END_LOGMNR;
END;
/

4. No use dbms_flashback.TRANSACTION_BACKOUT to backout the transaction:

BEGIN
  dbms_flashback.TRANSACTION_BACKOUT ( numtxns => 1 , xids => xid_array(
  '02001200ED010000'), OPTIONS => DBMS_FLASHBACK.CASCADE );
END;
/

5. Before you commit , just check your data to confirm that the transaction was backed out using the query below

SELECT
  a.xid ,
  b.xid_report
FROM
  dba_flashback_txn_state a ,
  dba_flashback_txn_report b
WHERE
  a.compensating_xid = b.compensating_xid
AND a.xid            = '02001200ED010000';


6. If you are ok then commit and the transaction will be backed out.

REMARKS

DBMS_FLASHBACK.TRANSACTION_BACKOUT options default value is NOCASCADE

Here is an explanation of the options values:

NOCASCADE  rollbacks the specified transaction, but if a dependency is found, an error will be raised.

NOCASCADE_FORCE  rollbacks the specified transaction without considering any dependent transactions.

CASCADE rollbacks the specified transaction along with the dependent transactions (child first parent later)


NONCONFLICT_ONLY Only non conflicting rows of the transaction are rolled back.


REFERENCES:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_flashb.htm#CHDFBDCG
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1008


No comments:

Post a Comment