總部 (北京) 需集中三地分公司 (上海、杭州、武漢) 的交易資料，用於全域報表與備份。分公司獨立運作，網路延遲較大，採**非同步、每日一次**刷新。
| 術語 | 角色 | 本案例對應 |
|---|---|---|
| Master Site (主站點) | 資料來源端 | 上海、杭州、武漢 |
| Materialized View Site (MV 站點) | 資料接收端 | 北京總部 |
| Materialized View (物化視圖) | 儲存快照資料的實體表 (可刷新) | 北京端每張來源表對應一個 MV |
| Refresh 類型 | FAST (增量) / COMPLETE (全量) / FORCE (嘗試 FAST 失敗轉 COMPLETE) | 每日 FAST，需 MV Log |
| MV Log (物化視圖日誌) | 記錄主表 DML 變更 (PK/ROWID)，支援 FAST REFRESH | 主站點每張複寫表建立一個 |
| Master Group / MV Group | 邏輯分組，統一管理/刷新 | 每分公司一組 (sh_rep, hz_rep, wh_rep) |
| Replication Administrator (RepAdmin) | 管理複寫環境的專用帳號 | 各站點建立 repadmin/mvadmin |
| Propagator | 推送延遲交易佇列的背景程序 | 主站點排程推送至 MV 站點 |
CONNECT system/password@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; -- 使用視圖管理器需此權限
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repadmin');
END;
/
CONNECT repadmin/repadmin@SH
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => SYSDATE,
interval => 'SYSDATE + 1', -- 每日
delay_seconds => 0);
END;
/
CONNECT system/password@SH
CREATE USER proxy_bj IDENTIFIED BY proxy_bj;
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(
user_name => 'proxy_bj',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/
GRANT SELECT_CATALOG_ROLE TO proxy_bj;
CONNECT repadmin/repadmin@SH
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP('sh_rep');
END;
/
-- 加入表 (假設 SHORACL.CREDIT_CARD 有 PK)
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => 'sh_rep',
type => 'TABLE',
oname => 'CREDIT_CARD',
sname => 'SHORACL',
use_existing_object => TRUE,
copy_rows => TRUE); -- 立即同步現有資料
END;
/
-- 加入索引 (不複製資料)
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => 'sh_rep',
type => 'INDEX',
oname => 'IDX_CREDIT_CARD',
sname => 'SHORACL',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
BEGIN
DBMS_REPCAT.SET_COLUMNS(
sname => 'SHORACL',
oname => 'CREDIT_CARD',
column_list => 'CREDIT_CARD_ID'); -- 唯一非空欄位組合
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname => 'SHORACL',
oname => 'CREDIT_CARD',
type => 'TABLE',
min_communication => TRUE); -- 最小通訊量
END;
/
-- 有主鍵
CREATE MATERIALIZED VIEW LOG ON SHORACL.CREDIT_CARD WITH PRIMARY KEY;
-- 無主鍵用 ROWID
CREATE MATERIALIZED VIEW LOG ON SHORACL.CREDIT_CARD WITH ROWID EXCLUDING NEW VALUES;
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY('sh_rep');
END;
/
CONNECT system/password@BJ
CREATE USER mvadmin IDENTIFIED BY mvadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA('mvadmin');
END;
/
GRANT COMMENT ANY TABLE TO mvadmin;
GRANT LOCK ANY TABLE TO mvadmin;
GRANT SELECT ANY DICTIONARY TO mvadmin;
CONNECT mvadmin/mvadmin@BJ
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR('mvadmin');
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => SYSDATE,
interval => 'SYSDATE + 1');
END;
/
CREATE PUBLIC DATABASE LINK ORACL_SH
CONNECT TO proxy_bj IDENTIFIED BY proxy_bj
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sh.db.server)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';
-- 同理建立 ORACL_HZ, ORACL_WH
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'ORACL_SH', -- 使用 Public DB Link 名
interval => 'SYSDATE + 1',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
CONNECT system/password@BJ
GRANT ALTER SESSION TO shoracl;
GRANT CREATE CLUSTER TO shoracl;
GRANT CREATE DATABASE LINK TO shoracl;
GRANT CREATE SEQUENCE TO shoracl;
GRANT CREATE SESSION TO shoracl;
GRANT CREATE SYNONYM TO shoracl;
GRANT CREATE TABLE TO shoracl;
GRANT CREATE VIEW TO shoracl;
GRANT CREATE PROCEDURE TO shoracl;
GRANT CREATE TRIGGER TO shoracl;
GRANT UNLIMITED TABLESPACE TO shoracl;
GRANT CREATE TYPE TO shoracl;
GRANT CREATE ANY SNAPSHOT TO shoracl; -- 關鍵權限
GRANT ALTER ANY SNAPSHOT TO shoracl;
CONNECT shoracl/shoracl@BJ
CREATE DATABASE LINK ORACL_SH
CONNECT TO shoracl IDENTIFIED BY shoracl
USING 'ORACL_SH'; -- 解析到 Public Link
CONNECT mvadmin/mvadmin@BJ
CREATE MATERIALIZED VIEW SHORACL.CREDIT_CARD
REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM SHORACL.CREDIT_CARD@ORACL_SH;
無主鍵表：REFRESH FAST WITH ROWID AS SELECT * FROM ...@ORACL_SH;
BEGIN
DBMS_REFRESH.MAKE(
name => 'mvadmin.sh_refresh',
list => '', -- 稍後 ADD
next_date => SYSDATE,
interval => 'SYSDATE + 1',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE, -- 刷新前先推送延遲交易
refresh_after_errors => FALSE);
END;
/
-- 將 MV 加入群組
BEGIN
DBMS_REFRESH.ADD(
name => 'mvadmin.sh_refresh',
list => 'SHORACL.CREDIT_CARD', ...); -- 列出該組所有 MV
END;
/
重複上述主站點 (HZ/WH) 與 MV 站點 (BJ 增加 HZORACL/WHORACL Schema、DB Link、MV、Refresh Group) 部署。
| 任務 | 檢查指令/視圖 | 異常處理 |
|---|---|---|
| 刷新狀態 | SELECT * FROM dba_mviews WHERE owner IN ('SHORACL','HZORACL','WHORACL'); 檢查 LAST_REFRESH, REFRESH_METHOD, STALENESS | STALENESS='UNUSABLE' → COMPLETE REFRESH; 錯誤 → 檢查 dba_mview_refresh_times |
| 延遲佇列堆積 | SELECT * FROM dba_def_error;, SELECT * FROM dba_def_tran; | 錯誤交易 → 分析原因、手動執行/刪除 |
| MV Log 增長 | SELECT m.log_table, COUNT(*) FROM user_mview_logs m JOIN user_tables t ON m.log_table=t.table_name GROUP BY m.log_table; | 過大 → 確認刷新正常、考慮 PURGE LOG |
| 刷新群組執行 | SELECT * FROM dba_refresh_children WHERE rname='SH_REFRESH'; | 未執行 → 檢查 JOB 隊列、DBMS_JOB/DBMS_SCHEDULER 狀態 |
DBMS_REPCAT.ALTER_MASTER_REPOBJECT 同步結構，或重建 MV。Oracle 物化視圖複寫 (Advanced Replication) 提供**異步、增量、可監控**的多站點資料集中能力。關鍵成功因素：
DBA_MVIEWS, DBA_DEF_ERROR, MV LOG 大小，異常即時處理。現代替代方案：GoldenGate (即時 CDC、異構、雲同步)、Data Guard Far Sync (零資料損失遠端備份)、DBMS_STREAMS (已棄用)、**自訂 ETL/ELT** (Data Pump + 外部表 + 排程)。視即時性、異構需求、授權成本選型。
