Oracle 資料庫複寫實作指南：多站點異步物化視圖複寫

業務場景

總部 (北京) 需整合三地分公司 (上海、杭州、武漢) 同結構資料庫資料，作為備份與綜合統計來源。採用 Oracle 9i Advanced Replication，**異步物化視圖複寫**，每日刷新一次。

架構角色定義

• Master Sites (多主站點)：上海、杭州、武漢 — 提供資料來源，擁有完整管理權限。
• Materialized View Site (物化視圖站點)：北京 — 接收複寫資料，建立唯讀物化視圖。
• Master Group：主站點被複寫物件集合 (如 `SH_REP`)。
• Materialized View Group：目標站點物化視圖集合 (如 `MV_ADMIN.SH_REFRESH`)。
• Materialized View Log：主站點記錄 DML 變更，支援快速刷新。

前置作業

1. 網路互通：配置 `tnsnames.ora`，服務名 `SH`、`HZ`、`WH`、`BJ`。
2. 版本一致：所有節點 Oracle 9i，字元集相同 (如 `ZHS16GBK`)。
3. 目標端預建表空間：`TBS_SH`、`TBS_HZ`、`TBS_WH`，大小 > 來源資料量 + 成長空間。
4. 目標端建立對應 Schema：`SH_ORACL`、`HZ_ORACL`、`WH_ORACL` (來源均用 `ORACL` Schema)。

步驟一：配置主站點 (以上海為例)

-- 1. 建立複寫管理員
CONNECT system/passwd@SH
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
  DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');
END;
/
GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;  -- 供 OEM/Grid Control 使用

-- 2. 註冊傳播者
BEGIN
  DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'repadmin');
END;
/

-- 3. 排程清除作業 (每日一次)
CONNECT repadmin/repadmin@SH
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE(
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1',
    delay_seconds => 0
  );
END;
/

-- 4. 建立代理使用者 (供目標端連線)
CONNECT system/passwd@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;

-- 5. 建立主群組
CONNECT repadmin/repadmin@SH
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP(gname => 'SH_REP');
END;
/

-- 6. 加入複寫物件 (表、索引)
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => 'SH_REP', type => 'TABLE',
    oname => 'CREDIT_CARD', sname => 'SH_ORACL',
    use_existing_object => TRUE, copy_rows => TRUE
  );
END;
/
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => 'SH_REP', type => 'INDEX',
    oname => 'IDX_CREDIT_CARD', sname => 'SH_ORACL',
    use_existing_object => TRUE, copy_rows => FALSE
  );
END;
/

-- 7. 若無 PK，指定識別欄位
BEGIN
  DBMS_REPCAT.SET_COLUMNS(
    sname => 'SH_ORACL', oname => 'CREDIT_CARD',
    column_list => 'CREDIT_CARD_ID'
  );
END;
/

-- 8. 產生複寫支援 (建立 Trigger/Package)
BEGIN
  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname => 'SH_ORACL', oname => 'CREDIT_CARD',
    type => 'TABLE', min_communication => TRUE
  );
END;
/

-- 9. 建立物化視圖日誌 (快速刷新必備)
CREATE MATERIALIZED VIEW LOG ON SH_ORACL.CREDIT_CARD
WITH PRIMARY KEY;  -- 無 PK 時用 WITH ROWID

-- 10. 啟動複寫
BEGIN
  DBMS_REPCAT.RESUME_MASTER_ACTIVITY(name => 'SH_REP');
END;
/

步驟二：配置物化視圖站點 (北京)

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

-- 2. 註冊傳播者
BEGIN
  DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'mvadmin');
END;
/

-- 3. 公共資料庫連結 (指向主站點全域名稱)
CREATE PUBLIC DATABASE LINK ORACL_SH
  CONNECT TO proxy_bj IDENTIFIED BY proxy_bj
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sh-host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))';

-- 4. 清除作業排程
CONNECT mvadmin/mvadmin@BJ
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE(
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1',
    delay_seconds => 0
  );
END;
/

-- 5. MV 管理員私有資料庫連結
CREATE DATABASE LINK ORACL_SH
  CONNECT TO proxy_bj IDENTIFIED BY proxy_bj
  USING 'ORACL_SH';  -- 使用公共連結名稱

-- 6. 推送作業排程 (每小時推送延遲佇列)
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH(
    destination => 'ORACL_SH',
    interval    => 'SYSDATE + 1/24',
    next_date   => SYSDATE,
    stop_on_error => FALSE,
    parallelism => 1
  );
END;
/

-- 7. 授權目標 Schema 建立物化視圖權限
CONNECT system/passwd@BJ
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SNAPSHOT,
      CREATE PROCEDURE, CREATE TRIGGER, UNLIMITED TABLESPACE TO sh_oracl;
-- ... 其他必要權限

-- 8. 目標 Schema 建立資料庫連結 (連回主站點原 Schema)
CONNECT sh_oracl/sh_oracl@BJ
CREATE DATABASE LINK ORACL_SH
  CONNECT TO oracl IDENTIFIED BY oracl
  USING 'ORACL_SH';

-- 9. 建立物化視圖 (快速刷新，主鍵模式)
CONNECT mvadmin/mvadmin@BJ
CREATE MATERIALIZED VIEW SH_ORACL.CREDIT_CARD
  REFRESH FAST WITH PRIMARY KEY
  AS SELECT * FROM ORACL.CREDIT_CARD@ORACL_SH;

-- 10. 建立刷新群組 (每日一次)
BEGIN
  DBMS_REFRESH.MAKE(
    name => 'MVADMIN.SH_REFRESH',
    list => '',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1',
    implicit_destroy => FALSE,
    push_deferred_rpc => TRUE,
    refresh_after_errors => FALSE
  );
END;
/

-- 11. 加入物化視圖至刷新群組
BEGIN
  DBMS_REFRESH.ADD(
    name => 'MVADMIN.SH_REFRESH',
    list => 'SH_ORACL.CREDIT_CARD',
    lax => TRUE
  );
END;
/

步驟三：驗證與監控

1. 查詢 `SYS.DBA_JOBS` 確認生成三類 Job：`PURGE`、`PUSH`、`REFRESH`。`WHAT` 欄位應包含對應 `DBMS_DEFER_SYS.PURGE/PUSH` 或 `DBMS_REFRESH.REFRESH` 呼叫。
2. 確認 `JOB_QUEUE_PROCESSES > 0` (init.ora)，否則 Job 不會自動執行。
3. 觀察 `DBA_JOBS.FAILURES`：失敗 16 次或間隔超過設定值 Job 會標記 `BROKEN`，需手動 `DBMS_JOB.RUN(job_no)` 重跑並排查錯誤。
4. 定期檢查 `DBA_MVIEW_REFRESH_TIMES`、`DBA_MVIEWS.LAST_REFRESH` 確認刷新成功。

關鍵注意事項

• 快速刷新前提：主表必須有 MV Log，且有 PK (或用 ROWID)。大量 DML 時 MV Log 增長快，需定期 `PURGE_MVIEW_LOG` 或 `TRUNCATE` (配合 `DBMS_MVIEW.PURGE_LOG`)。
• 衝突解決：唯讀 MV 無衝突；可更新 MV 需配置 `DBMS_REPCAT.CONFIGURE_CONFLICT_RESOLUTION`。
• 版本升級：複寫環境升級需遵循 Metalink Note 完整流程 (先升級主站點、再升級 MV 站點、重新產生支援)。
• 替代方案：Oracle 10g+ 推薦使用 **Streams / GoldenGate** 實現即時異構複寫，功能更強、延遲更低、管理更靈活。

總結

本文示範 Oracle 9i 多主站點異步物化視圖複寫的標準化部署流程。雖步驟繁瑣，但透過 `DBMS_REPCAT`/`DBMS_DEFER_SYS`/`DBMS_REFRESH` 套件實現了「配置一次、自動運行」的異地資料整合。生產環境務必建立完善的監控告警 (Job 失敗、刷新延遲、MV Log 空間) 並定期演練災難恢復。