基于物化视图日志更新远程表t2的最优方法及替代方案咨询

1. 确保物化视图日志配置满足同步需求 #

首先确认物化视图日志包含同步所需的关键信息，创建时需指定捕获新值、主键（用于定位远程记录）：

CREATE MATERIALIZED VIEW LOG ON T
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES
FOR INSERT, UPDATE, DELETE;

若已创建日志，可通过以下语句修改配置：

ALTER MATERIALIZED VIEW LOG ON T INCLUDING NEW VALUES;

同时设置日志保留时间，避免未同步记录被自动清理：

ALTER MATERIALIZED VIEW LOG ON T RETENTION 7 DAYS; -- 保留时长可根据同步频率调整

2. 读取日志并同步到远程t2 #

通过PL/SQL结合DBLINK实现同步，建议维护本地同步日志表记录上次同步时间，避免重复处理：

-- 创建本地同步日志表
CREATE TABLE t_sync_log (
  sync_id NUMBER GENERATED ALWAYS AS IDENTITY,
  sync_time DATE DEFAULT SYSDATE,
  status VARCHAR2(20) DEFAULT 'SUCCESS'
);

编写同步脚本：

DECLARE
  v_last_sync DATE;
BEGIN
  -- 获取上次同步时间，首次同步设为初始数据一致的基准时间
  SELECT NVL(MAX(sync_time), TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
  INTO v_last_sync
  FROM t_sync_log;

  -- 批量处理INSERT
  FORALL rec IN (
    SELECT primary_key_col, col1, col2 -- 替换为t2的实际列
    FROM MLOG$_T
    WHERE DMLTYPE$$ = 'I' AND SNAPTIME$$ > v_last_sync
  )
    INSERT INTO t2@remote_db_link (primary_key_col, col1, col2)
    VALUES (rec.primary_key_col, rec.col1, rec.col2);

  -- 批量处理UPDATE（依赖日志包含新值）
  FORALL rec IN (
    SELECT primary_key_col, col1, col2
    FROM MLOG$_T
    WHERE DMLTYPE$$ = 'U' AND SNAPTIME$$ > v_last_sync
  )
    UPDATE t2@remote_db_link
    SET col1 = rec.col1, col2 = rec.col2
    WHERE primary_key_col = rec.primary_key_col;

  -- 批量处理DELETE
  FORALL rec IN (
    SELECT primary_key_col
    FROM MLOG$_T
    WHERE DMLTYPE$$ = 'D' AND SNAPTIME$$ > v_last_sync
  )
    DELETE FROM t2@remote_db_link
    WHERE primary_key_col = rec.primary_key_col;

  COMMIT;
  -- 更新同步日志
  INSERT INTO t_sync_log (sync_time) VALUES (SYSDATE);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    INSERT INTO t_sync_log (sync_time, status) VALUES (SYSDATE, 'FAILED: ' || SQLERRM);
    RAISE;
END;
/

可通过DBMS_SCHEDULER将脚本设为定时任务（如每10分钟执行一次），实现自动同步。

1. 自定义触发器+轻量级变更日志 #

物化视图日志会记录全量字段，开销较高。可自定义触发器，仅捕获需要同步的字段，并过滤无意义的更新（如字段值未变化的UPDATE）：

-- 创建自定义变更日志表
CREATE TABLE t_custom_change_log (
  change_id NUMBER GENERATED ALWAYS AS IDENTITY,
  dml_type CHAR(1) CHECK (dml_type IN ('I','U','D')),
  primary_key_col NUMBER,
  -- 仅保留需要同步的字段
  sync_col1 VARCHAR2(100),
  sync_col2 DATE,
  change_time DATE DEFAULT SYSDATE
);

-- 创建行级触发器
CREATE OR REPLACE TRIGGER trg_t_sync_capture
AFTER INSERT OR UPDATE OR DELETE ON T
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO t_custom_change_log (dml_type, primary_key_col, sync_col1, sync_col2)
    VALUES ('I', :NEW.primary_key_col, :NEW.sync_col1, :NEW.sync_col2);
  ELSIF UPDATING THEN
    -- 仅捕获实际变更的字段，减少日志量
    IF :OLD.sync_col1 != :NEW.sync_col1 OR :OLD.sync_col2 != :NEW.sync_col2 THEN
      INSERT INTO t_custom_change_log (dml_type, primary_key_col, sync_col1, sync_col2)
      VALUES ('U', :NEW.primary_key_col, :NEW.sync_col1, :NEW.sync_col2);
    END IF;
  ELSIF DELETING THEN
    INSERT INTO t_custom_change_log (dml_type, primary_key_col)
    VALUES ('D', :OLD.primary_key_col);
  END IF;
END;
/

后续同步逻辑与物化视图日志方案一致，读取该自定义日志表即可，这种方式开销远低于物化视图日志。

2. Oracle GoldenGate（OGG） #

OGG基于Oracle重做日志（Redo Log）实现增量同步，完全无侵入式，无需在源表创建任何日志或触发器，对源库DML性能影响极小。它直接读取Redo Log中的变更记录，解析后同步到目标库。

• 核心优势：源库开销可忽略，支持高并发、大数据量同步，还能跨异构数据库（如目标库为MySQL、SQL Server等）。
• 配置思路：源端部署Extract进程捕获T的变更，通过网络传输到目标端，目标端部署Replicat进程将变更应用到t2。

3. 优化现有物化视图日志配置 #

若必须保留物化视图日志，可通过以下配置降低开销：

• 仅记录必要标识：创建日志时仅指定WITH PRIMARY KEY，不要使用WITH ALL COLUMNS，减少日志存储量；无需ROWID则去掉该选项。
• 定期清理已同步日志：同步完成后，手动清理物化视图日志中已处理的记录（注意不要影响正在使用的快速刷新MV）：

  DELETE FROM MLOG$_T
  WHERE SNAPTIME$$ < (SELECT MAX(sync_time) FROM t_sync_log);
  COMMIT;
  

• 避免日志无限增长：设置合理的RETENTION参数，如保留3天日志，超出自动清理。

4. 异步批量同步 #

无论采用哪种捕获方式，将实时同步改为异步批量处理都能显著降低源库开销：

• 用DBMS_SCHEDULER定时执行同步任务（如每5-15分钟一次），批量读取变更日志，再用FORALL语句批量执行远程DML，减少DBLINK连接次数和源库事务压力。
• 该方式适合对同步延迟要求不高的场景，能大幅降低源库CPU和IO开销。

内容的提问来源于stack exchange，提问作者moth