萬盛學電腦網

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

Oracle 存儲過程誤刪恢復三大法:閃回查詢、基表歷史、ODU 災難恢復

一、場景重現:建立、驗證、誤刪

CREATE OR REPLACE PROCEDURE proc_test_drop AS
BEGIN
  FOR x IN (SELECT sysdate FROM dual) LOOP
    DBMS_OUTPUT.put_line(x.sysdate);
  END LOOP;
END;
/
EXEC proc_test_drop;  -- 正常輸出
DROP PROCEDURE proc_test_drop;  -- 誤刪
SELECT text FROM dba_source WHERE owner='ROGER' AND name='PROC_TEST_DROP'; -- no rows

二、方法一:閃回查詢(AS OF TIMESTAMP)— 最簡快

-- 需具備 FLASHBACK ANY TABLE 或 SYS 權限
SELECT text FROM dba_source 
AS OF TIMESTAMP SYSDATE - 5/1440  -- 5 分鐘前
WHERE owner='ROGER' AND name='PROC_TEST_DROP';

輸出完整源碼,直接 CREATE OR REPLACE PROCEDURE ... 重建。

前提:UNDO 保留時間(UNDO_RETENTION)足夠、UNDO 表空間未被覆寫、表未啟用 NOROWDEPENDENCIES。

三、方法二:基表歷史查詢(SYS.SOURCE$ + OBJ$)— 精準定位

-- 1. 確認刪除前 OBJ#
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT obj# FROM obj$ 
AS OF TIMESTAMP TO_TIMESTAMP('2013-08-06 02:40:00','YYYY-MM-DD HH24:MI:SS')
WHERE name='PROC_TEST_DROP';  -- 假設得 52148

-- 2. 讀取 SOURCE$ 歷史源碼
SET LONG 9999999
SELECT source FROM source$ 
AS OF TIMESTAMP TO_TIMESTAMP('2013-08-06 02:40:00','YYYY-MM-DD HH24:MI:SS')
WHERE obj# = 52148 ORDER BY line;

優點:不依賴 DBA_SOURCE 視圖定義,直接讀取基表歷史版本,精準度最高。

四、方法三:ODU (Oracle Data Unloader) 物理層恢復— 終極手段

  1. 解析資料字典:ODU> UNLOAD DICT
  2. 掃描區塊:ODU> SCAN EXTENT PARALLEL 2
  3. 卸載 SOURCE$ 表:ODU> UNLOAD TABLE SYS.SOURCE$ → 產生 SYS_SOURCE$.txt、.sql、.ctl
  4. 建立臨時表並載入:
    SQL> @create.sql -- 建立 SYS_SOURCE$ 表
    SQL> LOAD DATA INFILE 'SYS_SOURCE$.txt' ... (使用 .ctl 控制檔)
  5. 從臨時表查詢目標程式源碼並重建。
適用場景:資料庫無法開啟、UNDO 已覆寫、系統表空間損壞、ASM 元資料丟失等極端情況。

五、三種方法適用性對比

方法適用條件操作難度恢復完整度風險
閃回查詢UNDO 保留充足、資料庫開啟低完整極低
基表歷史UNDO 保留充足、需 SYS 權限中完整低
ODU資料庫掛掉、UNDO 丟失、極端災難高視區塊完整度中(需專業知識)

六、預防建議

  • 啟用 DDL TRIGGER 記錄所有 DDL 操作至審計表。
  • 重要存儲過程納入版本控制,定期 DBMS_METADATA.GET_DDL 匯出備份。
  • 設定 UNDO_RETENTION ≥ 24 小時,UNDO 表空間啟用 RETENTION GUARANTEE。

誤刪存儲過程,首選閃回查詢(秒級恢復),次選基表歷史(分鐘級),末選 ODU(小時級)。建立「DDL 審計 + 版本控制 + UNDO 保障」三層防線,才能從容應對人為誤操作。

copyright © 萬盛學電腦網 all rights reserved