DRS

DM-DM之间DMDRS的同步，单向、双向测试

1测试说明

本次测试包含表、数据、视图、物化视图、触发器、存储过程、包、序列的同步

2测试环境

X86_64 4c

操作系统: Kylin V10 SP3

IP DM1:192.168.126.150:5236 DM2:192.168.126.150:5237

内存:6648

数据库版本: 03134284552-20260414-322369-20221

初始化参数: extent_size=32 page_size=32 charset=0 case_sensitive=1 blank_pad_mode=1

源端RLOG_APPEND_LOGIC=1 ARCH_INI=1 RLOG_APPEND_SYSTAB_LOGIC = 1

#其他参数依据参数优化脚本V5.6进行调整

3语句记录

--创建同步用户
CREATE USER DMDRS IDENTIFIED BY "Dmdrs@2026";
GRANT DBA TO DMDRS;
-- 建表
CREATE TABLE DMDRS.TEST (
    ID   INT,
    NAME VARCHAR(10)
);
-- 插入测试数据
INSERT INTO DMDRS.TEST VALUES (1, 'AAA');
INSERT INTO DMDRS.TEST VALUES (2, 'BBB');
INSERT INTO DMDRS.TEST VALUES (3, 'CCC');
COMMIT;
-- 查询验证
SELECT * FROM DMDRS.TEST;
-- 基于 TEST 表创建视图
CREATE VIEW DMDRS.V_TEST1 AS
SELECT ID, NAME
FROM DMDRS.TEST
WHERE ID > 2;
-- 查询视图
SELECT * FROM DMDRS.V_TEST1;
-- 创建物化视图，完全刷新、按需触发
CREATE MATERIALIZED VIEW DMDRS.MV_TEST1
REFRESH COMPLETE ON DEMAND
AS
SELECT ID, NAME
FROM DMDRS.TEST WHERE ID < 3;
-- 手动刷新
REFRESH MATERIALIZED VIEW DMDRS.MV_TEST1 COMPLETE;
-- 查询物化视图
REFRESH MATERIALIZED VIEW DMDRS.MV_TEST1 COMPLETE;
SELECT * FROM DMDRS.MV_TEST1;
-- 创建序列，供触发器或主键自增使用
CREATE SEQUENCE DMDRS.SEQ_TEST_ID
START WITH 100
INCREMENT BY 1
NOCYCLE
CACHE 10;
-- 测试取值
SELECT DMDRS.SEQ_TEST_ID.NEXTVAL FROM DUAL;
-- 触发器 插入 TEST 时，若 ID 为空，用序列自动填充
CREATE OR REPLACE TRIGGER DMDRS.TRG_TEST_ID
BEFORE INSERT ON DMDRS.TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
    SELECT DMDRS.SEQ_TEST_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
-- 测试：不指定 ID 插入
INSERT INTO DMDRS.TEST (NAME) VALUES ('EEE');
COMMIT;
-- 查看结果
SELECT * FROM DMDRS.TEST;
-- 存储过程：根据 ID 查询 NAME 并打印
CREATE OR REPLACE PROCEDURE DMDRS.P_TEST_QUERY(
    P_ID IN INT
) AS
    V_NAME VARCHAR(10);
BEGIN
    SELECT NAME INTO V_NAME FROM DMDRS.TEST WHERE ID = P_ID;
    DBMS_OUTPUT.PUT_LINE('ID=' || P_ID || ', NAME=' || V_NAME);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到 ID=' || P_ID);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('异常: ' || SQLERRM);
END;
/
-- 调用
SET SERVEROUTPUT ON;
CALL DMDRS.P_TEST_QUERY(1);
CALL DMDRS.P_TEST_QUERY(999);
-- 1. 包规范
CREATE OR REPLACE PACKAGE DMDRS.PKG_TEST AS
    FUNCTION F_COUNT RETURN INTEGER;
END PKG_TEST;
/
-- 2. 包体
CREATE OR REPLACE PACKAGE BODY DMDRS.PKG_TEST AS
    FUNCTION F_COUNT RETURN INTEGER IS
        V_CNT INTEGER;
    BEGIN
        SELECT COUNT(*) INTO V_CNT FROM DMDRS.TEST;
        RETURN V_CNT;
    END F_COUNT;
END PKG_TEST;
/
-- 3. 调用测试
SET SERVEROUTPUT ON;
DECLARE
    V_RESULT INTEGER;
BEGIN
    V_RESULT := DMDRS.PKG_TEST.F_COUNT();
    DBMS_OUTPUT.PUT_LINE('当前计数: ' || V_RESULT);
END;
/
-- 表数据
SELECT * FROM DMDRS.TEST;
-- 视图
SELECT * FROM DMDRS.V_TEST;
-- 物化视图
SELECT * FROM DMDRS.MV_TEST;
-- 序列
SELECT OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'DMDRS' AND OBJECT_TYPE = 'SEQUENCE'
ORDER BY OBJECT_NAME;
-- 触发器
SELECT OWNER, TRIGGER_NAME,STATUS FROM ALL_TRIGGERS WHERE OWNER = 'DMDRS';
-- 存储过程
SELECT OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'DMDRS' AND OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME;
-- 包
SELECT OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'DMDRS' AND OBJECT_TYPE = 'PACKAGE'
ORDER BY OBJECT_NAME;