Oracle 19c双向数据同步实现及ORA-12518错误解决

ORA-12518错误的核心原因是触发器双向递归调用导致连接资源耗尽：远程库创建触发器后，本地库的变更会触发远程库操作，而远程库的触发器又会立即触发本地库的反向操作，形成无限循环，导致数据库监听进程无法处理持续激增的连接请求，最终抛出该错误。

要解决这个问题，核心是切断触发器的递归同步链路，同时优化同步逻辑的准确性和效率，具体实现如下：

1. 给同步操作添加专属标识 #

通过USERENV('CLIENT_INFO')和DBMS_APPLICATION_INFO包，为触发器发起的同步操作打上唯一标识，触发器检测到该标识时直接跳过执行，避免递归循环。

2. 优化WHERE匹配条件 #

原触发器使用所有列作为WHERE条件，存在效率低、匹配不准确的问题，改为仅用主键（如id）作为唯一匹配依据，确保同步操作的精准性。

修改后的触发器代码 #

本地库（local db）触发器 #

CREATE OR REPLACE TRIGGER trg_test_order_local
AFTER INSERT OR UPDATE OR DELETE ON test_order_local
FOR EACH ROW
DECLARE
  v_client_info VARCHAR2(64);
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  -- 检查是否为远程库同步过来的操作，是则跳过
  SELECT USERENV('CLIENT_INFO') INTO v_client_info FROM DUAL;
  IF v_client_info = 'SYNC_FROM_REMOTE' THEN
    COMMIT;
    RETURN;
  END IF;

  -- 设置同步标识，告知远程库触发器这是同步操作
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYNC_FROM_LOCAL');

  IF INSERTING THEN
    INSERT INTO test_order_remote@test_remote1 (id, desc1, desc2, desc23)
    VALUES (:NEW.id, :NEW.desc1, :NEW.desc2, :NEW.desc23);
  ELSIF UPDATING THEN
    -- 仅用主键id匹配，避免多列匹配的误差
    UPDATE test_order_remote@test_remote1
    SET desc1 = :NEW.desc1, desc2 = :NEW.desc2, desc23 = :NEW.desc23
    WHERE id = :OLD.id;
  ELSIF DELETING THEN
    DELETE FROM test_order_remote@test_remote1
    WHERE id = :OLD.id;
  END IF;

  -- 重置客户端标识
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
  COMMIT;
END;
/

远程库（remote）触发器 #

CREATE OR REPLACE TRIGGER trg_test_order_remote
AFTER INSERT OR UPDATE OR DELETE ON test_order_remote
FOR EACH ROW
DECLARE
  v_client_info VARCHAR2(64);
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  -- 检查是否为本地库同步过来的操作，是则跳过
  SELECT USERENV('CLIENT_INFO') INTO v_client_info FROM DUAL;
  IF v_client_info = 'SYNC_FROM_LOCAL' THEN
    COMMIT;
    RETURN;
  END IF;

  -- 设置同步标识，告知本地库触发器这是同步操作
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SYNC_FROM_REMOTE');

  IF INSERTING THEN
    INSERT INTO test_order_local@test_local1 (id, desc1, desc2, desc23)
    VALUES (:NEW.id, :NEW.desc1, :NEW.desc2, :NEW.desc23);
  ELSIF UPDATING THEN
    UPDATE test_order_local@test_local1
    SET desc1 = :NEW.desc1, desc2 = :NEW.desc2, desc23 = :NEW.desc23
    WHERE id = :OLD.id;
  ELSIF DELETING THEN
    DELETE FROM test_order_local@test_local1
    WHERE id = :OLD.id;
  END IF;

  -- 重置客户端标识
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
  COMMIT;
END;
/

如果是生产环境的双向同步需求，不建议用触发器实现，推荐使用Oracle官方专业同步工具：

• Oracle GoldenGate：支持异构数据库、低延迟双向同步，适配复杂业务场景
• Oracle Data Guard：主备架构下可配置为Active Data Guard，实现读写分离+双向同步

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