读写分离三节点集群环境搭建

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



这篇关于读写分离三节点集群环境搭建的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程