使用Logmnr恢复误删的数据

1. 确认测试表T1当前的记录数
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select count(*) from t1;

COUNT(*)
----------
22

2. 测试delete操作，并记录前后时间戳
（实战中，为了尽可能准确定位数据，也需要尽可能准确的获取时间戳）
SQL> select sysdate from dual;

SYSDATE
-------------------
2008-09-22 11:32:25

SQL> delete from t1 where rownum<11;

已删除10行。

SQL> commit;

提交完成。

SQL> select count(*) from t1;

COUNT(*)
----------
12

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-09-22 11:32:45

[@more@]

3. 归档当前delete操作的在线日志
SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

SQL> alter system switch logfile;

系统已更改。

4. 根据获得的时间戳，定位archive日志文件
SQL> col name for a55
SQL> select name, first_time, next_time from v$archived_log;

NAME FIRST_TIME NEXT_TIME
------------------------------------------------------- ------------------- -------------------
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00002_0666095554.001 2008-09-22 10:33:16 2008-09-22 11:05:04
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00003_0666095554.001 2008-09-22 11:05:04 2008-09-22 11:05:05
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00004_0666095554.001 2008-09-22 11:05:05 2008-09-22 11:05:07
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00005_0666095554.001 2008-09-22 11:05:07 2008-09-22 11:05:45
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00006_0666095554.001 2008-09-22 11:05:45 2008-09-22 11:05:52
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00007_0666095554.001 2008-09-22 11:05:52 2008-09-22 11:06:03
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00008_0666095554.001 2008-09-22 11:06:03 2008-09-22 11:28:50
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00009_0666095554.001 2008-09-22 11:28:50 2008-09-22 11:28:52
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00010_0666095554.001 2008-09-22 11:28:52 2008-09-22 11:28:56
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00011_0666095554.001 2008-09-22 11:28:56 2008-09-22 11:29:32
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00012_0666095554.001 2008-09-22 11:29:32 2008-09-22 11:29:37

NAME FIRST_TIME NEXT_TIME
------------------------------------------------------- ------------------- -------------------
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00013_0666095554.001 2008-09-22 11:29:37 2008-09-22 11:29:43
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00014_0666095554.001 2008-09-22 11:29:43 2008-09-22 11:33:14
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00015_0666095554.001 2008-09-22 11:33:14 2008-09-22 11:33:15
D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00016_0666095554.001 2008-09-22 11:33:15 2008-09-22 11:33:19

已选择15行。

5. 添加待分析归档日志，多个文件可多次添加
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName=>'D:/ORACLE/ORADATA/ALEX/ARCHIVE/ARC00014_0666095554.001');

PL/SQL 过程已成功完成。

6. 开始分析归档日志
SQL> exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL 过程已成功完成。

7. 抓取待恢复的信息到临时表
SQL> create table tmp_logmnr as
2 select operation,sql_redo,sql_undo from v$logmnr_contents
3 where seg_name='T1' and operation='DELETE'
4 and timestamp between to_date('2008-09-22 11:32:25','yyyy-mm-dd hh24:mi:ss')
5 and to_date('2008-09-22 11:32:45','yyyy-mm-dd hh24:mi:ss');

表已创建。

SQL> select count(*) from tmp_logmnr;

COUNT(*)
----------
10

8. 结束归档日志分析操作
SQL> exec sys.dbms_logmnr.end_logmnr

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

10. 确认已恢复成功
SQL> select count(*) from t1;

COUNT(*)
----------
22

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

转载于:http://blog.itpub.net/7319461/viewspace-1010908/