Oracle 進階復制部署實戰：從主站點配置到物化視圖站點建立的逐步圖解指南

業務場景

總部 (北京) 需整合上海、杭州、武漢三分公司資料。三地軟體與資料庫結構相同，版本均為 9i。採用物化視圖復制：分公司為主站點，總部為物化視圖站點，實現單向資料同步。

核心概念速查

• 主站點：提供資料源 (如上海站)。
• 物化視圖站點：目標站點 (總部)。
• 物化視圖：目標端對應各復制表/視圖的實體表，僅透過復制機制增刪改。
• 快速/完整/強制刷新：快速僅同步變更；完整全量複製；強制=快速失敗轉完整。
• 主站點群組/物化視圖群組：被復制物件集合。
• 物化視圖日誌：主站點記錄變更供快速刷新用。

前置準備

• 網路暢通；收集各站點 DB 版本、大小、字元集、Schema 名稱。
• 總部建立集中資料庫，版本 ≥ 所有主站點，建議同字元集。
• 為各主站點建立對應表空間 (大於資料量)，建立對應 Schema (本例：SHORACL、HZORACL、WHORACL)。
• 配置本地服務名：SH(上海)、HZ(杭州)、WH(武漢)、BJ(北京)。

一、主站點配置 (以上海為例)

1. 建立復制管理員與授權：

   CONNECT system/passwd@SH
   CREATE USER repadmin IDENTIFIED BY repadmin;
   BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('repadmin'); END;/
   GRANT COMMENT ANY TABLE TO repadmin;
   GRANT LOCK ANY TABLE TO repadmin;
   GRANT SELECT ANY DICTIONARY TO repadmin;  -- 若使用視圖管理器

2. 註冊傳播者：

   BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repadmin'); END;/
   

3. 排程清除作業 (立即執行、每日一次)：

   CONNECT repadmin/repadmin@SH
   BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE(SYSDATE, 'SYSDATE+1', 0); END;/
   

4. 建立物化視圖站點代理用戶：

   CONNECT system/passwd@SH
   CREATE USER proxy_bjOracle IDENTIFIED BY proxy_bjOracle;
   BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP('proxy_bjOracle','PROXY_SNAPADMIN',NULL); END;/
   GRANT SELECT_CATALOG_ROLE TO proxy_bjOracle;

5. 建立主站點群組：

   CONNECT repadmin/repadmin@SH
   BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP('sh_rep'); END;/
   

6. 加入復制物件 (表、索引)：

   BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT('sh_rep','TABLE','CREDIT_CARD','SHORACL',TRUE,TRUE); END;/
   BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT('sh_rep','INDEX','INDEX_CREDIT_CARD','SHORACL',TRUE,FALSE); END;/
   

7. 無主鍵表設定主鍵替代欄位：

   BEGIN DBMS_REPCAT.SET_COLUMNS('SHORACL','CREDIT_CARD','CREDIT_CARD_ID'); END;/
   

8. 產生復制支援 (觸發器、套件、程序)：

   BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SHORACL','CREDIT_CARD','TABLE',TRUE); END;/
   

9. 建立物化視圖日誌 (快速刷新必備)：

   CREATE MATERIALIZED VIEW LOG ON SHORACL.CREDIT_CARD;
   -- 無主鍵表用：
   CREATE MATERIALIZED VIEW LOG ON SHORACL.CREDIT_CARD WITH ROWID EXCLUDING NEW VALUES;

10. 啟動復制：

    BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY('sh_rep'); END;/
    

二、物化視圖站點配置 (總部 BJ)

1. 建立復制管理員與授權 (同主站點，用戶 mvadmin)。
2. 註冊傳播者 (同主站點)。
3. 建立公共資料庫連結 (三個主站點各一)：

   CREATE PUBLIC DATABASE LINK ORACLSH USING 'Oracle.shanghai.com';
   -- 或使用連線描述字串

4. 排程清除作業 (同主站點)。
5. 建立 mvadmin 的私有資料庫連結：

   CREATE DATABASE LINK ORACLSH CONNECT TO proxy_bjOracle IDENTIFIED BY proxy_bjOracle;

6. 排程推送作業 (每小時推送延遲交易)：

   BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH('ORACLSH','SYSDATE+1',SYSDATE,FALSE,0,0); END;/
   

7. 授權 Schema 用戶 (SHORACL) 建立物化視圖權限 (ALTER SESSION, CREATE TABLE, CREATE VIEW, CREATE SNAPSHOT 等)。
8. 建立 Schema 的資料庫連結 (名稱與公共連結一致)：

   CONNECT SHORACL/SHORACL@BJ
   CREATE DATABASE LINK ORACLSH CONNECT TO ORACL IDENTIFIED BY ORACL;

9. 建立物化視圖 (快速刷新、主鍵/ROWID)：

   CONNECT mvadmin/mvadmin@BJ
   CREATE MATERIALIZED VIEW SHORACL.CREDIT_CARD REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM ORACL.CREDIT_CARD@ORACLSH;
   -- 無主鍵用 ROWID

10. 建立刷新群組並加入物化視圖：

    BEGIN DBMS_REFRESH.MAKE('mvadmin.sh_refresh','',SYSDATE,'SYSDATE+1',FALSE,'',TRUE,FALSE); END;/
    BEGIN DBMS_REFRESH.ADD('mvadmin.sh_refresh','SHORACL.CREDIT_CARD',TRUE); END;/
    

三、驗證與監控

1. 查詢 SYS.DBA_JOBS 確認三類作業：清除、推送、刷新 (杭州、武漢各建刷新群組則共 7 作業)。
2. 確保 JOB_QUEUE_PROCESSES ≠ 0 (預設為 0)，否則自動刷新不執行。
3. 觀察 FAILURES 欄位：失敗會按 1,2,4,8... 分鐘重試，達 16 次或超過間隔則標記 BROKEN。手工排查後用 DBMS_JOB.RUN(job_no) 重跑。

總結

本文示範最基礎的單向物化視圖復制。進階功能尚可更新物化視圖、複雜只讀物化視圖。實際應用應依系統功能與效能需求選擇適當復制技術。