Oracle 異地資料庫同步實戰：DBLINK + 觸發器實現雙向即時複製

架構目標

Server A (DB A) 表 TBL_TB 發生 DML 變更 → 即時同步至 Server B (DB B) 同名表。假設結構相同，PK 為 ID，含 NAME 欄位。

前置條件

• 雙向網路互通 (防火牆開放 1521/配置埠)
• 雙方資料庫版本相容
• 同步用戶具備來源表 SELECT、目標表 DML 權限

步驟 1：配置 TNSNAMES.ORA (Server A 端)

# $ORACLE_HOME/network/admin/tnsnames.ora
MASTER2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = master2)
    )
  )

Server A IP: 192.168.0.1，Server B IP: 192.168.0.2，Service: master2

步驟 2：建立 Database Link (Server A 端)

CREATE PUBLIC DATABASE LINK LINKMASTER2.ORACLE.COM
  CONNECT TO b_user IDENTIFIED BY "b_password"
  USING 'MASTER2';

• b_user：Server B 同步專用用戶
• 密碼含特殊字符需雙引號
• Global name LINKMASTER2.ORACLE.COM 需與 Server B GLOBAL_NAME 一致或設 GLOBAL_NAMES=FALSE

步驟 3：建立同義詞簡化引用 (Server A 端)

CREATE OR REPLACE SYNONYM TBL_TB_REMOTE
  FOR b_user.TBL_TB@LINKMASTER2.ORACLE.COM;

後續可直接 SELECT * FROM TBL_TB_REMOTE 查詢異地資料。

步驟 4：建立同步觸發器 (Server A 端，源表上)

INSERT 同步觸發器

CREATE OR REPLACE TRIGGER trg_tbl_tb_ins_sync
AFTER INSERT ON TBL_TB
FOR EACH ROW
BEGIN
  INSERT INTO TBL_TB_REMOTE (ID, NAME)
  VALUES (:NEW.ID, :NEW.NAME);
END;
/

UPDATE 同步觸發器

CREATE OR REPLACE TRIGGER trg_tbl_tb_upd_sync
AFTER UPDATE ON TBL_TB
FOR EACH ROW
BEGIN
  UPDATE TBL_TB_REMOTE
  SET ID = :NEW.ID, NAME = :NEW.NAME
  WHERE ID = :OLD.ID;
END;
/

DELETE 同步觸發器 (補充)

CREATE OR REPLACE TRIGGER trg_tbl_tb_del_sync
AFTER DELETE ON TBL_TB
FOR EACH ROW
BEGIN
  DELETE FROM TBL_TB_REMOTE WHERE ID = :OLD.ID;
END;
/

關鍵設計考量

進階：防遞迴同步觸發器模板

CREATE OR REPLACE TRIGGER trg_tbl_tb_bi_sync
AFTER INSERT OR UPDATE OR DELETE ON TBL_TB
FOR EACH ROW
DECLARE
  v_local_db VARCHAR2(30) := SYS_CONTEXT('USERENV', 'DB_NAME');
BEGIN
  -- 僅在本地資料庫觸發同步，避免遠端回寫再觸發
  IF v_local_db = 'MASTER1' THEN
    IF INSERTING THEN
      INSERT INTO TBL_TB_REMOTE (ID, NAME) VALUES (:NEW.ID, :NEW.NAME);
    ELSIF UPDATING THEN
      UPDATE TBL_TB_REMOTE SET NAME = :NEW.NAME WHERE ID = :OLD.ID;
    ELSIF DELETING THEN
      DELETE FROM TBL_TB_REMOTE WHERE ID = :OLD.ID;
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 記錄同步失敗，不拋出例外避免影響本地交易
    INSERT INTO sync_error_log (err_time, err_msg, sqlcode, sqlerrm, operation, pk_value)
    VALUES (SYSDATE, 'Remote sync failed', SQLCODE, SQLERRM,
            CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END,
            COALESCE(:NEW.ID, :OLD.ID));
END;
/

雙向同步部署清單

1. Server A 配置 TNS → DBLINK → 同義詞 → 觸發器 (同步至 B)
2. Server B 配置 TNS → DBLINK → 同義詞 → 觸發器 (同步至 A)
3. 雙方建立 sync_error_log 表記錄失敗
4. 驗證：A 插入 → B 可查；B 更新 → A 可查；無無限循環
5. 監控：定期比對雙邊行數、抽樣核對關鍵欄位
6. 備份：同步非備份替代，雙方仍需獨立備份策略

局限性與替代方案

防火牆與網路驗證

# Server A 驗證連線 Server B
tnsping MASTER2
sqlplus b_user/b_password@MASTER2

# 檢查防火牆
telnet 192.168.0.2 1521
# 或
nc -zv 192.168.0.2 1521

關鍵提醒：Server B 防火牆必須開放 1521 埠 (或配置埠)，否則 DBLINK 連線逾時 (ORA-12170/12535)。

DBLINK + 觸發器同步屬「輕量級即時複製」，適合表數少、變更頻率中等、可容忍毫秒級同步阻塞的場景。企業級關鍵業務、高併發、跨機房、需零資料損失、DDL 同步、衝突解決，請直接上 Oracle GoldenGate。