19 | 08 | 2017
Latest Articles
Popular Articles

11G

Flashback Transaction Backout using the command line

User Rating:  / 1
PoorBest 

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');
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