利用Logmnr找回误删除的数据

利用Logmnr找回误删除的数据，
这种方式可以在flashback也帮不上忙的起作用，
只要误操作时期的归档日志存在，
就可以通过归档日志来恢复误删除（delete）的数据。

测试环境oracle 9.2.0.8+winxp

1.创建测试数据

SQL>create user test identified by abc default tablespace users;
SQL>grant connect,resource to test;
SQl>connect test/abc
SQL>create table test_test (id number);
SQL> alter system switch logfile;
系统已更改。
SQL>
SQL> begin
2 for i in 1..10 loop
3 insert into test.test_test values(i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL>
SQL> select * from test.test_test;
ID
----------
1
2
3
4
5
6
7
8
9
10

已选择10行。
SQL> delete from test.test_test where id in (2,3,4,5);
已删除4行。
SQL> commit;
提交完成。
SQL> select * from test.test_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 "test"."test_TEST"("ID") values ('1'); delete from "test"."test_TEST" where "ID" = '1' and ROWID = 'AAABleAAFAAAAG+AAK';
INSERT insert into "test"."test_TEST"("ID") values ('2'); delete from "test"."test_TEST" where "ID" = '2' and ROWID = 'AAABleAAFAAAAG+AAA';
INSERT insert into "test"."test_TEST"("ID") values ('3'); delete from "test"."test_TEST" where "ID" = '3' and ROWID = 'AAABleAAFAAAAG+AAB';
INSERT insert into "test"."test_TEST"("ID") values ('4'); delete from "test"."test_TEST" where "ID" = '4' and ROWID = 'AAABleAAFAAAAG+AAC';
INSERT insert into "test"."test_TEST"("ID") values ('5'); delete from "test"."test_TEST" where "ID" = '5' and ROWID = 'AAABleAAFAAAAG+AAD';
INSERT insert into "test"."test_TEST"("ID") values ('6'); delete from "test"."test_TEST" where "ID" = '6' and ROWID = 'AAABleAAFAAAAG+AAE';
INSERT insert into "test"."test_TEST"("ID") values ('7'); delete from "test"."test_TEST" where "ID" = '7' and ROWID = 'AAABleAAFAAAAG+AAF';
INSERT insert into "test"."test_TEST"("ID") values ('8'); delete from "test"."test_TEST" where "ID" = '8' and ROWID = 'AAABleAAFAAAAG+AAG';
INSERT insert into "test"."test_TEST"("ID") values ('9'); delete from "test"."test_TEST" where "ID" = '9' and ROWID = 'AAABleAAFAAAAG+AAH';
INSERT insert into "test"."test_TEST"("ID") values ('10'); delete from "test"."test_TEST" where "ID" = '10' and ROWID = 'AAABleAAFAAAAG+AAI';
COMMIT commit;
START set transaction read write;
DELETE delete from "test"."test_TEST" where "ID" = '2' and ROWID = 'AAABleAAFAAAAG+AAA'; insert into "test"."test_TEST"("ID") values ('2');
DELETE delete from "test"."test_TEST" where "ID" = '3' and ROWID = 'AAABleAAFAAAAG+AAB'; insert into "test"."test_TEST"("ID") values ('3');
DELETE delete from "test"."test_TEST" where "ID" = '4' and ROWID = 'AAABleAAFAAAAG+AAC'; insert into "test"."test_TEST"("ID") values ('4');
DELETE delete from "test"."test_TEST" where "ID" = '5' and ROWID = 'AAABleAAFAAAAG+AAD'; insert into "test"."test_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 "test"."test_TEST"("ID") values ('2')
insert into "test"."test_TEST"("ID") values ('3')
insert into "test"."test_TEST"("ID") values ('4')
insert into "test"."test_TEST"("ID") values ('5')
PL/SQL procedure successfully completed
8.数据确认:
SQL> select * from test.test_test;
ID
----------
1
2
3
4
5
6
7
8
9
10

已选择10行。