DM8单实例部署数据守护与读写分离集群

部署环境介绍

三台主机，分别为主库、备库、监视器

端口规划

单实例DW架构图

主库为之前安装的单机数据库，一块网卡，现在关机增加一块网卡

准备工作

添加前网络配置
[root@dm001 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.229.131 netmask 255.255.255.0 broadcast 192.168.229.255
inet6 fe80::20c:29ff:fed6:58ce prefixlen 64 scopeid 0x20
ether 00:0c:29:d6:58:ce txqueuelen 1000 (Ethernet)
RX packets 115 bytes 27452 (26.8 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 142 bytes 16712 (16.3 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
添加后查看网络配置
[root@dm001 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.229.131 netmask 255.255.255.0 broadcast 192.168.229.255
inet6 fe80::20c:29ff:fed6:58ce prefixlen 64 scopeid 0x20
ether 00:0c:29:d6:58:ce txqueuelen 1000 (Ethernet)
RX packets 115 bytes 27452 (26.8 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 142 bytes 16712 (16.3 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

ens37: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
ether 00:0c:29:d6:58:d8 txqueuelen 1000 (Ethernet)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

[root@dm001 ~]# cd /etc/sysconfig/network-scripts/
[root@dm001 network-scripts]# ls
ifcfg-ens33 #####仅有最初一块网卡的配置文件
[root@dm001 network-scripts]# cp ifcfg-ens33 ifcfg-ens37 ####拷贝一份
[root@dm001 network-scripts]# vim ifcfg-ens37 ###编辑新的网卡配置文件
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
NAME=ens37
#UUID=f2d46326-13b3-4ad4-98b4-c2fcce5f81f9
DEVICE=ens37
ONBOOT=yes
IPADDR=192.168.171.11
NETWORK=255.255.255.0
[root@dm001 network-scripts]# nmcli c reload
[root@dm001 network-scripts]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.229.131 netmask 255.255.255.0 broadcast 192.168.229.255
inet6 fe80::20c:29ff:fed6:58ce prefixlen 64 scopeid 0x20
ether 00:0c:29:d6:58:ce txqueuelen 1000 (Ethernet)
RX packets 227 bytes 38733 (37.8 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 227 bytes 23892 (23.3 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

ens37: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.171.11 netmask 255.255.255.0 broadcast 192.168.171.255
inet6 fe80::20c:29ff:fed6:58d8 prefixlen 64 scopeid 0x20
ether 00:0c:29:d6:58:d8 txqueuelen 1000 (Ethernet)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 34 bytes 5099 (4.9 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

[root@dm001 network-scripts]# ping 192.168.171.33
PING 192.168.171.33 (192.168.171.33) 56(84) bytes of data.
64 bytes from 192.168.171.33: icmp_seq=1 ttl=64 time=0.642 ms
64 bytes from 192.168.171.33: icmp_seq=2 ttl=64 time=0.190 ms
^C
— 192.168.171.33 ping statistics —
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 0.190/0.416/0.642/0.226 ms
[root@dm001 network-scripts]#

DM8数据库安装请参考：
https://blog.csdn.net/xiaoke_2013/article/details/127763539?spm=1001.2014.3001.5501

主库开启归档

[root@dm001 ~]# mkdir -p /dmdbms/arch
[root@dm001 ~]# chown -R dmdba:dinstall /dmdbms/arch/
[root@dm001 ~]# chmod -R 775 /dmdbms/arch/
[dmdba@dm001 ~]$ disql sysdba
password:

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 15.350(ms)
disql V8
SQL> select arch_mode from v$database;

LINEID ARCH_MODE

---

1 N

used time: 14.076(ms). Execute id is 700.
SQL> alter database mount;
executed successfully
used time: 15.272(ms). Execute id is 0.
SQL> alter database add archivelog ‘DEST=/dmdbms/arch,type=local,file_size=128,space_limit=0’;
executed successfully
used time: 13.180(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 9.924(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 136.079(ms). Execute id is 0.
SQL> select arch_mode from v$database;

LINEID ARCH_MODE

---

1 Y

used time: 12.467(ms). Execute id is 701.
SQL>

主库模拟业务数据

[root@dm001 ~]# su - dmdba
[dmdba@dm001 ~]$ disql sysdba
password:
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 18.987(ms)
SQL> select status$ from v$database;LINEIDSTATUS$

---

1 4
used time: 37.345(ms). Execute id is 500.
创建test_dw用户和test_dw表，暂不插入数据
SQL> create user test_dw identified by testdw123456;
executed successfully
used time: 43.858(ms). Execute id is 501.
SQL> grant resource,soi,vti to test_dw;
executed successfully
used time: 16.170(ms). Execute id is 502.
SQL> conn test_dw/testdw123456;
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 4.305(ms)
SQL> create table test_dw(id number,name varchar(20));
executed successfully
used time: 26.197(ms). Execute id is 600.

备库和监视器环境准备

备库和监视器两台主机仅安装DM数据库软件
备库进行初始化
[root@dms1 ~]#su - dmdba
[dmdba@dms1 ~]$ vim .bash_profile
export PATH=$D{M}_{H}OME/bin:$path
[dmdba@dms1 ~]$ source .bash_profile
[dmdba@dms1 ~]$ dminit PATH=/dmdbms/dm8/data DB_NAME=DAMENG INSTANCE_NAME=DMSERVER2
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-09-27
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

log file path: /dmdbms/dm8/data/DAMENG/DAMENG01.log

log file path: /dmdbms/dm8/data/DAMENG/DAMENG02.log

write to dir [/dmdbms/dm8/data/DAMENG].
create dm database success. 2022-11-15 09:37:31
[dmdba@dms1 ~]$

关闭主库进行脱机备份

[root@dm001 ~]# su - dmdba
[dmdba@dm001 ~]$ ps -ef | grep dmserver
dmdba 1443 1 0 Nov14 ? 00:00:34 /dmdbms/dm8/bin/dmserver path=/dmdbms/dm8/data/DAMENG/dm.ini -noconsole
dmdba 65763 65710 0 09:40 pts/1 00:00:00 grep --color=auto dmserver
[dmdba@dm001 ~]$ systemctl stop DmServiceDMSERVER
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to stop ‘DmServiceDMSERVER.service’.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ====
[dmdba@dm001 ~]$ ps -ef | grep dmserver
dmdba 65875 65710 0 09:42 pts/1 00:00:00 grep --color=auto dmserver
[dmdba@dm001 ~]$

创建备份存放路径

[root@dm001 ~]# mkdir -p /dmdbms/fullbak
[root@dm001 ~]# chown -R dmdba:dinstall /dmdbms/fullbak/
[root@dm001 ~]# chmod -R 775 /dmdbms/fullbak/

[root@dm001 ~]# su - dmdba
[dmdba@dm001 ~]$ dmrman
dmrman V8
RMAN> backup database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ full backupset ‘/dmdbms/fullbak/’;
backup database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ full backupset ‘/dmdbms/fullbak/’;
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[55582], file_lsn[55582]
Processing backupset /dmdbms/fullbak
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.208
RMAN>

[root@dm001 ~]# cd /dmdbms/fullbak/
[root@dm001 fullbak]# ll
total 9760
-rw-r–r-- 1 dmdba dinstall 9894400 Nov 15 09:47 fullbak.bak
-rw-r–r-- 1 dmdba dinstall 94720 Nov 15 09:47 fullbak.meta

备份集拷贝至备库

[root@dm001 fullbak]# su - dmdba
[dmdba@dm001 ~]$ cd /dmdbms/fullbak/
[dmdba@dm001 fullbak]$ scp -p * dmdba@192.168.229.132:/dmdbms/fullbak
dmdba@192.168.229.132’s password:
fullbak.bak 100% 9663KB 257.5MB/s 00:00
fullbak.meta 100% 93KB 33.7MB/s 00:00
[dmdba@dm001 fullbak]$

备库进行还原恢复

[root@dms1 ~]# cd /dmdbms/fullbak/
[root@dms1 fullbak]# ll
total 9760
-rw-r–r-- 1 dmdba dinstall 9894400 Nov 15 09:47 fullbak.bak
-rw-r–r-- 1 dmdba dinstall 94720 Nov 15 09:47 fullbak.meta
[root@dms1 fullbak]#

[root@dms1 fullbak]# su - dmdba
[dmdba@dms1 ~]$ dmrman
dmrman V8
RMAN> restore database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ from backupset ‘/dmdbms/fullbak/’;
restore database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ from backupset ‘/dmdbms/fullbak/’;
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:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.439
RMAN> recover database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ from backupset ‘/dmdbms/fullbak/’;
recover database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ from backupset ‘/dmdbms/fullbak/’;
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[74497], file_lsn[74497]
no log generates while the backupset [/dmdbms/fullbak] created
recover successfully!
time used: 300.984(ms)
RMAN> recover database ‘/dmdbms/dm8/data/DAMENG/dm.ini’ update db_magic;
recover database ‘/dmdbms/dm8/data/DAMENG/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[74497], file_lsn[74497]
recover successfully!
time used: 00:00:01.024
RMAN>

配置文件修改

主库配置文件修改

[dmdba@dm001 fullbak]$ vim /dmdbms/dm8/data/DAMENG/dm.ini
INSTANCE_NAME=DMSERVER
ALTER_MODE_STATUS=0
ENABLE_OFFLINE_TS=2
MAL_INI=1
ARCH_INI=1

[dmdba@dm001 fullbak]$ vim /dmdbms/dm8/data/DAMENG/dmmal.ini ###主备库配置相同
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = DMSERVER
MAL_HOST = 192.168.171.11
MAL_PORT = 61141
MAL_INST_HOST = 192.168.229.131
MAL_INST_PORT = 5236
MAL_DW_PORT = 52141
MAL_INST_DW_PORT = 33141

[MAL_INST2]
MAL_INST_NAME = DMSERVER2
MAL_HOST = 192.168.171.22
MAL_PORT = 61142
MAL_INST_HOST = 192.168.229.132
MAL_INST_PORT = 5236
MAL_DW_PORT = 52142
MAL_INST_DW_PORT = 33142

[dmdba@dm001 fullbak]$ vim /dmdbms/dm8/data/DAMENG/dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DMSERVER2 ##注意：此处写对方实例名
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dmdbms/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

[dmdba@dm001 fullbak]$ vim /dmdbms/dm8/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dmdbms/dm8/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dmdbms/dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
[root@dm001 ~]#

备库配置文件修改

[dmdba@dms1 ~]$ vim /dmdbms/dm8/data/DAMENG/dm.ini
INSTANCE_NAME=DMSERVER2
ALTER_MODE_STATUS=0
ENABLE_OFFLINE_TS=2
MAL_INI=1
ARCH_INI=1

[dmdba@dms1 ~]$ vim /dmdbms/dm8/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = DMSERVER
MAL_HOST = 192.168.171.11
MAL_PORT = 61141
MAL_INST_HOST = 192.168.229.131
MAL_INST_PORT = 32141
MAL_DW_PORT = 52141
MAL_INST_DW_PORT = 33141

[MAL_INST2]
MAL_INST_NAME = DMSERVER2
MAL_HOST = 192.168.171.22
MAL_PORT = 61142
MAL_INST_HOST = 192.168.229.132
MAL_INST_PORT = 32142
MAL_DW_PORT = 52142
MAL_INST_DW_PORT = 33142

[root@dms1 ~]# mkdir -p /dmdbms/arch
[root@dms1 ~]# chown -R dmdba:dinstall /dmdbms/arch/
[root@dms1 ~]# chmod -R 775 /dmdbms/arch/
[dmdba@dms1 ~]$ vim /dmdbms/dm8/data/DAMENG/dmarch.ini
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DMSERVER ####注意：此处写对方INSTANCE_NAME
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dmdbms/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

[dmdba@dms1 ~]$ vim /dmdbms/dm8/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dmdbms/dm8/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dmdbms/dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
[root@dm001 ~]#

Mount模式启动主备数据库

主库
[dmdba@dm001 fullbak]$ dmserver /dmdbms/dm8/data/DAMENG/dm.ini mount
备库
[dmdba@dms1 ~]$ dmserver /dmdbms/dm8/data/DAMENG/dm.ini mount

主备库设置OGUID

主库
SQL> sp_set_oguid(453331);
DMSQL executed successfully
used time: 6.458(ms). Execute id is 0.
SQL>
备库
SQL> sp_set_oguid(453331);
DMSQL executed successfully
used time: 6.458(ms). Execute id is 0.
SQL>

修改主备库模式

主库
SQL> alter database primary;
executed successfully
used time: 2.313(ms). Execute id is 0.
SQL>

备库
SQL> alter database standby;
executed successfully
used time: 17.640(ms). Execute id is 0.
SQL>

监视器（DW_M）配置

配置dmmonitor.ini

[dmdba@dm003 ~]$ vim /dmdbms/dm8/data/DAMENG/dmmonitor.ini
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dmdbms/dm8/m_log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 453331
MON_DW_IP = 192.168.171.11:52142
MON_DW_IP = 192.168.171.22:52142
[dmdba@dm003 ~]$

启动监视器

[dmdba@dm003 ~]$dmmonitor /dmdbms/dm8/data/DAMENG/dmmonitor.ini
[monitor] 2022-11-15 14:37:16: DMMONITOR[4.0] V8
[monitor] 2022-11-15 14:37:16: DMMONITOR[4.0] IS READY.

[monitor] 2022-11-15 14:37:16: Received message from(DMSERVER2)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-11-15 14:37:16 OPEN OK DMSERVER2 OPEN STANDBY NULL 6 58083 58083

[monitor] 2022-11-15 14:37:16: Received message from(DMSERVER)
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-11-15 14:37:16 OPEN OK DMSERVER OPEN PRIMARY NULL 6 58083 58083

测试同步

主库向test_dw用户下的test_dw表中插入数据
SQL> insert into test_dw(id,name) values(1,‘JOY’);
affect rows 1
used time: 1.075(ms). Execute id is 601.
SQL> insert into test_dw(id,name) values(2,‘张’);
affect rows 1
used time: 12.501(ms). Execute id is 602.
SQL> commit;
SQL> select * from test_dw;
LINEID ID NAME

---

1 1 JOY
2 2 张
used time: 3.889(ms). Execute id is 603.
SQL>

备库查看
SQL> select * from test_dw.test_dw;
LINEID ID NAME

---

1 1 JOY
2 2 张
used time: 0.589(ms). Execute id is 203.
SQL>
数据同步成功。

达梦社区：https://eco.dameng.com