利用Logmnr找回誤刪除的資料

利用Logmnr找回誤刪除的資料，
這種方式可以在flashback也幫不上忙的起作用，
只要誤操作時期的歸檔日誌存在，

就可以透過歸檔日誌來恢復誤刪除（delete）的資料。

測試環境oracle 9.2.0.8+winxp

 

1.建立測試資料

SQL>create user hz identified by abc default tablespace users;
SQL>grant connect,resource to hz;
SQl>connect hz/abc
SQL>create table hz_test (id number);

SQL> alter system switch logfile;

系統已更改。

SQL>
SQL> begin
  2  for i in 1..10 loop
  3  insert into hz.hz_test values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL 過程已成功完成。

SQL>
SQL> select * from hz.hz_test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        

已選擇10行。

SQL>  delete from hz.hz_test where id in (2,3,4,5);

已刪除4行。

SQL> commit;

提交完成。

SQL> select * from hz.hz_test;

        ID
----------
         1
         6
         7
         8
         9
        10
已選擇6行。
SQL> alter system switch logfile;

系統已更改。

現在把刪除的2,3,4,5 四條資料恢復
2.增加產生的歸檔日誌序列號為153

SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'d:\backup\ARC00153.001',opti
ons=>dbms_logmnr.new);

PL/SQL 過程已成功完成。

3.分析日誌
SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online
_catalog);

PL/SQL 過程已成功完成。

4.把需要的資訊儲存到臨時表tmp_logmnr

SQL> create table tmp_logmnr as select operation,sql_redo,sql_undo from v$logmn
r_contents;

表已建立。

5.終止日誌分析

SQL> exec sys.dbms_logmnr.end_logmnr;

PL/SQL 過程已成功完成。

6.查詢臨時表資訊

 

SQL> select * from tmp_logmnr;

OPERATION       SQL_REDO                                                                         SQL_UNDO
------------------------------------------------------------------------- --------------------------------------------------------------------------------
START        set transaction read write;                                                     
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('1');                                   delete from "HZ"."HZ_TEST" where "ID" = '1' and ROWID = 'AAABleAAFAAAAG+AAK';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('2');                                   delete from "HZ"."HZ_TEST" where "ID" = '2' and ROWID = 'AAABleAAFAAAAG+AAA';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('3');                                   delete from "HZ"."HZ_TEST" where "ID" = '3' and ROWID = 'AAABleAAFAAAAG+AAB';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('4');                                   delete from "HZ"."HZ_TEST" where "ID" = '4' and ROWID = 'AAABleAAFAAAAG+AAC';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('5');                                   delete from "HZ"."HZ_TEST" where "ID" = '5' and ROWID = 'AAABleAAFAAAAG+AAD';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('6');                                   delete from "HZ"."HZ_TEST" where "ID" = '6' and ROWID = 'AAABleAAFAAAAG+AAE';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('7');                                   delete from "HZ"."HZ_TEST" where "ID" = '7' and ROWID = 'AAABleAAFAAAAG+AAF';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('8');                                   delete from "HZ"."HZ_TEST" where "ID" = '8' and ROWID = 'AAABleAAFAAAAG+AAG';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('9');                                   delete from "HZ"."HZ_TEST" where "ID" = '9' and ROWID = 'AAABleAAFAAAAG+AAH';
INSERT       insert into "HZ"."HZ_TEST"("ID") values ('10');                                  delete from "HZ"."HZ_TEST" where "ID" = '10' and ROWID = 'AAABleAAFAAAAG+AAI';
COMMIT       commit;                                                                         
START        set transaction read write;                                                     
DELETE       delete from "HZ"."HZ_TEST" where "ID" = '2' and ROWID = 'AAABleAAFAAAAG+AAA';    insert into "HZ"."HZ_TEST"("ID") values ('2');
DELETE       delete from "HZ"."HZ_TEST" where "ID" = '3' and ROWID = 'AAABleAAFAAAAG+AAB';    insert into "HZ"."HZ_TEST"("ID") values ('3');
DELETE       delete from "HZ"."HZ_TEST" where "ID" = '4' and ROWID = 'AAABleAAFAAAAG+AAC';    insert into "HZ"."HZ_TEST"("ID") values ('4');
DELETE       delete from "HZ"."HZ_TEST" where "ID" = '5' and ROWID = 'AAABleAAFAAAAG+AAD';    insert into "HZ"."HZ_TEST"("ID") values ('5');
COMMIT       commit;   

18 rows selected

7.恢復誤刪除的資料

SQL> declare
  2    mysql varchar2(4000);
  3    num number :=0;
  4  begin
  5    for c_tmp in (select sql_undo from tmp_logmnr where peration='DELETE') loop
  6      mysql := replace(c_tmp.sql_undo,';','');
  7      dbms_output.put_line(mysql);
  8      execute immediate mysql;
  9      num := num + 1;
 10      if mod(num,1000)=0 then
 11        commit;
 12      end if;
 13    end loop;
 14    commit;
 15  exception
 16    when others then
 17     rollback;
 18  end;
 19  /

insert into "HZ"."HZ_TEST"("ID") values ('2')
insert into "HZ"."HZ_TEST"("ID") values ('3')
insert into "HZ"."HZ_TEST"("ID") values ('4')
insert into "HZ"."HZ_TEST"("ID") values ('5')

PL/SQL procedure successfully completed

8.資料確認:

SQL> select * from hz.hz_test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        

已選擇10行。