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

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
Trackback

no comment untill now

Add your comment now

切换到手机版