DM8数据守护搭建
1.环境准备
测试环境信息:
主机类型 | IP地址 | 实例名 | 操作系统 |
---|---|---|---|
主库 | 192.0.2.111(外网) 10.0.0.111(内网) | DW01 | Kylin Linux Advanced Server release V10 (Sword) |
备库 | 192.0.2.112(外网) 10.0.0.112(内网) | DW02 | Kylin Linux Advanced Server release V10 (Sword) |
监控 | 192.0.2.113(外网) 10.0.0.113(内网) | Kylin Linux Advanced Server release V10 (Sword) |
端口规划
实例名 | PORT_NUM | DW_PORT | MAL_HOST | MAL_PORT | MAL_DW_PORT |
---|---|---|---|---|---|
DW01 | 5236 | 5237 | 10.0.0.111 | 5238 | 5239 |
DW02 | 5236 | 5237 | 10.0.0.112 | 5238 | 5239 |
2.创建主库并同步原始数据
同一数据守护环境中,主备库的db_name相同,实例名不同。
三台主机都已安装了达梦数据库软件,安装目录为/dm8,没有初始化实例。
⑴创建主库
①创建主库实例
[dmdba@dmdb ~]$ cd /dm8/bin
[dmdba@dmdb bin]$ dminit path=/dm8/data db_name=dwdb instance_name=dw01 port_num=5236
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-09-01
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dm8/data/dwdb/dwdb01.log
log file path: /dm8/data/dwdb/dwdb02.log
write to dir [/dm8/data/dwdb].
create dm database success. 2022-11-09 23:55:01
②以root用户注册实例服务
[root@dmdb ~]# /dm8/script/root/dm_service_installer.sh -t dmserver -dm_ini /dm8/data/dwdb/dm.ini -p dwdb
Created symlink /etc/systemd/system/multi-user.target.wants/DmServicedwdb.service → /usr/lib/systemd/system/DmServicedwdb.service.
创建服务(DmServicedwdb)完成
③初始化主库
[dmdba@dmdb ~]$ dmserver /dm8/data/dwdb/dm.ini
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134283914-20220901-168571-20009 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-09-01
file lsn: 0
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
pseg_set_gtv_trxid_low next trx id in mem:[1002]
pseg_collect_mgr_items, total collect 0 active_trxs, 0 cmt_trxs, 0 pre_cmt_trxs, 0 to_release_trxs, 0 active_pages, 0 cmt_pages, 0 pre_cmt_pages, 0 to_release_pages, 0 mgr pages, 0 mgr recs!
next trx id in mem:[2004]
NEXT TRX ID = 3006.
total 0 active crash trx, pseg_crash_trx_rollback sys_only(0) begin ...
pseg_crash_trx_rollback end, total 0 active crash trx, include 0 empty_trxs, 0 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
pseg recv finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
trx: 3006 purged 1 pages
trx: 3025 purged 1 pages
trx: 3026 purged 1 pages
....
trx: 3637 purged 1 pages
systables desc init success.
ndct_db_load_info success.
nsvr_process_before_open begin.
nsvr_process_before_open success.
total 0 active crash trx, pseg_crash_trx_rollback sys_only(0) begin ...
pseg_crash_trx_rollback end, total 0 active crash trx, include 0 empty_trxs, 0 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
SYSTEM IS READY.
④启动归档模式
[dmdba@dmdb ~]$ disql sysdba/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.975(ms)
disql V8
SQL> alter database mount;
操作已执行
已用时间: 1.419(毫秒). 执行号:0.
SQL> alter database add archivelog 'dest=/dm8/arch,type=local,file_size=64,space_limit=0';
操作已执行
已用时间: 1.031(毫秒). 执行号:0.
SQL> alter database archivelog;
操作已执行
已用时间: 5.060(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 6.872(毫秒). 执行号:0.
⑤停止主库
因为之前是使用DMSERVER启动的,直接在之前的窗口Ctrl+C完成实例的关闭
^CServer is stopping...
listener closed and all sessions disconnected
adjust undo_retention & wakeup purge thread...full check point starting...
generate force checkpoint, rlog free space[528116736], used space[8745984]
checkpoint begin, used_space[8745984], free_space[528116736]...
checkpoint end, 0 pages flushed, used_space[9728], free_space[536852992].
full check point end.
shutdown audit subsystem...OK
shutdown schedule subsystem...OK
shutdown timer successfully.
pre-shutdown MAL subsystem...OK
shutdown worker threads subsystem...OK
shutdown local parallel threads pool successfully.
shutdown replication subsystem...OK
shutdown sequence cache subsystem...OK
wait for mtsk link worker to exit..OK
shutdown mpp session subsystem...OK
wait for rapply is all over... OK
rapply worker threads exit successfully.
pre ending task & worker threads...OK
shutdown dblink subsystem...OK
shutdown pthd_pools...OK
shutdown session subsystem...shutdown aux session subsystem...OK
shutdown rollback segments purging subsystem...OK
shutdown rps subsystem...OK
shutdown transaction subsystem...OK
shutdown locking subsystem...OK
shutdown dbms_lock subsystem...OK
ending tsk and worker threads...OK
ckpt2_exec_immediately begin.
checkpoint begin, used_space[9728], free_space[536852992]...
checkpoint end, 0 pages flushed, used_space[19968], free_space[536842752].
checkpoint begin, used_space[19968], free_space[536842752]...
checkpoint end, 0 pages flushed, used_space[0], free_space[536862720].
checkpoint begin, used_space[0], free_space[536862720]...
checkpoint end, 0 pages flushed, used_space[0], free_space[536862720].
shutdown archive subsystem...OK
shutdown redo log subsystem...OK
shutdown MAL subsystem...OK
shutdown message compress subsystem successfully.
shutdown task subsystem...OK
shutdown trace subsystem...OK
shutdown svr_log subsystem...OK
shutdown plan cache subsystem...OK
shutdown database dictionary subsystem...OK
shutdown file subsystem...OK
shutdown mac cache subsystem...OK
shutdown dynamic login cache subsystem...OK
shutdown ifun/bifun/sfun/afun cache subsystem...OK
shutdown crypt subsystem...OK
shutdown pipe subsystem...OK
shutdown compress component...OK
shutdown slave redo subsystem...OK
shutdown kernel buffer subsystem...OK
shutdown SQL capture subsystem...OK
shutdown control file system...OK
shutdown dtype subsystem...OK
shutdown huge buffer and memory pools...OK
close lsnr socket
DM Database Server shutdown successfully.
⑵脱机备份主库
使用DMRMAN脱机备份主库,备份之前要确保DMAP是启动的。
[dmdba@dmdb bin]$ ./dmrman CTLSTMT="backup database '/dm8/data/dwdb/dm.ini' backupset '/dm8/backup'"
dmrman V8
backup database '/dm8/data/dwdb/dm.ini' backupset '/dm8/backup'
file dm.key not found, use default license!
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[42149], file_lsn[42149]
Processing backupset /dm8/backup
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.165
⑶创建备库
①在备库主机中创建备库实例
[dmdba@dmdb02 ~]$ dminit path=/dm8/data db_name=dwdb instance_name=dw02 port_num=5236
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-09-01
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dm8/data/dwdb/dwdb01.log
log file path: /dm8/data/dwdb/dwdb02.log
write to dir [/dm8/data/dwdb].
create dm database success. 2022-11-10 00:16:06
②以root用户注册实例服务
[root@dmdb02 ~]# /dm8/script/root/dm_service_installer.sh -t dmserver -dm_ini /dm8/data/dwdb/dm.ini -p dwdb
Created symlink /etc/systemd/system/multi-user.target.wants/DmServicedwdb.service → /usr/lib/systemd/system/DmServicedwdb.service.
创建服务(DmServicedwdb)完成
⑷备库上恢复备份
①将主库备份传到备库
[dmdba@dmdb backup]$ scp -r /dm8/backup 192.0.2.112:/dm8
dmdba@192.0.2.112's password:
backup.bak 100% 7830KB 229.4MB/s 00:00
backup.meta 100% 85KB 31.0MB/s 00:00
②恢复备库
//还原数据库
[dmdba@dmdb02 ~]$ dmrman CTLSTMT="restore database '/dm8/data/dwdb/dm.ini' from backupset '/dm8/backup'"
dmrman V8
restore database '/dm8/data/dwdb/dm.ini' from backupset '/dm8/backup'
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.897
//恢复数据库
[dmdba@dmdb02 ~]$ dmrman CTLSTMT="recover database '/dm8/data/dwdb/dm.ini' from backupset '/dm8/backup'"
dmrman V8
recover database '/dm8/data/dwdb/dm.ini' from backupset '/dm8/backup'
file dm.key not found, use default license!
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[42149], file_lsn[42149]
备份集[/dm8/backup]备份过程中未产生日志
recover successfully!
time used: 679.090(ms)
//更新数据库db_magic
[dmdba@dmdb02 ~]$ dmrman CTLSTMT="recover database '/dm8/data/dwdb/dm.ini' update db_magic"
dmrman V8
recover database '/dm8/data/dwdb/dm.ini' update db_magic
file dm.key not found, use default license!
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[42149], file_lsn[42149]
recover successfully!
time used: 00:00:01.018
3.配置主备库参数
所有配置文件都在实例目录下创建,本例中为/dm8/data/dwdb
⑴dm.ini
在所有主备节点的dm.ini文件中修改以下参数值:
INSTANCE_NAME = DW01
#INSTANCE_NAME = DW02 #根据实例名填写具体值
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
dmmal.ini是MAL系统的篇日志文件,各主备库的dmmal.ini配置必须完全一致。默认没有dmmal.ini文件,需单独创建并添加以下内容
MAL_CHECK_INTERVAL = 5 #MAL链路检测时间间隔
MAL_CONN_FAIL_INTERVAL = 5 #判定MAL链路断开的时间
[MAL_INST1]
MAL_INST_NAME = DW01 #实例名,与dm.ini中的INSTANCE_NAME一致
MAL_HOST = 10.0.0.111 #MAL系统监听TCP连接的IP地址(内网IP)
MAL_PORT = 5238 #MAL系统监听TCP连接的端口
MAL_INST_HOST = 192.0.2.111 #实例的对外服务IP地址
MAL_INST_PORT = 5236 #实例的对外服务端口,和dm.ini中的PORT_NUM一致
MAL_DW_PORT = 5239 #守护进程监听TCP连接的端口
MAL_INST_DW_PORT = 5237 #实例监听守护进程TCP连接的端口
[MAL_INST2]
MAL_INST_NAME = DW02
MAL_HOST = 10.0.0.112
MAL_PORT = 5238
MAL_INST_HOST = 192.0.2.112
MAL_INST_PORT = 5236
MAL_DW_PORT = 5239
MAL_INST_DW_PORT = 5237
⑶dmarch.ini
归档配置文件,在两个主备节点环境中,数据同步的ARCH_DEST互相写对方的实例。
如果之前已启用了归档,则该文件已存在;若未启用归档,则需手工创建并添加以下内容:
ARCH_WAIT_APPLY = 1 #0表示备库收到REDO日志后立即响应主库,1表示重演完成后响应主库
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = DW02 #主库写备库,备库写主库
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/arch
ARCH_FILE_SIZE = 64
ARCH_SPACE_LIMIT = 0
⑷dmwatcher.ini
守护进程配置文件,除了异步主备,其他类型的主备必须配置为全局守护类型。
主备节点都创建并添加以下内容:
[GRP1]
DW_TYPE = GLOBAL #全局守护类型
DW_MODE = AUTO #自动切换模式
DW_ERROR_TIME = 10 #远程守护进程故障认定时间
INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间
INST_ERROR_TIME = 10 #本地实例故障认定时间
INST_OGUID = 453331 #守护进程唯一OGUID值
INST_INI = /dm8/data/dwdb/dm.ini #dm.ini配置文件路径
INST_AUTO_RESTART = 1 #打开实例的自动启动功能
INST_STARTUP_CMD = /dm8/bin/dmserver #命令行方式启动
RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阈值,默认关闭
RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阈值,默认关闭
⑸启动主备库
为保证主备库数据一致性,在数据守护搭建时,这一步必须以配置模式(MOUNT)启动主备库。
①启动主库
//启动数据库到MOUNT状态
[dmdba@dmdb dwdb]$ dmserver /dm8/data/dwdb/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134283914-20220901-168571-20009 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-09-01
file lsn: 42149
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.
②启动备库
//启动数据库到MOUNT状态
[dmdba@dmdb02 dwdb]$ dmserver /dm8/data/dwdb/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134283914-20220901-168571-20009 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-09-01
file lsn: 42149
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
分别在主库和备库执行以下命令设置数据守护环境的OGUID
[dmdba@dmdb ~]$ disql sysdba/SYSDBA
服务器[LOCALHOST:5236]:处于普通配置状态
登录使用时间 : 2.587(ms)
disql V8
SQL> sp_set_oguid(453331); //设置OGUID
DMSQL 过程已成功完成
已用时间: 4.552(毫秒). 执行号:0.
⑺设置数据库模式
使用SQL语句,将主库的模式设置为PRIMARY,备库的模式设置为STANDBY。
SQL> alter database primary; //主库执行,修改数据库模式为主库
操作已执行
已用时间: 52.335(毫秒). 执行号:0.
SQL> alter database standby; //备库执行,修改数据库模式为备库
操作已执行
已用时间: 49.903(毫秒). 执行号:0.
以上操作是从NORMAL模式修改到其他模式,如果当前数据库不是NORMAL模式,需要先修改dm.ini中的ALTER_MODE_STATUS值为1,使允许修改数据库模式,修改对应模式后再修改回0
SQL> sp_set_para_value(1,'ALTER_MODE_STATUS',1); //修改数据库模式为允许修改
SQL> alter database standby; //修改数据库模式为备库
SQL> sp_set_para_value(1,'ALTER_MODE_STATUS',0); //修改数据库模式为禁止修改
4.注册并启动守护进程
⑴注册守护进程服务
以root用户将守护进程注册到服务,主备库都需要注册,操作步骤相同
[root@dmdb ~]# /dm8/script/root/dm_service_installer.sh -t dmwatcher -watcher_ini /dm8/data/dwdb/dmwatcher.ini -p dwdb
Created symlink /etc/systemd/system/multi-user.target.wants/DmWatcherServicedwdb.service → /usr/lib/systemd/system/DmWatcherServicedwdb.service.
创建服务(DmWatcherServicedwdb)完成
⑵启动守护进程
[root@dmdb ~]# systemctl start DmWatcherServicedwdb //主备都启
若不配置服务,则通过以下命令前台启动watcher
./dmwatcher /dm8/data/dwdb/dmwatcher.ini
⑶守护进程启动后,会自动将库OPEN
SQL> select instance_name,status$,mode$ from v$instance;
行号 INSTANCE_NAME STATUS$ MODE$
---------- ------------- ------- ---------
1 DW01 OPEN PRIMARY
SQL> select instance_name,status$,mode$ from v$instance;
行号 INSTANCE_NAME STATUS$ MODE$
---------- ------------- ------- ---------
1 DW02 OPEN STANDBY
5.配置监视器
监视器上只需要安装达梦数据库软件即可
⑴配置监视器参数文件
在监控节点的/dm8/data目录下创建并修改dmmonitor.ini配置文件
MON_DW_CONFIRM = 1 #确认监视器模式
MON_LOG_PATH = /dm8/log #监视器日志文件存放路径
MON_LOG_INTERVAL = 60 #每隔60s定时记录系统信息到日志文件
MON_LOG_FILE_SIZE = 32 #每个日志文件最大为32MB
MON_LOG_SPACE_LIMIT = 0 #不限定日志文件总占用空间
[GRP1]
MON_INST_OGUID = 453331 #组GRP1的唯一OGUID值
MON_DW_IP = 10.0.0.111:5239
MON_DW_IP = 10.0.0.112:5239 #IP和PORT与dmmal.ini中MAL_HOST和MAL_DW_PORT保持一致
⑵以root用户注册服务
[root@dmdb-mon data]# /dm8/script/root/dm_service_installer.sh -t dmmonitor -monitor_ini /dm8/data/dmmonitor.ini -p dwdb
Created symlink /etc/systemd/system/multi-user.target.wants/DmMonitorServicedwdb.service → /usr/lib/systemd/system/DmMonitorServicedwdb.service.
创建服务(DmMonitorServicedwdb)完成
⑶启动监视器
[root@dmdb-mon data]# systemctl start DmMonitorServicedwdb
[root@dmdb-mon data]# ps -ef|grep dmmonitor
dmdba 4857 1 0 01:49 ? 00:00:00 /dm8/bin/dmmonitor path=/dm8/data/dmmonitor.ini
root 4883 4225 0 01:49 pts/1 00:00:00 grep dmmoni
6.主备同步测试
⑴主库进行建表操作
SQL> create table test as select * from dba_tables;
操作已执行
已用时间: 59.360(毫秒). 执行号:700.
SQL> select count(1) from test;
行号 COUNT(1)
---------- --------------------
1 73
已用时间: 0.799(毫秒). 执行号:701.
⑵备库查询验证
SQL> select instance_name,status$,mode$ from v$instance;
行号 INSTANCE_NAME STATUS$ MODE$
---------- ------------- ------- -------
1 DW02 OPEN STANDBY
已用时间: 3.315(毫秒). 执行号:100.
SQL> select count(1) from test;
行号 COUNT(1)
---------- --------------------
1 73
已用时间: 2.794(毫秒). 执行号:101.
至此,2节点的实时主备搭建完成。