Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Oracle 11g Flashback Transaction in OEM LogMiner Interface Change or switch undo tablespace in Oracle database

Oracle 11g DBMS_FLASHBACK TRANSACTION_BACKOUT

Posted by Zahid on July 5, 2010.

In a previous article, I explained how Flashback Transaction works in Oracle 11g using Oracle enterprise manager logminer interface. What if you don't have access to enterprise manager or for any other reason you don't want to use the oem? The good news is the Flashback Transaction feature can be used manually without oem in oracle 11g.

How to use DBMS_FLASHBAKC.TRANSACTION_BACKOUT to manually flashback transactions?

/* 
  Supplemental logging for primary key columns must be enabled for flashback transaction
  to work.
*/
 
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Login with user scott and execute a few transactions.

conn scott/tiger
Connected.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
create table student 
(
	student_id NUMBER,
	name       VARCHAR2(30),
	class      VARCHAR2(30),
	fee        NUMBER,
	constraint student_pk PRIMARY KEY (student_id)
);
select sysdate from dual;

SYSDATE
--------------------
05-JUL-2010 05:43:14

insert into student values (1,'ALLEN','OCP',1000);
commit;
update student
set class='OCA'
where student_id=1;
commit;
delete from student where student_id=1;
commit;
select sysdate from dual;

SYSDATE
--------------------
05-JUL-2010 05:43:31

We have three transactions between a time stamp of 05-JUL-2010 05:43:14 and 05-JUL-2010 05:43:31. T1 depends upon T2 and T2 depends upon T3 i.e. if we want to rollback t2 we cannot do it without rollingback t3 first.

conn / as sysdba
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

begin
     dbms_logmnr.start_logmnr 
     (
        starttime => '05-JUL-2010 05:43:10',
        endtime   => '05-JUL-2010 05:43:35',
        options   => dbms_logmnr.dict_from_online_catalog +
		     dbms_logmnr.continuous_mine +
		     dbms_logmnr.no_sql_delimiter +
		     dbms_logmnr.print_pretty_sql
     );
end;
/

PL/SQL procedure successfully completed.

-- This starts a logminer session to review transactions in the given time period.

column username format a8
column operation format a10
column sql_redo format a35
set lines 10000
set pages 500
select username , xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'SCOTT'
and   username = 'SCOTT';

USERNAME XID              OPERATION  SQL_REDO
-------- ---------------- ---------- -----------------------------------
SCOTT    0600110000030000 INSERT     insert into "SCOTT"."STUDENT"
                                      values
                                         "STUDENT_ID" = 1,
                                         "NAME" = 'ALLEN',
                                         "CLASS" = 'OCP',
                                         "FEE" = 1000

SCOTT    03001200ED020000 UPDATE     update "SCOTT"."STUDENT"
                                       set
                                         "CLASS" = 'OCA'
                                       where
                                         "STUDENT_ID" = 1 and
                                         "CLASS" = 'OCP' and
                                         ROWID = 'AAASNxAAEAAAAKnAAA'

SCOTT    05000F00F0020000 DELETE     delete from "SCOTT"."STUDENT"
                                      where
                                         "STUDENT_ID" = 1 and
                                         "NAME" = 'ALLEN' and
                                         "CLASS" = 'OCA' and
                                         "FEE" = 1000 and
                                         ROWID = 'AAASNxAAEAAAAKnAAA'



SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

The v$logmne_contents shows all transactions happened on SCOTT's objects by SCOTT in the given time period. We can see all three transactions of our example here with their Transaction IDs.

Using DBMS_FLASHBACK to backout a transaction

Oracle 11g introduced a new procedure TRANSACTION_BACKOUT in the package DBMS_FLASHBACK. This procedure takes an array of transactions flashback them along optionally with their dependent transactions. It uses undo data to create and execute the compensating transactions that return the affected data to its original state.

Click here to see details about DBMS_FLASHBACK.TRANSACTION_BACKOUT

begin
     dbms_flashback.TRANSACTION_BACKOUT
     (
	numtxns => 1 ,
	xids    => xid_array('03001200ED020000'),
        options => DBMS_FLASHBACK.NOCASCADE
     );
end;
/
begin
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 2

DBMS_FLASHBACK.TRANSACTION_BACKOUT

has a default value NOCASCADE in the OPTIONS parameter which will not work if the transaction depends upon another transaction. Here is list of options that can be provided to the procedure.

NOCASCADE

- Rollsback the specified transaction. But if a dependency is found, an error will be raised.

NOCASCADE_FORCE

- Rollsback the specified transaction without considering any dependent transactions. It is recpmmended that you review all the changes made by the flashback and if you are satisfied then commit this operation otherwise just rollback.

CASCADE

- Rollsback the specified transaction along with the dependent transactions in a post order fashion i.e. child first parent later.

NONCONFLICT_ONLY

- Only nonconflicting rows of the transaction are rolled back. Database remains consistent, but transaction atomicity is lost.

The cascade option suits us best in our example.

begin
     dbms_flashback.TRANSACTION_BACKOUT
     (
	numtxns => 1 ,
        xids    => xid_array('03001200ED020000'),
        options => DBMS_FLASHBACK.CASCADE
     );
end;
/

PL/SQL procedure successfully completed.

The transaction backout is successful. However, this doesn't make anything permanent in the database unless you explicitly perform a COMMIT. Before that you may want to review the results of the transaction backout to make sure everything happened as you desire.

The procedure DBMS_FLASHBACK.TRANSACTION_BACKOUT fills up two dictionary views *_flasgback_txn_state and *_flashback_txn_report.

SQL> desc dba_flashback_txn_state
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 COMPENSATING_XID                             RAW(8)
 XID                                          RAW(8)
 DEPENDENT_XID                                RAW(8)
 BACKOUT_MODE                                 VARCHAR2(16)
 USERNAME                            NOT NULL VARCHAR2(30)

SQL> desc dba_flashback_txn_report
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 COMPENSATING_XID                    NOT NULL RAW(8)
 COMPENSATING_TXN_NAME                        VARCHAR2(256)
 COMMIT_TIME                                  DATE
 XID_REPORT                                   CLOB
 USERNAME                            NOT NULL VARCHAR2(30)

dba_flashback_txn_state has the details of the target transaction, the mode and the compensating transaction. The dba_flashback_txn_report has a full report of the process against the compensating transaction. We can join both to retrieve the full report against the target transaction.

set long 100000
set lines 10000
set pages 1000
column xid_report format a70

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 = '03001200ED020000';

XID              XID_REPORT
---------------- ----------------------------------------------------------------------
03001200ED020000 <?xml version="1.0" encoding="ISO-8859-1"?>
                 <COMP_XID_REPORT XID="05000400F0020000">
                        <TRANSACTION XID="03001200ED020000">
                        <CHARACTERISTICS>
                        </CHARACTERISTICS>
                        <UNDO_SQL>
                                <USQL exec="yes">
                                 update "SCOTT"."STUDENT" set "CLASS" = 'OCP' where 
                 "STUDENT_ID" = '1' and "CLASS" = 'OCA'
                                </USQL>
                        </UNDO_SQL>
                        <DEPENDENT_XIDS>
                                <TRANSACTION XID="05000F00F0020000">
                                <CHARACTERISTICS>
                                </CHARACTERISTICS>
                                <UNDO_SQL>
                                        <USQL exec="yes">
                                         insert into "SCOTT"."STUDENT"
                 ("STUDENT_ID","NAME","CLASS","FEE") values ('1','ALLEN','OCA','1000')
                                        </USQL>
                                </UNDO_SQL>
                                <DEPENDENT_XIDS>
                                </DEPENDENT_XIDS>
                                </TRANSACTION>
                        </DEPENDENT_XIDS>
                        </TRANSACTION>
                 <EXECUTED_UNDO_SQL>
                 <EXEC_USQL>insert into "SCOTT"."STUDENT"
                 ("STUDENT_ID","NAME","CLASS","FEE") values ('1','ALLEN','OCA','1000')
                 </EXEC_USQL>
                 <EXEC_USQL>update "SCOTT"."STUDENT" set "CLASS" = 'OCP' where
                 "STUDENT_ID" = '1' and "CLASS" = 'OCA'

                 </EXEC_USQL>
                 </EXECUTED_UNDO_SQL>
                 </COMP_XID_REPORT>

-- Execute a query on the affected data to see, whether it is flashed back or not.

select * from scott.student;

STUDENT_ID NAME                           CLASS                                 FEE
---------- ------------------------------ ------------------------------ ----------
         1 ALLEN                          OCP                                  1000

-- When you are satisfied with every thing issue a commit to make the flashback permanent.
SQL> COMMIT; 

DBMS_FLASHBACK.TRANSACTION_BACKOUT Usage Considerations

Since this procedure uses Undo data to create and execute the compensating transactions, just watch your Undo may have been over written already. And in that case you will hit the world famous "Snapshot too old error".

begin
     dbms_flashback.TRANSACTION_BACKOUT
     (
	numtxns => 1 ,
        xids    => xid_array('09001100B7020000'),
        options => DBMS_FLASHBACK.CASCADE
     );
end;
/
begin
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 2

I had a transaction id old enough that I was sure its undo was overwritten. I used it with the procedure and I got the pretty error.

See also
How to read or analyze redo log files using LogMiner
Using Oracle LogMiner to undo incorrectly committed changes
Oracle 11g Using LogMiner to analyze redo log files
Oracle 11g Flashback Transaction in OEM LogMiner Interface