|« 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
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.
Notice a button Flashback Transaction at the top of the transaction details. Press that button.
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.
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.
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.
After pressing next button you will be moved to the Flashback Transaction Review page where you can review the details of the Flashback process.
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.
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.
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).
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>