Oracle 物化視圖異步複製實戰：多站點資料匯總至總部

業務情境

A 公司總部（北京）需每日匯總上海、杭州、武漢三分公司資料。採用物化視圖異步複製，來源端為 Master Site，目標端為 Materialized View Site，每日一次快速重新整理。

核心概念速查

• Master Site：提供來源資料的節點
• Materialized View Site：接收複製資料的節點
• Materialized View Log：記錄 Master 表異動，支援快速重新整理
• Refresh 模式：FAST（僅異動）、COMPLETE（全量）、FORCE（快速失敗轉全量）

Master Site 設定 (上海範例)

-- 1. 建立複製管理員
CONNECT system/pwd@SH
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('repadmin'); END;/
GRANT COMMENT ANY TABLE, LOCK ANY TABLE 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. 建立 Proxy 使用者供遠端連線
CONNECT system/pwd@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. 建立 Master Group 並加入物件
CONNECT repadmin/repadmin@SH
BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP('sh_rep'); END;/
BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT('sh_rep','TABLE','CREDIT_CARD','SHORACL',TRUE,TRUE); END;/
BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT('sh_rep','INDEX','IDX_CREDIT_CARD','SHORACL',TRUE,FALSE); END;/

-- 6. 無 PK 表指定辨識欄
BEGIN DBMS_REPCAT.SET_COLUMNS('SHORACL','CREDIT_CARD','CREDIT_CARD_ID'); END;/

-- 7. 產生複製支援
BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SHORACL','CREDIT_CARD','TABLE',TRUE); END;/

-- 8. 建立 MV Log
CREATE MATERIALIZED VIEW LOG ON SHORACL.CREDIT_CARD;

-- 9. 啟動複製
BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY('sh_rep'); END;/

Materialized View Site 設定 (北京)

-- 1. 建立 MV 管理員
CONNECT system/pwd@BJ
CREATE USER mvadmin IDENTIFIED BY mvadmin;
BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('mvadmin'); END;/
GRANT COMMENT ANY TABLE, LOCK ANY TABLE, SELECT ANY DICTIONARY TO mvadmin;

-- 2. 註冊傳播者 & 排程清除
BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR('mvadmin'); END;/
CONNECT mvadmin/mvadmin@BJ
BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE(SYSDATE, 'SYSDATE+1', 0, ''); END;/

-- 3. 公共 DB Link 指向 Master
CREATE PUBLIC DATABASE LINK oraclsh USING 'oracle.shanghai.com';

-- 4. MV 管理員私有 DB Link
CREATE DATABASE LINK oraclsh CONNECT TO proxy_bjoracle IDENTIFIED BY proxy_bjoracle;

-- 5. 排程推送異動
BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH('oraclsh', 'SYSDATE+1', SYSDATE, FALSE, 0, 0); END;/

-- 6. 授權目標 Schema 建立 MV
GRANT CREATE SESSION, CREATE TABLE, CREATE SNAPSHOT, ... TO shoracl;

-- 7. 目標 Schema 建立 DB Link
CONNECT shoracl/shoracl@BJ
CREATE DATABASE LINK oraclsh CONNECT TO shoracl IDENTIFIED BY shoracl;

-- 8. 建立物化視圖 (快速重新整理)
CONNECT mvadmin/mvadmin@BJ
CREATE MATERIALIZED VIEW shoracl.credit_card
REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM shoracl.credit_card@oraclsh;

-- 9. 建立重新整理群組 (每日一次)
BEGIN DBMS_REFRESH.MAKE('mvadmin.sh_refresh', '', SYSDATE, 'SYSDATE+1', FALSE, '', TRUE, FALSE); END;/

杭州、武漢同理擴展

重複上述步驟，分別建立 hz_rep、wh_rep Master Group 及對應 MV，北京端建立多個 Refresh Group 排程。