pacemarker 主備備是否可以單獨配置優先級切換詳情 - sed,SQL,數據庫,機器學習,人工智能 mob64ca140b0bc8 博客

部署環境
部署規劃

主庫
備庫
守護進程規劃

數據準備

初始化實例

主庫
備庫

註冊服務

主庫
備庫

啓動主庫
配置歸檔
備份還原

聯機備份EP01並脱機還原到EP11
聯機備份EP02並脱機還原到EP22

配置文件

配置主庫 GRP1_MPP_EP01

dm.ini
dmmal.ini
dmarch.ini
dmmpp.ctl
啓動主庫
設置OGUID
修改數據庫模式

配置主庫GRP2_MPP_EP02

dm.ini
dmmal.ini
dmarch.ini
dmmpp.ctl
啓動主庫
設置OGUID
修改數據庫模式

配置備庫GRP1_MPP_EP11

dm.ini
dmmal.ini
dmarch.ini
dmmpp.ctl
啓動備庫
設置OGUID
修改數據庫模式

配置備庫GRP2_MPP_EP22

dm.ini
dmmal.ini
dmarch.ini
dmmpp.ctl
啓動備庫
設置OGUID
修改數據庫模式

配置dmwatcher
配置dmmonitor
啓動watcher
啓動monitor
客户端配置
測試驗證

部署環境

機器名	IP地址	初始狀態	操作系統
A	8.142.141.128 172.21.215.74 (內網IP)	主庫 GRP1_MPP_EP01 備庫 GRP2_MPP_EP22	Debian 9
B	39.103.139.80 172.17.44.74 (內網IP)	主庫 GRP1_MPP_EP02 備庫 GRP2_MPP_EP11	Debian 9
C	本機	監視器	windows 11

部署規劃

主庫

實例名	PORT_NUM	MAL_INST_DW_PORT	MAL_HOST	MAL_PORT	MAL_DW_PORT	MPP_SEQNO
GRP1_MPP_EP01	5237	5243	172.21.215.74	5337	5253	0
GRP2_MPP_EP02	5237	5243	172.17.44.74	5337	5253	1

備庫

實例名	PORT_NUM	MAL_INST_DW_PORT	MAL_HOST	MAL_PORT	MAL_DW_PORT	MPP_SEQNO
GRP1_MPP_EP11	5238	5244	172.17.44.74	5338	5254	0
GRP2_MPP_EP22	5238	5244	172.21.215.74	5338	5254	1

守護進程規劃

組名	實例名	所在機器
GRP1	GRP1_MPP_EP01	172.21.215.74
	GRP1_MPP_EP11	172.17.44.74
GRP2	GRP2_MPP_EP02	172.17.44.74
	GRP2_MPP_EP22	172.21.215.74

數據準備

初始化實例

主庫

--A機器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP01 INSTANCE_NAME=GRP1_MPP_EP01 PORT_NUM=5237

--B機器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP02 INSTANCE_NAME=GRP2_MPP_EP02 PORT_NUM=5237

備庫

--A機器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP22 INSTANCE_NAME=GRP2_MPP_EP22 PORT_NUM=5238

--B機器
dminit path=/dm8/data/ PAGE_SIZE=32 EXTENT_SIZE=32 CASE_SENSITIVE=y CHARSET=1 DB_NAME=EP11 INSTANCE_NAME=GRP1_MPP_EP11 PORT_NUM=5238

註冊服務

主庫

./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP01/dm.ini -p EP01
./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP02/dm.ini -p EP02

備庫

./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP22/dm.ini -p EP22
./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/EP11/dm.ini -p EP11

啓動主庫

root@lxm2:/home/dmdba/dmdbms/script/root# systemctl start DmServiceEP01.service 
root@lxm:/home/dmdba/dmdbms/script/root# systemctl start DmServiceEP02.service

配置歸檔

--A機器
dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237

Server[localhost:5237]:mode is normal, state is open
login used time : 2.574(ms)
disql V8
SQL> ALTER DATABASE MOUNT;
executed successfully
used time: 0.643(ms). Execute id is 0.
SQL> ALTER DATABASE ADD ARCHIVELOG 'dest=/dm8/data/EP01/dm_arch,type=local,file_size=64,space_limit=20480';
executed successfully
used time: 3.270(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 7.166(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 18.316(ms). Execute id is 0.
SQL> SELECT ARCH_MODE FROM V$DATABASE;

LINEID     ARCH_MODE
---------- ---------
1          Y

used time: 2.610(ms). Execute id is 55400.


--B機器
ALTER DATABASE MOUNT;
ALTER DATABASE ADD ARCHIVELOG 'dest=/dm8/data/EP02/dm_arch,type=local,file_size=64,space_limit=20480';
alter database archivelog;
alter database open;
SELECT ARCH_MODE FROM V$DATABASE;

備份還原

聯機備份EP01並脱機還原到EP11

--備份
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/BACKUP_FILE_01';
executed successfully
used time: 00:00:03.753. Execute id is 55501.
SQL> exit

--拷貝
dmdba@lxm2:/dm8/data$ scp -r BACKUP_FILE_01/ dmdba@39.103.139.80:/dm8/data
dmdba@39.103.139.80's password: 
BACKUP_FILE_01.bak                                                                                                                                       100%   89MB  13.4MB/s   00:06    
BACKUP_FILE_01.meta                                                                                                                                      100%   89KB   8.5MB/s   00:00    
BACKUP_FILE_01_1.bak                                                                                                                                     100%  485KB  15.0MB/s   00:00    
--備機還原
dmdba@lxm:~$ rlwrap dmrman
dmrman V8
RESTORE DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
RESTORE DATABASE '/dm8/data/EP11/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01';
file dm.key not found, use default license!
RECOVER DATABASE '/dm8/data/EP11/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.765
RMAN> RECOVER DATABASE '/dm8/data/EP11/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[37302], file_lsn[37302]
[Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]                               
recover successfully!
time used: 00:00:02.540
RMAN> RECOVER DATABASE '/dm8/data/EP11/dm.ini' UPDATE DB_MAGIC;
RECOVER DATABASE '/dm8/data/EP11/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[38498], file_lsn[38498]
recover successfully!
time used: 00:00:01.055

聯機備份EP02並脱機還原到EP22

--備份
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/BACKUP_FILE_02';
--拷貝
dmdba@lxm2:/dm8/data$ scp -r BACKUP_FILE_02/ dmdba@8.142.141.128:/dm8/data
--備庫還原
RESTORE DATABASE '/dm8/data/EP22/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_02';
RECOVER DATABASE '/dm8/data/EP22/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_02';
RECOVER DATABASE '/dm8/data/EP22/dm.ini' UPDATE DB_MAGIC;

配置文件

配置主庫 GRP1_MPP_EP01

dm.ini

INSTANCE_NAME = GRP1_MPP_EP01
PORT_NUM = 5237 #數據庫實例監聽端口
DW_INACTIVE_INTERVAL = 60 #接收守護進程消息超時時間
ALTER_MODE_STATUS = 0 #不允許手工方式修改實例模式/狀態/OGUID
ENABLE_OFFLINE_TS = 2 #不允許備庫 OFFLINE 表空間
MAL_INI = 1 #打開 MAL 系統
ARCH_INI = 1 #打開歸檔配置
MPP_INI = 1 #啓用 MPP 配置
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_MPP_EP01 #實例名，和 dm.ini 中的 INSTANCE_NAME 一致
 MAL_HOST = 172.21.215.74 #MAL 系統監聽 TCP 連接的 IP 地址
 MAL_PORT = 5337 #MAL 系統監聽 TCP 連接的端口
 MAL_INST_HOST = 8.142.141.128 #實例的對外服務 IP 地址
 MAL_INST_PORT = 5237 #實例的對外服務端口，和 dm.ini 中的 PORT_NUM 一致
 MAL_DW_PORT = 5253 #實例對應的守護進程監聽 TCP 連接的端口
 MAL_INST_DW_PORT = 5243 #實例監聽守護進程 TCP 連接的端口
[MAL_INST2]
 MAL_INST_NAME = GRP2_MPP_EP02
 MAL_HOST = 172.17.44.74 
 MAL_PORT = 5337
 MAL_INST_HOST = 39.103.139.80
 MAL_INST_PORT = 5237
 MAL_DW_PORT = 5253
MAL_INST_DW_PORT = 5243
[MAL_INST3]
 MAL_INST_NAME = GRP1_MPP_EP11
 MAL_HOST = 172.17.44.74 
 MAL_PORT = 5338
 MAL_INST_HOST = 39.103.139.80
 MAL_INST_PORT = 5238
 MAL_DW_PORT = 5254
MAL_INST_DW_PORT = 5244
[MAL_INST4]
 MAL_INST_NAME = GRP2_MPP_EP22
 MAL_HOST = 172.21.215.74
 MAL_PORT = 5338
 MAL_INST_HOST = 8.142.141.128
 MAL_INST_PORT = 5238
 MAL_DW_PORT = 5254
 MAL_INST_DW_PORT = 5244

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #實時歸檔類型
ARCH_DEST = GRP1_MPP_EP11 #實時歸檔目標實例名
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地歸檔類型
ARCH_DEST = /dm8/data/EP01/arch #本地歸檔文件存放路徑
ARCH_FILE_SIZE = 128 #單位 Mb，本地單個歸檔文件最大值
ARCH_SPACE_LIMIT = 0 #單位 Mb，0 表示無限制，範圍 1024~4294967294M

dmmpp.ctl

--新建dmmpp.ini
[service_name1]
mpp_seq_no = 0
mpp_inst_name = GRP1_MPP_EP01
[service_name2] 
mpp_seq_no = 1
mpp_inst_name = GRP2_MPP_EP02

--生成dmmpp.ctl
dmctlcvt TYPE=2 SRC=/dm8/data/EP01/dmmpp.ini DEST=/dm8/data/EP01/dmmpp.ctl

啓動主庫

dmserver /dm/data/EP01/dm.ini mount

設置OGUID

dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237#"{mpp_type=local}"

Server[localhost:5237]:mode is normal, state is mount
login used time : 1.708(ms)
disql V8
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45330);
DMSQL executed successfully
used time: 9.792(ms). Execute id is 0.
SQL> DMSQL executed successfully
used time: 9.320(ms). Execute id is 1.
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
DMSQL executed successfully
used time: 4.399(ms). Execute id is 2.

修改數據庫模式

SQL>alter database primary;

配置主庫GRP2_MPP_EP02

dm.ini

INSTANCE_NAME = GRP2_MPP_EP02
PORT_NUM = 5237 #數據庫實例監聽端口
DW_INACTIVE_INTERVAL = 60 #接收守護進程消息超時時間
ALTER_MODE_STATUS = 0 #不允許手工方式修改實例模式/狀態/OGUID
ENABLE_OFFLINE_TS = 2 #不允許備庫 OFFLINE 表空間
MAL_INI = 1 #打開 MAL 系統
ARCH_INI = 1 #打開歸檔配置
MPP_INI = 1 #啓用 MPP 配置
RLOG_SEND_APPLY_MON = 64 #統計最近 64 次的日誌發送信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #實時歸檔類型
ARCH_DEST = GRP2_MPP_EP22 #實時歸檔目標實例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地歸檔類型
ARCH_DEST = /dm8/data/EP02/arch#本地歸檔文件存放路徑
ARCH_FILE_SIZE = 128 #單位 Mb，本地單個歸檔文件最大值
ARCH_SPACE_LIMIT = 0 #單位 Mb，0 表示無限制，範圍 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

啓動主庫

dmserver /dm8/data/EP02/dm.ini mount

設置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45331);
SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);

修改數據庫模式

alter database primary;

配置備庫GRP1_MPP_EP11

dm.ini

INSTANCE_NAME = GRP1_MPP_EP11
PORT_NUM = 5238 #數據庫實例監聽端口
DW_INACTIVE_INTERVAL = 60 #接收守護進程消息超時時間
ALTER_MODE_STATUS = 0 #不允許手工方式修改實例模式/狀態/OGUID
ENABLE_OFFLINE_TS = 2 #不允許備庫 OFFLINE 表空間
MAL_INI = 1 #打開 MAL 系統
ARCH_INI = 1 #打開歸檔配置
MPP_INI = 1 #打開 MPP 配置
RLOG_SEND_APPLY_MON = 64 #統計最近 64 次的日誌重演信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #實時歸檔類型
ARCH_DEST = GRP1_MPP_EP01 #實時歸檔目標實例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地歸檔類型
ARCH_DEST = /dm8/data/EP11/arch#本地歸檔文件存放路徑
ARCH_FILE_SIZE = 128 #單位 Mb，本地單個歸檔文件最大值
ARCH_SPACE_LIMIT = 0 #單位 Mb，0 表示無限制，範圍 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

啓動備庫

dmserver /dm/data/EP11/DAMENG/dm.ini mount

設置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45330);
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);

配置備庫GRP2_MPP_EP22

dm.ini

INSTANCE_NAME = GRP2_MPP_EP22
PORT_NUM = 5238 #數據庫實例監聽端口
DW_INACTIVE_INTERVAL = 60 #接收守護進程消息超時時間
ALTER_MODE_STATUS = 0 #不允許手工方式修改實例模式/狀態/OGUID
ENABLE_OFFLINE_TS = 2 #不允許備庫 OFFLINE 表空間
MAL_INI = 1 #打開 MAL 系統
ARCH_INI = 1 #打開歸檔配置
MPP_INI = 1 #打開 MPP 配置
RLOG_SEND_APPLY_MON = 64 #統計最近 64 次的日誌重演信息

dmmal.ini

同GRP1_MPP_EP01.

dmarch.ini

[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME #實時歸檔類型
ARCH_DEST = GRP2_MPP_EP02 #實時歸檔目標實例名

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL #本地歸檔類型
ARCH_DEST = /dm8/data/EP22/arch #本地歸檔文件存放路徑
ARCH_FILE_SIZE = 128 #單位 Mb，本地單個歸檔文件最大值
ARCH_SPACE_LIMIT = 0 #單位 Mb，0 表示無限制，範圍 1024~4294967294M

dmmpp.ctl

同GRP1_MPP_EP01.

啓動備庫

dmserver /dm8/data/EP22/dm.ini mount

設置OGUID

SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
sp_set_oguid(45331);
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);

配置dmwatcher

#A機器
[GRP1]
DW_TYPE = GLOBAL #全局守護類型
DW_MODE = AUTO #自動切換模式
DW_ERROR_TIME = 10 #遠程守護進程故障認定時間
INST_RECOVER_TIME = 60 #主庫守護進程啓動恢復的間隔時間
INST_ERROR_TIME = 10 #本地實例故障認定時間
INST_OGUID = 45330 #守護系統唯一 OGUID 值
INST_INI = /dm8/data/EP01/dm.ini #dm.ini 配置文件路徑
INST_AUTO_RESTART = 1 #打開實例的自動啓動功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式啓動
RLOG_SEND_THRESHOLD = 0 #指定主庫發送日誌到備庫的時間閾值，默認關閉
RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閾值，默認關閉
[GRP2]
DW_TYPE = GLOBAL #全局守護類型
DW_MODE = AUTO #自動切換模式
DW_ERROR_TIME = 10 #遠程守護進程故障認定時間
INST_RECOVER_TIME = 60 #主庫守護進程啓動恢復的間隔時間
INST_ERROR_TIME = 10 #本地實例故障認定時間
INST_OGUID = 45331 #守護系統唯一 OGUID 值
INST_INI = /dm8/data/EP22/dm.ini #dm.ini 配置文件路徑
INST_AUTO_RESTART = 1 #打開實例的自動啓動功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式啓動
RLOG_SEND_THRESHOLD = 0 #指定主庫發送日誌到備庫的時間閾值，默認關閉
RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閾值，默認關閉

#B機器
[GRP1]
DW_TYPE = GLOBAL #全局守護類型
DW_MODE = AUTO #自動切換模式
DW_ERROR_TIME = 10 #遠程守護進程故障認定時間
INST_RECOVER_TIME = 60 #主庫守護進程啓動恢復的間隔時間
INST_ERROR_TIME = 10 #本地實例故障認定時間
INST_OGUID = 45330 #守護系統唯一 OGUID 值
INST_INI = /dm8/data/EP11/dm.ini #dm.ini 配置文件路徑
INST_AUTO_RESTART = 1 #打開實例的自動啓動功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式啓動
RLOG_SEND_THRESHOLD = 0 #指定主庫發送日誌到備庫的時間閾值，默認關閉
RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閾值，默認關閉
[GRP2]
DW_TYPE = GLOBAL #全局守護類型
DW_MODE = AUTO #自動切換模式
DW_ERROR_TIME = 10 #遠程守護進程故障認定時間
INST_RECOVER_TIME = 60 #主庫守護進程啓動恢復的間隔時間
INST_ERROR_TIME = 10 #本地實例故障認定時間
INST_OGUID = 45331 #守護系統唯一 OGUID 值
INST_INI = /dm8/data/EP02/dm.ini #dm.ini 配置文件路徑
INST_AUTO_RESTART = 1 #打開實例的自動啓動功能
INST_STARTUP_CMD = /home/dmdba/dmdbms/bin/dmserver #命令行方式啓動
RLOG_SEND_THRESHOLD = 0 #指定主庫發送日誌到備庫的時間閾值，默認關閉
RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閾值，默認關閉

配置dmmonitor

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 = 45330 #組 GRP1 的唯一 OGUID 值
#以下配置為監視器到組 GRP1 的守護進程的連接信息，以―IP:PORT‖的形式配置
#IP 對應 dmmal.ini 中的 MAL_HOST，PORT 對應 dmmal.ini 中的 MAL_DW_PORT
MON_DW_IP = 8.142.141.128:5253
MON_DW_IP = 39.103.139.80:5254
[GRP2]
MON_INST_OGUID = 45331 #組 GRP2 的唯一 OGUID 值
#以下配置為監視器到組 GRP2 的守護進程的連接信息，以―IP:PORT‖的形式配置
#IP 對應 dmmal.ini 中的 MAL_HOST，PORT 對應 dmmal.ini 中的 MAL_DW_PORT
MON_DW_IP = 8.142.141.128:5254
MON_DW_IP = 39.103.139.80:5253

啓動watcher

#A機器，B機器也相同。
dmdba@lxm2:~$ dmwatcher /dm8/data/dmwatcher.ini
DMWATCHER[4.0] V8
DMWATCHER[4.0] IS READY
show
2022-07-10 18:08:44
---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART DW_STATUS       DW_SUB_STATUS   DW_CTL_STATUS   
GRP1             GLOBAL    AUTO      45330       TRUE      TRUE         STARTUP         SUB_STATE_START VALID           

INST_OK   NAME             SVR_MODE  SYS_STATUS   RTYPE     FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG 
OK        GRP1_MPP_EP01    PRIMARY   MOUNT        REALTIME  5810            39581           5810            39581           0            
---------------------------------------------------------------------------

---------------------------------------------------------------------------
GROUP_NAME       TYPE      MODE      OGUID       MPP_FLAG  AUTO_RESTART DW_STATUS       DW_SUB_STATUS   DW_CTL_STATUS   
GRP2             GLOBAL    AUTO      45331       TRUE      TRUE         STARTUP         SUB_STATE_START VALID           

INST_OK   NAME             SVR_MODE  SYS_STATUS   RTYPE     FSEQ            FLSN            CSEQ            CLSN            DW_STAT_FLAG 
OK        GRP2_MPP_EP22    STANDBY   MOUNT        REALTIME  5084            38632           5084            38632           0            

DATABASE(GRP2_MPP_EP22) APPLY INFO:
REDOS_PARALLEL_NUM (1) 
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[5084, 5084, 5084], (RLSN, SLSN, KLSN)[38632, 38632, 38632], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (38632)

---------------------------------------------------------------------------

啓動monitor

C:\dmdbms\bin>dmmonitor.exe C:\dmdbms\data\dmmonitor.ini
[monitor]         2022-07-10 18:11:20: DMMONITOR[4.0] V8
[monitor]         2022-07-10 18:11:20: DMMONITOR[4.0] IS READY.

[monitor]         2022-07-10 18:11:20: Received message from(GRP2_MPP_EP22)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP2_MPP_EP22    OPEN        STANDBY   NULL     3        42257           42257

[monitor]         2022-07-10 18:11:20: Received message from(GRP1_MPP_EP01)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        41955           41955

[monitor]         2022-07-10 18:11:20: Received message from(GRP2_MPP_EP02)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP2_MPP_EP02    OPEN        PRIMARY   VALID    3        42257           42258

[monitor]         2022-07-10 18:11:20: Received message from(GRP1_MPP_EP11)
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:11:19  OPEN           OK        GRP1_MPP_EP11    OPEN        STANDBY   VALID    3        41954           41954

客户端配置

dmsvc.conf

測試驗證

(a)測試建表和插入數據是否會根據分佈列進行自動分發。
(b)測試故障一個節點後，集羣和持續提供服務。

dmdba@lxm2:~$ rlwrap disql SYSDBA/SYSDBA@localhost:5237#"{mpp_type=global}"

Server[localhost:5237]:mode is primary, state is open
login used time : 2.571(ms)
disql V8
SQL> select * from V$INSTANCE;

LINEID     NAME          INSTANCE_NAME INSTANCE_NUMBER HOST_NAME SVR_VERSION                DB_VERSION          START_TIME          STATUS$ MODE$   OGUID       DSC_SEQNO   DSC_ROLE
---------- ------------- ------------- --------------- --------- -------------------------- ------------------- ------------------- ------- ------- ----------- ----------- --------
           BUILD_VERSION                      BUILD_TIME          
           ---------------------------------- --------------------
1          GRP1_MPP_EP01 GRP1_MPP_EP01 1               lxm2      DM Database Server x64 V8  DB Version: 0x7000c 2022-07-10 16:20:56 OPEN    PRIMARY 45330       0           NULL
           1-2-114-22.05.25-161267-10045-ENT  May 25 2022 11:22:12

2          GRP2_MPP_EP02 GRP2_MPP_EP02 2               lxm       DM Database Server x64 V8  DB Version: 0x7000c 2022-07-10 16:40:32 OPEN    PRIMARY 45331       0           NULL
           1-2-114-22.05.25-161267-10045-ENT  May 25 2022 11:22:12


used time: 10.279(ms). Execute id is 954698.
SQL> CREATE TABLE T_RANGE(C1 INT,C2 CHAR(10)) DISTRIBUTED BY RANGE(C1)(VALUES EQU OR LESS THAN (100) ON GRP1_MPP_EP01, VALUES LESS THAN(MAXVALUE) ON GRP2_MPP_EP02);
executed successfully
used time: 31.373(ms). Execute id is 601.
SQL> insert into T_RANGE values(1,'Chris'),(51,'Lily'),(101,'Peter'),(120,'Tim'),(200,'Summy');
affect rows 5

used time: 4.900(ms). Execute id is 954711.
SQL> insert into T_RANGE values(1,'Chris'),(51,'Lily'),(181,'Petr'),(320,'Ti'),(900,'Sumy');
affect rows 5

used time: 3.262(ms). Execute id is 954713.
SQL> COMMIT;
executed successfully
used time: 6.231(ms). Execute id is 954714.
SQL> SP_GET_EP_COUNT('SYSDBA','T_RANGE'); 

LINEID     SEQNO       N_ROWS              
---------- ----------- --------------------
1          0           4
2          1           6

used time: 33.026(ms). Execute id is 954715.
SQL>

測試kill掉EP01實例，監視器監控到後，守護進程會把實例自動拉起來。

dmdba@lxm2:~$ ps -ef|grep dms
dmdba    21310 21229  0 16:20 pts/0    00:00:08 dmserver /dm8/data/EP01/dm.ini mount
dmdba    21676 21404  0 17:34 pts/1    00:00:05 dmserver /dm8/data/EP22/dm.ini mount
dmdba    21933 21784  0 18:59 pts/2    00:00:00 grep dms
dmdba@lxm2:~$ kill -9 21310
dmdba@lxm2:~/dmdbms/bin$ ps -ef|grep dms
dmdba    21676 21404  0 17:34 pts/1    00:00:05 dmserver /dm8/data/EP22/dm.ini mount
dmdba    21935     1  1 18:59 ?        00:00:00 /home/dmdba/dmdbms/bin/dmserver /dm8/data/EP01/dm.ini mount
dmdba    22036 21229  0 19:00 pts/0    00:00:00 grep dms

[monitor]         2022-07-10 18:59:37: Instance GRP1_MPP_EP01[PRIMARY, OPEN, ISTAT_SAME:TRUE] error
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:35  STARTUP        ERROR     GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        42983           42984

[monitor]         2022-07-10 18:59:37: Dmwatcher process GRP1_MPP_EP01 status switching [OPEN-->STARTUP]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:35  STARTUP        ERROR     GRP1_MPP_EP01    OPEN        PRIMARY   VALID    3        42983           42984


--monitor
[monitor]         2022-07-10 18:59:37: Check primary instance error in group(GRP1), start to auto takeover

[monitor]         2022-07-10 18:59:37: Notify group(GRP1)'s active dmwatcher to set MID
[monitor]         2022-07-10 18:59:37: Notify group(GRP1)'s active dmwatcher to set MID success
[monitor]         2022-07-10 18:59:37: Notify group(GRP2)'s active dmwatcher to set MID
[monitor]         2022-07-10 18:59:37: Notify group(GRP2)'s active dmwatcher to set MID success
[monitor]         2022-07-10 18:59:37: Start to takeover use instance GRP1_MPP_EP11
[monitor]         2022-07-10 18:59:37: Notify dmwatcher(GRP1_MPP_EP11) switch to TAKEOVER status
[monitor]         2022-07-10 18:59:38: Dmwatcher process GRP1_MPP_EP11 status switching [OPEN-->TAKEOVER]
[monitor]         2022-07-10 18:59:38: Switch dmwatcher GRP1_MPP_EP11 to TAKEOVER status success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 7)
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 execute sql SP_SET_GLOBAL_DW_STATUS(0, 7) success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql SP_APPLY_KEEP_PKG()
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 execute sql SP_APPLY_KEEP_PKG() success
[monitor]         2022-07-10 18:59:38: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE MOUNT
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE MOUNT success
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE PRIMARY
[monitor]         2022-07-10 18:59:39: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE PRIMARY success
[monitor]         2022-07-10 18:59:39: Notify instance GRP1_MPP_EP11 to change all arch status to be invalid
[monitor]         2022-07-10 18:59:39: Succeed to change all instances arch status to be invalid
[monitor]         2022-07-10 18:59:39: Build new mppctl file success
[monitor]         2022-07-10 18:59:39: Notify to update instance GRP1_MPP_EP11[PRIMARY, MOUNT, ISTAT_SAME:TRUE] mppctl file
[monitor]         2022-07-10 18:59:39: Notify instance GRP1_MPP_EP11[PRIMARY, MOUNT, ISTAT_SAME:TRUE] update mppctl file success
[monitor]         2022-07-10 18:59:39: Notify to update instance GRP2_MPP_EP02[PRIMARY, OPEN, ISTAT_SAME:TRUE] mppctl file
[monitor]         2022-07-10 18:59:39: Notify dmwatcher(GRP2_MPP_EP02) switch to MPPCTL UPDATE status
[monitor]         2022-07-10 18:59:40: Dmwatcher process GRP2_MPP_EP02 status switching [OPEN-->MPPCTL UPDATE]
[monitor]         2022-07-10 18:59:40: Switch dmwatcher GRP2_MPP_EP02 to MPPCTL UPDATE status success
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 start to execute sql SP_SET_GLOBAL_DW_STATUS(0, 12)
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 execute sql SP_SET_GLOBAL_DW_STATUS(0, 12) success
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 start to execute sql SP_SET_GLOBAL_DW_STATUS(12, 0)
[monitor]         2022-07-10 18:59:40: Instance GRP2_MPP_EP02 execute sql SP_SET_GLOBAL_DW_STATUS(12, 0) success
[monitor]         2022-07-10 18:59:40: Notify dmwatcher(GRP2_MPP_EP02) switch to OPEN status
[monitor]         2022-07-10 18:59:41: Dmwatcher process GRP2_MPP_EP02 status switching [MPPCTL UPDATE-->OPEN]
[monitor]         2022-07-10 18:59:42: Switch dmwatcher GRP2_MPP_EP02 to OPEN status success
[monitor]         2022-07-10 18:59:42: Notify instance GRP2_MPP_EP02[PRIMARY, OPEN, ISTAT_SAME:TRUE] update mppctl file success
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 start to execute sql ALTER DATABASE OPEN FORCE
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 execute sql ALTER DATABASE OPEN FORCE success
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 start to execute sql SP_SET_GLOBAL_DW_STATUS(7, 0)
[monitor]         2022-07-10 18:59:42: Instance GRP1_MPP_EP11 execute sql SP_SET_GLOBAL_DW_STATUS(7, 0) success
[monitor]         2022-07-10 18:59:42: Notify dmwatcher(GRP1_MPP_EP11) switch to OPEN status
[monitor]         2022-07-10 18:59:42: Dmwatcher process GRP1_MPP_EP11 status switching [TAKEOVER-->OPEN]
[monitor]         2022-07-10 18:59:43: Switch dmwatcher GRP1_MPP_EP11 to OPEN status success
[monitor]         2022-07-10 18:59:43: Notify group(GRP1)'s dmwatcher to do clear
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP1_MPP_EP01 success
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP1_MPP_EP11 success
[monitor]         2022-07-10 18:59:43: Notify group(GRP2)'s dmwatcher to do clear
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP2_MPP_EP22 success
[monitor]         2022-07-10 18:59:43: Clean request of dmwatcher processer GRP2_MPP_EP02 success
[monitor]         2022-07-10 18:59:43: Success to takeover use instance GRP1_MPP_EP11

[monitor]         2022-07-10 18:59:43: Group(GRP1) use instance GRP1_MPP_EP11 auto takeover success

[monitor]         2022-07-10 18:59:50: Instance GRP1_MPP_EP01[PRIMARY, AFTER REDO, ISTAT_SAME:TRUE] recover to OK
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  STARTUP        OK        GRP1_MPP_EP01    AFTER REDO  PRIMARY   VALID    3        42983           42983

[monitor]         2022-07-10 18:59:50: Dmwatcher process GRP1_MPP_EP01 status switching [STARTUP-->OPEN]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  OPEN           OK        GRP1_MPP_EP01    OPEN        STANDBY   INVALID  3        42983           42983

[monitor]         2022-07-10 18:59:50: Dmwatcher process GRP1_MPP_EP11 status switching [OPEN-->RECOVERY]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:48  RECOVERY       OK        GRP1_MPP_EP11    OPEN        PRIMARY   VALID    4        45431           45432

[monitor]         2022-07-10 18:59:51: Dmwatcher process GRP1_MPP_EP11 status switching [RECOVERY-->OPEN]
                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN
                  2022-07-10 18:59:50  OPEN           OK        GRP1_MPP_EP11    OPEN        PRIMARY   VALID    4        45432           45432

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

博客 / 詳情

pacemarker 主備備是否可以單獨配置優先級切換

部署環境

部署規劃

主庫

備庫

守護進程規劃

數據準備

初始化實例

主庫

備庫

註冊服務

主庫

備庫

啓動主庫

配置歸檔

備份還原

聯機備份EP01並脱機還原到EP11

聯機備份EP02並脱機還原到EP22

配置文件

配置主庫 GRP1_MPP_EP01

dm.ini

dmmal.ini

dmarch.ini

dmmpp.ctl

啓動主庫

設置OGUID

修改數據庫模式

配置主庫GRP2_MPP_EP02

dm.ini

dmmal.ini

dmarch.ini

dmmpp.ctl

啓動主庫

設置OGUID

修改數據庫模式

配置備庫GRP1_MPP_EP11

dm.ini

dmmal.ini

dmarch.ini

dmmpp.ctl

啓動備庫

設置OGUID

修改數據庫模式

配置備庫GRP2_MPP_EP22

dm.ini

dmmal.ini

dmarch.ini

dmmpp.ctl

啓動備庫

設置OGUID

修改數據庫模式

配置dmwatcher

配置dmmonitor

啓動watcher

啓動monitor

客户端配置

測試驗證

發佈 評論

發佈評論