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
Upgrade Oracle 10g Release 2 from 10201 to 10204 Oracle 11g DBMS_FLASHBACK TRANSACTION_BACKOUT

Oracle 11g Flashback Transaction in OEM LogMiner Interface

Posted by Zahid on July 4, 2010.

There has been many ways to recover from unwanted committed transactions so far in the history of oracle like flashback query, flashback table and flashback database. In oracle 11g we have a new feature called flashback transaction that can be used to rollback a whole transaction, optionally along with its dependent transactions if there are any.

How Flashback Transaction works in Oracle 11g using OEM?

/*
  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 
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
--------------------
04-JUL-2010 20:14:32

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
--------------------
04-JUL-2010 20:16:43

Notice a commit after every DML, so we have three transactions here between a time stamp of 04-JUL-2010 20:14:32 and 04-JUL-2010 20:16:43. The first transaction creates a record in the table the second modifies the same record and third deletes the very record.

Now if we want to rollback the second transaction we cannot do it unless we also rollback the third transaction. So t2 depends upon t3.

Open Oracle Enterprise Manager and start reviewing transactions between the above mentioned time frame. Please see below

LogMiner Oracle 11g Enterprise Manager

Please click Oracle 11g Using LogMiner to analyze redo log files to see how to review transactions in Oracle 11g Enterprise Manager and reach the above screen.

In the above screen click the Transaction ID for the transaction you wish to flashback/rollback. IN our case its the update statement with the transaction id 09001100B7020000. Once you click the Transaction ID you should see the transaction details as below.

LogMiner Transaction Details Oracle 11g Enterprise Manager

Notice a button Flashback Transaction at the top of the transaction details. Press that button.

LogMiner Transaction Details Oracle 11g Enterprise Manager

Click yes to proceed further. You will see all the dependent transactions for the target transaction here and the option that Enterprise Manager will use during flashback. The default is NONCONFLICT_ONLY which means only the non-conflicting rows of the target transaction will be flashedback.

LogMiner Flashback Transaction Show Dependencies Oracle 11g Enterprise Manager

You may click on the Transaction ID to view its contents and press the Change recovery option button to chnage the recovery option from NONCONFLICTING_ONLY to something else. For us to flashback the 09001100B7020000, the transaction 02000800AF020000 must be rolled back first, so we need to change the recovery option. When you press the change recovery option button you should see three recovery options as below and a description is given with every option. For our scenario we need CASCADE option.

LogMiner Show Dependencies Change Recovery Option Oracle 11g Enterprise Manager

Select the cascade option and click ok. You should now again see the Show Dependencies page but with the option CASCADE as below. Click Next to proceed further.

LogMiner Flashback Transaction Show Dependencies Oracle 11g Enterprise Manager

After pressing next button you will be moved to the Flashback Transaction Review page where you can review the details of the Flashback process.

LogMiner Flashback Transaction Review Oracle 11g Enterprise Manager

Before pressing Finish and committing the Flashback, you may query the state of the data to make sure the Flashback has worked for you as you desire. Write a query on your data in the Execute SQL box and press execute sql. You should see the state of data as it will be after the flash back.

LogMiner Review Execute SQL results Oracle 11g Enterprise Manager

As we can see in the Execute Sql results the update of class from OCP to OCA is rolled back. Press ok to go back. You may also click "Show Undo Sql Script" to see the Undo Statement that will be executed for the flashback on the Review Page.

LogMiner Review Show Undo Sql Oracle 11g Enterprise Manager

The Undo Sql shows that both the DELETE and UPDATE transactions will be flashedback. After reviewing the state of the data after flashback and the undo sql script if you are ok with the outcome of the flashback transaction then go back to the review page and press finish to make the flashback permanent. When you press finish you should see the Flashback Transaction Results page as below which shows the Transaction Id for the compensating transaction (The transaction that was used to perform flashback).

LogMiner Flashback Transaction Results Oracle 11g Enterprise Manager

This compensating transaction id "07001A0020020000" can be used for reporting purpose later on. The dictionary view DBA_FLASHBACK_TXN_REPORT contains a full report of this flashback process against its compensating_xid.

set lines 10000
set pages 1000
set long 100000
column xid_report format a100
 
select xid_report 
from dba_flashback_txn_report
where compensating_xid = '07001A0020020000';

XID_REPORT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="07001A0020020000" NAME="_SYS_COMP_TXN_9633873_TIM_12782425
88">
        <TRANSACTION XID="09001100B7020000">
        <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="02000800AF020000">
                <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>

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