【piner】利用Logmnr找回誤刪除資料

idba發表於2008-07-10

Logmnr在Oracle 9i以後做了眾多的改進，如可以不需要build flat文字檔案就可以分析日誌了，也就表示可以不需要修改引數utl_file_dir就可以分析日誌了，避免了修改引數utl_file_dir導致的重起資料庫問題。另外也開始支援把字典資訊build到聯機日誌中，在異地分析歸檔日誌。

在flashback也不能幫上忙的時候，logmnr卻是非常有用的。因為只要誤操作時期的歸檔日誌存在，就可以通過歸檔日誌來恢復誤刪除（delete）的資料。

如果在Oracle 9i以上，採用在本地的線上資料字典分析歸檔日誌，就這麼簡單：

  1. Piner@9iR2>exec -
  2.            sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',-
  3.            ptions => dbms_logmnr.new);
  4. PL/SQL procedure successfully completed.
  6. Piner@9iR2>exec sys.dbms_logmnr.start_logmnr( -
  7.            ptions => sys.dbms_logmnr.dict_from_online_catalog);
  8. PL/SQL procedure successfully completed.

可以看到，線上分析其實就只需要這兩步，新增日誌並分析日誌。注意以上OPTIONS => DBMS_LOGMNR.NEW，表示新增第一個日誌，如需要另外新增更多的日誌，可以用如下方式即可。

  1. Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');

如果日誌分析完成，可以把需要的資訊儲存到臨時表，如

  1. Piner@9iR2>create table tmp_logmnr as
  2.      2  select operation,sql_redo,sql_undo from v$logmnr_contents
  3.      3  where seg_name='TEST';

然後，終止日誌分析過程。

  1. Piner@9iR2>exec sys.dbms_logmnr.end_logmnr

對於臨時表中的SQL_UNDO，可以選擇性的恢復，如採用如下的指令碼來恢復，這裡為了減少阻塞，每1000條提交一次。另外，需要注意的是，SQL_UNDO中的分號，如果想用動態SQL來執行的話，是需要去掉的。

  1. 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.   --去掉語句中的分號,這裡假定只有語句結尾有分號，語句中間並沒有分號。
  7.   --如果語句中也有分號，則可以考慮替換語句結尾的;'（分號單引號）為'（單引號）。
  8.     mysql := replace(c_tmp.sql_undo,';','');
  9.     execute immediate mysql;
  10.     num := num + 1;
  11.     if mod(num,1000)=0 then
  12.       commit;
  13.     end if;
  14.   end loop;
  15.   commit;
  16. exception
  17.   when others then
  18.    --異常處理
  19. end;

以上的PL/SQL程式碼其實還可以加強，如恢復成功一條，就設定狀態值為1，否則，設定狀態值為-1，方便跟蹤那些記錄恢復成功，哪些記錄恢復失敗了。

另外，需要注意的是，在Oracle 10g以下，LOGMNR的臨時表v$logmnr_contents，使用的是system表空間，在Oracle 10g以後改為sysaux表空間。可以使用如下的命令，更改logmnr的特定表空間，防止system表空間出現空間不夠。

http://www.ixdba.com/html/y2007/m12/175-logmnr-recover-data.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1384/viewspace-374955/，如需轉載，請註明出處，否則將追究法律責任。

相關文章