Oracle 資料遺失恢復四大主流方案：邏輯備份、物理備份不完全恢復、LogMiner 挖掘重做日誌、Flashback 閃回技術。本文以「資產表誤更新數千筆記錄」為場景，逐一實演操作流程、優缺點與適用條件。
場景設定
- 前日 23:00：
EXP 全庫邏輯備份 + 熱備份資料檔
- 當日 10:00：誤執行
UPDATE TFUNDASSET SET ztm=錯誤值 WHERE ... 並 COMMIT
- 表特性：資產表，資料變動較少
方案一：邏輯備份 IMP 恢復
方法 A：原表覆蓋法
-- 1. 備份現有資料
CREATE TABLE tfundasset_bak AS SELECT * FROM tfundasset;
-- 2. 清空原表
TRUNCATE TABLE tfundasset;
-- 3. 匯入單表
imp system/manager tables=tfundasset ignore=y file=backup.dmp log=imp.log
方法 B：臨時用戶法
-- 1. 匯入到臨時用戶
imp system/manager fromuser=original touser=temp_user tables=tfundasset file=backup.dmp
-- 2. 清空原表並回寫
TRUNCATE TABLE tfundasset;
INSERT INTO tfundasset SELECT * FROM temp_user.tfundasset;
優缺點
| 優點 | 缺點 |
|---|
| 操作簡單、工具成熟 | 僅能恢復至備份時間點，中間異動需人工補齊 |
| 適用靜態/低頻變動表 | 高頻交易表資料遺失量大，補齊成本高 |
方案二：物理備份 + 不完全恢復 (PITR)
前提：資料庫運行於歸檔模式
SQL> ARCHIVE LOG LIST;
Database log mode: Archive Mode -- 必須為歸檔模式
操作步驟
SHUTDOWN IMMEDIATE 關閉資料庫- 還原前日備份的所有資料檔（保留控制檔）
STARTUP MOUNT- 基於時間點恢復：
RECOVER DATABASE UNTIL TIME '2015-10-20 09:58:00';
ALTER DATABASE OPEN RESETLOGS
優缺點
| 優點 | 缺點 |
|---|
| 可恢復至秒級時間點 | 需停機、影響可用性 |
| 所有物件一致性恢復 | 恢復點後所有異動遺失 |
方案三：LogMiner 挖掘 Redo Log
前置設定
-- 1. 設定 UTL_FILE_DIR 並重啟
UTL_FILE_DIR = '/oracle/logmnr'
-- 2. 重建 DBMS_LOGMNR_D 套件
@?/rdbms/admin/dbmslmd.sql
核心流程
- 建立外部資料字典：
EXEC DBMS_LOGMNR_D.BUILD('dict.ora', '/oracle/logmnr');
- 加入日誌檔：
EXEC DBMS_LOGMNR.ADD_LOGFILE('/path/redo01.log', DBMS_LOGMNR.NEW);
- 啟動分析：
EXEC DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/oracle/logmnr/dict.ora');
- 查詢
V$LOGMNR_CONTENTS 取得反向 SQL
- 結束：
EXEC DBMS_LOGMNR.END_LOGMNR;
關鍵查詢
SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'TFUNDASSET' AND OPERATION = 'UPDATE'
ORDER BY TIMESTAMP;
優缺點
| 優點 | 缺點 |
|---|
| 線上操作、無需停機 | 需歸檔模式，否則日誌可能被覆寫 |
| 可精準提取單表/單交易反向 SQL | 大量日誌分析耗時、記憶體壓力大 |
方案四：Flashback 閃回技術（最推薦）
前提條件
- 自動 UNDO 管理：
UNDO_MANAGEMENT = AUTO
- 保留時間：
UNDO_RETENTION（秒），涵蓋誤操作時間點
SQL 閃回查詢
-- 方式 1：AS OF TIMESTAMP
SELECT * FROM tfundasset AS OF TIMESTAMP SYSDATE - 1/24;
-- 方式 2：AS OF SCN
SELECT * FROM tfundasset AS OF SCN 123456789;
-- 更新恢復（需單列結果或游標）
UPDATE tfundasset SET ztm = (
SELECT ztm FROM tfundasset AS OF TIMESTAMP SYSDATE - 1/24
WHERE pk_col = tfundasset.pk_col
);
DBMS_FLASHBACK 套件
DECLARE
CURSOR c_data IS SELECT * FROM tfundasset AS OF TIMESTAMP SYSDATE - 1/24;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 1/24);
FOR r IN c_data LOOP
-- 寫入臨時表或直接更新
END LOOP;
DBMS_FLASHBACK.DISABLE;
END;
優缺點
| 優點 | 缺點 |
|---|
| 線上、秒級、語法最簡單 | 受 UNDO_RETENTION 限制（通常小時級） |
| 不影響其他物件、無需停機 | 需足夠 UNDO 表空間、高併發可能衝突 |
方案選擇決策樹
- 誤操作在
UNDO_RETENTION 內 → Flashback 首選
- 超過 UNDO 保留、有歸檔日誌、可停機 → PITR
- 超過 UNDO、有歸檔、不可停機 → LogMiner
- 靜態表、有邏輯備份、可接受人工補齊 → IMP
根本預防措施
- 環境隔離：不同環境不同用戶/密碼
- 權限最小化：Owner 與應用用戶分離，僅授必要權限
- 執行前驗證：DML 前先用相同條件
SELECT 確認影響範圍
備份策略設計應結合 RPO/RTO 需求，多層防護才能從容應對人為錯誤。
