DMHS集群双向同步搭建过程分享
一、DMHS功能介绍
DMHS是达梦公司推出的支持易构环境的高性能、高可用、高可扩展的数据库实时同步软件。
DMHS分为日志分析（CPT）、数据传输（NET）、数据入库（EXEC）三大功能模块。CPT负责对源数据库日志进行分析，联合字典信息提取其中额数据变化，如插入（INSERT）、删除（DELECT）、修改（UODATE）和其他DDL操作，这些操作经过包装后投递给消息发送（NET）模块。日志分析到达NET模块后，在这里进行分发，分发前可以对操作进行过滤，或者对表进行过滤和映射，还可以对消息的列进行映射（改名、剔除和函数计算）。经过NET的处理，消息通过网络（TCP/IP）发送给下一级EXEC模块，或者保存到本地。EXEC 模块接收到 NET 接受子模块发来的消息，把这些消息按事务分类，并构成相应的SQL，进入目的端数据库。并通过不断刷新检查点的方式，推进LSN变化，当两端LSN一致时，则两端数据完全一致。
DMHS支持同步类型：
1) DMHS 支持的数据类型包括：数值类型（INT、NUMERIC、DOUBLE）、字符类
型（CHAR、VARCHAR）、二进制数据类型（BINARY、VARBINARY）、时间日期类型
（DATE、TIME、DATETIME）、大对象类型（CLOB、BLOB）。
2) DMHS 支持的语句类型包括：DML 语句（INSERT、UPDATE、DELETE）、DDL
语句（CREATE TABLE、DROP TABLE、TRUNCATE TABLE、ALTER TABLE）。
二、DMHS搭建_单机单向同步
2.1开启归档和逻辑日志
2.1.1配置数据库dm.ini文件
ARCH_INI = 1
RLOG_APPEND_LOGIC = 1
注意：dm.ini配置参数中"FAST_COMMIT" 必须为0，否则会导致逻辑日志不全而影响同步
2.1.2添加归档配置文件，vim dmarch.ini
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /data/DAMENG/arch #归档目录
ARCH_FILE_SIZE = 128 #归档文件大小，单位MB
ARCH_SPACE_LIMIT = 0 #空间大小限制，0表示不限制（此处生产环境可配置限制，一般为挂盘空间的10%左右）
2.1.3重启数据库，使配置文件生效
cd /home/dmdba/dmdbms/bin/
./DmServiceDMSERVER restart
2.1.4验证归档开启状态（使用./disql）
1)检查归档日志开启状态
select arch_mode from v$database;
2)检查逻辑追加日志
select para_value from v$dm_ini where para_name =‘RLOG_APPEND_LOGIC’;
2.2环境变量设置（源端、目的端）
2.2.1检查系统的环境变量(临时生效)
export LD_LIBRARY_PATH=/opt/dmdbms/bin:$LD_LIBRARY_PATH
echo $LD_LIBRARY_PATH
2.2.2检查系统的环境变量(永久生效)
vim /etc/profile
LD_LIBRARY_PATH=/home/dmdba/dmdbms/bin:$LD_LIBRARY_PATH
source /etc/profile
2.3添加服务名
vi /etc/dm_svc.conf
源端集群：
DM=(192.168.110.141:5236,192.168.110.142:5236)
目的端集群：
IDCDM=(192.168.110.143:5236,192.168.110.144:5236)
服务名可随意配置，但2.6、2.7配置dmhs.hs时要与配置文件中db_server一致，例如：<db_server>DM</db_server>
2.4命令行安装DMHS
使用命令行安装：./DMHSInstall.bin -i
输入命令：
1)1 #英文安装
2)2 #输入dmhs.key文件（此处也可暂跳过）
3)/opt/dmhs.hs #dmhs.key路径
4)3 #自定义安装
5)1,2,7,8 #自定义需要安装的服务
6)/home/dmhs #dmhs安装路径，可任意定义
7)Y #确认安装路径
8)2 #现在初始化
9)1 #dmhs语言，英文
10)mgr_port、chk_interval、ckpt_interval默认安装，按回车确认
11)4 #sitid，主备一致，同步库之间不能一样
12)一路默认，回车确认
13)5236 #数据库端口，默认5236
14)其余一致默认，回车确认
15)安装完成
2.5配置DDL同步
如果需要配置文件中配置DDL同步，那么需要创建DDL触发器和辅助表。创建时需要使用SYSDBA操作。
---=======================================================
---version 2.2
---note: please do not modify this file name and comment line.
---=======================================================
--1.TABLE: DMHS_DDL_COL
CREATE TABLE SYSDBA.DMHS_DDL_COL(
ID INT,
COLID SMALLINT,
NAME VARCHAR(128),
TYPE VARCHAR(128),
LENGTH INT,
SCALE SMALLINT,
NULLABLE CHAR(1),
DEFVAL VARCHAR(2048)
);
--2.TABLE: DMHS_DDL_SQL
CREATE TABLE SYSDBA.DMHS_DDL_SQL(
OP_TYPE VARCHAR(32),
OBJ_SCHNAME VARCHAR(128),
OBJ_NAME VARCHAR(128),
OBJ_TYPE VARCHAR(128),
OBJID INT,
OP_SQL CLOB,
OP_TIME DATETIME
);
--3.TABLE: DMHS_DDL_CONS
CREATE TABLE SYSDBA.DMHS_DDL_CONS(
ID INT,
NAME VARCHAR(128),
OPTYPE VARCHAR(16),
TABLEID INT,
TYPE CHAR(1),
ISDATA CHAR(1),
VALID CHAR(1),
IDX_N_KEY INT,
IDX_KEYINFO VARBINARY(816),
FSCH_NAME VARCHAR(128),
FTAB_NAME VARCHAR(128),
FTAB_ID INT,
FIDX_N_KEY INT,
FIDX_KEYINFO VARBINARY(816),
CHECKINFO VARCHAR(2048)
);
--4.TABLE: DMHS_DDL_IDX
CREATE TABLE SYSDBA.DMHS_DDL_IDX(
ID INT,
PID INT,
XTYPE INT,
FLAG INT,
KEYNUM SMALLINT,
KEYINFO VARBINARY(816)
);
--5.TABLE: DMHS_DDL_RENAME
CREATE TABLE SYSDBA.DMHS_DDL_RENAME(
OBJ_SCHNAME VARCHAR(128),
OBJ_NAME VARCHAR(128),
ID INT
);
--6.TABLE: DMHS_DDL_SEQ
CREATE TABLE SYSDBA.DMHS_DDL_SEQ(
OBJ_SCHNAME VARCHAR(128),
OBJ_NAME VARCHAR(128),
ID INT,
INFO1 INT,
START BIGINT, --INFO3
STEP BIGINT, --INFO4
INFO5 VARBINARY(32) --MAX/MIN VALUE
);
--7.TABLE: DMHS_DDL_PART
CREATE TABLE SYSDBA.DMHS_DDL_PART(
OP_TYPE VARCHAR(8),
ROOT_ID INT,
BASE_TABID INT,
PART_TABID INT,
--/* INFO1保存分区堆表的存储参数，对应SYSOBJECTS(INFO1) */
INFO1 INT,
--/* INFO3分区类型；子表描述，对应SYSOBJECTS(INFO3) */
INFO3 BIGINT,
SCH_NAME VARCHAR(128),
TAB_NAME VARCHAR(128),
PART_NAME VARCHAR(128),
HIGH_VALUE VARCHAR(8188),
INCLUDE_HVAL CHAR(1),
PART_POS INT,
PART_COLINFO VARBINARY(2048),
--/*以下3个用于判断交换分区*/
GROUP_ID SMALLINT,
ROOT_FILE SMALLINT,
ROOT_PAGE INT,
TS_NAME VARCHAR(256),
RESERVED VARCHAR(128)
);
--8.TRIGGER: DMHS_DDL_TRIGGER_GRANT
CREATE OR REPLACE TRIGGER SYSDBA.DMHS_DDL_TRIGGER_GRANT
BEFORE GRANT ON DATABASE
BEGIN
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
'GRANT',
:EVENTINFO.OPUSER,
:EVENTINFO.GRANTEE,
:EVENTINFO.GRANTTYPE,
NULL,
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
/
--9.TRIGGER: DMHS_DDL_TRIGGER_REVOKE
CREATE OR REPLACE TRIGGER SYSDBA.DMHS_DDL_TRIGGER_REVOKE
BEFORE REVOKE ON DATABASE
BEGIN
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
'REVOKE',
:EVENTINFO.OPUSER,
:EVENTINFO.GRANTEE,
:EVENTINFO.GRANTTYPE,
NULL,
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
/
--10.TRIGGER: DMHS_DDL_TRIGGER_BEFORE
CREATE OR REPLACE TRIGGER SYSDBA.DMHS_DDL_TRIGGER_BEFORE
BEFORE CREATE OR ALTER OR DROP OR TRUNCATE ON DATABASE
DECLARE
E1 EXCEPTION FOR -20002;
TBL_ID INT := NULL;
BEGIN
IF :EVENTINFO.OBJECTNAME LIKE 'DMHS_DDL%' THEN
IF :EVENTINFO.OBJECTTYPE = 'TABLE' AND :EVENTINFO.OPTYPE = 'DROP' THEN
PRINT('TABLE CANNOT DROP BEFORE ALL DMHS TRIGGERS ARE DROPED');
RAISE E1;
END IF;
RETURN;
END IF;
IF :EVENTINFO.OBJECTTYPE = 'TABLE' THEN
IF :EVENTINFO.SCHEMANAME = 'SYSDBA' AND :EVENTINFO.OBJECTNAME LIKE 'DMHS%' THEN
RETURN;
END IF;
IF :EVENTINFO.OPTYPE = 'CREATE' THEN
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
:EVENTINFO.OPTYPE,
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
:EVENTINFO.OBJECTTYPE,
NULL,
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
RETURN;
END IF;
SELECT SO.ID INTO TBL_ID FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.TYPE$ = 'SCHOBJ' AND SO.SUBTYPE$ = 'UTAB';
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
:EVENTINFO.OPTYPE,
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
:EVENTINFO.OBJECTTYPE,
TBL_ID,
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
IF :EVENTINFO.OPTYPE = 'ALTER' THEN
INSERT INTO SYSDBA.DMHS_DDL_RENAME(OBJ_SCHNAME, OBJ_NAME, ID) VALUES(:EVENTINFO.SCHEMANAME, :EVENTINFO.OBJECTNAME, TBL_ID);
--修改表操作: 添加原始的约束
INSERT INTO SYSDBA.DMHS_DDL_CONS (ID, NAME, TABLEID, TYPE, VALID) SELECT S.ID, O.NAME, S.TABLEID, S.TYPE$, S.VALID FROM SYS.SYSCONS S, SYS.SYSOBJECTS O WHERE S.ID = O.ID AND S.TABLEID = TBL_ID;
--修改表操作：添加原始的分区信息
IF EXISTS (SELECT TOP 1 1 FROM SYS.SYSHPARTTABLEINFO WHERE BASE_TABLE_ID = TBL_ID) THEN
INSERT INTO SYSDBA.DMHS_DDL_PART(ROOT_ID, BASE_TABID, PART_TABID, PART_NAME) SELECT TBL_ID, BASE_TABLE_ID, PART_TABLE_ID, PARTITION_NAME FROM SYS.SYSHPARTTABLEINFO WHERE BASE_TABLE_ID = TBL_ID;
ELSE
INSERT INTO SYSDBA.DMHS_DDL_PART(ROOT_ID, BASE_TABID, GROUP_ID, ROOT_FILE, ROOT_PAGE)
SELECT TBL_ID, O_TAB.ID, GROUPID, ROOTFILE, ROOTPAGE
FROM SYSINDEXES S_IDX, SYSOBJECTS O_IDX, SYSOBJECTS O_TAB
WHERE S_IDX.ID = O_IDX.ID AND O_IDX.PID = O_TAB.ID AND O_IDX.SUBTYPE$ = 'INDEX' AND (S_IDX.XTYPE & 0X00000001) = 0X00000000 AND O_TAB.ID = TBL_ID;
END IF;
END IF;
ELSIF :EVENTINFO.OBJECTTYPE = 'INDEX' THEN
IF :EVENTINFO.OPTYPE = 'DROP' THEN
SELECT SO.ID INTO TBL_ID FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.TYPE$ = 'TABOBJ' AND SO.SUBTYPE$ = 'INDEX';
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
:EVENTINFO.OPTYPE,
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
:EVENTINFO.OBJECTTYPE,
TBL_ID, --INDEX ID
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
INSERT INTO SYSDBA.DMHS_DDL_IDX SELECT
SI.ID,
SO.PID,
SI.XTYPE,
SI.FLAG,
SI.KEYNUM,
SI.KEYINFO
FROM SYS.SYSINDEXES SI, SYS.SYSOBJECTS SO WHERE SI.ID = SO.ID AND SI.ID = TBL_ID AND SO.ID = TBL_ID;
END IF;
--ELSIF :EVENTINFO.OBJECTTYPE IN ('PROCEDURE', 'FUNCTION', 'VIEW', 'TRIGGER', 'PACKAGE', 'PACKAGEBODY') THEN
ELSE
IF :EVENTINFO.OPTYPE = 'DROP' AND EXISTS(SELECT TOP 1 1 FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME) THEN
SELECT SO.ID INTO TBL_ID FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.TYPE$ = 'SCHOBJ';
END IF;
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
:EVENTINFO.OPTYPE,
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
:EVENTINFO.OBJECTTYPE,
TBL_ID,
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
END IF;
EXCEPTION
WHEN E1 THEN RAISE E1;
WHEN NO_DATA_FOUND THEN
RETURN;
END;
/
--11.TRIGGER: DMHS_DDL_TRIGGER_AFTER
CREATE OR REPLACE TRIGGER SYSDBA.DMHS_DDL_TRIGGER_AFTER
AFTER CREATE OR ALTER
ON DATABASE
DECLARE
E1 EXCEPTION FOR -20002;
TBL_ID INT;
NEW_TAB_NAME VARCHAR(128);
CONS_OLD_FLAG INT;
CONS_NEW_FLAG INT;
OLD_HP_CNT INT;
NEW_HP_CNT INT;
HP_INS_FLAG INT;
HP_OP_TYPE VARCHAR(32);
BEGIN
IF :EVENTINFO.OBJECTNAME LIKE 'DMHS_DDL%' THEN
IF :EVENTINFO.OBJECTTYPE = 'TABLE' AND :EVENTINFO.OPTYPE = 'DROP' THEN
PRINT('TABLE CANNOT DROP BEFORE ALL DMHS TRIGGERS ARE DROPED');
RAISE E1;
END IF;
RETURN;
END IF;
IF :EVENTINFO.OBJECTTYPE = 'TABLE' THEN
IF :EVENTINFO.SCHEMANAME = 'SYSDBA' AND :EVENTINFO.OBJECTNAME LIKE 'DMHS%' THEN
RETURN;
END IF;
IF :EVENTINFO.OPTYPE = 'CREATE' THEN
SELECT SO.ID INTO TBL_ID FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.TYPE$ = 'SCHOBJ' AND SO.SUBTYPE$ = 'UTAB';
INSERT INTO SYSDBA.DMHS_DDL_RENAME(OBJ_SCHNAME, OBJ_NAME, ID) VALUES(:EVENTINFO.SCHEMANAME, :EVENTINFO.OBJECTNAME, TBL_ID);
DELETE FROM SYSDBA.DMHS_DDL_RENAME WHERE OBJ_SCHNAME = :EVENTINFO.SCHEMANAME AND OBJ_NAME = :EVENTINFO.OBJECTNAME;
IF EXISTS(SELECT TOP 1 1 FROM SYS.SYSCONS WHERE TABLEID = TBL_ID) THEN
INSERT INTO SYSDBA.DMHS_DDL_CONS
SELECT S_CONS.ID, O_CONS.NAME, 'ADD', S_CONS.TABLEID, S_CONS.TYPE$, DECODE((C_IDX.XTYPE & 0X01), 0, 'Y', 'N') ISDATA, S_CONS.VALID, C_IDX.KEYNUM, C_IDX.KEYINFO,
F_SCH.NAME, F_TAB.NAME, F_TAB.ID, F_IDX.KEYNUM, F_IDX.KEYINFO, S_CONS.CHECKINFO
FROM SYS.SYSCONS S_CONS LEFT JOIN SYS.SYSOBJECTS O_CONS ON S_CONS.ID = O_CONS.ID LEFT JOIN SYS.SYSINDEXES C_IDX ON S_CONS.INDEXID = C_IDX.ID
LEFT JOIN SYS.SYSINDEXES F_IDX ON S_CONS.FINDEXID = F_IDX.ID LEFT JOIN SYS.SYSOBJECTS F_OBJ ON F_IDX.ID = F_OBJ.ID
LEFT JOIN SYS.SYSOBJECTS F_TAB ON F_TAB.ID = F_OBJ.PID LEFT JOIN SYS.SYSOBJECTS F_SCH ON F_SCH.ID = F_TAB.SCHID
WHERE S_CONS.TABLEID = TBL_ID;
DELETE FROM SYSDBA.DMHS_DDL_CONS WHERE TABLEID = TBL_ID;
END IF;
--添加分区表信息
INSERT INTO SYSDBA.DMHS_DDL_PART
SELECT
'CREATE',
TBL_ID,
A.BASE_TABLE_ID BASE_TABID,
A.PART_TABLE_ID PART_TABID,
A.INFO1,
A.INFO3,
A.SCH_NAME SCH_NAME,
A.TAB_NAME TAB_NAME,
A.PARTITION_NAME PART_NAME,
A.HIGH_VALUE,
A.INCLUDE_HIGH_VALUE INCLUDE_HVAL,
A.PART_POS,
A.PART_COLINFO,
NULL GROUP_ID,
NULL ROOT_FILE_ID,
NULL ROOT_PAGE_NO,
TS.NAME TS_NAME, NULL
FROM
(
SELECT T_HP.*, OBJ.BIN_VALUE PART_COLINFO, SCH.NAME SCH_NAME,TAB.NAME TAB_NAME, TAB.INFO1, TAB.INFO3
FROM (SELECT BASE_TABLE_ID, PART_TABLE_ID, PARTITION_NAME, SF_GET_PART_HIGH_VALUE(BASE_TABLE_ID, PART_TABLE_ID) HIGH_VALUE,
INCLUDE_HIGH_VALUE, SF_GET_PART_SEQNO(BASE_TABLE_ID, PART_TABLE_ID) PART_POS
FROM SYS.SYSHPARTTABLEINFO
CONNECT BY PRIOR PART_TABLE_ID = BASE_TABLE_ID
START WITH BASE_TABLE_ID = TBL_ID
) T_HP
LEFT JOIN SYS.SYSOBJINFOS OBJ ON BASE_TABLE_ID = OBJ.ID
LEFT JOIN SYS.SYSOBJECTS TAB ON TAB.ID = BASE_TABLE_ID
LEFT JOIN SYS.SYSOBJECTS SCH ON SCH.ID = TAB.SCHID
WHERE OBJ.TYPE$ = 'TABPART'
AND SCH.TYPE$ = 'SCH'
AND TAB.SUBTYPE$ = 'UTAB'
AND TAB.TYPE$ = 'SCHOBJ'
)
A
LEFT JOIN SYS.SYSOBJECTS O_IDX ON O_IDX.PID = A.PART_TABLE_ID
LEFT JOIN SYS.SYSINDEXES S_IDX ON S_IDX.ID = O_IDX.ID
LEFT JOIN SYS.V$TABLESPACE TS ON TS.ID = S_IDX.GROUPID
WHERE
O_IDX.SUBTYPE$='INDEX' AND (S_IDX.XTYPE & 0x00000001) = 0x00000000
ORDER BY A.BASE_TABLE_ID, PART_POS;
DELETE FROM SYSDBA.DMHS_DDL_PART WHERE ROOT_ID = TBL_ID;
ELSE
--这里只允许ALTER事件
SELECT ID INTO TBL_ID FROM SYSDBA.DMHS_DDL_RENAME WHERE OBJ_SCHNAME = :EVENTINFO.SCHEMANAME AND OBJ_NAME = :EVENTINFO.OBJECTNAME;
--判断是否为RENAME
SELECT NAME INTO NEW_TAB_NAME FROM SYS.SYSOBJECTS WHERE ID = TBL_ID;
IF NEW_TAB_NAME != :EVENTINFO.OBJECTNAME THEN
UPDATE SYSDBA.DMHS_DDL_RENAME SET OBJ_NAME = NEW_TAB_NAME WHERE ID = TBL_ID;
END IF;
DELETE FROM SYSDBA.DMHS_DDL_RENAME WHERE ID = TBL_ID;
END IF;
INSERT INTO SYSDBA.DMHS_DDL_COL SELECT
ID,
COLID,
NAME,
TYPE$,
LENGTH$,
SCALE,
NULLABLE$,
DEFVAL
FROM SYS.SYSCOLUMNS WHERE ID = TBL_ID;
--修改表操作：添加或者删除约束
IF :EVENTINFO.OPTYPE = 'ALTER' THEN
SELECT COUNT(*) INTO CONS_OLD_FLAG FROM SYSDBA.DMHS_DDL_CONS WHERE TABLEID = TBL_ID;
SELECT COUNT(*) INTO CONS_NEW_FLAG FROM SYS.SYSCONS WHERE TABLEID = TBL_ID;
IF CONS_NEW_FLAG > CONS_OLD_FLAG THEN --ADD
INSERT INTO SYSDBA.DMHS_DDL_CONS
SELECT S_CONS.ID,
O_CONS.NAME,
'ADD',
S_CONS.TABLEID, S_CONS.TYPE$, DECODE((C_IDX.XTYPE & 0X01), 0, 'Y', 'N') ISDATA, S_CONS.VALID, C_IDX.KEYNUM, C_IDX.KEYINFO,
F_SCH.NAME, F_TAB.NAME, F_TAB.ID, F_IDX.KEYNUM, F_IDX.KEYINFO, S_CONS.CHECKINFO
FROM SYS.SYSCONS S_CONS LEFT JOIN SYS.SYSOBJECTS O_CONS ON S_CONS.ID = O_CONS.ID LEFT JOIN SYS.SYSINDEXES C_IDX ON S_CONS.INDEXID = C_IDX.ID
LEFT JOIN SYS.SYSINDEXES F_IDX ON S_CONS.FINDEXID = F_IDX.ID LEFT JOIN SYS.SYSOBJECTS F_OBJ ON F_IDX.ID = F_OBJ.ID
LEFT JOIN SYS.SYSOBJECTS F_TAB ON F_TAB.ID = F_OBJ.PID LEFT JOIN SYS.SYSOBJECTS F_SCH ON F_SCH.ID = F_TAB.SCHID
WHERE S_CONS.TABLEID = TBL_ID AND S_CONS.ID NOT IN (SELECT ID FROM SYSDBA.DMHS_DDL_CONS WHERE TABLEID = TBL_ID);
ELSIF CONS_NEW_FLAG < CONS_OLD_FLAG THEN --DROP
INSERT INTO SYSDBA.DMHS_DDL_CONS (ID, NAME, OPTYPE, TABLEID, TYPE, VALID)
SELECT ID,
NAME,
'DROP',
TABLEID,
TYPE,
VALID
FROM SYSDBA.DMHS_DDL_CONS
WHERE TABLEID = TBL_ID AND ID NOT IN (SELECT ID FROM SYS.SYSCONS WHERE TABLEID = TBL_ID);
ELSE
INSERT INTO SYSDBA.DMHS_DDL_CONS (ID, NAME, OPTYPE, TABLEID, TYPE, VALID)
SELECT D.ID,
D.NAME,
'MODIFY',
D.TABLEID,
D.TYPE,
S.VALID
FROM SYSDBA.DMHS_DDL_CONS D, SYS.SYSCONS S
WHERE D.TABLEID = TBL_ID AND S.TABLEID = TBL_ID AND D.TABLEID = S.TABLEID AND D.ID = S.ID AND D.VALID != S.VALID;
END IF;
DELETE FROM SYSDBA.DMHS_DDL_CONS WHERE TABLEID = TBL_ID;
--处理分区表的修改操作
IF EXISTS (SELECT TOP 1 1 FROM SYSDBA.DMHS_DDL_PART WHERE ROOT_ID = TBL_ID) THEN
SELECT COUNT(*) INTO OLD_HP_CNT FROM SYSDBA.DMHS_DDL_PART WHERE PART_NAME IS NOT NULL AND PART_NAME NOT IN (SELECT PARTITION_NAME FROM SYS.SYSHPARTTABLEINFO WHERE BASE_TABLE_ID = TBL_ID) AND BASE_TABID = TBL_ID;
SELECT COUNT(*) INTO NEW_HP_CNT FROM SYS.SYSHPARTTABLEINFO WHERE PARTITION_NAME NOT IN (SELECT PART_NAME FROM SYSDBA.DMHS_DDL_PART WHERE BASE_TABID = TBL_ID) AND BASE_TABLE_ID = TBL_ID;
HP_INS_FLAG = 0;
IF NEW_HP_CNT = 1 THEN
IF OLD_HP_CNT = 0 THEN
HP_OP_TYPE = 'ADD';
HP_INS_FLAG = 0X10;
ELSIF OLD_HP_CNT = 2 THEN
HP_OP_TYPE = 'MERGE';
HP_INS_FLAG = 0X11;
END IF;
ELSIF OLD_HP_CNT = 1 THEN
IF NEW_HP_CNT = 0 THEN
HP_OP_TYPE = 'DROP';
HP_INS_FLAG = 0X01;
ELSIF NEW_HP_CNT = 2 THEN
HP_OP_TYPE = 'SPLIT';
HP_INS_FLAG = 0X11;
END IF;
END IF;
IF (HP_INS_FLAG & 0X01) = 0X01 THEN
INSERT INTO SYSDBA.DMHS_DDL_PART(OP_TYPE, ROOT_ID, BASE_TABID, PART_TABID, PART_NAME)
SELECT HP_OP_TYPE, TBL_ID, BASE_TABID, PART_TABID, PART_NAME FROM SYSDBA.DMHS_DDL_PART WHERE PART_NAME NOT IN (SELECT PARTITION_NAME FROM SYS.SYSHPARTTABLEINFO WHERE BASE_TABLE_ID = TBL_ID) AND BASE_TABID = TBL_ID;
END IF;
IF (HP_INS_FLAG & 0X10) = 0X10 THEN
INSERT INTO SYSDBA.DMHS_DDL_PART
SELECT HP_OP_TYPE, TBL_ID, A.BASE_TABLE_ID BASE_TABID,A.PART_TABLE_ID PART_TABID,A.INFO1,A.INFO3,:EVENTINFO.SCHEMANAME,:EVENTINFO.OBJECTNAME,
A.PARTITION_NAME PART_NAME,A.HIGH_VALUE,A.INCLUDE_HIGH_VALUE INCLUDE_HVAL, A.PART_POS,A.PART_COLINFO,NULL,NULL,NULL,TS.NAME TS_NAME,NULL
FROM (SELECT BASE_TABLE_ID, PART_TABLE_ID, PARTITION_TYPE, PARTITION_NAME,SF_GET_PART_HIGH_VALUE(BASE_TABLE_ID, PART_TABLE_ID) HIGH_VALUE,INCLUDE_HIGH_VALUE,
SF_GET_PART_SEQNO(BASE_TABLE_ID, PART_TABLE_ID) PART_POS,OBJ.BIN_VALUE PART_COLINFO,TAB.INFO1, TAB.INFO3
FROM SYS.SYSOBJINFOS OBJ LEFT JOIN SYS.SYSHPARTTABLEINFO HPART ON BASE_TABLE_ID = OBJ.ID
LEFT JOIN SYS.SYSOBJECTS TAB ON TAB.ID = PART_TABLE_ID
WHERE OBJ.TYPE$ = 'TABPART' AND TAB.TYPE$ = 'SCHOBJ' AND TAB.SUBTYPE$ = 'UTAB'
AND OBJ.ID = TBL_ID AND HPART.PARTITION_NAME NOT IN (SELECT PART_NAME FROM SYSDBA.DMHS_DDL_PART WHERE BASE_TABID = TBL_ID)
)A
LEFT JOIN SYS.SYSOBJECTS O_IDX ON O_IDX.PID = A.PART_TABLE_ID
LEFT JOIN SYS.SYSINDEXES S_IDX ON S_IDX.ID = O_IDX.ID
LEFT JOIN SYS.V$TABLESPACE TS ON TS.ID = S_IDX.GROUPID
WHERE
O_IDX.SUBTYPE$='INDEX' AND (S_IDX.XTYPE & 0x00000001) = 0x00000000;
END IF;
IF OLD_HP_CNT = 0 AND NEW_HP_CNT = 0 AND
NOT EXISTS ( SELECT TOP 1 1 FROM SYSDBA.DMHS_DDL_PART PART, SYSINDEXES S_IDX, SYSOBJECTS O_IDX, SYSOBJECTS O_TAB
WHERE S_IDX.ID = O_IDX.ID AND O_IDX.PID = O_TAB.ID AND O_IDX.SUBTYPE$ = 'INDEX' AND (S_IDX.XTYPE & 0X00000001) = 0X00000000 AND O_TAB.ID = TBL_ID
AND PART.GROUP_ID = S_IDX.GROUPID AND PART.ROOT_FILE = S_IDX.ROOTFILE AND PART.ROOT_PAGE = S_IDX.ROOTPAGE
) THEN
INSERT INTO SYSDBA.DMHS_DDL_PART(OP_TYPE, ROOT_ID, BASE_TABID, PART_TABID, PART_NAME)
SELECT 'EXCHANGE', TBL_ID, TBL_ID, O_TAB.ID, O_TAB.NAME
FROM SYSINDEXES S_IDX, SYSOBJECTS O_IDX, SYSOBJECTS O_TAB, SYSDBA.DMHS_DDL_PART O_PART
WHERE GROUPID = O_PART.GROUP_ID AND ROOTFILE = O_PART.ROOT_FILE
AND ROOTPAGE = O_PART.ROOT_PAGE AND O_IDX.SUBTYPE$ = 'INDEX' AND (S_IDX.XTYPE & 0X00000001) = 0X00000000
AND S_IDX.ID = O_IDX.ID AND O_IDX.PID = O_TAB.ID AND O_PART.BASE_TABID = TBL_ID;
END IF;
DELETE FROM SYSDBA.DMHS_DDL_PART WHERE ROOT_ID = TBL_ID;
END IF;
END IF;
--INDEX
ELSIF :EVENTINFO.OBJECTTYPE = 'INDEX' THEN
IF :EVENTINFO.OPTYPE = 'CREATE' THEN
SELECT SO.ID INTO TBL_ID FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.SCHID = SC.ID AND SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.TYPE$ = 'TABOBJ' AND SO.SUBTYPE$ = 'INDEX';
INSERT INTO SYSDBA.DMHS_DDL_SQL VALUES(
:EVENTINFO.OPTYPE,
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
:EVENTINFO.OBJECTTYPE,
TBL_ID,--INDEX ID
SF_CUR_SQL_STR(1),
:EVENTINFO.OPTIME);
INSERT INTO SYSDBA.DMHS_DDL_IDX SELECT
SI.ID,
SO.PID,
SI.XTYPE,
SI.FLAG,
SI.KEYNUM,
SI.KEYINFO
FROM SYS.SYSINDEXES SI, SYS.SYSOBJECTS SO WHERE SI.ID = SO.ID AND SI.ID = TBL_ID AND SO.ID = TBL_ID;
END IF;
ELSIF :EVENTINFO.OBJECTTYPE = 'SEQUENCE' THEN
INSERT INTO SYSDBA.DMHS_DDL_SEQ SELECT
:EVENTINFO.SCHEMANAME,
:EVENTINFO.OBJECTNAME,
SO.ID,
SO.INFO1,
SO.INFO3,
SO.INFO4,
SO.INFO5
FROM SYS.SYSOBJECTS SO, SYS.SYSOBJECTS SC WHERE SO.NAME = :EVENTINFO.OBJECTNAME AND SC.NAME = :EVENTINFO.SCHEMANAME AND SO.SCHID = SC.ID;
END IF;
EXCEPTION
WHEN E1 THEN RAISE E1;
WHEN NO_DATA_FOUND THEN
RETURN;
END;
/
--12.END OF SCRIPT
2.5.1检查创建的触发器和辅助表是否有效
select owner, table_name from dba_tables where owner = 'SYSDBA' and table_name like 'DMHS%' and status = 'VALID';
2.5.2检查触发器
select owner, trigger_name from dba_triggers where owner = 'SYSDBA' and trigger_name like 'DMHS%' and status = 'Y';
2.6配置源端dmhs.hs
一般情况下，建议新建dmhs.hs需要新bin2、bin3，区分cpt与exec端，保留源bin文件夹，用于后续升级版本、配stat等功能。
vi /home/dmhs/bin2/dmhs.hs
<?xml version="1.0" encoding="GB2312" standalone="no"?>
<dmhs>
<base>
<lang>ch</lang>
<mgr_port>5345</mgr_port>
<chk_interval>3</chk_interval>
<ckpt_interval>60</ckpt_interval>
<group>1</group>
<siteid>5</siteid>
<version>2.0</version>
</base>
<exec>
<recv>
<data_port>5346</data_port>
</recv>
<db_type>DM7</db_type>
<db_server>DM</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<level>65536</level>
<exec_thr>4</exec_thr>
<exec_sql>1024</exec_sql>
<exec_trx>5000</exec_trx>
<exec_rows>250</exec_rows>
<case_sensitive>1</case_sensitive>
<toggle_case>0</toggle_case>
<exec_policy>2</exec_policy>
<commit_policy>1</commit_policy>
<enable_merge>1</enable_merge>
<affect_row>0</affect_row>
<ddl_log>1</ddl_log>
</exec>
<cpt>
<db_type>DM7</db_type>
<db_server>DM</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<idle_time>10</idle_time>
<read_again_interval>255</read_again_interval>
<ddl_mask>op:obj:rec</ddl_mask>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.110.143</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<standby>
<ip>192.168.110.144</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
</standby>
<net_pack_size>256</net_pack_size>
<net_turns>0</net_turns>
<crc_check>1</crc_check>
<trigger>1</trigger>
<constraint>1</constraint>
<identity>1</identity>
<filter>
<enable>
<item>TEST.TEST1</item>
</enable>
<disable/>
</filter>
<map>
<item>TEST.TEST1==TEST.TABLE1</item>
</map>
</send>
</cpt>
</dmhs>
2.7配置目的端dmhs.hs
vi /home/dmhs/bin2/dmhs.hs
<?xml version="1.0" encoding="GB2312" standalone="no"?>
<dmhs>
<base>
<lang>ch</lang>
<mgr_port>5345</mgr_port>
<chk_interval>3</chk_interval>
<ckpt_interval>60</ckpt_interval>
<group>1</group>
<siteid>12</siteid>
<version>2.0</version>
</base>
<exec>
<recv>
<data_port>5350</data_port>
</recv>
<db_type>DM7</db_type>
<db_server>IDCDM</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<level>65536</level>
<exec_thr>4</exec_thr>
<exec_sql>1024</exec_sql>
<exec_trx>5000</exec_trx>
<exec_rows>250</exec_rows>
<case_sensitive>1</case_sensitive>
<toggle_case>0</toggle_case>
<exec_policy>2</exec_policy>
<commit_policy>1</commit_policy>
<enable_merge>1</enable_merge>
<affect_row>0</affect_row>
<ddl_log>1</ddl_log>
</exec>
<cpt>
<db_type>DM7</db_type>
<db_server>DM</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<idle_time>10</idle_time>
<read_again_interval>255</read_again_interval>
<ddl_mask>op:obj:rec</ddl_mask>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.110.141</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<standby>
<ip>192.168.110.142</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
</standby>
<net_pack_size>256</net_pack_size>
<net_turns>0</net_turns>
<crc_check>1</crc_check>
<trigger>1</trigger>
<constraint>1</constraint>
<identity>1</identity>
<filter>
<enable>
<item>TEST.TEST1</item>
</enable>
<disable/>
</filter>
<map>
<item>TEST.TEST1==TEST.TABLE1</item>
</map>
</send>
</cpt>
</dmhs>
注意事项： 1、源端、目的端siteid不能一致；
2、dbserver要与dm_svc.conf中的服务名一致。
三、启动HS服务
3.1启动源端dmhs服务
进入源端bin目录，执行启动脚本（或先修改启动脚本）。
cd /home/dmhs/bin2
./DmhsServiceHSSERVER start
3.2启动目的端dmhs服务
进入目的端bin目录，执行启动脚本（或先修改启动脚本）。
cd /home/dmhs/bin2
./DmhsServiceHSSERVER start
3.3开启集群各主库的dmhs接收服务（双向两台）
./dmhs_console
connect （connect 127.0.0.1:5345/5346 默认是5345）
start exec
3.4源端主库装载数据
./dmhs_console
connect （connect 127.0.0.1:5345/5346 默认是5345）
※clear exec lsn （慎用，除非确认hs同步停止前后，源端、目的两端数据无变化，否则，清楚lsn将造成不读取旧归档文件，造成数据不一致；新表不用执行）
copy 0 “sch.name=TEST” COMMENT|CREATE|INSERT|INDEX|REG
或者装载表格：
copy 0 “sch.name =’ TEST ’ and tab.name=TEST1” DROP|CREATE|INSERT|INDEX|COMMENT|REG
3.5目的端主库装载数据字典
copy 0 “sch.name=’TEST’” DICT
3.6开启主库的dmhs发送服务（双向两台）
start cpt
3.7检查cpt与exec发送、读取归档时间
使用cpt、exec检查归档日志读取进去，再检查归档目录下归档文件是否为最新归档，如数据非最新归档数据，需要观察读取进度，直至归档数据同步到当前归档文件。
200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
