达梦数据库主从集群环境故障处理

1.查看主从监控
[dmdba@db02 bin]$ ./dmmonitor /dm8/data/DM/dmmonitor.ini
[monitor] 2022-07-28 14:40:11: DMMONITOR[4.0] V8
[monitor] 2022-07-28 14:40:11: DMMONITOR[4.0] IS READY.

[monitor] 2022-07-28 14:40:11: 收到守护进程(DM02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 14:40:11 STARTUP OK DM02 MOUNT PRIMARY VALID 22 82446 82446

[monitor] 2022-07-28 14:40:11: 收到守护进程(DM01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 14:40:11 STARTUP OK DM01 MOUNT STANDBY VALID 24 89478 89478

备注：从库的FLSN，CLSN都比主库的FLSN，CLSN 大，造成主从数据库无法正常open。守护进程分裂了。

2.开始处理流程：

（1）关闭主从守护进程监控服务
（2）关闭主从守护进程服务
（2）将主从数据库都修改成NOrmal状态
[dmdba@db01 bin]$ ./disql SYSDBA/dmorcl123456:15236

服务器[LOCALHOST:15236]:处于备库配置状态
登录使用时间 : 10.876(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
DMSQL 过程已成功完成
已用时间: 24.462(毫秒). 执行号:700.
SQL> alter database normal;
操作已执行
已用时间: 00:00:01.728. 执行号:0.
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
DMSQL 过程已成功完成
已用时间: 10.324(毫秒). 执行号:701.
SQL>

[dmdba@db02 bin]$ ./disql sysdba/dmorcl123456:25236

服务器[LOCALHOST:25236]:处于主库配置状态
登录使用时间 : 28.951(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
DMSQL 过程已成功完成
已用时间: 171.633(毫秒). 执行号:0.
SQL> alter database normal;
操作已执行
已用时间: 19.624(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
DMSQL 过程已成功完成
已用时间: 12.786(毫秒). 执行号:1.
SQL>

（3）确认FLSN，CLSN大的库为我们新的主库，这里新增一个表
[dmdba@db01 bin]$ ./disql SYSDBA/dmorcl123456:15236

服务器[LOCALHOST:15236]:处于普通打开状态
登录使用时间 : 5.116(ms)
disql V8
SQL> alter user scott identified by “tiger123”;
alter user scott identified by “tiger123”;
第1 行附近出现错误[-2504]:密码长度不符合要求.
已用时间: 10.214(毫秒). 执行号:0.
SQL> alter user scott identified by “tiger123456”;
操作已执行
已用时间: 188.426(毫秒). 执行号:1300.
SQL> conn scott/tiger123456:15236

服务器[LOCALHOST:15236]:处于普通打开状态
登录使用时间 : 10.176(ms)
SQL> select user();

行号 USER()

---

1 SCOTT

已用时间: 33.823(毫秒). 执行号:1400.
SQL> CREATE TABLE city
(
city_id CHAR(3) NOT NULL,
city_name VARCHAR(40) NULL,
region_id INT NULL
);2 3 4 5 6
操作已执行
已用时间: 57.512(毫秒). 执行号:1401.
SQL> INSERT INTO city(city_id,city_name,region_id) VALUES(‘BJ’,‘北京’,1);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘SJZ’,‘石家庄’,1);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘SH’,‘上海’,2);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘NJ’,‘南京’,2);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘GZ’,‘广州’,3);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘HK’,‘海口’,3);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘WH’,‘武汉’,4);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘CS’,‘长沙’,4);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘SY’,‘沈阳’,5);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘XA’,‘西安’,6);
INSERT INTO city(city_id,city_name,region_id) VALUES(‘CD’,‘成都’,7); 影响行数

（4）脱机全备新主库
[dmdba@db01 bin]$ ./dmrman
dmrman V8
RMAN> backup database ‘/dm8/data/DM/dm.ini’ full to dmdb_full01 backupset ‘/dm8/data/dmdb_full01’;
backup database ‘/dm8/data/DM/dm.ini’ full to dmdb_full01 backupset ‘/dm8/data/dmdb_full01’;
file dm.key not found, use default license!
Database mode = 0, oguid = 453331
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[92228], file_lsn[92228]
Processing backupset /dm8/data/dmdb_full01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:04][Remaining:00:00:00]
backup successfully!
time used: 00:00:06.039
（5）拷贝备份集到旧的从库上
[dmdba@db01 data]$ scp -r dmdb_full01 dmdba@192.168.54.120:/dm8/data
dmdba@192.168.54.120’s password:
dmdb_full01.bak 100% 20MB 12.7MB/s 00:01
dmdb_full01.meta 100% 77KB 11.7MB/s 00:00
[dmdba@db01 data]$

（6）旧从库做恢复
[dmdba@db02 bin]$ ./dmrman
dmrman V8
RMAN> restore database ‘/dm8/data/DM/dm.ini’ from backupset ‘/dm8/data/dmdb_full01’;
restore database ‘/dm8/data/DM/dm.ini’ from backupset ‘/dm8/data/dmdb_full01’;
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.981
RMAN> recover database ‘/dm8/data/DM/dm.ini’ from backupset ‘/dm8/data/dmdb_full01’;
recover database ‘/dm8/data/DM/dm.ini’ from backupset ‘/dm8/data/dmdb_full01’;
Database mode = 0, oguid = 453331
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[92228], file_lsn[92228]
备份集[/dm8/data/dmdb_full01]备份过程中未产生日志
recover successfully!
time used: 508.583(ms)
RMAN> recover database ‘/dm8/data/DM/dm.ini’ update db_magic;
recover database ‘/dm8/data/DM/dm.ini’ update db_magic;
Database mode = 0, oguid = 453331
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[92228], file_lsn[92228]
recover successfully!
time used: 00:00:01.086
RMAN>

（7）以mount方式启动主从库前台服务，修改模式
[dmdba@db01 bin]$ ./dmserver path=/dm8/data/DM/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server x64 V8 1-2-70-21.09.08-147080-10028-ENT startup…
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Database mode = 0, oguid = 453331
License will expire on 2022-09-08
file lsn: 92228
ndct db load 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.

[dmdba@db01 bin]$ disql sysdba/dmorcl123456:15236

服务器[LOCALHOST:15236]:处于普通配置状态
登录使用时间 : 59.235(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
DMSQL 过程已成功完成
已用时间: 135.034(毫秒). 执行号:0.
SQL> alter database primary;
操作已执行
已用时间: 9.922(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
DMSQL 过程已成功完成
已用时间: 7.419(毫秒). 执行号:1.
SQL>

[dmdba@db02 bin]$ ./dmserver path=/dm8/data/DM/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server x64 V8 1-2-70-21.09.08-147080-10028-ENT startup…
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Database mode = 0, oguid = 453331
License will expire on 2022-09-08
file lsn: 92228
ndct db load 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.

[dmdba@db02 bin]$ ./disql sysdba/dmorcl123456:25236

服务器[LOCALHOST:25236]:处于普通配置状态
登录使用时间 : 7.428(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 1);
DMSQL 过程已成功完成
已用时间: 58.585(毫秒). 执行号:0.
SQL> alter database standby;
操作已执行
已用时间: 197.672(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, ‘ALTER_MODE_STATUS’, 0);
DMSQL 过程已成功完成
已用时间: 7.349(毫秒). 执行号:1.
SQL>

（8）停止前台数据库服务，启动守护进程
[dmdba@db01 bin]$ ./dmwatcher /dm8/data/DM/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY

[dmdba@db02 bin]$ ./dmwatcher /dm8/data/DM/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY

（9）查看主从守护监控进程
[dmdba@db02 bin]$ ./dmmonitor /dm8/data/DM/dmmonitor.ini
[monitor] 2022-07-28 15:40:10: DMMONITOR[4.0] V8
[monitor] 2022-07-28 15:40:10: DMMONITOR[4.0] IS READY.

[monitor] 2022-07-28 15:40:10: 收到守护进程(DM02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 15:40:10 OPEN OK DM02 OPEN STANDBY NULL 26 94572 94572

[monitor] 2022-07-28 15:40:10: 收到守护进程(DM01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 15:40:10 OPEN OK DM01 OPEN PRIMARY VALID 26 94572 94572

（7）登录主从数据库查看刚刚创建表的数据是否存在
服务器[LOCALHOST:15236]:处于主库打开状态
登录使用时间 : 3.482(ms)
SQL>
SQL> select user();

行号 USER()

---

1 SCOTT

已用时间: 16.848(毫秒). 执行号:500.
SQL> select count(*) from city;

行号 COUNT(*)

---

1 11

[dmdba@db02 bin]$ ./disql scott/tiger123456:25236

服务器[LOCALHOST:25236]:处于备库打开状态
登录使用时间 : 7.345(ms)
disql V8
SQL> select count(*) from city;

行号 COUNT(*)

---

1 11

已用时间: 68.994(毫秒). 执行号:0.

（8）测试主从是否正常可以切换
choose switchover GRP1
Can choose one of the following instances to do switchover:
1: DM02

switchover GRP1.DM02

[dmdba@db02 bin]$ ./dmmonitor /dm8/data/DM/dmmonitor.ini
[monitor] 2022-07-28 15:46:35: DMMONITOR[4.0] V8
[monitor] 2022-07-28 15:46:35: DMMONITOR[4.0] IS READY.

[monitor] 2022-07-28 15:46:35: 收到守护进程(DM02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 15:46:35 OPEN OK DM02 OPEN PRIMARY VALID 27 97028 97028

[monitor] 2022-07-28 15:46:35: 收到守护进程(DM01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 15:46:35 OPEN OK DM01 OPEN STANDBY VALID 27 97028 97028

备注：这时主库为DM02

（9）测试主从是否自动接管（DM01关机）,切回去，因为我的监控在从库上面，监控不能关掉
[dmdba@db02 bin]$ ./dmmonitor /dm8/data/DM/dmmonitor.ini
[monitor] 2022-07-28 15:52:22: DMMONITOR[4.0] V8
[monitor] 2022-07-28 15:52:25: DMMONITOR[4.0] IS READY.

[monitor] 2022-07-28 15:52:26: 收到守护进程(DM02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-07-28 15:52:25 OPEN OK DM02 OPEN PRIMARY VALID 29 102050 102050