利用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行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-503895/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-503895/