您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

實戰經驗談：對百萬筆既有資料欄位啟用 Oracle TDE 透明加密的完整避坑指南

編輯：Oracle 資料庫基礎

前言

Oracle Transparent Data Encryption (TDE) 自 10gR2 引入，加密資料檔、Redo、備份集中的敏感欄位。官方文件說明新增欄位或空表加密極簡單，但**對含百萬筆資料的既有欄位加密**卻隱藏諸多陷阱：索引限制、效能損耗、停機時間、資料殘留、重新加密、執行計畫變更。本文記錄真實專案完整應對流程。

一、前置限制檢查

檢查項目	限制	應對
索引類型	位圖索引禁止加密；B-tree 允許但不可使用 SALT	目標欄位有多個 B-tree 索引 → 指定 `ENCRYPT NO SALT`
外鍵關聯	參照/被參照欄位皆不可加密 (各表金鑰不同)	確認無 FK 關係
索引實用性	若索引未被使用，可刪除減少加密開銷	啟用 `ALTER INDEX ... MONITORING USAGE` 觀察一段時間，確認仍被使用 → 保留

二、效能基準測試 (沙箱環境)


-- 1. 建立測試表、索引、模擬資料 50 萬筆
CREATE TABLE transactions (
  trans_id NUMBER PRIMARY KEY USING INDEX TABLESPACE indx_tbs,
  credit_card NUMBER NOT NULL
) TABLESPACE data_tbs;

CREATE INDEX idx_cc ON transactions(credit_card) TABLESPACE indx_tbs;

-- 2. 明文載入計時
SET TIMING ON
BEGIN
  FOR i IN 1..500000 LOOP
    INSERT INTO transactions VALUES (i, '4'||TRUNC(DBMS_RANDOM.VALUE(0,99999999999999)));
    IF MOD(i,10000)=0 THEN COMMIT; END IF;
  END LOOP;
END;
/
-- 耗時 56 秒

-- 3. 加密欄位 (保留索引)
ALTER TABLE transactions MODIFY (credit_card ENCRYPT NO SALT);
-- 耗時 76 秒 (+36%)

-- 4. 重測加密後載入
TRUNCATE TABLE transactions;
-- 同樣迴圈載入
-- 耗時 62 秒 (+11%)

結論：加密 DDL 本身較慢 (掃描全表、重建索引)；DML 寫入額外開銷約 10%~15%，可接受。

三、縮短停機窗口：先 Drop 索引再加密


-- 策略：Drop 索引 → 加密 → 重建索引 (NOLOGGING + PARALLEL)
DROP INDEX idx_cc;
ALTER TABLE transactions MODIFY (credit_card ENCRYPT NO SALT);  -- 1 分 15 秒
CREATE INDEX idx_cc ON transactions(credit_card) TABLESPACE indx_tbs 
  PARALLEL 4 NOLOGGING;  -- 3 秒
-- 總計 ~78 秒，遠快於保留索引加密的 2 分 27 秒

生產環境驗證：百萬筆實際資料，索引保留加密需 70 分鐘；Drop/Rebuild 僅 20 分鐘，完美納入維護窗口。

四、清除明文資料殘留 (Ghost Copies)

加密 DDL 採「寫新區塊、標記舊區塊未用」機制，舊明文資料仍殘留資料檔，合規稽核 (PCI-DSS) 不接受。必須物理銷毀：


-- 1. 建立新表空間
CREATE TABLESPACE data_new DATAFILE ... SIZE 10G;
CREATE TABLESPACE indx_new DATAFILE ... SIZE 5G;

-- 2. 產生移動腳本 (含表、PK、索引)
SET HEADING OFF FEEDBACK OFF
SELECT 'ALTER '||segment_type||' '||owner||'.'||segment_name||
       DECODE(segment_type,'TABLE',' MOVE ',' REBUILD ')||
       'TABLESPACE '||DECODE(segment_type,'TABLE','data_new','indx_new')||';'
FROM dba_segments
WHERE owner='APP' AND tablespace_name IN ('DATA_TBS','INDX_TBS')
  AND segment_type IN ('TABLE','INDEX')
ORDER BY DECODE(segment_type,'TABLE',1,2);

-- 3. 執行移動
ALTER TABLE app.transactions MOVE TABLESPACE data_new;
ALTER INDEX app.transactions_pk REBUILD TABLESPACE indx_new;
ALTER INDEX app.idx_cc REBUILD TABLESPACE indx_new;

-- 4. 統計資訊、檢查無效索引、修改使用者預設表空間
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('APP');
SELECT owner, index_name FROM dba_indexes WHERE status='UNUSABLE';
ALTER USER app DEFAULT TABLESPACE data_new;

-- 5. 刪除舊表空間 (保留檔案) → OS 層面碎檔銷毀
DROP TABLESPACE data_tbs INCLUDING CONTENTS KEEP DATAFILES;
DROP TABLESPACE indx_tbs INCLUDING CONTENTS KEEP DATAFILES;

-- Linux/Unix: shred -n 200 -z -u /path/to/old_datafile.dbf
-- Windows: sdelete /p 3 /z old_datafile.dbf  (Sysinternals)

此步驟確保磁碟層面無明文殘留，通過稽核。

五、未來重新加密 (Re-key) 準備

流程同加密：Drop 索引 → ALTER TABLE ... MODIFY (col ENCRYPT USING 'AES256' ...) → 重建索引。
若擔心舊金鑰殘留，再執行一次「移動表空間 + 碎檔銷毀」。
PCI 稽核通常接受 Master Key Rotation，不強制資料層面 Re-encrypt (需停機天數不可接受)。

六、執行計畫變更風險：範圍掃描失效


-- 加密前：範圍查詢使用 INDEX RANGE SCAN
SELECT * FROM transactions WHERE credit_card BETWEEN 3499990000000000 AND 3499999999999999;
-- Plan: INDEX RANGE SCAN

-- 加密後 (NO SALT)：等值查詢仍用 INDEX RANGE SCAN
SELECT * FROM transactions WHERE credit_card = 3485458104610650;
-- Plan: INDEX RANGE SCAN (OK)

-- 但範圍查詢變全表掃描！
SELECT * FROM transactions WHERE credit_card BETWEEN ... ;
-- Plan: TABLE ACCESS FULL
-- Predicate: INTERNAL_FUNCTION("CREDIT_CARD") >= ...

原因：加密後索引值經加密函數轉換，失去原始排序特性，導致範圍謂詞無法走索引。

應對：

沙箱蒐集所有高頻 SQL (AWR Top SQL)，逐一 EXPLAIN PLAN 對比加密前後。
發現計畫退化 → 評估改寫 SQL (改等值查詢)、建立 Function-Based Index、或接受全表掃描 (資料量小)。
本專案發現一支關鍵報表 SQL 計畫改變，最終改寫為等值查詢 + UNION ALL 規避。

七、生產部署檢核清單

階段	動作	驗收標準
前置	備份 (RMAN Level 0)、匯出統計資訊、截圖索引監控結果	可回滾、可比較
停機	關閉應用、Drop 索引、加密欄位、重建索引 (PARALLEL NOLOGGING)	耗時 < 維護窗口
驗證	應用煙霧測試、核心 SQL 執行計畫對比、資料抽樣解密檢查	功能正常、計畫無退化
清理	移動表空間、碎檔銷毀、修改預設表空間、重新收集統計	稽核通過
事後	監控一週效能指標 (CPU、IO、Buffer Hit Ratio、關鍵 SQL Elapsed Time)	無異常飆升