Oracle 複寫技術演進與物化視圖站點建置完整實戰腳本

複寫架構核心概念

Oracle 複寫隨版本演進日趨精細。物化視圖 (Materialized View) 架構要求每個複寫站點建立三個資料庫連結：公共連結 (雙向)、複寫管理員私有連結、排程推送專用連結。公共連結需指定全域資料庫名稱 (Global Database Name) 或完整連線描述符。

完整建置腳本 (以 BJ 站點為例)

1. 建立複寫管理員並授權

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. 建立公共資料庫連結 (指定全域名稱)

-- 方式 A: 使用全域資料庫名
CREATE PUBLIC DATABASE LINK ORACLSH USING 'Oracle.shanghai.com';

-- 方式 B: 使用完整連線描述符 (TNS)
CREATE PUBLIC DATABASE LINK ORACLSH USING '(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=oracl)))';

4. 建立清除延遲交易佇列排程

CONNECT mvadmin/mvadmin@BJ
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE(
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/24',  -- 每小時
    delay_seconds => 0,
    rollback_segment => '');
END;
/

5. 建立複寫管理員私有資料庫連結

CREATE DATABASE LINK ORACLSH
  CONNECT TO proxy_bj IDENTIFIED BY proxy_bj
  USING 'Oracle.shanghai.com';

6. 建立複寫推送排程

BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH(
    destination => 'ORACLSH',
    interval    => 'SYSDATE + 1/24',
    next_date   => SYSDATE,
    stop_on_error => FALSE,
    delay_seconds => 0,
    parallelism => 0);
END;
/

7. 授權物化視圖建立所需權限給應用 Schema (如 CRM)

CONNECT system/passwd@BJ
GRANT ALTER SESSION TO crm;
GRANT CREATE CLUSTER TO crm;
GRANT CREATE DATABASE LINK TO crm;
GRANT CREATE SEQUENCE TO crm;
GRANT CREATE SESSION TO crm;
GRANT CREATE SYNONYM TO crm;
GRANT CREATE TABLE TO crm;
GRANT CREATE VIEW TO crm;
GRANT CREATE PROCEDURE TO crm;
GRANT CREATE TRIGGER TO crm;
GRANT UNLIMITED TABLESPACE TO crm;
GRANT CREATE TYPE TO crm;
GRANT CREATE ANY SNAPSHOT TO crm;      -- 9i/10g 語法
GRANT ALTER ANY SNAPSHOT TO crm;
-- 11g+ 建議改用 MATERIALIZED VIEW 權限
GRANT CREATE MATERIALIZED VIEW TO crm;
GRANT ALTER MATERIALIZED VIEW TO crm;

關鍵注意事項

• 全域資料庫名稱 (GLOBAL_NAME) 必須在所有節點一致且唯一，init.ora 設定 GLOBAL_NAMES=TRUE。
• 三類連結用途不同：公共連結供查詢/驗證，私有連結供複寫管理員傳播異動，推送連結專門排程推送 Deferred Queue。
• SCHEDULE_PUSH 的 parallelism 設為 0 表示串列推送，大量異動可設 1-4 並行。
• 11g 後 SNAPSHOT 術語正式更名為 MATERIALIZED VIEW，權限授予需對應版本。