达梦主备监视器部署方案

一调整系统参数

1.调整sysctl.conf
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.numa_stat = 0
vm.overcommit_memory=0

2.调整limits.conf
root - nice 0
dmdba - nice 0
root - as unlimited
dmdba - as unlimited
root - fsize unlimited
dmdba - fsize unlimited
root - nproc 131072
dmdba - nproc 131072
root - nofile 131072
dmdba - nofile 131072
root - core unlimited
dmdba - core unlimited
root - data unlimited
dmdba - data unlimited

3.调整profile参数
ulimit -SHn 65536

4.调整login
session required /lib64/security/pam_limits.so
session required pam_limits.so

5.关闭selinux
SELINUX=disabled

二规划用户以及安装目录
1.创建用户
groupadd dinstall -g 2001
useradd -g dinstall dmdba -u 1001
passwd dmdba

2.创建目录以及授权
mkdir /app/dmdata
mkdir /app/dmdbms
mkdir /app/dmsoft
chown dmdba.dinstall /app/dmdbms/ -R;chmod 777 /app/dmdbms/ -R
chown dmdba.dinstall /app/dmdata/ -R;chmod 777 /app/dmdata/ -R
chown dmdba.dinstall /app/dmsoft/ -R;chmod 777 /app/dmsoft/ -R

三安装数据库
数据库版本路径 /app/dmsoft
数据库安装路径 /app/dmdbms
数据库数据路径 /app/dmdata

1.将镜像文件挂载到/app/dmsoft
mount /app/dmsoft/dm8_20220919_x86_kylin10_64_ent_8.1.2.128_pack5.iso /app/dmsoft/
2.dmdba用户执行DMInstall.bin
/app/dmsoft/DMInstall.bin -i
Please select the installer's language (E/e:English C/c:Chinese) [E/e]:c
解压安装程序.........
欢迎使用达梦数据库安装程序

是否输入Key文件路径? (Y/y:是 N/n:否) [Y/y]:n

是否设置时区? (Y/y:是 N/n:否) [Y/y]:y
设置时区:
[ 1]: GTM-12=日界线西
[ 2]: GTM-11=萨摩亚群岛
[ 3]: GTM-10=夏威夷
[ 4]: GTM-09=阿拉斯加
[ 5]: GTM-08=太平洋时间（美国和加拿大）
[ 6]: GTM-07=亚利桑那
[ 7]: GTM-06=中部时间（美国和加拿大）
[ 8]: GTM-05=东部部时间（美国和加拿大）
[ 9]: GTM-04=大西洋时间（美国和加拿大）
[10]: GTM-03=巴西利亚
[11]: GTM-02=中大西洋
[12]: GTM-01=亚速尔群岛
[13]: GTM=格林威治标准时间
[14]: GTM+01=萨拉热窝
[15]: GTM+02=开罗
[16]: GTM+03=莫斯科
[17]: GTM+04=阿布扎比
[18]: GTM+05=伊斯兰堡
[19]: GTM+06=达卡
[20]: GTM+07=曼谷，河内
[21]: GTM+08=中国标准时间
[22]: GTM+09=汉城
[23]: GTM+10=关岛
[24]: GTM+11=所罗门群岛
[25]: GTM+12=斐济
[26]: GTM+13=努库阿勒法
[27]: GTM+14=基里巴斯
请选择设置时区 [21]:21

安装类型:
1 典型安装
2 服务器
3 客户端
4 自定义
请选择安装类型的数字序号 [1 典型安装]:1
所需空间: 1835M

请选择安装目录 [/home/dmdba/dmdbms]:/app/dmdbms
可用空间: 498G
是否确认安装路径(/app/dmdbms)? (Y/y:是 N/n:否) [Y/y]:y

安装前小结
安装位置: /app/dmdbms
所需空间: 1835M
可用空间: 498G
版本信息:
有效日期:
安装类型: 典型安装
是否确认安装? (Y/y:是 N/n:否):y
2022-10-18 14:20:37
[INFO] 安装基础模块...
2022-10-18 14:20:37
[INFO] 安装达梦数据库...
2022-10-18 14:20:40
[INFO] 安装服务器模块...
2022-10-18 14:20:40
[INFO] 安装客户端模块...
2022-10-18 14:20:41
[INFO] 安装驱动模块...
2022-10-18 14:20:42
[INFO] 安装手册模块...
2022-10-18 14:20:42
[INFO] 安装服务模块...
2022-10-18 14:20:44
[INFO] 移动日志文件。
2022-10-18 14:20:45
[INFO] 安装达梦数据库完成。

请以root系统用户执行命令:
/app/dmdbms/script/root/root_installer.sh

安装结束

2.使用root用户执行/app/dmdbms/script/root/root_installer.sh
[root@itsm-z dmdbms]# sh /app/dmdbms/script/root/root_installer.sh
移动 /app/dmdbms/bin/dm_svc.conf 到/etc目录
修改服务器权限
创建DmAPService服务
Created symlink /etc/systemd/system/multi-user.target.wants/DmAPService.service → /usr/lib/systemd/system/DmAPService.service.
创建服务(DmAPService)完成
启动DmAPService服务

3.初始化实例
页大小（PAGE_SIZE）：32
日志大小（LOG_SIZE）：2048
大小写（CASE_SENSITIVE）[Y:敏感 / N:不敏感]：Y
字符集（CHARSET）[0:GB18030 / 1:UTF-8]：1
数据库名（DB_NAME）：ITSM_DB
实例名（INSTANCE_NAME）：ITSMZ(主) ITSMB（备）
[dmdba@itsm-z dmsoft]$ cd /app/dmdbms/bin
[dmdba@itsm-z bin]$ ./dminit PATH=/app/dmdata PAGE_SIZE=32 LOG_SIZE=2048 CHARSET=1 CASE_SENSITIVE=Y DB_NAME=ITSM_DB INSTANCE_NAME=ITSMZ
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-08-12
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

log file path: /app/dmdata/ITSM_DB/ITSM_DB01.log

log file path: /app/dmdata/ITSM_DB/ITSM_DB02.log

write to dir [/app/dmdata/ITSM_DB].
create dm database success. 2022-10-18 15:23:30

4.使用root用户创建实例服务
[root@itsm-z ~]# /app/dmdbms/script/root/dm_service_installer.sh -t dmserver -dm_ini /app/dmdata/ITSM_DB/dm.ini -p ITSMZ
Created symlink /etc/systemd/system/multi-user.target.wants/DmServiceITSMZ.service → /usr/lib/systemd/system/DmServiceITSMZ.service.
创建服务(DmServiceITSMZ)完成

5启动实例
[dmdba@itsm-z bin]$ ./DmServiceITSMZ start
Starting DmServiceITSMZ: [ OK ]
[dmdba@itsm-z bin]$ ps -ef | grep dm.ini
dmdba 39753 1 10 15:41 pts/0 00:00:08 /app/dmdbms/bin/dmserver path=/app/dmdata/ITSM_DB/dm.ini -noconsole
dmdba 39836 19890 0 15:43 pts/0 00:00:00 grep dm.ini

四主备监视器配置
1.配置主机器
--启动服务
[dmdba@itsm-z bin]$ ./DmServiceITSMZ start
Starting DmServiceITSMZ: [ OK ]

--开启归档
[dmdba@itsm-z bin]$ ./disql SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.846(ms)
disql V8
SQL> ALTER DATABASE MOUNT;
操作已执行
已用时间: 3.667(毫秒). 执行号:0.
SQL> ALTER DATABASE ARCHIVELOG;
操作已执行
已用时间: 13.644(毫秒). 执行号:0.
SQL> ALTER DATABASE ADD ARCHIVELOG'DEST=/app/dmarch,TYPE=LOCAL,FILE_SIZE=1024,SPACE_LIMIT=51200';
操作已执行
已用时间: 3.030(毫秒). 执行号:0.
SQL> ALTER DATABASE OPEN;
操作已执行
已用时间: 17.666(毫秒). 执行号:0.

--备份数据
SQL> BACKUP DATABASE BACKUPSET '/app/backup/BACKUP_FILE';
操作已执行
已用时间: 00:00:03.131. 执行号:500.

--修改dm.ini
SQL> SP_SET_PARA_VALUE (2,'PORT_NUM',5236);
DMSQL 过程已成功完成
已用时间: 13.678(毫秒). 执行号:501.
SQL> SP_SET_PARA_VALUE (2,'DW_INACTIVE_INTERVAL',60);
DMSQL 过程已成功完成
已用时间: 13.609(毫秒). 执行号:502.
SQL> SP_SET_PARA_VALUE (2,'ALTER_MODE_STATUS',0);
DMSQL 过程已成功完成
已用时间: 13.609(毫秒). 执行号:503.
SQL> SP_SET_PARA_VALUE (2,'ENABLE_OFFLINE_TS',2);
DMSQL 过程已成功完成
已用时间: 13.452(毫秒). 执行号:504.
SQL> SP_SET_PARA_VALUE (2,'MAL_INI',1);
DMSQL 过程已成功完成
已用时间: 13.430(毫秒). 执行号:505.
SQL> SP_SET_PARA_VALUE (2,'RLOG_SEND_APPLY_MON',64);
DMSQL 过程已成功完成
已用时间: 13.561(毫秒). 执行号:506.

--替换dmarch.ini
[dmdba@itsm-z ITSM_DB]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /app/dmarch
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 51200
ARCH_FLUSH_BUF_SIZE = 0
ARCH_HANG_FLAG = 1
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = ITSMB #实时归档目标实例名

--创建dmmal.ini
[dmdba@itsm-z ITSM_DB]$ cat dmmal.ini
MAL_CHECK_INTERVAL = 10 #MAL链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 10 #判定MAL链路断开的时间
MAL_TEMP_PATH = /app/dmdata/malpath/ #临时文件目录
MAL_BUF_SIZE = 512 #单个MAL缓存大小，单位MB
MAL_SYS_BUF_SIZE = 2048 #MAL总大小限制，单位MB
MAL_COMPRESS_LEVEL = 0 #MAL消息压缩等级，0表示不压缩

[MAL_INST1]
MAL_INST_NAME = ITSMZ #实例名，和 dm.ini的INSTANCE_NAME一致
MAL_HOST = 10.202.23.96 #MAL系统监听TCP连接的IP地址
MAL_PORT = 5336 #MAL系统监听TCP连接的端口
MAL_INST_HOST = 10.202.23.96 #实例的对外服务IP地址
MAL_INST_PORT = 5236 #实例对外服务端口，和dm.ini的PORT_NUM一致
MAL_DW_PORT = 5436 #实例对应的守护进程监听TCP连接的端口
MAL_INST_DW_PORT = 5536 #实例监听守护进程TCP连接的端口
[MAL_INST2]
MAL_INST_NAME = ITSMB
MAL_HOST = 10.202.23.97
MAL_PORT = 5336
MAL_INST_HOST = 10.202.23.97
MAL_INST_PORT = 5236
MAL_DW_PORT = 5436
MAL_INST_DW_PORT = 5536

--创建dmwatcher.ini
[dmdba@itsm-z ITSM_DB]$ cat dmwatcher.ini
[GDW1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #故障自动切换模式 AUTO/MANUAL
DW_ERROR_TIME = 20 #远程守护进程故障认定时间
INST_ERROR_TIME = 20 #本地实例故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_OGUID = 45331 #守护系统唯一OGUID值
INST_INI = /app/dmdata/ITSM_DB/dm.ini #dm.ini文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /app/dmdbms/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭

--拷贝实例
[dmdba@itsm-z ITSM_DB]$ scp -r /app/dmdata/ITSM_DB dmdba@10.202.23.97:/app/dmdata/
The authenticity of host '10.202.23.97 (10.202.23.97)' can't be established.
ECDSA key fingerprint is SHA256:nLaL50otCpu/rATI/qZN7BeuFn6cI6G4TUYxGCybVeA.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.202.23.97' (ECDSA) to the list of known hosts.

Authorized users only. All activities may be monitored and reported.
dmdba@10.202.23.97's password:
dminit20221018152327.log 100% 942 360.2KB/s 00:00
dm.ini 100% 60KB 48.4MB/s 00:00
sqllog.ini 100% 481 888.5KB/s 00:00
dm.ctl 100% 5632 9.1MB/s 00:00
ITSM_DB01.log 100% 2048MB 156.7MB/s 00:13
ITSM_DB02.log 100% 2048MB 172.4MB/s 00:11
dm_20221018152329_440595.ctl 100% 5632 18.7KB/s 00:00
dm_20221018154155_247313.ctl 100% 5632 2.0MB/s 00:00
dm_20221018154526_579297.ctl 100% 5632 58.8KB/s 00:00
dm_20221018155440_093523.ctl 100% 5632 17.6KB/s 00:00
dm_20221018160206_358456.ctl 100% 5632 14.2KB/s 00:00
dm_20221018163535_873001.ctl 100% 5632 26.1KB/s 00:00
SYSTEM.DBF 100% 98MB 46.0MB/s 00:02
dm_service.prikey 100% 633 950.5KB/s 00:00
MAIN.DBF 100% 128MB 182.6MB/s 00:00
ROLL.DBF 100% 128MB 180.6MB/s 00:00
TEMP.DBF 100% 42MB 55.3MB/s 00:00
rep_conflict.log 100% 12 0.0KB/s 00:00
dmarch.ini 100% 459 4.2KB/s 00:00
.dmarch.ini.swp 100% 12KB 58.9KB/s 00:00
dmmal.ini 100% 1250 2.5KB/s 00:00
dmwatcher.ini 100% 828 3.8KB/s 00:00
[dmdba@itsm-z ITSM_DB]$

--注册服务
[root@itsm-z bin]# /app/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /app/dmdata/ITSM_DB/dmwatcher.ini
Created symlink /etc/systemd/system/multi-user.target.wants/DmWatcherServiceWatcher.service → /usr/lib/systemd/system/DmWatcherServiceWatcher.service.
创建服务(DmWatcherServiceWatcher)完成

2.配置备机器
--修改dm.ini
[dmdba@itsm-b ITSM_DB]$ cat dm.ini | grep INSTANCE_NAME
INSTANCE_NAME = ITSMB #Instance name

--替换dmarch.ini
[dmdba@itsm-b ITSM_DB]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /app/dmarch
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 51200
ARCH_FLUSH_BUF_SIZE = 0
ARCH_HANG_FLAG = 1
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME #实时归档类型
ARCH_DEST = ITSMZ #实时归档目标实例名
--dmmal.ini和dmwatcher.ini无需更改，和主保持一致

--注册服务
[root@itsm-b app]# /app/dmdbms/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /app/dmdata/ITSM_DB/dmwatcher.ini
Created symlink /etc/systemd/system/multi-user.target.wants/DmWatcherServiceWatcher.service → /usr/lib/systemd/system/DmWatcherServiceWatcher.service.
创建服务(DmWatcherServiceWatcher)完成

--恢复数据
[dmdba@itsm-b bin]$ ./dmrman CTLSTMT="RESTORE DATABASE '/app/dmdata/ITSM_DB/dm.ini' FROM BACKUPSET '/app/backup/BACKUP_FILE'"
dmrman V8
RESTORE DATABASE '/app/dmdata/ITSM_DB/dm.ini' FROM BACKUPSET '/app/backup/BACKUP_FILE'
file dm.key not found, use default license!
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:03][Remaining:00:00:00]
restore successfully.
time used: 00:00:03.480

[dmdba@itsm-b bin]$ ./dmrman CTLSTMT="RECOVER DATABASE '/app/dmdata/ITSM_DB/dm.ini' from BACKUPSET '/app/backup/BACKUP_FILE'"
dmrman V8
RECOVER DATABASE '/app/dmdata/ITSM_DB/dm.ini' from BACKUPSET '/app/backup/BACKUP_FILE'
file dm.key not found, use default license!
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[43922], file_lsn[43922]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:02.787

[dmdba@itsm-b bin]$ ./dmrman CTLSTMT="RECOVER DATABASE '/app/dmdata/ITSM_DB/dm.ini' UPDATE DB_MAGIC"
dmrman V8
RECOVER DATABASE '/app/dmdata/ITSM_DB/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
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[45220], file_lsn[45220]
recover successfully!
time used: 00:00:01.055

3.配置监视器
--创建dmmonitor.ini
[dmdba@itsm-jsq bin]$ cat dmmonitor.ini
MON_DW_CONFIRM = 1 #0为非确认，1为确认
MON_LOG_PATH = ../log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔 60s 定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 512 #单个日志大小，单位MB
MON_LOG_SPACE_LIMIT = 2048 #日志上限，单位MB

[GDW1]
MON_INST_OGUID = 45331 #组GDW1的唯一OGUID 值
MON_DW_IP = 10.202.23.96:5436 #IP对应MAL_HOST，PORT对应MAL_DW_PORT
MON_DW_IP = 10.202.23.97:5436

--注册服务
[root@itsm-jsq dmdbms]# /app/dmdbms/script/root/dm_service_installer.sh -t dmmonitor -p Monitor -monitor_ini /app/dmdbms/bin/dmmonitor.ini
Created symlink /etc/systemd/system/multi-user.target.wants/DmMonitorServiceMonitor.service → /usr/lib/systemd/system/DmMonitorServiceMonitor.service.
创建服务(DmMonitorServiceMonitor)完成

--创建dmmonitor_0.ini
MON_DW_CONFIRM = 0 #0为非确认，1为确认
MON_LOG_PATH = ../log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔 60s 定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 512 #单个日志大小，单位MB
MON_LOG_SPACE_LIMIT = 2048 #日志上限，单位MB

[GDW1]
MON_INST_OGUID = 45331 #组GDW1的唯一OGUID 值
MON_DW_IP = 10.202.23.96:5436 #IP对应MAL_HOST，PORT对应MAL_DW_PORT
MON_DW_IP = 10.202.23.97:5436

五启动相关服务

--启动主备数据库实例
[dmdba@itsm-z bin]$ ./DmServiceITSMZ start
Starting DmServiceITSMZ: [ OK ]
[dmdba@itsm-b bin]$ ./DmServiceITSMB start
Starting DmServiceITSMB: [ OK ]
--启动主备守护进程
[dmdba@itsm-z bin]$ ./DmWatcherServiceWatcher start
Starting DmWatcherServiceWatcher: [ OK ]
[dmdba@itsm-b bin]$ ./DmWatcherServiceWatcher start
Starting DmWatcherServiceWatcher: [ OK ]
--启动监视器
[dmdba@itsm-jsq bin]$ ./DmMonitorServiceMonitor start
Starting DmMonitorServiceMonitor: [ OK ]
--进入监视器
[dmdba@itsm-jsq bin]$ ./dmmonitor ./dmmonitor_0.ini

--修改主参数
SQL> ALTER DATABASE MOUNT;
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE PRIMARY;
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
SQL> ALTER DATABASE OPEN FORCE;
--修改备参数
SQL> ALTER DATABASE MOUNT;
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE STANDBY;
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
SQL> ALTER DATABASE OPEN FORCE;

六验证数据实时同步
--主库建表插入数据
SQL> CREATE TABLE DMTEST(ID int);
executed successfully
used time: 15.800(ms). Execute id is 1100.
SQL> insert into DMTEST VALUES (1);
affect rows 1

used time: 1.616(ms). Execute id is 1101.
SQL> COMMIT;
executed successfully
used time: 2.919(ms). Execute id is 1102.
--备库查看
SQL> SELECT * FROM DMTEST;

LINEID ID

1 1

used time: 4.640(ms). Execute id is 300.

达梦主备监视器部署方案

作者