Oracle 物化視圖實現異構數據庫雙向/單向數據同步完整實施

架構拓撲

• 源庫 (Master)： 192.168.1.81，業務寫入端
• 目標庫 (Replica)： 192.168.1.96，報表/查詢端
• 同步模式：單向 (源→目) 物化視圖 + FAST REFRESH ON COMMIT/DEMAND

源庫配置 (192.168.1.81)

1. 創建只讀查詢用戶

CONN / AS SYSDBA
CREATE USER snap_query IDENTIFIED BY snap_query
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT TO snap_query;
GRANT SELECT ON userb.test1 TO snap_query;

2. 創建物化視圖日志 (MLog) — 必須有主鍵

CREATE MATERIALIZED VIEW LOG ON userb.test1
  TABLESPACE ts12
  WITH PRIMARY KEY
  EXCLUDING NEW VALUES;

關鍵點： WITH PRIMARY KEY 支持快速刷新；EXCLUDING NEW VALUES 減少日志體積 (不記錄新值，僅記錄主鍵)。

目標庫配置 (192.168.1.96)

1. 啟用 JOB 隊列進程

SHOW PARAMETER job_queue_processes;
ALTER SYSTEM SET job_queue_processes=10 SCOPE=SPFILE;  -- 需重啟或動態生效

2. 創建表空間與用戶

CREATE TABLESPACE ts_event
  DATAFILE 'I:\ts_event_01.dbf' SIZE 5000M,
           'I:\ts_event_02.dbf' SIZE 5000M;

CREATE USER ts12_st IDENTIFIED BY ts12_st_2010
  DEFAULT TABLESPACE ts_event TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE SNAPSHOT TO ts12_st;
GRANT CREATE DATABASE LINK TO ts12_st;

3. 配置 TNS 別名與 DB Link

-- tnsnames.ora 新增
TS12TJ = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.81)(PORT=1521))
           (CONNECT_DATA=(SERVICE_NAME=source_svc)))

-- 創建 DB Link
CONN ts12_st/ts12_st_2010
CREATE DATABASE LINK DBLINK_SNAP_QUERY
  CONNECT TO snap_query IDENTIFIED BY snap_query
  USING 'TS12TJ';

4. 創建物化視圖 (採用 FORCE REFRESH)

CREATE MATERIALIZED VIEW test1_st2
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE
  NEXT SYSDATE + 12/24  -- 每 12 小時自動刷新
AS
SELECT Event_ID, EVENT_CODE, CALL_TIME, TELE_X, TELE_Y, EVENT_TYPE,
       EVENT_CONTENT, ROAD_CODE, PLACE, DIRECTION, PLACE_X, PLACE_Y,
       UPDATE_TIME, MAIN_ASSISTANT_ROAD
FROM userb.test1@DBLINK_SNAP_QUERY;

REFRESH FORCE：優先嘗試 FAST REFRESH (需 MLog)，失敗則降級 COMPLETE REFRESH。

源庫後續授權 (關鍵補充)

GRANT SELECT ON mlog$_test1 TO snap_query;

MLog 表 (mlog$_test1) 也需授予查詢權限，否則快速刷新會報 ORA-12004。

運行維護操作

手工刷新

EXEC DBMS_MVIEW.REFRESH('TEST1_ST2');

監控刷新狀態

SELECT mview_name, last_refresh_type, last_refresh_date, staleness
FROM user_mviews;

日志表空間回收 (長期運行後)

ALTER TABLE mlog$_test1 MOVE TABLESPACE ts12;  -- 移動回收高水位
-- 或
TRUNCATE TABLE mlog$_test1;  -- 僅在確認無進行中刷新時執行

性能參考

300 萬行數據，FORCE REFRESH 耗時約 1 小時 (含網絡傳輸)。優化建議：
- 確保源表主鍵索引、MLog 表主鍵索引完好
- 目標庫 DB_FILE_MULTIBLOCK_READ_COUNT 調大
- 並行刷新：DBMS_MVIEW.REFRESH('TEST1_ST2', METHOD=>'F', PARALLELISM=>4) (11g+)

遷移至 GoldenGate/CDC 的判斷標準

• 需秒級延遲 → GoldenGate / XStreams
• 需雙向寫入且衝突檢測 → GoldenGate CDR
• 可接受分鐘/小時級、單向、表結構固定 → 物化視圖最低成本