达梦8搭建数据守护集群
1.安装规划
1.1.环境说明
机器名 | IP地址 | 初始状态 | 操作系统 |
---|---|---|---|
DW_P | 192.168.1.111 | 主库:GRP1_RW_01 | 中标麒麟 |
DW_S1 | 192.168.1.112 | 备库:GRP1_RW_02 | 中标麒麟 |
DW_S2 | 192.168.1.113 | 备库:GRP1_RW_03 | 中标麒麟 |
1.2.端口规划
实例名 | PORT_NUM | MAL_INST_PORT | MAL_INST_DW_PORT | MAL_HOST | MAL_PORT | MAL_DW_PORT |
---|---|---|---|---|---|---|
GRP1_RW_01 | 5236 | 5236 | 5337 | 192.168.1.111 | 5238 | 5239 |
GRP1_RW_02 | 5236 | 5236 | 5337 | 192.168.1.112 | 5238 | 5239 |
GRP1_RW_03 | 5236 | 5236 | 5337 | 192.168.1.113 | 5238 | 5239 |
2.数据准备
2.1.准备主库
2.1.1.初始化主库
[root@dw_p ~]# su - dmdba
[dmdba@dw_p ~]$ dminit path=/dm8/data
initdb V8
db version: 0x7000a
file dm.key not found, use default license!
License will expire on 2021-06-24
log file path: /dm8/data/DAMENG/DAMENG01.log
log file path: /dm8/data/DAMENG/DAMENG02.log
write to dir [/dm8/data/DAMENG].
create dm database success. 2020-09-27 06:08:32
2.1.2.调整主库参数开启归档
开启归档
[dmdba@dw_p ~]$ cd /dm8/data/DAMENG/
[dmdba@dw_p DAMENG]$ vi dm.ini
ARCH_INI = 1
配置归档参数
echo "[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/data/DAMENG/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0" > /dm8/data/DAMENG/dmarch.ini
2.1.3.注册主库服务
[root@dw_p ~]# /dm8/script/root/dm_service_installer.sh -t dmserver -dm_ini /dm8/data/DAMENG/dm.ini -p DMSERVER
Move the service script file(/dm8/bin/DmServiceDMSERVER to /etc/rc.d/init.d/DmServiceDMSERVER)
Finished to create the service (DmServiceDMSERVER)
[root@dw_p ~]#
2.1.4.正常开启主库并备份
[root@dw_p ~]# service DmServiceDMSERVER start
Starting DmServiceDMSERVER: [ OK ]
[root@dw_p ~]# su - dmdba
[dmdba@dw_p ~]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is normal, state is open
login used time: 3.733(ms)
disql V8
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/DAMENG/BACKUP_FILE_01';
executed successfully
used time: 00:00:01.156. Execute id is 504.
SQL>
2.1.5.拷贝备份文件到两台备库服务器
拷贝到192.168.1.112
[dmdba@dw_p DAMENG]$ scp -r BACKUP_FILE_01 /dmdba@192.168.1.112:/dm8/tmp
RSA key fingerprint is 79:5b:3f:7d:68:68:a1:f8:e1:50:1f:ab:51:7e:55:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.112' (RSA) to the list of known hosts.
dmdba@192.168.83.102's password:
BACKUP_FILE_01.meta 100% 81KB 80.5KB/s 00:00
BACKUP_FILE_01_1.bak 100% 5632 5.5KB/s 00:00
BACKUP_FILE_01.bak 100% 15MB 15.1MB/s 00:00
[dmdba@dw_p DAMENG]$
拷贝到192.168.1.113
[dmdba@dw_p DAMENG]$ scp -r BACKUP_FILE_01 dmdba@192.168.1.113:/dm8/
The authenticity of host '192.168.1.113 (192.168.1.113)' can't be established.
RSA key fingerprint is 79:5b:3f:7d:68:68:a1:f8:e1:50:1f:ab:51:7e:55:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.113' (RSA) to the list of known hosts.
dmdba@192.168.83.103's password:
BACKUP_FILE_01.meta 100% 81KB 80.5KB/s 00:00
BACKUP_FILE_01_1.bak 100% 5632 5.5KB/s 00:00
BACKUP_FILE_01.bak 100% 15MB 15.1MB/s 00:00
[dmdba@dw_p DAMENG]$
停掉主库
[root@dw_p ~]# service DmServiceDMSERVER stop
2.2.准备备库(两台服务器操作一样,以dw_s1为案例)
2.2.1.先初始化数据库
[dmdba@dw_s1 ~]$ dminit path=/dm8/data
initdb V8
db version: 0x7000a
file dm.key not found, use default license!
License will expire on 2021-06-24
log file path: /dm8/data/DAMENG/DAMENG01.log
log file path: /dm8/data/DAMENG/DAMENG02.log
write to dir [/dm8/data/DAMENG].
create dm database success. 2020-09-27 06:19:17
2.2.2.恢复备库
[dmdba@dw_s1 ~]$ ./dmrman CTLSTMT="RESTORE DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/tmp/BACKUP_FILE_01'"
dmrman V8
RESTORE DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/BACKUP_FILE_01'
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dm8/BACKUP_FILE_01] START......
total 0 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
CMD END.CODE:[0]
restore successfully.
time used: 624.947(ms)
[dmdba@dw_s1 ~]$ ./dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/tmp/BACKUP_FILE_01'"
dmrman V8
RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/BACKUP_FILE_01'
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[35240]
RESTORE RLOG CHECK......
RESTORE RLOG, gen tmp file......
RESTORE RLOG FROM BACKUPSET [/dm8/BACKUP_FILE_01] START......
CMD END.CODE:[0]
EP:0 total 1 pkgs applied, percent: 100%
Recover from archive log finished, time used:0.003s.
recover successfully!
time used: 286.545(ms)
[dmdba@dw_s1 ~]$
[dmdba@dw_s1 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC"
dmrman V8
RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[35240]
EP[0]'s apply_lsn[35240] >= end_lsn[35240]
recover successfully!
time used: 997.664(ms)
2.2.3.注册数据库服务
[root@dw_s1 ~]# /dm8/script/root/dm_service_installer.sh -t dmserver -dm_ini /dm8/data/DAMENG/dm.ini -p DMSERVER
Move the service script file(/dm8/bin/DmServiceDMSERVER to /etc/rc.d/init.d/DmServiceDMSERVER)
Finished to create the service (DmServiceDMSERVER)
3.配置主库GRP1_RW_01
3.1.修改dm.ini参数
[dmdba@dw_p DAMENG]$ vi /dm8/data/DAMENG/dm.ini
INSTANCE_NAME = GRP1_RW_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次的日志发送信息
3.2.配置dmmal.ini
[dmdba@dw_p DAMENG]$ vi /dm8/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_RW_01
MAL_HOST = 192.168.1.111
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.111
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST2]
MAL_INST_NAME = GRP1_RW_02
MAL_HOST = 192.168.1.112
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.112
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST3]
MAL_INST_NAME = GRP1_RW_03
MAL_HOST = 192.168.1.113
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.113
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
3.3.配置dmarch.ini
[dmdba@dw_p DAMENG]$ vi /dm8/data/DAMENG/dmarch.ini
[ARCHIVE_TIMELY1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_02
[ARCHIVE_TIMELY2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_03
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/data/DAMENG/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
3.4.配置dmwatcher.ini
[dmdba@dw_p DAMENG]$ vi /dm8/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 333555
INST_INI = /dm8/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
3.5.启动主库到mount状态
[dmdba@dw_p DAMENG]$ /etc/rc.d/init.d/DmServiceDMSERVER stop
Stopping DmServiceDMSERVER: [ OK ]
[dmdba@dw_p DAMENG]$ /etc/rc.d/init.d/DmServiceDMSERVER start mount
Starting DmServiceDMSERVER: [ OK ]
3.6.设置OGUID并修改数据库模式
[dmdba@dw_p DAMENG]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 3.251(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 4.841(ms). Execute id is 1.
SQL> sp_set_oguid(333555);
DMSQL executed successfully
used time: 8.961(ms). Execute id is 2.
SQL> alter database primary;
executed successfully
used time: 2.144(ms). Execute id is 0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL executed successfully
used time: 3.567(ms). Execute id is 3.
SQL>
4.配置备库GRP1_RW_02
4.1.配置dm.ini参数
[dmdba@dw_s1 DAMENG]$ vi /dm8/data/DAMENG/dm.ini
INSTANCE_NAME = GRP1_RW_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次的日志发送信息
4.2.配置dmmal.ini
[dmdba@dw_s1 DAMENG]$ vi /dm8/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_RW_01
MAL_HOST = 192.168.1.111
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.111
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST2]
MAL_INST_NAME = GRP1_RW_02
MAL_HOST = 192.168.1.112
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.112
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST3]
MAL_INST_NAME = GRP1_RW_03
MAL_HOST = 192.168.1.113
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.113
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
4.3.配置dmarch.ini
[dmdba@dw_s1 DAMENG]$ vi /dm8/data/DAMENG/dmarch.ini
[ARCHIVE_TIMELY1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_01
[ARCHIVE_TIMELY2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_03
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/data/DAMENG/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
4.4.配置dmwatcher.ini
[dmdba@dw_s1 DAMENG]$ vi /dm8/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 333555
INST_INI = /dm8/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
4.5.启动备库到mount状态
[dmdba@dw_s1 DAMENG]$ service DmServiceDMSERVER start mount
Starting DmServiceDMSERVER: [ OK ]
4.6.设置OGUID并修改数据库模式
[dmdba@dw_s1 DAMENG]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 3.251(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 4.841(ms). Execute id is 1.
SQL> sp_set_oguid(333555);
DMSQL executed successfully
used time: 8.961(ms). Execute id is 2.
SQL> alter database standby;
executed successfully
used time: 2.144(ms). Execute id is 0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL executed successfully
used time: 3.567(ms). Execute id is 3.
SQL>
5.配置备库GRP1_RW_03
5.1.配置dm.ini参数
[dmdba@dw_s2 DAMENG]$ vi /dm8/data/DAMENG/dm.ini
INSTANCE_NAME = GRP1_RW_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次的日志发送信息
5.2.配置dmmal.ini
[dmdba@dw_s2 DAMENG]$ vi /dm8/data/DAMENG/dmmal.ini
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_RW_01
MAL_HOST = 192.168.1.111
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.111
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST2]
MAL_INST_NAME = GRP1_RW_02
MAL_HOST = 192.168.1.112
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.112
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
[MAL_INST3]
MAL_INST_NAME = GRP1_RW_03
MAL_HOST = 192.168.1.113
MAL_PORT = 5238
MAL_INST_PORT = 5236
MAL_INST_HOST = 192.168.1.113
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
5.3.配置dmarch.ini
[dmdba@dw_s2 DAMENG]$ vi /dm8/data/DAMENG/dmarch.ini
[ARCHIVE_TIMELY1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_01
[ARCHIVE_TIMELY2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_RW_02
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/data/DAMENG/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0
5.4.配置dmwatcher.ini
[dmdba@dw_s2 DAMENG]$ vi /dm8/data/DAMENG/dmwatcher.ini
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 333555
INST_INI = /dm8/data/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
5.5.启动备库到mount状态
[dmdba@dw_s2 DAMENG]$ service DmServiceDMSERVER start mount
Starting DmServiceDMSERVER: [ OK ]
5.6.设置OGUID并修改数据库模式
[dmdba@dw_s2 DAMENG]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is normal, state is mount
login used time: 3.251(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',1);
DMSQL executed successfully
used time: 4.841(ms). Execute id is 1.
SQL> sp_set_oguid(333555);
DMSQL executed successfully
used time: 8.961(ms). Execute id is 2.
SQL> alter database standby;
executed successfully
used time: 2.144(ms). Execute id is 0.
SQL> SP_SET_PARA_VALUE(1,'ALTER_MODE_STATUS',0);
DMSQL executed successfully
used time: 3.567(ms). Execute id is 3.
SQL>
6.配置监视器
任选一台机器
vi /dm8/data/DAMENG/dmmonitor.ini
MON_DW_Confirm = 1
MON_LOG_PATH = /dm8/data/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 333555
MON_DW_IP = 192.168.1.111:5239
MON_DW_IP = 192.168.1.112:5239
MON_DW_IP = 192.168.1.113:5239
7.启动守护进程(主备都启动)
7.1 启动主库守护进程
[dmdba@dw_p DAMENG]$ /dm8/bin/dmwatcher /dm8/data/DAMENG/dmwatcher.ini
7.2 启动备库守护进程
[dmdba@dw_s1 ~]$ /dm8/bin/dmwatcher /dm8/data/DAMENG/dmwatcher.ini
[dmdba@dw_s2 ~]$ /dm8/bin/dmwatcher /dm8/data/DAMENG/dmwatcher.ini
7.2 启动监视器
[dmdba@dw_p DAMENG]$ /dm8/bin/dmmonitor /dm8/data/DAMENG/dmmonitor.ini
8.测试同步状态
8.1 主库建表插入数据
[dmdba@dw_p ~]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is primary, state is open
login used time: 4.218(ms)
disql V8
SQL>
SQL>
SQL> create table t1(id int,name varchar2(10));
executed successfully
used time: 6.153(ms). Execute id is 4.
SQL> insert into t1 values(1,'qqq');
affect rows 1
used time: 0.381(ms). Execute id is 5.
SQL> commit;
executed successfully
used time: 1.282(ms). Execute id is 6.
SQL> exit
[dmdba@dw_p ~]$
8.2 去备库查询
[dmdba@dw_s1 ~]$ disql SYSDBA/SYSDBA
Server[LOCALHOST:5236]:mode is standby, state is open
login used time: 20.984(ms)
disql V8
SQL> select * from t1;
LINEID ID NAME
---------- ----------- ----
1 1 qqq
used time: 101.362(ms). Execute id is 1.
SQL>
8.3 如果使用手动方式关闭数据守护系统,请严格按照以下顺序:
1) 如果启动了确认监视器,先关闭确认监视器(防止自动接管)
2) 关闭备库守护进程(防止重启实例)
3) 关闭主库守护进程(防止重启实例)
4) Shutdown 主库
5) Shutdown 备库