Oracle Flashback Query 實戰：誤刪資料秒級恢復完整操作手冊

適用場景與前提

• Oracle 9i Release 2 及以上版本
• 誤刪除/修改資料且已 COMMIT
• UNDO 保留時間 (UNDO_RETENTION) 覆蓋誤操作時間點
• 資料庫以 AUTOMATIC UNDO MANAGEMENT 運行

四步恢復標準流程

步驟 1：確認 SCN 與時間對應關係

COL fscn FOR 9999999999999999999
COL nscn FOR 9999999999999999999
SELECT name, FIRST_CHANGE# fscn, NEXT_CHANGE# nscn, FIRST_TIME 
FROM v$archived_log ORDER BY FIRST_TIME;

對照歸檔日誌的 FIRST_CHANGE# (起始 SCN) 與 FIRST_TIME，鎖定誤操作前的 SCN 區間。

步驟 2：獲取當前系統 SCN 基準

SELECT dbms_flashback.get_system_change_number FROM dual;

步驟 3：嘗試多個 SCN 定位最佳恢復點

-- 建立恢復表結構
CREATE TABLE hs_passport_recov AS SELECT * FROM hs_passport WHERE 1=0;

-- 測試不同 SCN 的資料量
SELECT COUNT(*) FROM hs_passport AS OF SCN 12929941968;  -- 861684 行
SELECT COUNT(*) FROM hs_passport AS OF SCN 12929928784;  -- 825110 行

關鍵技巧：若知悉確切誤操作時間，可用 AS OF TIMESTAMP 語法更直觀：

SELECT * FROM hs_passport 
AS OF TIMESTAMP TO_TIMESTAMP('2024-06-22 14:38:00', 'YYYY-MM-DD HH24:MI:SS');

步驟 4：執行閃回插入恢復

INSERT INTO hs_passport_recov
SELECT * FROM hs_passport AS OF SCN 12929941968;
COMMIT;

進階：Flashback Table 整表恢復 (10g+)

-- 啟用列追蹤 (需一次)
ALTER TABLE hs_passport ENABLE ROW MOVEMENT;

-- 一鍵恢復至指定 SCN/時間
FLASHBACK TABLE hs_passport TO SCN 12929941968;
-- 或
FLASHBACK TABLE hs_passport TO TIMESTAMP SYSDATE - 1/24;  -- 1 小時前

常見失敗原因與對策

最佳實踐清單

1. 設定 UNDO_RETENTION ≥ 業務容忍的最大恢復窗口（建議 ≥ 24 小時）
2. 為關鍵表啟用 ROW MOVEMENT 支援 Flashback Table
3. 定期演練閃回恢復流程，驗證 UNDO 容量充足性
4. 結合 FLASHBACK TRANSACTION QUERY 審計誤操作 SQL