数据守护 data watch

1、规化
datawatch1 192.168.94.120 主机
datawatch2 192.168.94.121 备机
dwm 192.168.94.119 监视器

端口规化：
数据库名实例名 port_num mal_inst_dw_port mal_host mal_port mal_dw_port
dwp1 dwp1 5246 45101 192.168.94.120 55101 65101
dwp2 dwp2 5246 45121 192.168.94.121 55121 65121

2、建库
• 主库建库
dminit path=/dm/data DB_NAME=dwp1 INSTANCE_NAME=dwp1 port_num=5246 PAGE_SIZE=8 EXTENT_SIZE=16 LOG_SIZE=500 CASE_SENSITIVE=Y SYSDBA_PWD=dameng123
• 备库建库
dminit path=/dm/data DB_NAME=dwp2 INSTANCE_NAME=dwp2 port_num=5246 PAGE_SIZE=8 EXTENT_SIZE=16 LOG_SIZE=500 CASE_SENSITIVE=Y SYSDBA_PWD=dameng123
• 主库初始化数据库(以root用户运行)
/dm/script/root/dm_service_installer.sh -t dmserver -p dwp1 -dm_ini /dm/data/dwp1/dm.ini
• 备库初始化数据库(以root用户运行)
/dm/script/root/dm_service_installer.sh -t dmserver -p dwp2 -dm_ini /dm/data/dwp2/dm.ini
• 开启库规档并修改规档目录（主、备库双个节点运行）
SQL> alter database archivelog;
操作已执行
已用时间: 5.651(毫秒). 执行号:0.
SQL>
SQL> select arch_mode from v$database;

行号 ARCH_MODE
---------- ---------
1 Y

SQL> alter database add archivelog 'type=local,dest=/dm/arch,file_size=1024,space_limit=10240';
操作已执行
已用时间: 2.502(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 6.138(毫秒). 执行号:0.
SQL> select ARCH_DEST,ARCH_FILE_SIZE,ARCH_SPACE_LIMIT from v$dm_arch_ini;

行号 ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT
---------- --------- -------------- ----------------
1 /dm/arch 1024 10240

已用时间: 0.198(毫秒). 执行号:60503.
[dmdba@datawatch1 ~]$ DmServicedwp1 stop
Stopping DmServicedwp1: [ OK ]
3、对主库进行全库备份
[dmdba@datawatch1 ~]$ dmrman CTLSTMT="BACKUP DATABASE '/dm/data/dwp1/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm/arch/BACKUP_FILE_01'"
dmrman V8
BACKUP DATABASE '/dm/data/dwp1/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm/arch/BACKUP_FILE_01'
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[41497], file_lsn[41497]
Processing backupset /dm/arch/BACKUP_FILE_01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.213
4、拷贝备份集到备库
[dmdba@datawatch1 arch]$ scp -r BACKUP_FILE_01 192.168.94.121:/dm/arch/
The authenticity of host '192.168.94.121 (192.168.94.121)' can't be established.
ECDSA key fingerprint is SHA256:cqepnZdcLFh0iRwmCVHJ56msNvI0ip9hXGj1pjGg3uc.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.94.121' (ECDSA) to the list of known hosts.

Authorized users only. All activities may be monitored and reported.
dmdba@192.168.94.121's password:
BACKUP_FILE_01.bak 100% 8838KB 159.6MB/s 00:00
BACKUP_FILE_01.meta 100% 98KB 33.6MB/s 00:00
5、恢复备份集到备库
[dmdba@datawatch2 arch]$ dmrman CTLSTMT="RESTORE DATABASE '/dm/data/dwp2/dm.ini' FROM BACKUPSET '/dm/arch/BACKUP_FILE_01'"
dmrman V8
RESTORE DATABASE '/dm/data/dwp2/dm.ini' FROM BACKUPSET '/dm/arch/BACKUP_FILE_01'
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.844
[dmdba@datawatch2 arch]$ dmrman CTLSTMT="RECOVER DATABASE '/dm/data/dwp2/dm.ini' FROM BACKUPSET '/dm/arch/BACKUP_FILE_01'"
dmrman V8
RECOVER DATABASE '/dm/data/dwp2/dm.ini' FROM BACKUPSET '/dm/arch/BACKUP_FILE_01'
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[41497], file_lsn[41497]
recover successfully!
time used: 426.566(ms)
[dmdba@datawatch2 arch]$ dmrman CTLSTMT="RECOVER DATABASE '/dm/data/dwp2/dm.ini' UPDATE DB_MAGIC"
dmrman V8
RECOVER DATABASE '/dm/data/dwp2/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[41497], file_lsn[41497]
recover successfully!
time used: 00:00:01.073
6、修改主库配置文件
--------主库primary配置------------
/dm/data/dwp1/dm.ini
----------
INSTANCE_NAME = dwp1
PORT_NUM = 5246
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1
RLOG_SEND_APPLY_MON = 64

/dm/data/dwp1/dmmal.ini
------------
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = dwp1
MAL_HOST = 192.168.94.120
MAL_PORT = 55101
MAL_INST_HOST = 192.168.94.120
MAL_INST_PORT = 5246
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = dwp2
MAL_HOST = 192.168.94.121
MAL_PORT = 55121
MAL_INST_HOST = 192.168.94.121
MAL_INST_PORT = 5246
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

/dm/data/dwp1/dmarch.ini （注：arch_dest备机写主机实例，主机写备机实例）
------------
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = dwp2
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

/dm/data/dwp1/dmwatcher.ini
----------------
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dm8/data/dwp1/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm/bin/dmserver
RLOG_SEND_THRESHOLD = 0
RLOG_APPLY_THRESHOLD = 0
7、启动主库到mount
[dmdba@datawatch1 bin]$ dmserver /dm/data/dwp1/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134284094-20231108-207962-20067 startup...
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
8、修改主库的模式和设置oguid
[dmdba@datawatch1 dm]$ disql sysdba/dameng123:5246

服务器[LOCALHOST:5246]:处于普通配置状态
登录使用时间 : 2.371(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
DMSQL 过程已成功完成
已用时间: 9.978(毫秒). 执行号:1.
SQL> sp_set_oguid(453331);
DMSQL 过程已成功完成
已用时间: 4.883(毫秒). 执行号:2.
SQL> alter database primary;
操作已执行
已用时间: 13.857(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL 过程已成功完成
已用时间: 9.781(毫秒). 执行号:3.
9、修改备库配置文件
--------主库primary配置------------
/dm/data/dwp2/dm.ini
----------
INSTANCE_NAME = dwp2
PORT_NUM = 5246
DW_INACTIVE_INTERVAL = 60
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
MAL_INI = 1
ARCH_INI = 1
RLOG_SEND_APPLY_MON = 64

/dm/data/dwp2/dmmal.ini
------------
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = dwp1
MAL_HOST = 192.168.94.120
MAL_PORT = 55101
MAL_INST_HOST = 192.168.94.120
MAL_INST_PORT = 5246
MAL_DW_PORT = 65101
MAL_INST_DW_PORT = 45101
[MAL_INST2]
MAL_INST_NAME = dwp2
MAL_HOST = 192.168.94.121
MAL_PORT = 55121
MAL_INST_HOST = 192.168.94.121
MAL_INST_PORT = 5246
MAL_DW_PORT = 65121
MAL_INST_DW_PORT = 45121

/dm/data/dwp2/dmarch.ini （注：arch_dest备机写主机实例，主机写备机实例）
------------
[ARCHIVE_REALTIME]
ARCH_TYPE = REALTIME
ARCH_DEST = dwp1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm/arch
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 0

/dm/data/dwp2/dmwatcher.ini
----------------
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = AUTO
DW_ERROR_TIME = 10
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 10
INST_OGUID = 453331
INST_INI = /dm/data/dwp2/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm/bin/dmserver
RLOG_APPLY_THRESHOLD = 0
10、启动备库到mount
[dmdba@datawatch1 bin]$ dmserver /dm/data/dwp2/dm.ini mount
file dm.key not found, use default license!
version info: develop
DM Database Server 64 V8 03134284094-20231108-207962-20067 startup...
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
11、修改备库的模式和设置oguid
[dmdba@datawatch2 ~]$ disql sysdba/dameng123:5246

服务器[LOCALHOST:5246]:处于普通配置状态
登录使用时间 : 2.333(ms)
disql V8
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
DMSQL 过程已成功完成
已用时间: 10.170(毫秒). 执行号:1.
SQL> sp_set_oguid(453331);
DMSQL 过程已成功完成
已用时间: 6.282(毫秒). 执行号:2.
SQL> alter database standby;
操作已执行
已用时间: 15.924(毫秒). 执行号:0.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL 过程已成功完成
已用时间: 10.005(毫秒). 执行号:3.
12、启动主库守护进程
[dmdba@datawatch1 dm]$ dmwatcher /dm/data/dwp1/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
13、启动备库守护进程
[dmdba@datawatch1 dm]$ dmwatcher /dm/data/dwp2/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
14、确认主、备库都处于打开状态
[dmdba@datawatch1 ~]$ disql sysdba/dameng123:5246

服务器[LOCALHOST:5246]:处于主库打开状态
登录使用时间 : 2.322(ms)
[dmdba@datawatch2 ~]$ disql sysdba/dameng123:5246

服务器[LOCALHOST:5246]:处于备库打开状态
登录使用时间 : 2.208(ms)
15、配置监视器
/dm/dmmonitor.ini
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dm/data/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 0
[GRP1]
MON_INST_OGUID = 453331
MON_DW_IP = 192.168.94.120:65101
MON_DW_IP = 192.168.94.121:65121
16、注册监视器服务
/dm/script/root/dm_service_installer.sh -t dmmonitor -p ITSM_DB -monitor_ini /dm/dmmonitor.ini
17、查看监视器
[dmdba@dwm ~]$ dmmonitor /dm/dmmonitor.ini
[monitor] 2024-10-28 23:31:13: DMMONITOR[4.0] V8
[monitor] 2024-10-28 23:31:14: DMMONITOR[4.0] IS READY.

[monitor] 2024-10-28 23:31:14:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(DWP2), THE FIRST LINE IS SELF INFO.

DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2024-10-28 23:31:14 TRUE 264704622 ::ffff:192.168.94.119 DMMONITOR[4.0] V8

#--------------------------------------------------------------------------------#

[monitor] 2024-10-28 23:31:14: 收到守护进程(DWP2)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2024-10-28 23:31:14 OPEN OK DWP2 OPEN STANDBY NULL 2 41670 41670

[monitor] 2024-10-28 23:31:14: 收到守护进程(DWP1)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2024-10-28 23:31:14 OPEN OK DWP1 OPEN PRIMARY VALID 2 41670 41670

show
2024-10-28 23:31:19
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 453331 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
192.168.94.120 65101 2024-10-28 23:31:19 GLOBAL VALID OPEN DWP1 OK 1 1 OPEN PRIMARY DSC_OPEN REALTIME 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
192.168.94.120 5246 OK DWP1 OPEN PRIMARY 0 0 REALTIME VALID 4832 41670 4832 41670 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
192.168.94.121 65121 2024-10-28 23:31:19 GLOBAL VALID OPEN DWP2 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME 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
192.168.94.121 5246 OK DWP2 OPEN STANDBY 0 0 REALTIME VALID 4832 41670 4832 41670 NONE

DATABASE(DWP2) APPLY INFO FROM (DWP1), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4832, 4832, 4832], (RLSN, SLSN, KLSN)[41670, 41670, 41670], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (41670)

#================================================================================#
18、主库注册守护进程
注册主库的守护进程
[root@~]# /dm/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /dm/data/dwp1/dmwatcher.ini
Created symlink /etc/systemd/system/multi-user.target.wants/DmWatcherServiceWatcher.service → /usr/lib/systemd/system/DmWatcherServiceWatcher.service.
创建服务(DmWatcherServiceWatcher)完成

注册备库的守护进程
[root@~]# /dm/script/root/dm_service_installer.sh -t dmwatcher -p Watcher -watcher_ini /dm/data/dwp2/dmwatcher.ini

作者