Oracle 11g 閃回資料歸檔 (FDA) 深度解析：從原理到實戰

前言：為什麼需要 FDA？

傳統方案需自建歷史表 + 觸發器，面臨 DDL 同步、觸發器失效、誤刪風險等痛點。Oracle 11g 引入 Flashback Data Archive (FDA)，以內核級非同步擷取機制，提供零侵入、自動壓縮、防篡改的歷史資料保留方案。

一、FDA 核心架構與運作原理

1. 背景進程 FBDA

• 專屬背景進程 FBDA 非同步擷取異動
• 優先從 Undo 快取讀取；快取無資料則讀 Undo 表空間
• 將異動列整理後寫入 FDA 歷史表 (內部分區 + 壓縮存儲)

2. 儲存結構

每張啟用 FDA 的表自動對應三張系統表 (命名規則：<owner>.SYS_FBA_<目的>_<object_id>)：

3. 自動保留與清理策略

• 建立多個 FDA，各自指定 RETENTION (如 90 天、1 年、7 年)
• 多張表可共用同一 FDA (同保留策略)
• 到期資料由 FBDA 自動清理，不影響線上效能

二、FDA 部署六步驟

1. 建立表空間

CREATE TABLESPACE fbda_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/fbda01.dbf' SIZE 24M;

2. 建立 FDA 管理員並授權

CREATE USER fda_admin IDENTIFIED BY password;
GRANT FLASHBACK ARCHIVE ADMINISTER TO fda_admin;

3. 建立多層次 FDA (不同保留期)

-- 短期 90 天
CREATE FLASHBACK ARCHIVE fda_90d TABLESPACE fbda_ts QUOTA 1G RETENTION 90 DAY;
-- 長期 1 年
CREATE FLASHBACK ARCHIVE fda_1y TABLESPACE fbda_ts QUOTA 5G RETENTION 1 YEAR;
-- 法規 7 年
CREATE FLASHBACK ARCHIVE fda_7y TABLESPACE fbda_ts QUOTA 20G RETENTION 7 YEAR;

4. 授予業務用戶 FDA 使用權限

GRANT FLASHBACK ARCHIVE ON fda_90d TO app_user;
GRANT FLASHBACK ARCHIVE ON fda_1y TO app_user;
GRANT FLASHBACK ARCHIVE ON fda_7y TO app_user;

5. 授予閃回查詢與套件執行權限

GRANT FLASHBACK ANY TABLE TO app_user;
GRANT EXECUTE ON DBMS_FLASHBACK TO app_user;

6. 於目標表啟用 FDA

-- 使用預設 FDA
ALTER TABLE hr.employees FLASHBACK ARCHIVE;

-- 指定特定 FDA
ALTER TABLE hr.departments FLASHBACK ARCHIVE fda_1y;
ALTER TABLE oe.orders FLASHBACK ARCHIVE fda_7y;

三、核心應用場景

1. 稽核歷史異動

SELECT employee_id, salary, VERSIONS_STARTSCN, VERSIONS_OPERATION
FROM hr.employees
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '30' DAY AND SYSTIMESTAMP
WHERE employee_id = 100;

2. 修復誤刪/誤改資料

-- 刪除錯誤
DELETE FROM hr.applicants WHERE application_date <= TO_DATE('11-10-2008','dd-mm-yyyy');
COMMIT;

-- 利用 FDA 還原
INSERT INTO hr.applicants
SELECT * FROM hr.applicants
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 10:00','yyyy-mm-dd hh24:mi') AND MAXVALUE
WHERE VERSIONS_OPERATION = 'D';
COMMIT;

3. 歷史資料即時查詢 (AS OF SCN/TIMESTAMP)

SELECT * FROM hr.employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

四、維護操作指令

五、空間不足錯誤與應對

六、關鍵限制 (DDL 禁止操作)

啟用 FDA 的表 不可執行：

• DROP/RENAME/MODIFY COLUMN
• PARTITION/SUBPARTITION 操作
• LONG → LOB 轉換
• DROP/TRUNCATE/RENAME TABLE
• UPGRADE TABLE

需先 ALTER TABLE ... NO FLASHBACK ARCHIVE 停用 FDA 再執行上述 DDL。

七、最佳實踐建議

1. 查詢歷史資料前先 COMMIT/ROLLBACK 確保一致性
2. 優先使用 SCN 定位 (TIMESTAMP_TO_SCN 精度約 3 秒)
3. 善用 INTERVAL 運算：SYSTIMESTAMP - INTERVAL '20' DAY
4. 注意 NLS 參數 (語言、字元集) 在歷史資料保留期間可能異動

八、結語

FDA 以內核級非同步擷取、自動壓縮分區、彈性保留策略，徹底解決歷史資料審計與誤操作復原難題。部署簡單、維護自動化，是 Oracle DBA 與開發人員的必備利器。