MPP主备集群搭建

2022/7/10 23:55:27

编程Tag： 集群 10 07 主备 18 2022 MPP GRP1

本文主要是介绍MPP主备集群搭建，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录
  • 部署环境
  • 部署规划
    • 主库
    • 备库
    • 守护进程规划
  • 数据准备
    • 初始化实例
      • 主库
      • 备库
    • 注册服务
      • 主库
      • 备库
    • 启动主库
    • 配置归档
    • 备份还原
      • 联机备份EP01并脱机还原到EP11
      • 联机备份EP02并脱机还原到EP22
  • 配置文件
    • 配置主库 GRP1_MPP_EP01
      • dm.ini
      • dmmal.ini
      • dmarch.ini
      • dmmpp.ctl
      • 启动主库
      • 设置OGUID
      • 修改数据库模式
    • 配置主库GRP2_MPP_EP02
      • dm.ini
      • dmmal.ini
      • dmarch.ini
      • dmmpp.ctl
      • 启动主库
      • 设置OGUID
      • 修改数据库模式
    • 配置备库GRP1_MPP_EP11
      • dm.ini
      • dmmal.ini
      • dmarch.ini
      • dmmpp.ctl
      • 启动备库
      • 设置OGUID
      • 修改数据库模式
    • 配置备库GRP2_MPP_EP22
      • dm.ini
      • dmmal.ini
      • dmarch.ini
      • dmmpp.ctl
      • 启动备库
      • 设置OGUID
      • 修改数据库模式
  • 配置dmwatcher
  • 配置dmmonitor
  • 启动watcher
  • 启动monitor
  • 客户端配置
  • 测试验证

部署环境

机器名 IP地址 初始状态 操作系统
A 8.142.141.128
172.21.215.74 (内网IP)		 主库 GRP1_MPP_EP01
备库 GRP2_MPP_EP22		 Debian 9
B 39.103.139.80
172.17.44.74 (内网IP)		 主库 GRP1_MPP_EP02
备库 GRP2_MPP_EP11		 Debian 9
C 本机 监视器 windows 11

部署规划

主库

实例名 PORT_NUM MAL_INST_DW_PORT MAL_HOST MAL_PORT MAL_DW_PORT MPP_SEQNO
GRP1_MPP_EP01 5237 5243 172.21.215.74 5337 5253 0
GRP2_MPP_EP02 5237 5243 172.17.44.74 5337 5253 1

备库

实例名 PORT_NUM MAL_INST_DW_PORT MAL_HOST MAL_PORT MAL_DW_PORT MPP_SEQNO
GRP1_MPP_EP11 5238 5244 172.17.44.74 5338 5254 0
GRP2_MPP_EP22 5238 5244 172.21.215.74 5338 5254 1

守护进程规划

组名 实例名 所在机器
GRP1 GRP1_MPP_EP01 172.21.215.74
GRP1_MPP_EP11 172.17.44.74
GRP2 GRP2_MPP_EP02 172.17.44.74
GRP2_MPP_EP22 172.21.215.74

数据准备

初始化实例

主库

--A机器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP01 INSTANCE_NAME=GRP1_MPP_EP01 PORT_NUM=5237

--B机器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP02 INSTANCE_NAME=GRP2_MPP_EP02 PORT_NUM=5237

备库

--A机器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP22 INSTANCE_NAME=GRP2_MPP_EP22 PORT_NUM=5238

--B机器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP11 INSTANCE_NAME=GRP1_MPP_EP11 PORT_NUM=5238

注册服务

主库

./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP01/dm.ini -p EP01
./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP02/dm.ini -p EP02

备库

./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP22/dm.ini -p EP22
./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP11/dm.ini -p EP11

启动主库

root@lxm2:/home/dmdba/dmdbms/script/root# systemctl start DmServiceEP01.service 
root@lxm:/home/dmdba/dmdbms/script/root# systemctl start DmServiceEP02.service

配置归档

--A机器
dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237

Server[localhost:5237]:mode is normal, state is open
login used time : 2.574(ms)
disql V8
SQL> ALTER DATABASE MOUNT;
executed successfully
used time: 0.643(ms). Execute id is 0.
SQL> ALTER DATABASE ADD ARCHIVELOG 'dest=/dm8/data/EP01/dm_arch,type=local,file_size=64,space_limit=20480';
executed successfully
used time: 3.270(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 7.166(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 18.316(ms). Execute id is 0.
SQL> SELECT ARCH_MODE FROM V$DATABASE;

LINEID     ARCH_MODE
---------- ---------
1          Y

used time: 2.610(ms). Execute id is 55400.


--B机器
ALTER DATABASE MOUNT;
ALTER DATABASE ADD ARCHIVELOG 'dest=/dm8/data/EP02/dm_arch,type=local,file_size=64,space_limit=20480';
alter database archivelog;
alter database open;
SELECT ARCH_MODE FROM V$DATABASE;

备份还原

联机备份EP01并脱机还原到EP11

--备份
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/BACKUP_FILE_01';
executed successfully
used time: 00:00:03.753. Execute id is 55501.
SQL> exit

--拷贝
dmdba@lxm2:/dm8/data$ scp -r BACKUP_FILE_01/ dmdba@39.103.139.80:/dm8/data
dmdba@39.103.139.80's password: 
BACKUP_FILE_01.bak                                                                                                                                       100%   89MB  13.4MB/s   00:06    
BACKUP_FILE_01.meta                                                                                                                                      100%   89KB   8.5MB/s   00:00    
BACKUP_FILE_01_1.bak                                                                                                                                     100%  485KB  15.0MB/s   00:00    
--备机还原
dmdba@lxm:~$ rlwrap dmrman
dmrman V8
RESTORE DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
RESTORE DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
file dm.key not found, use default license!
RECOVER DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]                                 
restore successfully.
time used: 00:00:02.765
RMAN> RECOVER DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[37302], file_lsn[37302]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]                               
recover successfully!
time used: 00:00:02.540
RMAN> RECOVER DATABASE '/dm8/data/EP11/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dm8/data/EP11/dm.ini' UPDATE DB_MAGIC;
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[38498], file_lsn[38498]
recover successfully!
time used: 00:00:01.055

联机备份EP02并脱机还原到EP22

--备份
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/BACKUP_FILE_02';
--拷贝
dmdba@lxm2:/dm8/data$ scp -r BACKUP_FILE_02/ dmdba@8.142.141.128:/dm8/data
--备库还原
RESTORE DATABASE '/dm8/data/EP22/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_02';
RECOVER DATABASE '/dm8/data/EP22/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_02';
RECOVER DATABASE '/dm8/data/EP22/dm.ini' UPDATE DB_MAGIC;

配置文件

配置主库 GRP1_MPP_EP01

dm.ini

INSTANCE_NAME = GRP1_MPP_EP01
PORT_NUM = 5237 #数据库实例监听端口
DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间
ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID
ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间
MAL_INI = 1 #打开 MAL 系统
ARCH_INI = 1 #打开归档配置
MPP_INI = 1 #启用 MPP 配置
RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志发送信息

dmmal.ini

MAL_CHECK_INTERVAL = 5 #MAL 链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 5 #判定 MAL 链路断开的时间
[MAL_INST1]
 MAL_INST_NAME = GRP1_MPP_EP01 #实例名，和 dm.ini 中的 INSTANCE_NAME 一致
 MAL_HOST = 172.21.215.74 #MAL 系统监听 TCP 连接的 IP 地址
 MAL_PORT = 5337 #MAL 系统监听 TCP 连接的端口
 MAL_INST_HOST = 8.142.141.128 #实例的对外服务 IP 地址
 MAL_INST_PORT = 5237 #实例的对外服务端口，和 dm.ini 中的 PORT_NUM 一致
 MAL_DW_PORT = 5253 #实例对应的守护进程监听 TCP 连接的端口
 MAL_INST_DW_PORT = 5243 #实例监听守护进程 TCP 连接的端口
[MAL_INST2]
 MAL_INST_NAME = GRP2_MPP_EP02
 MAL_HOST = 172.17.44.74 
 MAL_PORT = 5337
 MAL_INST_HOST = 39.103.139.80
 MAL_INST_PORT = 5237
 MAL_DW_PORT = 5253
MAL_INST_DW_PORT = 5243
[MAL_INST3]
 MAL_INST_NAME = GRP1_MPP_EP11
 MAL_HOST = 172.17.44.74 
 MAL_PORT = 5338
 MAL_INST_HOST = 39.103.139.80
 MAL_INST_PORT = 5238
 MAL_DW_PORT = 5254
MAL_INST_DW_PORT = 5244
[MAL_INST4]
 MAL_INST_NAME = GRP2_MPP_EP22
 MAL_HOST = 172.21.215.74
 MAL_PORT = 5338
 MAL_INST_HOST = 8.142.141.128
 MAL_INST_PORT = 5238
 MAL_DW_PORT = 5254
 MAL_INST_DW_PORT = 5244

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = GRP1_MPP_EP11 #实时归档目标实例名
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /dm8/data/EP01/arch #本地归档文件存放路径
ARCH_FILE_SIZE = 128 #单位 Mb，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 0 #单位 Mb，0 表示无限制，范围 1024~4294967294M

dmmpp.ctl

--新建dmmpp.ini
[service_name1]
mpp_seq_no = 0
mpp_inst_name = GRP1_MPP_EP01
[service_name2] 
mpp_seq_no = 1
mpp_inst_name = GRP2_MPP_EP02

--生成dmmpp.ctl
dmctlcvt TYPE=2 SRC=/dm8/data/EP01/dmmpp.ini DEST=/dm8/data/EP01/dmmpp.ctl

启动主库

dmserver /dm/data/EP01/dm.ini mount

设置OGUID

dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237#"{mpp_type=local}"

Server[localhost:5237]:mode is normal, state is mount
login used time : 1.708(ms)
disql V8
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45330);
DMSQL executed successfully
used time: 9.792(ms). Execute id is 0.
SQL> DMSQL executed successfully
used time: 9.320(ms). Execute id is 1.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL executed successfully
used time: 4.399(ms). Execute id is 2.

修改数据库模式

SQL>alter database primary;

配置主库GRP2_MPP_EP02

dm.ini

INSTANCE_NAME = GRP2_MPP_EP02
PORT_NUM = 5237 #数据库实例监听端口
DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间
ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID
ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间
MAL_INI = 1 #打开 MAL 系统
ARCH_INI = 1 #打开归档配置
MPP_INI = 1 #启用 MPP 配置
RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志发送信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = GRP2_MPP_EP22 #实时归档目标实例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /dm8/data/EP02/arch#本地归档文件存放路径
ARCH_FILE_SIZE = 128 #单位 Mb，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 0 #单位 Mb，0 表示无限制，范围 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

启动主库

dmserver /dm8/data/EP02/dm.ini mount

设置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45331);
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

修改数据库模式

alter database primary;

配置备库GRP1_MPP_EP11

dm.ini

INSTANCE_NAME = GRP1_MPP_EP11
PORT_NUM = 5238 #数据库实例监听端口
DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间
ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID
ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间
MAL_INI = 1 #打开 MAL 系统
ARCH_INI = 1 #打开归档配置
MPP_INI = 1 #打开 MPP 配置
RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志重演信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = GRP1_MPP_EP01 #实时归档目标实例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /dm8/data/EP11/arch#本地归档文件存放路径
ARCH_FILE_SIZE = 128 #单位 Mb，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 0 #单位 Mb，0 表示无限制，范围 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

启动备库

dmserver /dm/data/EP11/DAMENG/dm.ini mount

设置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45330);
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

修改数据库模式

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); 
ALTER DATABASE STANDBY; 
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

配置备库GRP2_MPP_EP22

dm.ini

INSTANCE_NAME = GRP2_MPP_EP22
PORT_NUM = 5238 #数据库实例监听端口
DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间
ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID
ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间
MAL_INI = 1 #打开 MAL 系统
ARCH_INI = 1 #打开归档配置
MPP_INI = 1 #打开 MPP 配置
RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志重演信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = GRP2_MPP_EP02 #实时归档目标实例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地归档类型
ARCH_DEST = /dm8/data/EP22/arch #本地归档文件存放路径
ARCH_FILE_SIZE = 128 #单位 Mb，本地单个归档文件最大值
ARCH_SPACE_LIMIT = 0 #单位 Mb，0 表示无限制，范围 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

启动备库

dmserver /dm8/data/EP22/dm.ini mount

设置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45331);
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

修改数据库模式

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); 
ALTER DATABASE STANDBY; 
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

配置dmwatcher

#A机器
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #自动切换模式
DW_ERROR_TIME = 10 #远程守护进程故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_ERROR_TIME = 10 #本地实例故障认定时间
INST_OGUID = 45330 #守护系统唯一 OGUID 值
INST_INI = /dm8/data/EP01/dm.ini #dm.ini 配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭
[GRP2]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #自动切换模式
DW_ERROR_TIME = 10 #远程守护进程故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_ERROR_TIME = 10 #本地实例故障认定时间
INST_OGUID = 45331 #守护系统唯一 OGUID 值
INST_INI = /dm8/data/EP22/dm.ini #dm.ini 配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭

#B机器
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #自动切换模式
DW_ERROR_TIME = 10 #远程守护进程故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_ERROR_TIME = 10 #本地实例故障认定时间
INST_OGUID = 45330 #守护系统唯一 OGUID 值
INST_INI = /dm8/data/EP11/dm.ini #dm.ini 配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭
[GRP2]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #自动切换模式
DW_ERROR_TIME = 10 #远程守护进程故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_ERROR_TIME = 10 #本地实例故障认定时间
INST_OGUID = 45331 #守护系统唯一 OGUID 值
INST_INI = /dm8/data/EP02/dm.ini #dm.ini 配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭

配置dmmonitor

MON_DW_CONFIRM = 1 #确认监视器模式
MON_LOG_PATH = C:\dmdbms #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔 60s 定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 32 #每个日志文件最大 32M
MON_LOG_SPACE_LIMIT = 0 #不限定日志文件总占用空间
[GRP1]
MON_INST_OGUID = 45330 #组 GRP1 的唯一 OGUID 值
#以下配置为监视器到组 GRP1 的守护进程的连接信息，以―IP:PORT‖的形式配置
#IP 对应 dmmal.ini 中的 MAL_HOST，PORT 对应 dmmal.ini 中的 MAL_DW_PORT
MON_DW_IP = 8.142.141.128:5253
MON_DW_IP = 39.103.139.80:5254
[GRP2]
MON_INST_OGUID = 45331 #组 GRP2 的唯一 OGUID 值
#以下配置为监视器到组 GRP2 的守护进程的连接信息，以―IP:PORT‖的形式配置
#IP 对应 dmmal.ini 中的 MAL_HOST，PORT 对应 dmmal.ini 中的 MAL_DW_PORT
MON_DW_IP = 8.142.141.128:5254
MON_DW_IP = 39.103.139.80:5253

启动watcher

#A机器，B机器也相同。
dmdba@lxm2:~$ dmwatcher /dm8/data/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
show
2022-07-10 18:08:44
---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART DW_STATUS       DW_SUB_STATUS   DW_CTL_STATUS   
GRP1             GLOBAL    AUTO      45330       TRUE      TRUE         STARTUP         SUB_STATE_START VALID           

INST_OK   NAME             SVR_MODE  SYS_STATUS   RTYPE     FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG 
OK        GRP1_MPP_EP01    PRIMARY   MOUNT        REALTIME  5810            39581           5810            39581           0            
---------------------------------------------------------------------------

---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART DW_STATUS       DW_SUB_STATUS   DW_CTL_STATUS   
GRP2             GLOBAL    AUTO      45331       TRUE      TRUE         STARTUP         SUB_STATE_START VALID           

INST_OK   NAME             SVR_MODE  SYS_STATUS   RTYPE     FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG 
OK        GRP2_MPP_EP22    STANDBY   MOUNT        REALTIME  5084            38632           5084            38632           0            

DATABASE(GRP2_MPP_EP22) APPLY INFO:
REDOS_PARALLEL_NUM (1) 
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[5084, 5084, 5084], (RLSN, SLSN, KLSN)[38632, 38632, 38632], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (38632)

---------------------------------------------------------------------------

启动monitor

C:\dmdbms\bin>dmmonitor.exe C:\dmdbms\data\dmmonitor.ini
[monitor]         2022-07-10 18:11:20: DMMONITOR[4.0] V8
[monitor]         2022-07-10 18:11:20: DMMONITOR[4.0] IS READY.

[monitor]         2022-07-10 18:11:20: Received message from(GRP2_MPP_EP22)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP2_MPP_EP22    OPEN        STANDBY   NULL     3        42257           42257

[monitor]         2022-07-10 18:11:20: Received message from(GRP1_MPP_EP01)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        41955           41955

[monitor]         2022-07-10 18:11:20: Received message from(GRP2_MPP_EP02)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP2_MPP_EP02    OPEN        PRIMARY   VALID    3        42257           42258

[monitor]         2022-07-10 18:11:20: Received message from(GRP1_MPP_EP11)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP1_MPP_EP11    OPEN        STANDBY   VALID    3        41954           41954

客户端配置

dmsvc.conf

测试验证

(a)测试建表和插入数据是否会根据分布列进行自动分发。
(b)测试故障一个节点后，集群和持续提供服务。

dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237#"{mpp_type=global}"

Server[localhost:5237]:mode is primary, state is open
login used time : 2.571(ms)
disql V8
SQL> select * from V$INSTANCE;

LINEID     NAME          INSTANCE_NAME INSTANCE_NUMBER HOST_NAME SVR_VERSION                DB_VERSION          START_TIME          STATUS$ MODE$   OGUID       DSC_SEQNO   DSC_ROLE
---------- ------------- ------------- --------------- --------- -------------------------- ------------------- ------------------- ------- ------- ----------- ----------- --------
           BUILD_VERSION                      BUILD_TIME          
           ---------------------------------- --------------------
1          GRP1_MPP_EP01 GRP1_MPP_EP01 1               lxm2      DM Database Server x64 V8  DB Version: 0x7000c 2022-07-10 16:20:56 OPEN    PRIMARY 45330       0           NULL
           1-2-114-22.05.25-161267-10045-ENT  May 25 2022 11:22:12

2          GRP2_MPP_EP02 GRP2_MPP_EP02 2               lxm       DM Database Server x64 V8  DB Version: 0x7000c 2022-07-10 16:40:32 OPEN    PRIMARY 45331       0           NULL
           1-2-114-22.05.25-161267-10045-ENT  May 25 2022 11:22:12


used time: 10.279(ms). Execute id is 954698.
SQL> CREATE TABLE T_RANGE(C1 INT,C2 CHAR(10)) DISTRIBUTED BY RANGE(C1)(VALUES EQU OR LESS THAN (100) ON GRP1_MPP_EP01, VALUES LESS THAN(MAXVALUE) ON GRP2_MPP_EP02);
executed successfully
used time: 31.373(ms). Execute id is 601.
SQL> insert into T_RANGE values(1,'Chris'),(51,'Lily'),(101,'Peter'),(120,'Tim'),(200,'Summy');
affect rows 5

used time: 4.900(ms). Execute id is 954711.
SQL> insert into T_RANGE values(1,'Chris'),(51,'Lily'),(181,'Petr'),(320,'Ti'),(900,'Sumy');
affect rows 5

used time: 3.262(ms). Execute id is 954713.
SQL> COMMIT;
executed successfully
used time: 6.231(ms). Execute id is 954714.
SQL> SP_GET_EP_COUNT('SYSDBA','T_RANGE'); 

LINEID     SEQNO       N_ROWS              
---------- ----------- --------------------
1          0           4
2          1           6

used time: 33.026(ms). Execute id is 954715.
SQL>

测试kill掉EP01实例，监视器监控到后，守护进程会把实例自动拉起来。

dmdba@lxm2:~$ ps -ef|grep dms
dmdba    21310 21229  0 16:20 pts/0    00:00:08 dmserver /dm8/data/EP01/dm.ini mount
dmdba    21676 21404  0 17:34 pts/1    00:00:05 dmserver /dm8/data/EP22/dm.ini mount
dmdba    21933 21784  0 18:59 pts/2    00:00:00 grep dms
dmdba@lxm2:~$ kill -9 21310
dmdba@lxm2:~/dmdbms/bin$ ps -ef|grep dms
dmdba    21676 21404  0 17:34 pts/1    00:00:05 dmserver /dm8/data/EP22/dm.ini mount
dmdba    21935     1  1 18:59 ?        00:00:00 /home/dmdba/dmdbms/bin/dmserver /dm8/data/EP01/dm.ini mount
dmdba    22036 21229  0 19:00 pts/0    00:00:00 grep dms

[monitor]         2022-07-10 18:59:37: Instance GRP1_MPP_EP01[PRIMARY, OPEN, ISTAT_SAME:TRUE] error
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:35  STARTUP        ERROR     GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        42983           42984

[monitor]         2022-07-10 18:59:37: Dmwatcher process GRP1_MPP_EP01 status switching [OPEN-->STARTUP]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:35  STARTUP        ERROR     GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        42983           42984


--monitor
[monitor]         2022-07-10 18:59:37: Check primary instance error in group(GRP1), start to auto takeover

[monitor]         2022-07-10 18:59:37: Notify group(GRP1)'s active dmwatcher to set MID
[monitor]         2022-07-10 18:59:37: Notify group(GRP1)'s active dmwatcher to set MID success
[monitor]         2022-07-10 18:59:37: Notify group(GRP2)'s active dmwatcher to set MID
[monitor]         2022-07-10 18:59:37: Notify group(GRP2)'s active dmwatcher to set MID success
[monitor]         2022-07-10 18:59:37: Start to takeover use instance GRP1_MPP_EP11
[monitor]         2022-07-10 18:59:37: Notify dmwatcher(GRP1_MPP_EP11) switch to TAKEOVER status
[monitor]         2022-07-10 18:59:38: Dmwatcher process GRP1_MPP_EP11 status switching [OPEN-->TAKEOVER]
[monitor]         2022-07-10 18:59:38: Switch dmwatcher GRP1_MPP_EP11 to TAKEOVER status success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 7)
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 execute sql SP_SET_GLOBAL_DW_STATUS(0, 7) success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql SP_APPLY_KEEP_PKG()
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 execute sql SP_APPLY_KEEP_PKG() success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE MOUNT
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE MOUNT success
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE PRIMARY
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE PRIMARY success
[monitor]         2022-07-10 18:59:39: Notify instance GRP1_MPP_EP11 to change all arch status to be invalid
[monitor]         2022-07-10 18:59:39: Succeed to change all instances arch status to be invalid
[monitor]         2022-07-10 18:59:39: Build new mppctl file success
[monitor]         2022-07-10 18:59:39: Notify to update instance GRP1_MPP_EP11[PRIMARY, MOUNT, ISTAT_SAME:TRUE] mppctl file
[monitor]         2022-07-10 18:59:39: Notify instance GRP1_MPP_EP11[PRIMARY, MOUNT, ISTAT_SAME:TRUE] update mppctl file success
[monitor]         2022-07-10 18:59:39: Notify to update instance GRP2_MPP_EP02[PRIMARY, OPEN, ISTAT_SAME:TRUE] mppctl file
[monitor]         2022-07-10 18:59:39: Notify dmwatcher(GRP2_MPP_EP02) switch to MPPCTL UPDATE status
[monitor]         2022-07-10 18:59:40: Dmwatcher process GRP2_MPP_EP02 status switching [OPEN-->MPPCTL UPDATE]
[monitor]         2022-07-10 18:59:40: Switch dmwatcher GRP2_MPP_EP02 to MPPCTL UPDATE status success
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 12)
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 execute sql SP_SET_GLOBAL_DW_STATUS(0, 12) success
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 start to execute sql SP_SET_GLOBAL_DW_STATUS(12, 0)
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 execute sql SP_SET_GLOBAL_DW_STATUS(12, 0) success
[monitor]         2022-07-10 18:59:40: Notify dmwatcher(GRP2_MPP_EP02) switch to OPEN status
[monitor]         2022-07-10 18:59:41: Dmwatcher process GRP2_MPP_EP02 status switching [MPPCTL UPDATE-->OPEN]
[monitor]         2022-07-10 18:59:42: Switch dmwatcher GRP2_MPP_EP02 to OPEN status success
[monitor]         2022-07-10 18:59:42: Notify instance GRP2_MPP_EP02[PRIMARY, OPEN, ISTAT_SAME:TRUE] update mppctl file success
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE OPEN FORCE
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE OPEN FORCE success
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 start to execute sql SP_SET_GLOBAL_DW_STATUS(7, 0)
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 execute sql SP_SET_GLOBAL_DW_STATUS(7, 0) success
[monitor]         2022-07-10 18:59:42: Notify dmwatcher(GRP1_MPP_EP11) switch to OPEN status
[monitor]         2022-07-10 18:59:42: Dmwatcher process GRP1_MPP_EP11 status switching [TAKEOVER-->OPEN]
[monitor]         2022-07-10 18:59:43: Switch dmwatcher GRP1_MPP_EP11 to OPEN status success
[monitor]         2022-07-10 18:59:43: Notify group(GRP1)'s dmwatcher to do clear
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP1_MPP_EP01 success
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP1_MPP_EP11 success
[monitor]         2022-07-10 18:59:43: Notify group(GRP2)'s dmwatcher to do clear
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP2_MPP_EP22 success
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP2_MPP_EP02 success
[monitor]         2022-07-10 18:59:43: Success to takeover use instance GRP1_MPP_EP11

[monitor]         2022-07-10 18:59:43: Group(GRP1) use instance GRP1_MPP_EP11 auto takeover success

[monitor]         2022-07-10 18:59:50: Instance GRP1_MPP_EP01[PRIMARY, AFTER REDO, ISTAT_SAME:TRUE] recover to OK
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  STARTUP        OK        GRP1_MPP_EP01    AFTER REDO  PRIMARY   VALID    3        42983           42983

[monitor]         2022-07-10 18:59:50: Dmwatcher process GRP1_MPP_EP01 status switching [STARTUP-->OPEN]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  OPEN           OK        GRP1_MPP_EP01    OPEN        STANDBY   INVALID  3        42983           42983

[monitor]         2022-07-10 18:59:50: Dmwatcher process GRP1_MPP_EP11 status switching [OPEN-->RECOVERY]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  RECOVERY       OK        GRP1_MPP_EP11    OPEN        PRIMARY   VALID    4        45431           45432

[monitor]         2022-07-10 18:59:51: Dmwatcher process GRP1_MPP_EP11 status switching [RECOVERY-->OPEN]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:50  OPEN           OK        GRP1_MPP_EP11    OPEN        PRIMARY   VALID    4        45432           45432

更多内容参见达梦技术社区：https://eco.dameng.com



这篇关于MPP主备集群搭建的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程