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


Friday, June 5, 2015

Database open resetlogs fails with ORA-00392: log X of thread Y is being cleared, operation not allowed

I encountered this error when during point in time recovery the server i run out of space.

When i freed up some space and tried to pen the database with the resetlogs clause i got ORA-00392:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1:
'/aux/AUX/onlinelog/o1_mf_3_%u_.log'
ORA-00312: online log 3 thread 1:
'/aux/fra/AUX/onlinelog/o1_mf_3_%u_.log'

This happened because at the time that DB was trying to clear and archive the online logs (after the open resetlogs command) the server run out of space and operation did not completed successfully.

After releasing space on the server the solution is the following :

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database open resetlogs;

Database altered.