萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle LogMiner 實戰:誤刪/誤更新資料恢復完整流程

一、環境準備與測試資料建立

-- 建立表空間與用戶
CREATE TABLESPACE zwc DATAFILE 'C:\oracle\zwc01.dbf' SIZE 500M;
CREATE USER zwc IDENTIFIED BY zwc;
GRANT RESOURCE, CONNECT TO zwc;

-- 建立測試表並插入 200 萬筆資料
CONN zwc/zwc
CREATE TABLE zwc.tab01 (
  a INT PRIMARY KEY, 
  b VARCHAR2(100), 
  c VARCHAR2(100), 
  d DATE DEFAULT SYSDATE
) TABLESPACE zwc;

CREATE OR REPLACE PROCEDURE p_inst_tab01 AS
BEGIN
  FOR i IN 1..2000000 LOOP
    INSERT INTO tab01(a,b,c,d) VALUES (i,i,i,SYSDATE);
    IF MOD(i,2000)=0 THEN COMMIT; END IF;
  END LOOP;
END;
/
EXEC p_inst_tab01; -- 耗時約數分鐘

二、啟用補充日誌並模擬誤操作

CONN / AS SYSDBA
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- 必須開啟

-- 模擬誤刪除 100 筆、誤更新 10 筆
DELETE FROM zwc.tab01 WHERE rownum <= 100;
UPDATE zwc.tab01 SET d = SYSDATE-100 WHERE rownum <= 10;
COMMIT;

三、LogMiner 字典建立與日誌掛載

-- 設定 UTL_FILE_DIR 需重啟實例生效
ALTER SYSTEM SET utl_file_dir='c:\oracle' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE; STARTUP;

-- 建立 LogMiner 字典檔
EXEC DBMS_LOGMNR_D.BUILD(
  dictionary_filename => 'test.ora', 
  dictionary_location => 'c:\oracle'
);

-- 掛載歸檔日誌(需指定正確路徑)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
  logfilename => 'C:\arch\ARC00041_0842045960.001', 
  options => DBMS_LOGMNR.NEW
);

-- 啟動 LogMiner
EXEC DBMS_LOGMNR.START_LOGMNR(dictFilename => 'c:\oracle\test.ora');

-- 將挖掘結果存入表
CREATE TABLE zwc.t_logminer TABLESPACE zwc AS 
SELECT * FROM v$logmnr_contents;

EXEC DBMS_LOGMNR.END_LOGMNR;

四、查詢恢復 SQL

-- 查看挖掘筆數
SELECT COUNT(*) FROM zwc.t_logminer; -- 212
SELECT COUNT(*) FROM zwc.t_logminer 
WHERE seg_name='TAB01' AND seg_owner='ZWC'; -- 110

-- 取得誤刪除的回滾 SQL (SQL_UNDO)
SELECT sql_undo 
FROM t_logminer 
WHERE seg_name='TAB01' AND seg_owner='ZWC' AND operation='DELETE';

輸出範例:

INSERT INTO "ZWC"."TAB01"("A","B","C","D") 
VALUES ('201','201','201',TO_DATE('02-12月-13','DD-MON-RR'));
...

五、關鍵欄位說明

欄位用途
SQL_UNDO回滾 SQL,用於恢復誤刪除/誤更新前的資料
SQL_REDO重做 SQL,即原本執行的誤操作
OPERATION操作類型:INSERT/UPDATE/DELETE/DDL
SEG_OWNER, SEG_NAME段擁有者與段名稱
TIMESTAMP, SCN操作時間與系統變更號

六、最佳實踐建議

  1. 生產庫務必啟用 SUPPLEMENTAL LOG DATA (ALL) 以捕獲完整欄位值。
  2. 定期備份 LogMiner 字典檔,避免結構變更導致解析失敗。
  3. 大量資料恢復時,考慮分批提交 SQL_UNDO,避免單一交易過大。
  4. 結合 FLASHBACK TABLE、FLASHBACK QUERY 可處理更近期的誤操作(需啟用 UNDO 保留與閃回區)。
copyright © 萬盛學電腦網 all rights reserved