11G
Flashback Transaction Backout using the command line
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Thursday, 25 March 2010 20:29
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 25 March 2010 20:29
-
Written by Guy Lambregts
-
Hits: 5689
Flashback Transaction Backout using the command line
Flashback Transaction Backout is about backout of transaction(s) as well as dependant "child" transactions. Flashback Transaction Backout is built on top of the somewhat cumbersome logminer. You can use the GUI database Control for it as well as the command line.
We use here the command line, for this purpose we use the dbms_flashback package. Flashback Transaction Backout is used when the database is online and is actually a method to rewind logical corruption caused by transactions.
Prerequisite are
1. that your database operates in archive log mode since we are going to mine the database (transaction)logs.
2. that supplemental logging is enabled at the database level
Shoot
Let us validate the prerequisites
PLATINUM_SYS >select log_mode from v$database;
LOG_MODE
ARCHIVELOG
PLATINUM_SYS >select supplemental_log_data_min "MIN",supplemental_log_data_pk "PK",supplemental_log_data_fk "FK",supplemental_log_data_ui "UI",
supplemental_log_data_all "ALL" from v$database;
MIN PK FK UI ALL
YES YES YES YES NO
In our fantasy test database we have our table t_preciousmetals_pm
PLATINUM_SYS >connect pm/pm
Connected.
PLATINUM_PM >select * from t_preciousmetals_pm;
PM PM_NAME PM_
-- -------------------- ---
Ag Silver NO
Au Gold NO
IR Iridium YES
OS Osmium YES
PD Palladium YES
PT Platinium YES
RU Ruthenium YES
7 rows selected.
Let us insert another metal : Copper
PLATINUM_PM >insert into t_preciousmetals_pm values ('CU','Copper','NO');
1 row created.
PLATINUM_PM >commit;
Commit complete.
And let us have some transaction done at the Amsterdam Stock Exchange
PLATINUM_PM >desc t_exchangerate_er;
Name Null? Type
----------------------------------------- -------- ------------
ER_PK NOT NULL NUMBER
ER_FK_PM NOT NULL VARCHAR2(2)
ER_RATE NOT NULL NUMBER
ER_TIME NOT NULL TIMESTAMP(6)
PLATINUM_PM >insert into t_exchangerate_er values (seq_er.nextval,'CU',100,systimestamp);
1 row created.
PLATINUM_PM >insert into t_exchangerate_er values (seq_er.nextval,'CU',101,systimestamp);
1 row created.
PLATINUM_PM >insert into t_exchangerate_er values (seq_er.nextval,'CU',99,systimestamp);
1 row created.
PLATINUM_PM >commit;
Commit complete.
Despite the fact Copper is a very valuable and useful metal it is not considered as a precious metal. We have inserted the record by mistake into t_preciousmetals_pm. In between we have inserted other data into our database. We can' t afford loosing these transactions. We can' t afford a point in time recovery. We have to backout the changes related to Copper.
Let us start Logminer to retrieve the transactions we want to backout.
PLATINUM_SYS >exec dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\11.1.0\FLASH_RECOVERY_AREA\PLATINUM\ARCHIVELOG\2009_08_04\O1_MF_1_23_57K1YLT7_.ARC');
PL/SQL procedure successfully completed.
PLATINUM_SYS >exec dbms_logmnr.add_logfile('C:\ORACLE\PRODUCT\11.1.0\FLASH_RECOVERY_AREA\PLATINUM\ARCHIVELOG\2009_08_04\O1_MF_1_24_57K2LS7F_.ARC');
PL/SQL procedure successfully completed.
PLATINUM_SYS >begin
2 DBMS_LOGMNR.start_logmnr (
3 options => Dbms_Logmnr?.Dict_From_Online_Catalog);
4 END;
5 /
PL/SQL procedure successfully completed.
PLATINUM_SYS >select xid,sql_redo from v$logmnr_contents where seg_owner='PM';
03000A0071080000
insert into "PM"."T_PRECIOUSMETALS_PM"("PM_PK","PM_NAME","PM_IS_PGM") values ('CU','Copper','N');
01001600EB060000
insert into "PM"."T_EXCHANGERATE_ER"("ER_PK","ER_FK_PM","ER_RATE","ER_TIME") values ('1663016','CU','100',TO_TIMESTAMP('04-AUG-09 09.21.01.337000 PM'));
01001600EB060000
insert into "PM"."T_EXCHANGERATE_ER"("ER_PK","ER_FK_PM","ER_RATE","ER_TIME") values ('1663017','CU','101',TO_TIMESTAMP('04-AUG-09 09.21.05.439000 PM'));
01001600EB060000
insert into "PM"."T_EXCHANGERATE_ER"("ER_PK","ER_FK_PM","ER_RATE","ER_TIME") values ('1663018','CU','102',TO_TIMESTAMP('04-AUG-09 09.21.09.129000 PM'));
14 rows selected.
PLATINUM_SYS >begin
2 dbms_logmnr.end_logmnr;
3 end;
4 /
PL/SQL procedure successfully completed.
We will initiate dbms_flashback with the option SCNHINT The starting point. We could have used as well the TIMEHINT. If both are ommitted then the undo_retention is used.
PLATINUM_SYS >select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIME
1 1 25 52428800 2 NO CURRENT
3502961 04-08-2009:21:22:00
2 1 23 52428800 2 YES INACTIVE
3463314 03-08-2009:22:44:18
3 1 24 52428800 2 YES INACTIVE
3501872 04-08-2009:21:11:06
PLATINUM_SYS >select first_change# from v$archived_log where sequence#=23;
FIRST_CHANGE#
3463314
PLATINUM_SYS >declare
2 trans_arr xid_array;
3 begin
4 trans_arr := xid_array('03000A0071080000');
5 dbms_flashback.transaction_backout (
6 numtxns => 1,
7 xids => trans_arr,
8 options => dbms_flashback.cascade,
9 scnhint => 3463314
10 );
11 end;
12 /
declare
ERROR at line 1:
ORA-55511: Flashback Transaction experienced error in executing undo SQL
ORA-02292: integrity constraint (ORA-02292: integrity constraint (PM.FK_ER_PM)
violated - child record found
.) violated - child record found
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
PLATINUM_SYS >declare
2 trans_arr xid_array;
3 begin
4 trans_arr := xid_array('01001600EB060000','03000A0071080000');
5 dbms_flashback.transaction_backout (
6 numtxns => 2,
7 xids => trans_arr,
8 options => dbms_flashback.cascade,
9 scnhint => 3463314
10 );
11 end;
12 /
PL/SQL procedure successfully completed.
PLATINUM_SYS >select * from pm.t_preciousmetals_pm;
PM PM_NAME PM_
-- -------------------- ---
Ag Silver NO
Au Gold NO
IR Iridium YES
OS Osmium YES
PD Palladium YES
PT Platinium YES
RU Ruthenium YES
7 rows selected.
PLATINUM_SYS >commit;
Commit complete.
Note that initially I expected that the first error would not occured since I used dbms_flashback.cascade. This error wouldn' t have occured if I would have defined the foreign constraint with the on delete cascade constraint ( which i should have done ).
See also
Metalink Doc ID: 737332.1
Subject : Flashback transactions using dbms_flashback.transaction_backout procedure
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_flashb.htm#sthref3374