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