读写分离三节点集群环境搭建
2022/7/10 23:55:21
本文主要是介绍读写分离三节点集群环境搭建，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录- 0.环境检查
- 1.数据准备
- 2.配置主库
- 配置文件
- 启动主库
- 设置OGUID
- 修改数据库模式
- 3.配置备库01
- 配置文件
- 启动备库
- 设置OGUID
- 修改数据库模式
- 4.配置备库02
- 配置文件
- 启动备库
- 设置OGUID
- 修改数据库模式
- 5.配置单实例监视器
- 6.启动主备库守护进程
- 7.启动监视器
- 8.测试验证
- 模拟场景故障处理
- 制造主机故障
- 备机自动接管
- 恢复故障主机
- 测试读事务自动分发到备机
- 配置dm_svc.conf
- 搭建开发环境，使用springboot项目测试主备集群数据库
- 接口查询代码
- 监控备机
0.环境检查
数据守护系统中各实例使用的 DM 服务器版本应一致，同时还应注意各实例所在主机的操作系统位数、大小端模式、时区及时间设置都应一致。
| 系统 | 版本 |
|---|---|
| 服务器 | 阿里云 |
| 操作系统 | Debian 9 |
| 数据库系统 | DM Database 64 V8 03134283890-20220525-161267-10045 |
| 数据守护 | V4.0 |
1.数据准备
--主库 SQL> BACKUP DATABASE BACKUPSET '/dm8/data/BACKUP_FILE_01'; executed successfully used time: 00:00:03.785. Execute id is 55404. SQL> EXIT dmdba@lxm2:~$ cd /dm8/data dmdba@lxm2:/dm8/data$ ls BACKUP_FILE_01 dm_arch DMDB dmdba@lxm2:/dm8/data$ scp -r BACKUP_FILE_01 dmdba@39.99.248.41:/dm8/data The authenticity of host '39.99.248.41 (39.99.248.41)' can't be established. ECDSA key fingerprint is SHA256:6ewGXHplgu8H5fnCMwoI8dcE8PLgR4c74ra1kldues4. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '39.99.248.41' (ECDSA) to the list of known hosts. dmdba@39.99.248.41's password: BACKUP_FILE_01.bak BACKUP_FILE_01.meta BACKUP_FILE_01_1.bak --备库 root@lxm:/dm8/data# ls -lrth total 12K drwxr-xr-x 6 dmdba dinstall 4.0K Jul 1 14:09 DMDB drwxr-xr-x 2 dmdba dinstall 4.0K Jul 1 14:42 dm_arch drwxr-xr-x 2 dmdba dinstall 4.0K Jul 1 15:19 BACKUP_FILE_01 root@lxm:/dm8/data# systemctl stop DmServiceDMSERVER.service root@lxm:/dm8/data# su - dmdba dmdba@lxm:~$ ls COL.LOG DB_DMDB_FULL_2022_06_24_11_54_00 DB_DMDB_FULL_2022_06_24_11_56_00 DB_DMDB_FULL_2022_07_01_11_56_02 dm_arch dmdbms dmdba@lxm:~$ rlwrap dmrman dmrman V8 RMAN> RESTORE DATABASE '/dm8/data/DMDB/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'; RESTORE DATABASE '/dm8/data/DMDB/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.756 RMAN> RECOVER DATABASE '/dm8/data/DMDB/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'; RECOVER DATABASE '/dm8/data/DMDB/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[40343], file_lsn[40343] [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:02.576 RMAN> RECOVER DATABASE '/dm8/data/DMDB/dm.ini' UPDATE DB_MAGIC; RECOVER DATABASE '/dm8/data/DMDB/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[42046], file_lsn[42046] recover successfully! time used: 00:00:01.055 RMAN>
2.配置主库
配置文件
--dm.ini INSTANCE_NAME = GRP1_RWW_01 PORT_NUM = 5236 #数据库实例监听端口 DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间 ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间 MAL_INI = 1 #打开 MAL 系统 ARCH_INI = 1 #打开归档配置 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_RWW_01 #实例名，和 dm.ini 中的 INSTANCE_NAME 一致 MAL_HOST = 172.21.215.74 #MAL 系统监听 TCP 连接的 IP 地址 MAL_PORT = 61141 #MAL 系统监听 TCP 连接的端口 MAL_INST_HOST = 8.142.141.128 #实例的对外服务 IP 地址 MAL_INST_PORT = 5236 #实例的对外服务端口，和 dm.ini 中的 PORT_NUM 一致 MAL_DW_PORT = 52141 #实例对应的守护进程监听 TCP 连接的端口 MAL_INST_DW_PORT = 33141 #实例监听守护进程 TCP 连接的端口 [MAL_INST2] MAL_INST_NAME = GRP1_RWW_02 MAL_HOST = 172.17.44.74 MAL_PORT = 61142 MAL_INST_HOST = 39.103.139.80 MAL_INST_PORT = 5236 MAL_DW_PORT = 52142 MAL_INST_DW_PORT = 33142 [MAL_INST3] MAL_INST_NAME = GRP1_RWW_03 MAL_HOST = 172.21.215.76 MAL_PORT = 61143 MAL_INST_HOST = 39.99.248.41 MAL_INST_PORT = 5236 MAL_DW_PORT = 52143 MAL_INST_DW_PORT = 33143 --dm_arch.ini [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_02 #即时归档目标实例名 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_03 #即时归档目标实例名 --dmwatcher.ini [GRP1] DW_TYPE = GLOBAL #全局守护类型 DW_MODE = AUTO #自动切换模式 DW_ERROR_TIME = 10 #远程守护进程故障认定时间 INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间 INST_ERROR_TIME = 10 #本地实例故障认定时间 INST_OGUID = 453332 #守护系统唯一 OGUID 值 INST_INI = /dm8/data/DMDB/dm.ini #dm.ini 配置文件路径 INST_AUTO_RESTART = 1 #打开实例的自动启动功能 INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动 RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭 RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭
启动主库
dmdba@lxm2:~$ dmserver /dm8/data/DMDB/dm.ini mount & [1] 2611 dmdba@lxm2:~$ file dm.key not found, use default license! version info: develop DM Database Server 64 V8 03134283890-20220525-161267-10045 startup... Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL Database mode = 0, oguid = 0 License will expire on 2023-05-25 begin redo pwr log collect, last ckpt lsn: 41498 ... redo pwr log collect finished main rfil[/dm8/data/DMDB/DMDB01.log]'s grp collect 0 valid pwr record, discard 0 invalid pwr record EP[0]'s cur_lsn[41498], file_lsn[41498] begin redo log recover, last ckpt lsn: 41498 ... redo log recover finished ndct db load finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct fill fast pool finished nsvr_startup end. aud sys init success. aud rt sys init success. systables desc init success. ndct_db_load_info success. SYSTEM IS READY.
设置OGUID
dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is normal, state is mount login used time : 1.846(ms) disql V8 SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); sp_set_oguid(453332); DMSQL executed successfully used time: 9.607(ms). Execute id is 0. SQL> DMSQL executed successfully used time: 8.003(ms). Execute id is 1. SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 5.090(ms). Execute id is 2.
修改数据库模式
SQL> alter database primary; executed successfully used time: 9.360(ms). Execute id is 0.
3.配置备库01
配置文件
INSTANCE_NAME = GRP1_RWW_02 PORT_NUM = 5236 #数据库实例监听端口 DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间 ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间 MAL_INI = 1 #打开 MAL 系统 ARCH_INI = 1 #打开归档配置 RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志重演信息 MAL_CHECK_INTERVL = 5 #MAL 链路检测时间间隔 MAL_CONN_FAIL_INTERVAL = 5 #判定 MAL 链路断开的时间 [MAL_INST1] MAL_INST_NAME = GRP1_RWW_01 #实例名，和 dm.ini 中的 INSTANCE_NAME 一致 MAL_HOST = 172.21.215.74 #MAL 系统监听 TCP 连接的 IP 地址 MAL_PORT = 61141 #MAL 系统监听 TCP 连接的端口 MAL_INST_HOST = 8.142.141.128 #实例的对外服务 IP 地址 MAL_INST_PORT = 5236 #实例的对外服务端口，和 dm.ini 中的 PORT_NUM 一致 MAL_DW_PORT = 52141 #实例对应的守护进程监听 TCP 连接的端口 MAL_INST_DW_PORT = 33141 #实例监听守护进程 TCP 连接的端口 [MAL_INST2] MAL_INST_NAME = GRP1_RWW_02 MAL_HOST = 172.17.44.74 MAL_PORT = 61142 MAL_INST_HOST = 39.103.139.80 MAL_INST_PORT = 5236 MAL_DW_PORT = 52142 MAL_INST_DW_PORT = 33142 [MAL_INST3] MAL_INST_NAME = GRP1_RWW_03 MAL_HOST = 172.21.215.76 MAL_PORT = 61143 MAL_INST_HOST = 39.99.248.41 MAL_INST_PORT = 5236 MAL_DW_PORT = 52143 MAL_INST_DW_PORT = 33143 [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_01 #即时归档目标实例名 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_03 #即时归档目标实例名 [GRP1] DW_TYPE =GLOBAL #全局守护类型 DW_MODE = AUTO #自动切换模式 DW_ERROR_TIME = 10 #远程守护进程故障认定时间 INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间 INST_ERROR_TIME = 10 #本地实例故障认定时间 INST_OGUID = 453332 #守护系统唯一 OGUID 值 INST_INI = /dm8/data/DMDB/dm.ini #dm.ini 配置文件路径 INST_AUTO_RESTART = 1 #打开实例的自动启动功能 INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动 RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭 RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭
启动备库
dmdba@lxm:~$ dmserver /dm8/data/DMDB/dm.ini mount & [1] 3553 dmdba@lxm:~$ file dm.key not found, use default license! version info: develop DM Database Server 64 V8 03134283890-20220525-161267-10045 startup... Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL Database mode = 0, oguid = 0 License will expire on 2023-05-25 file lsn: 39229 ndct db load finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct fill fast pool finished nsvr_startup end. aud sys init success. aud rt sys init success. systables desc init success. ndct_db_load_info success. SYSTEM IS READY.
设置OGUID
dmdba@lxm:~$ rlwrap disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is normal, state is mount login used time : 1.686(ms) disql V8 SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 6.989(ms). Execute id is 0. SQL> sp_set_oguid(453332); DMSQL executed successfully used time: 3.644(ms). Execute id is 1. SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 4.848(ms). Execute id is 2. SQL>
修改数据库模式
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 4.853(ms). Execute id is 3. SQL> alter database standby; executed successfully used time: 6.120(ms). Execute id is 0. SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 4.636(ms). Execute id is 4.
4.配置备库02
配置文件
INSTANCE_NAME = GRP1_RWW_03 PORT_NUM = 5236 #数据库实例监听端口 DW_INACTIVE_INTERVAL = 60 #接收守护进程消息超时时间 ALTER_MODE_STATUS = 0 #不允许手工方式修改实例模式/状态/OGUID ENABLE_OFFLINE_TS = 2 #不允许备库 OFFLINE 表空间 MAL_INI = 1 #打开 MAL 系统 ARCH_INI = 1 #打开归档配置 RLOG_SEND_APPLY_MON = 64 #统计最近 64 次的日志重演信息 MAL_CHECK_INTERVAL = 5 #MAL 链路检测时间间隔 MAL_CONN_FAIL_INTERVAL = 5 #判定 MAL 链路断开的时间 [MAL_INST1] MAL_INST_NAME = GRP1_RWW_01 #实例名，和 dm.ini 中的 INSTANCE_NAME 一致 MAL_HOST = 172.21.215.74 #MAL 系统监听 TCP 连接的 IP 地址 MAL_PORT = 61141 #MAL 系统监听 TCP 连接的端口 MAL_INST_HOST = 8.142.141.128 #实例的对外服务 IP 地址 MAL_INST_PORT = 5236 #实例的对外服务端口，和 dm.ini 中的 PORT_NUM 一致 MAL_DW_PORT = 52141 #实例对应的守护进程监听 TCP 连接的端口 MAL_INST_DW_PORT = 33141 #实例监听守护进程 TCP 连接的端口 [MAL_INST2] MAL_INST_NAME = GRP1_RWW_02 MAL_HOST = 172.17.44.74 MAL_PORT = 61142 MAL_INST_HOST = 39.103.139.80 MAL_INST_PORT = 5236 MAL_DW_PORT = 52142 MAL_INST_DW_PORT = 33142 [MAL_INST3] MAL_INST_NAME = GRP1_RWW_03 MAL_HOST = 172.21.215.76 MAL_PORT = 61143 MAL_INST_HOST = 39.99.248.41 MAL_INST_PORT = 5236 MAL_DW_PORT = 52143 MAL_INST_DW_PORT = 33143 [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_01 #即时归档目标实例名 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY #即时归档类型 ARCH_DEST = GRP1_RWW_02 #即时归档目标实例名 [GRP1] DW_TYPE = GLOBAL #全局守护类型 DW_MODE = AUTO #自动切换模式 DW_ERROR_TIME = 10 #远程守护进程故障认定时间 INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间 INST_ERROR_TIME = 10 #本地实例故障认定时间 INST_OGUID = 453332 #守护系统唯一 OGUID 值 INST_INI = /dm8/data/DMDB/dm.ini #dm.ini 配置文件路径 INST_AUTO_RESTART = 1 #打开实例的自动启动功能 INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式启动 RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值，默认关闭 RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值，默认关闭
启动备库
dmdba@lxm3:~$ dmserver /dm8/data/DMDB/dm.ini mount & [1] 19937 dmdba@lxm3:~$ file dm.key not found, use default license! version info: develop DM Database Server 64 V8 03134283890-20220525-161267-10045 startup... Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL Database mode = 0, oguid = 0 License will expire on 2023-05-25 file lsn: 39229 ndct db load finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct second level fill fast pool finished ndct third level fill fast pool finished ndct fill fast pool finished nsvr_startup end. aud sys init success. aud rt sys init success. systables desc init success. ndct_db_load_info success. SYSTEM IS READY.
设置OGUID
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); sp_set_oguid(453332); 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);
5.配置单实例监视器
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 = 453332 #组 GRP1 的唯一 OGUID 值 #以下配置为监视器到组 GRP1 的守护进程的连接信息，以―IP:PORT‖的形式配置 #IP 对应 dmmal.ini 中的 MAL_HOST，PORT 对应 dmmal.ini 中的 MAL_DW_PORT MON_DW_IP = 8.142.141.128:52141 MON_DW_IP = 39.103.139.80:52142 MON_DW_IP = 39.99.248.41:52143
6.启动主备库守护进程
dmdba@lxm2:~$ dmwatcher /dm8/data/DMDB/dmwatcher.ini DMWATCHER[4.0] V8 DMWATCHER[4.0] IS READY SHOW 2022-07-04 21:51:30 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART DW_STATUS DW_SUB_STATUS DW_CTL_STATUS GRP1 GLOBAL AUTO 453332 FALSE TRUE OPEN SUB_STATE_START VALID INST_OK NAME SVR_MODE SYS_STATUS RTYPE FSEQ FLSN CSEQ CLSN DW_STAT_FLAG OK GRP1_RWW_01 PRIMARY OPEN TIMELY 7708 43849 7708 43850 0 --------------------------------------------------------------------------- --备库略
7.启动监视器
C:\dmdbms\bin>dmmonitor.exe C:\dmdbms\data\dmmonitor.ini
[monitor] 2022-07-04 21:54:43: DMMONITOR[4.0] V8
[monitor] 2022-07-04 21:54:43: DMMONITOR[4.0] IS READY.
[monitor] 2022-07-04 21:54:43: 收到守护进程(GRP1_RWW_03)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:54:43 OPEN OK GRP1_RWW_03 OPEN STANDBY NULL 3 43914 43914
[monitor] 2022-07-04 21:54:43: 收到守护进程(GRP1_RWW_01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:54:43 OPEN OK GRP1_RWW_01 OPEN PRIMARY VALID 3 43914 43914
[monitor] 2022-07-04 21:54:43: 收到守护进程(GRP1_RWW_02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:54:43 OPEN OK GRP1_RWW_02 OPEN STANDBY VALID 3 43914 43914
8.测试验证
完成读写分离集群的部署，并测试建表和插入数据备机是否能及时同步，并可查询到数据。
--主库建表 dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is primary, state is open login used time : 1.731(ms) disql V8 SQL> CREATE TABLE T2(COL1 INT,COL2 INT,COL3 VARCHAR); executed successfully used time: 16.185(ms). Execute id is 600. SQL> INSERT INTO T2 VALUES(1002,1002,'数据守护集群'); affect rows 1 used time: 0.801(ms). Execute id is 601. SQL> COMMIT; executed successfully used time: 2.178(ms). Execute id is 602. --备库查询 SQL> SQL> SELECT * FROM T2; LINEID COL1 COL2 COL3 ---------- ----------- ----------- ------------------ 1 1002 1002 数据守护集群 used time: 0.235(ms). Execute id is 102. SQL>
模拟场景故障处理
(a)制造主机故障，测试备机可接管并持续提供服务。
(b)恢复故障主机，将故障主机加入集群。
(c)手动切换主备集群，恢复原主备集群的位置。
(d)测试读事务自动分发到备机的过程。
制造主机故障
root@lxm2:/home/dmdba/dmdbms/script/root# shutdown Shutdown scheduled for Mon 2022-07-04 22:08:17 CST, use 'shutdown -c' to cancel.
备机自动接管
[monitor] 2022-07-04 21:59:22: 接收守护进程(GRP1_RWW_02)消息超时
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:59:11 ERROR OK GRP1_RWW_02 OPEN STANDBY VALID 3 44003 44003
[monitor] 2022-07-04 21:59:23: 守护进程(GRP1_RWW_02)状态切换 [NONE-->STARTUP]
[monitor] 2022-07-04 21:59:24: 守护进程(GRP1_RWW_02)状态切换 [STARTUP-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:59:24 OPEN OK GRP1_RWW_02 OPEN STANDBY VALID 3 44007 44007
[monitor] 2022-07-04 21:59:42: 守护进程(GRP1_RWW_01)状态切换 [NONE-->STARTUP]
[monitor] 2022-07-04 21:59:44: 守护进程(GRP1_RWW_01)状态切换 [STARTUP-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 21:59:44 OPEN OK GRP1_RWW_01 OPEN PRIMARY VALID 3 44014 44014
[monitor] 2022-07-04 22:07:54: 守护进程(GRP1_RWW_02)状态切换 [NONE-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:07:54 OPEN OK GRP1_RWW_02 OPEN STANDBY VALID 3 44229 44229
[monitor] 2022-07-04 22:07:54: 守护进程(GRP1_RWW_01)状态切换 [NONE-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:07:54 OPEN OK GRP1_RWW_01 OPEN PRIMARY VALID 3 44229 44230
[monitor] 2022-07-04 22:08:27: 接收守护进程(GRP1_RWW_01)消息超时
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:08:16 ERROR OK GRP1_RWW_01 OPEN PRIMARY VALID 3 44237 44237
[monitor] 2022-07-04 22:08:27: 检测到PRIMARY实例故障，开始对组(GRP1)执行自动接管
[monitor] 2022-07-04 22:08:27: 通知组(GRP1)当前活动的守护进程设置MID
[monitor] 2022-07-04 22:08:27: 通知组(GRP1)当前活动的守护进程设置MID成功
[monitor] 2022-07-04 22:08:27: 开始使用实例GRP1_RWW_02接管
[monitor] 2022-07-04 22:08:27: 通知守护进程GRP1_RWW_02切换TAKEOVER状态
[monitor] 2022-07-04 22:08:27: 守护进程(GRP1_RWW_02)状态切换 [OPEN-->TAKEOVER]
[monitor] 2022-07-04 22:08:27: 切换守护进程GRP1_RWW_02为TAKEOVER状态成功
[monitor] 2022-07-04 22:08:27: 实例GRP1_RWW_02开始执行SP_SET_GLOBAL_DW_STATUS(0, 7)语句
[monitor] 2022-07-04 22:08:27: 实例GRP1_RWW_02执行SP_SET_GLOBAL_DW_STATUS(0, 7)语句成功
[monitor] 2022-07-04 22:08:27: 实例GRP1_RWW_02开始执行ALTER DATABASE MOUNT语句
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02执行ALTER DATABASE MOUNT语句成功
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02开始执行ALTER DATABASE PRIMARY语句
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02执行ALTER DATABASE PRIMARY语句成功
[monitor] 2022-07-04 22:08:28: 通知实例GRP1_RWW_02修改所有归档状态无效
[monitor] 2022-07-04 22:08:28: 修改所有实例归档为无效状态成功
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02开始执行SP_SET_GLOBAL_DW_STATUS(7, 0)语句
[monitor] 2022-07-04 22:08:28: 实例GRP1_RWW_02执行SP_SET_GLOBAL_DW_STATUS(7, 0)语句成功
[monitor] 2022-07-04 22:08:28: 通知守护进程GRP1_RWW_02切换OPEN状态
[monitor] 2022-07-04 22:08:28: 守护进程(GRP1_RWW_02)状态切换 [TAKEOVER-->OPEN]
[monitor] 2022-07-04 22:08:29: 切换守护进程GRP1_RWW_02为OPEN状态成功
[monitor] 2022-07-04 22:08:30: 通知组(GRP1)的守护进程执行清理操作
[monitor] 2022-07-04 22:08:30: 清理守护进程(GRP1_RWW_02)请求成功
[monitor] 2022-07-04 22:08:30: 清理守护进程(GRP1_RWW_03)请求成功
[monitor] 2022-07-04 22:08:30: 使用实例GRP1_RWW_02接管成功
[monitor] 2022-07-04 22:08:30: 组(GRP1)使用实例GRP1_RWW_02自动接管成功
[monitor] 2022-07-04 22:08:31: 守护进程(GRP1_RWW_02)状态切换 [OPEN-->RECOVERY]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:08:31 RECOVERY OK GRP1_RWW_02 OPEN PRIMARY VALID 4 46709 46719
[monitor] 2022-07-04 22:08:33: 守护进程(GRP1_RWW_02)状态切换 [RECOVERY-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:08:33 OPEN OK GRP1_RWW_02 OPEN PRIMARY VALID 4 46725 46726
恢复故障主机
#================================================================================#
[monitor] 2022-07-04 22:15:30: 守护进程(GRP1_RWW_01)状态切换 [NONE-->STARTUP]
[monitor] 2022-07-04 22:15:32: 守护进程(GRP1_RWW_01)状态切换 [STARTUP-->UNIFY EP]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:15:32 UNIFY EP OK GRP1_RWW_01 MOUNT PRIMARY VALID 3 44237 44237
[monitor] 2022-07-04 22:15:32: 守护进程(GRP1_RWW_01)状态切换 [UNIFY EP-->STARTUP]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:15:32 STARTUP OK GRP1_RWW_01 MOUNT STANDBY INVALID 3 44237 44237
[monitor] 2022-07-04 22:15:32: 守护进程(GRP1_RWW_01)状态切换 [STARTUP-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:15:32 OPEN OK GRP1_RWW_01 OPEN STANDBY INVALID 3 44237 44237
[monitor] 2022-07-04 22:15:32: 守护进程(GRP1_RWW_02)状态切换 [OPEN-->RECOVERY]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:15:32 RECOVERY OK GRP1_RWW_02 OPEN PRIMARY VALID 4 51023 51023
[monitor] 2022-07-04 22:15:35: 守护进程(GRP1_RWW_02)状态切换 [RECOVERY-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:15:35 OPEN OK GRP1_RWW_02 OPEN PRIMARY VALID 4 51050 51050
show
2022-07-04 22:15:54
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453332 TRUE AUTO FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.103.139.80 52142 2022-07-04 22:15:53 GLOBAL VALID OPEN GRP1_RWW_02 OK 1 1 OPEN PRIMARY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.103.139.80 5236 OK GRP1_RWW_02 OPEN PRIMARY 0 0 TIMELY VALID 8515 51231 8515 51231 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
8.142.141.128 52141 2022-07-04 22:15:53 GLOBAL VALID OPEN GRP1_RWW_01 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
8.142.141.128 5236 OK GRP1_RWW_01 OPEN STANDBY 0 0 TIMELY VALID 8059 51231 8059 51231 NONE
DATABASE(GRP1_RWW_01) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[8515, 8515, 8515], (RLSN, SLSN, KLSN)[51231, 51231, 51231], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (51231)
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.99.248.41 52143 2022-07-04 22:15:53 GLOBAL VALID OPEN GRP1_RWW_03 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.99.248.41 5236 OK GRP1_RWW_03 OPEN STANDBY 0 0 TIMELY VALID 5645 51221 5645 51221 NONE
DATABASE(GRP1_RWW_03) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[8514, 8514, 8514], (RLSN, SLSN, KLSN)[51221, 51221, 51221], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (51221)
#================================================================================#
恢复原主备位置
switchover GRP1_RWW_01
[monitor] 2022-07-04 22:26:53: 存在SLSN更大的活动实例GRP1_RWW_03[STANDBY, OPEN, ISTAT_SAME:TRUE]
LOGIN
用户名:SYSDBA
密码:
[monitor] 2022-07-04 22:27:11: 登录监视器成功!
switchover GRP1_RWW_01
[monitor] 2022-07-04 22:27:17: 存在SLSN更大的活动实例GRP1_RWW_03[STANDBY, OPEN, ISTAT_SAME:TRUE]
SHOW
2022-07-04 22:28:32
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453332 TRUE AUTO FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.103.139.80 52142 2022-07-04 22:28:31 GLOBAL VALID OPEN GRP1_RWW_02 OK 1 1 OPEN PRIMARY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.103.139.80 5236 OK GRP1_RWW_02 OPEN PRIMARY 0 0 TIMELY VALID 9409 58993 9409 58994 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
8.142.141.128 52141 2022-07-04 22:28:31 GLOBAL VALID OPEN GRP1_RWW_01 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
8.142.141.128 5236 OK GRP1_RWW_01 OPEN STANDBY 0 0 TIMELY VALID 8059 58983 8059 58983 NONE
DATABASE(GRP1_RWW_01) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9408, 9408, 9408], (RLSN, SLSN, KLSN)[58983, 58983, 58983], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (58983)
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.99.248.41 52143 2022-07-04 22:28:31 GLOBAL VALID OPEN GRP1_RWW_03 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.99.248.41 5236 OK GRP1_RWW_03 OPEN STANDBY 0 0 TIMELY VALID 5645 58993 5645 58993 NONE
DATABASE(GRP1_RWW_03) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9409, 9409, 9409], (RLSN, SLSN, KLSN)[58993, 58993, 58993], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (58993)
#================================================================================#
SHOW
2022-07-04 22:33:04
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453332 TRUE AUTO FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.103.139.80 52142 2022-07-04 22:33:04 GLOBAL VALID OPEN GRP1_RWW_02 OK 1 1 OPEN PRIMARY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.103.139.80 5236 OK GRP1_RWW_02 OPEN PRIMARY 0 0 TIMELY VALID 9716 61791 9716 61791 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
8.142.141.128 52141 2022-07-04 22:33:04 GLOBAL VALID OPEN GRP1_RWW_01 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
8.142.141.128 5236 OK GRP1_RWW_01 OPEN STANDBY 0 0 TIMELY VALID 8059 61780 8059 61780 NONE
DATABASE(GRP1_RWW_01) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9714, 9714, 9714], (RLSN, SLSN, KLSN)[61780, 61780, 61780], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (61780)
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.99.248.41 52143 2022-07-04 22:33:04 GLOBAL VALID OPEN GRP1_RWW_03 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.99.248.41 5236 OK GRP1_RWW_03 OPEN STANDBY 0 0 TIMELY VALID 5645 61780 5645 61780 NONE
DATABASE(GRP1_RWW_03) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9714, 9714, 9714], (RLSN, SLSN, KLSN)[61780, 61780, 61780], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (61780)
#================================================================================#
switchover GRP1_RWW_01
[monitor] 2022-07-04 22:33:17: 开始切换实例GRP1_RWW_01
[monitor] 2022-07-04 22:33:17: 通知守护进程GRP1_RWW_02切换SWITCHOVER状态
[monitor] 2022-07-04 22:33:17: 守护进程(GRP1_RWW_02)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2022-07-04 22:33:18: 切换守护进程GRP1_RWW_02为SWITCHOVER状态成功
[monitor] 2022-07-04 22:33:18: 通知守护进程GRP1_RWW_01切换SWITCHOVER状态
[monitor] 2022-07-04 22:33:18: 守护进程(GRP1_RWW_01)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2022-07-04 22:33:18: 切换守护进程GRP1_RWW_01为SWITCHOVER状态成功
[monitor] 2022-07-04 22:33:18: 实例GRP1_RWW_02开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2022-07-04 22:33:18: 实例GRP1_RWW_02执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2022-07-04 22:33:18: 实例GRP1_RWW_01开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02开始执行ALTER DATABASE MOUNT语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02执行ALTER DATABASE MOUNT语句成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01开始执行ALTER DATABASE MOUNT语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01执行ALTER DATABASE MOUNT语句成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02开始执行ALTER DATABASE STANDBY语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02执行ALTER DATABASE STANDBY语句成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01开始执行ALTER DATABASE PRIMARY语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01执行ALTER DATABASE PRIMARY语句成功
[monitor] 2022-07-04 22:33:19: 通知实例GRP1_RWW_01修改所有归档状态无效
[monitor] 2022-07-04 22:33:19: 修改所有实例归档为无效状态成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_02执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2022-07-04 22:33:19: 实例GRP1_RWW_01开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2022-07-04 22:33:20: 实例GRP1_RWW_01执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2022-07-04 22:33:20: 实例GRP1_RWW_02开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2022-07-04 22:33:20: 实例GRP1_RWW_02执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2022-07-04 22:33:20: 实例GRP1_RWW_01开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2022-07-04 22:33:20: 实例GRP1_RWW_01执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2022-07-04 22:33:20: 通知守护进程GRP1_RWW_02切换OPEN状态
[monitor] 2022-07-04 22:33:20: 守护进程(GRP1_RWW_02)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2022-07-04 22:33:20: 切换守护进程GRP1_RWW_02为OPEN状态成功
[monitor] 2022-07-04 22:33:20: 通知守护进程GRP1_RWW_01切换OPEN状态
[monitor] 2022-07-04 22:33:20: 守护进程(GRP1_RWW_01)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2022-07-04 22:33:21: 切换守护进程GRP1_RWW_01为OPEN状态成功
[monitor] 2022-07-04 22:33:21: 通知组(GRP1)的守护进程执行清理操作
[monitor] 2022-07-04 22:33:21: 清理守护进程(GRP1_RWW_01)请求成功
[monitor] 2022-07-04 22:33:21: 清理守护进程(GRP1_RWW_02)请求成功
[monitor] 2022-07-04 22:33:21: 清理守护进程(GRP1_RWW_03)请求成功
[monitor] 2022-07-04 22:33:22: 实例GRP1_RWW_01切换成功
2022-07-04 22:33:22
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453332 TRUE AUTO FALSE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
8.142.141.128 52141 2022-07-04 22:33:21 GLOBAL VALID OPEN GRP1_RWW_01 OK 1 1 OPEN PRIMARY DSC_OPEN TIMELY VALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
8.142.141.128 5236 OK GRP1_RWW_01 OPEN PRIMARY 0 0 TIMELY VALID 9740 64394 9740 64394 NONE
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.103.139.80 52142 2022-07-04 22:33:21 GLOBAL VALID OPEN GRP1_RWW_02 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY INVALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.103.139.80 5236 OK GRP1_RWW_02 OPEN STANDBY 0 0 TIMELY INVALID 9737 61947 9737 61947 NONE
DATABASE(GRP1_RWW_02) APPLY INFO FROM (GRP1_RWW_01), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9737, 9737, 9737], (RLSN, SLSN, KLSN)[61947, 61947, 61947], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (61947)
<<DATABASE GLOBAL INFO:>>
DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
39.99.248.41 52143 2022-07-04 22:33:21 GLOBAL VALID OPEN GRP1_RWW_03 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY INVALID
EP INFO:
INST_IP INST_PORT INST_OK INAME ISTATUS IMODE DSC_SEQNO DSC_CTL_NODE RTYPE RSTAT FSEQ FLSN CSEQ CLSN DW_STAT_FLAG
39.99.248.41 5236 OK GRP1_RWW_03 OPEN STANDBY 0 0 TIMELY INVALID 5645 61947 5645 61947 NONE
DATABASE(GRP1_RWW_03) APPLY INFO FROM (GRP1_RWW_02), REDOS_PARALLEL_NUM (1):
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[9737, 9737, 9737], (RLSN, SLSN, KLSN)[61947, 61947, 61947], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (61947)
#================================================================================#
[monitor] 2022-07-04 22:33:23: 守护进程(GRP1_RWW_01)状态切换 [OPEN-->RECOVERY]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:33:23 RECOVERY OK GRP1_RWW_01 OPEN PRIMARY VALID 5 64394 64395
[monitor] 2022-07-04 22:33:25: 守护进程(GRP1_RWW_01)状态切换 [RECOVERY-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-04 22:33:25 OPEN OK GRP1_RWW_01 OPEN PRIMARY VALID 5 64395 64395
测试读事务自动分发到备机
配置dm_svc.conf
TIME_ZONE=(480) LANGUAGE=(cn) DW_SVC=(8.142.141.128:5236,39.103.139.80:5236,39.99.248.41:5236) [DW_SVC] LOGIN_MODE=(1) RW_SEPARATE=(1) RW_PERCENT=(30) SWITCH_TIME=6000 SWITCH_INTERVAL=500 TRACE=DEBUG
搭建开发环境，使用springboot项目测试主备集群数据库
server.port= 8080 spring.datasource.url= jdbc:dm://DW_SVC?rwSeparate=1&rwPercent=30&logLevel=all spring.datasource.driver-class-name= dm.jdbc.driver.DmDriver spring.datasource.password= SYSDBA spring.datasource.username= SYSDBA spring.datasource.druid.initial-size=1 spring.datasource.druid.min-idle=1 spring.datasource.druid.max-active=20 spring.datasource.druid.test-on-borrow=true spring.datasource.druid.stat-view-servlet.allow=true
package com.example.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class JdbcConfig {
@Bean
@ConfigurationProperties(prefix = "jdbc")
public DataSource dataSource(){
DruidDataSource dataSource= new DruidDataSource();
return dataSource;
}
}
接口查询代码
package com.example.demo;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
@Controller
@RequestMapping("/jdbc")
public class JdbcController {
@Resource
private JdbcTemplate jdbcTemplate;
@RequestMapping("/info")
@ResponseBody
public List<Map<String, Object>> list() {
String sql = "SELECT * FROM T2";
List<Map<String, Object>> infoList = jdbcTemplate.queryForList(sql);
for (Map<String, Object> info : infoList) {
System.out.println(info.toString());
}
return infoList;
}
}
使用jemeter测试工具100并发压测数据库，日志如下。
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { DmDriver@5a6fd36d } connect(DmProperties): conn-1007, sessionID-0xdc17c40; [PARAMS]: {switchinterval=500, rwseparate=1, lastloginip=::ffff:223.71.97.46, svrstat=4, language=cn, rwpercent=30, globalserverseries=7, lastlogintime=2022-07-05 23:16:53, svrmode=1, dsccontrol=true, , backslashescape=false, clientuser=SYSDBA, localtimezone=480, host=8.142.141.128, switchtimes=6000, loginmode=1, loginwarningid=2, epgroup=DW_SVC=([8.142.141.128:5236 (PRIMARY, OPEN, DSC CONTROL), sessions: 0, 39.103.139.80:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0, 39.99.248.41:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0]), lifetimeremainder=-1, gracetimeremainder=0, failedattempts=0, serverversion=8.1.2.114, clienthostname=8.142.141.128, url=jdbc:dm://DW_SVC?rwSeparate=1&rwPercent=30&logLevel=all, applicationname=, instancename=GRP1_RWW_01, dbname=DMDB, port=5236, loglevel=all, guid=16EE621216EE621247FBC7757841ABD7, user=SYSDBA};
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1007 } isClosed(): false;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1007 } clearWarnings();
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect loop 0
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_STARTUP
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_LOGIN
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect fail [39.103.139.80:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0] 服务器模式不匹配
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_STARTUP
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_LOGIN
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect fail [39.99.248.41:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0] 服务器模式不匹配
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_STARTUP
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_LOGIN
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect success [8.142.141.128:5236]
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_STMT_ALLOCATE
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_PREPARE
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect loop 0
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1010 } accessStandby(); CMD_STARTUP
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1010 } accessStandby(); CMD_LOGIN
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] try connect success [39.103.139.80:5236]
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { DmDriver@5a6fd36d } connect(DmProperties): conn-1009, sessionID-0x7f1610525b70; [PARAMS]: {switchinterval=500, rwseparate=1, lastloginip=::ffff:223.71.97.46, svrstat=4, language=cn, rwpercent=30, globalserverseries=7, lastlogintime=2022-07-05 23:16:53, svrmode=1, dsccontrol=true, , backslashescape=false, clientuser=SYSDBA, localtimezone=480, host=8.142.141.128, switchtimes=6000, loginmode=1, loginwarningid=2, epgroup=DW_SVC=([8.142.141.128:5236 (PRIMARY, OPEN, DSC CONTROL), sessions: 0, 39.103.139.80:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0, 39.99.248.41:5236 (STANDBY, OPEN, DSC CONTROL), sessions: 0]), lifetimeremainder=-1, gracetimeremainder=0, failedattempts=0, serverversion=8.1.2.114, clienthostname=8.142.141.128, url=jdbc:dm://DW_SVC?rwSeparate=1&rwPercent=30&logLevel=all, applicationname=, instancename=GRP1_RWW_01, dbname=DMDB, port=5236, loglevel=all, guid=16EE621216EE621247FBC7757841ABD7, user=SYSDBA};
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } isClosed(): false;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } clearWarnings();
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } getAutoCommit(): true;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } getTransactionIsolation(): 2;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } getTransactionIsolation(): 2;
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1010 } accessStandby(); CMD_STMT_ALLOCATE
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } createStatement(): stmt-19, handle-0;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19 } setQueryTimeout(Integer); [PARAMS]: 10;
[DEBUG - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009 } access(); CMD_PREPARE
[SQL - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19 } executeQuery(String): rs-10; [PARAMS]: "select * from T2;"; [USED TIME]: 11.4807ms;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } getMetaData(): rsmd-5;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { rsmd-5 } getColumnCount(): 3;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { rsmd-5 } getColumnLabel(Integer): "COL1"; [PARAMS]: 1;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { rsmd-5 } getColumnLabel(Integer): "COL2"; [PARAMS]: 2;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { rsmd-5 } getColumnLabel(Integer): "COL3"; [PARAMS]: 3;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } next(): true;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } getObject(Integer): 1002; [PARAMS]: 1;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } getObject(Integer): 1002; [PARAMS]: 2;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } getObject(Integer): "数据守护集群"; [PARAMS]: 3;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } next(): false;
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19, rs-10 } close();
[INFO - 2022-07-05 23:16:52] tid:457 - [Thread Group 1-1] { conn-1009, stmt-19 } close();
中间有出现服务器模式不匹配，是集群节点选主的过程。
监控备机
压测期间查看备机库sql运行情况, 主备读写分离生效。
dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA Server[LOCALHOST:5236]:mode is standby, state is open login used time : 1.649(ms) disql V8 SQL> alter system set 'ENABLE_MONITOR'=1; DMSQL executed successfully used time: 16.890(ms). Execute id is 294200. SQL> SELECT TOP_SQL_TEXT,START_TIME FROM V$SQL_HISTORY ORDER BY START_TIME DESC LIMIT 10; LINEID TOP_SQL_TEXT START_TIME ---------- ---------------- -------------------------- 1 SELECT * FROM T2 2022-07-05 22:17:37.000000 2 SELECT * FROM T2 2022-07-05 22:17:37.000000 3 SELECT * FROM T2 2022-07-05 22:17:37.000000 4 SELECT * FROM T2 2022-07-05 22:17:37.000000 5 SELECT * FROM T2 2022-07-05 22:17:37.000000 6 SELECT * FROM T2 2022-07-05 22:17:37.000000 7 SELECT * FROM T2 2022-07-05 22:17:37.000000 8 SELECT * FROM T2 2022-07-05 22:17:37.000000 9 SELECT * FROM T2 2022-07-05 22:17:37.000000 10 SELECT * FROM T2 2022-07-05 22:17:37.000000 10 rows got used time: 0.996(ms). Execute id is 3327.
更多内容请参见达梦社区：https://eco.dameng.com
这篇关于读写分离三节点集群环境搭建的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-12-27数据结构与算法面试题详解及练习
- 2024-12-27网络请求面试题详解与实战
- 2024-12-27数据结构和算法面试真题详解与实战教程
- 2024-12-27网络请求面试真题解析与实战教程
- 2024-12-27数据结构和算法大厂面试真题详解与实战指南
- 2024-12-27TS大厂面试真题解析与应对策略
- 2024-12-27TS大厂面试真题详解与解析
- 2024-12-27网站安全入门：如何识别和修复漏洞
- 2024-12-27SQL注入基础教程
- 2024-12-27初学者指南：理解和修复跨域漏洞
