LogMiner救命稻草_找回误删除数据(二)

新增日志列表,重新开始挖掘

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/arch/1_60_847657195.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
 
PL/SQL procedure successfully completed

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
 
PL/SQL procedure successfully completed

查询V$LOGMNR_CONTENTS

select scn,
       timestamp,
       (xidusn || '.' || xidslt || '.' || xidsqn) as xid,
       info,
       seg_owner,
       seg_name,
       operation,
       sql_redo,
       sql_undo
  from v$logmnr_contents
 where seg_name in ('T1', 'T2')
/

SCN TIMESTAMP   XID            SEG_OWNER        SEG_NAME    OPERATION     SQL_REDO                                                                         SQL_UNDO
---------- ----------- -------------- ---------------- ----------- ------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
   1212893 2015/1/10 1 4.1.550        SYS              T1          DDL           create table t1 (a number,b char(2));                                            
   1212907 2015/1/10 1 10.12.537      SYS              T2          DDL           create table t2 (c number,d char(2));                                            
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A","B") values ('1','r1');                               delete from "SYS"."T1" where "A" = '1' and "B" = 'r1' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A","B") values ('2','r2');                               delete from "SYS"."T1" where "A" = '2' and "B" = 'r2' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T1          INSERT        insert into "SYS"."T1"("A","B") values ('3','r3');                               delete from "SYS"."T1" where "A" = '3' and "B" = 'r3' and ROWID = 'AAANBSAABAAAP
   1212913 2015/1/10 1 5.40.556       SYS              T2          INSERT        insert into "SYS"."T2"("C","D") values ('1','t2');                               delete from "SYS"."T2" where "C" = '1' and "D" = 't2' and ROWID = 'AAANBTAABAAAP
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '1' and "B" = 'r1' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A","B") values ('1','r1');
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '2' and "B" = 'r2' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A","B") values ('2','r2');
   1213003 2015/1/10 1 5.47.556       SYS              T1          DELETE        delete from "SYS"."T1" where "A" = '3' and "B" = 'r3' and ROWID = 'AAANBSAABAAAP insert into "SYS"."T1"("A","B") values ('3','r3');
 
9 rows selected

执行SQL_UNDO字段的SQL即可

SQL> insert into "SYS"."T1"("A","B") values ('1','r1');
 
1 row inserted
SQL> insert into "SYS"."T1"("A","B") values ('2','r2');
 
1 row inserted
SQL> insert into "SYS"."T1"("A","B") values ('3','r3');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> 
SQL> select * from t1;
 
         A B
---------- --
         1 r1
         2 r2
         3 r3

至此,被误删除的数据就找回来了.

关闭LogMiner

SQL> EXECUTE DBMS_LOGMNR.end_logmnr;
 
PL/SQL procedure successfully completed