DM8動態增加讀寫分離叢集節點
動態增加讀寫分離叢集節點
配置讀寫分離叢集
下列機器事先都安裝了DM，安裝路徑為’/dm8’，執行程式儲存在’/dm8/bin’目錄中，資料存放路徑為’/dm8/data’。
各主備庫的例項名建議採用“組名_守護環境_序號”的方式命名，方便按組區分不同例項，注意總長度不能超過16。本示例中組名為“GRP1”，配置為讀寫分離叢集，主庫命名為“GRP1_JY_01”，備庫分別命名為“GRP1_JY_02”和“GRP1_JY_03”。
機器名 IP地址 初始狀態 作業系統 dm8rw1 10.138.130.213(對外) 主庫 jy1 redhat 7.8 10.138.130.213(mal對內) dm8rw2 10.138.130.214(對外) 10.138.130.214(mal對內) 備庫 jy2 redhat 7.8 dm8rw3 10.138.130.215(對外) 10.138.130.215(mal對內) 備庫 jy3 redhat 7.8 dm8rw4 10.138.130.216 確認監視器 redhat 7.8 例項名 port_num mal_inst_dw_port mal_host mal_port mal_dw_port jy1 5236 5237 10.138.130.213 5238 5239 jy2 5236 5237 10.138.130.214 5238 5239 jy3 5236 5237 10.138.130.215 5238 5239
安裝DM資料庫軟體
資料準備
在主庫機器上初始化資料庫到目錄/dm8/data:
[dmdba@dm8rw1 dm8]$ dminit path=/dm8/data db_name=jy instance_name=jy1 port_num=5236 page_size=8 charset=0 initdb V8 db version: 0x7000c file dm.key not found, use default license! License will expire on 2022-10-21 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL log file path: /dm8/data/jy/jy01.log log file path: /dm8/data/jy/jy02.log write to dir [/dm8/data/jy]. create dm database success. 2022-01-18 21:10:20
建立DmAPService用於dmrman來執行備份
主庫
[root@dm8rw1 bin]# /dm8/script/root/dm_service_installer.sh -t dmap Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service. Finished to create the service (DmAPService) [root@dm8rw1 bin]# service DmAPService start Redirecting to /bin/systemctl start DmAPService.service
備庫jy2
[root@dm8rw2 bin]# /dm8/script/root/dm_service_installer.sh -t dmap Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service. Finished to create the service (DmAPService) [root@dm8rw2 bin]# service DmAPService start Redirecting to /bin/systemctl start DmAPService.service
備庫jy3
[root@dm8rw3 bin]# /dm8/script/root/dm_service_installer.sh -t dmap Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service. Finished to create the service (DmAPService) [root@dm8rw3 bin]# service DmAPService start Redirecting to /bin/systemctl start DmAPService.service
註冊服務用於啟動資料庫
[root@dm8rw1 bin]# /dm8/script/root/dm_service_installer.sh -dm_ini /dm8/data/jy/dm.ini -p jy1 -t dmserver Created symlink from /etc/systemd/system/multi-user.target.wants/DmServicejy1.service to /usr/lib/systemd/system/DmServicejy1.service. Finished to create the service (DmServicejy1)
正常啟動資料庫並正常關閉
[root@dm8rw1 bin]# service DmServicejy1 start Redirecting to /bin/systemctl start DmServicejy1.service [dmdba@dm8rw1 jy]$ disql SySDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is open login used time : 4.305(ms) disql V8 SQL> select instance_name from v$instance; LINEID INSTANCE_NAME ---------- ------------- 1 JY1 used time: 5.512(ms). Execute id is 53700. [root@dm8rw1 bin]# service DmServicejy1 stop Redirecting to /bin/systemctl stop DmServicejy1.service
備份主庫
1. 正常關閉資料庫
[root@dm8rw1 bin]# service DmServicejy1 stop Redirecting to /bin/systemctl stop DmServicejy1.service
2.使用dmrman來進行冷備份
[dmdba@dm8rw1 jy]$ dmrman CTLSTMT="backup database '/dm8/data/jy/dm.ini' full backupset '/dm8/jy1_full_bak_1'" dmrman V8 backup database '/dm8/data/jy/dm.ini' full backupset '/dm8/jy1_full_bak_1' 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[26788], file_lsn[26788] Processing backupset /dm8/jy1_full_bak_1 [Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00] backup successfully! time used: 00:00:01.468
將主庫的備份複製備庫：
[dmdba@dm8rw1 dm8]$ scp -r jy1_full_bak_1 10.138.130.214:/dm8 dmdba@10.138.130.214's password: jy1_full_bak_1.bak 100% 6334KB 24.8MB/s 00:00 jy1_full_bak_1.meta 100% 77KB 4.2MB/s 00:00 [dmdba@dm8rw1 dm8]$ scp -r jy1_full_bak_1 10.138.130.215:/dm8 dmdba@10.138.130.215's password: jy1_full_bak_1.bak 100% 6334KB 10.9MB/s 00:00 jy1_full_bak_1.meta 100% 77KB 1.2MB/s 00:00 [dmdba@dm8rw1 dm8]$
建立備庫備庫並恢復
備庫jy2:
[dmdba@dm8rw2 dm8]$ dminit path=/dm8/data db_name=jy instance_name=jy2 port_num=5236 page_size=8 charset=0 initdb V8 db version: 0x7000c file dm.key not found, use default license! License will expire on 2022-10-21 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL log file path: /dm8/data/jy/jy01.log log file path: /dm8/data/jy/jy02.log write to dir [/dm8/data/jy]. create dm database success. 2022-01-18 21:46:05
備庫jy3:
[dmdba@dm8rw3 dm8]$ dminit path=/dm8/data db_name=jy instance_name=jy3 port_num=5236 page_size=8 charset=0 initdb V8 db version: 0x7000c file dm.key not found, use default license! License will expire on 2022-10-21 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL log file path: /dm8/data/jy/jy01.log log file path: /dm8/data/jy/jy02.log write to dir [/dm8/data/jy]. create dm database success. 2022-01-18 21:46:37
恢復備庫
備庫jy2:
[dmdba@dm8rw2 ~]$ dmrman CTLSTMT="restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_1'" dmrman V8 restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_1' 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:05][Remaining:00:00:00] restore successfully. time used: 00:00:05.615 [dmdba@dm8rw2 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_1'" dmrman V8 RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_1' 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[26788], file_lsn[26788] no log generates while the backupset [/dm8/jy1_full_bak_1] created recover successfully! time used: 345.359(ms) [dmdba@dm8rw2 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' UPDATE DB_MAGIC" dmrman V8 RECOVER DATABASE '/dm8/data/jy/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[26788], file_lsn[26788] recover successfully! time used: 00:00:01.066
備庫jy3:
[dmdba@dm8rw3 ~]$ dmrman CTLSTMT="restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_1'" dmrman V8 restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_1' 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:06][Remaining:00:00:00] restore successfully. time used: 00:00:06.606 [dmdba@dm8rw3 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_1'" dmrman V8 RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_1' 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[26788], file_lsn[26788] no log generates while the backupset [/dm8/jy1_full_bak_1] created recover successfully! time used: 323.405(ms) [dmdba@dm8rw3 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' UPDATE DB_MAGIC" dmrman V8 RECOVER DATABASE '/dm8/data/jy/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[26788], file_lsn[26788] recover successfully! time used: 00:00:01.069
配置操作
一.主庫
配置dm.ini檔案，配置以下引數
INSTANCE_NAME = jy1 PORT_NUM = 5236 #資料庫例項監聽埠 DW_INACTIVE_INTERVAL = 60 #接收守護程式訊息超時時間 ALTER_MODE_STATUS = 0 #不允許手工方式修改例項模式/狀態 ENABLE_OFFLINE_TS = 2 #不允許備庫OFFLINE表空間 MAL_INI = 1 #開啟MAL系統 ARCH_INI = 1 #開啟歸檔配置 RLOG_SEND_APPLY_MON = 64 #統計最近64次的日誌傳送資訊
配置dmmal.ini檔案
配置MAL系統，各主備庫的dmmal.ini配置必須完全一致，MAL_HOST使用內部網路IP，MAL_PORT與dm.ini中PORT_NUM使用不同的埠值，MAL_DW_PORT是各例項對應的守護程式之間，以及守護程式和監視器之間的通訊埠，配置如下：
MAL_CHECK_INTERVAL = 5 #MAL鏈路檢測時間間隔 MAL_CONN_FAIL_INTERVAL = 5 #判定MAL鏈路斷開的時間 [MAL_INST1] MAL_INST_NAME = jy1 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.213 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.213 #例項的對外服務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 = jy2 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.214 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.214 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [MAL_INST3] MAL_INST_NAME = jy3 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.215 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.215 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [dmdba@dm8rw1 jy]$ vi dmmal.ini MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = jy1 MAL_HOST = 10.138.130.213 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.213 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST2] MAL_INST_NAME = jy2 MAL_HOST = 10.138.130.214 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.214 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST3] MAL_INST_NAME = jy3 MAL_HOST = 10.138.130.215 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.215 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237
配置dmarch.ini
修改dmarch.ini，配置本地歸檔和即時歸檔。
除了本地歸檔外，其他歸檔配置項中的ARCH_DEST表示例項是Primary模式時，需要同步歸檔資料的目標例項名。當前例項jy1是主庫，需要向即時備庫jy2/jy3同步資料，因此即時歸檔的ARCH_DEST分別配置為jy2和jy3。
[ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY #即時歸檔型別 ARCH_DEST = jy2 #即時歸檔目標例項名 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY #即時歸檔型別 ARCH_DEST = jy3 #即時歸檔目標例項名 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL #本地歸檔型別 ARCH_DEST = /dm8/arch #本地歸檔檔案存放路徑 ARCH_FILE_SIZE = 128 #單位Mb，本地單個歸檔檔案最大值 ARCH_SPACE_LIMIT = 0 #單位Mb，0表示無限制，範圍1024~4294967294M [dmdba@dm8rw1 jy]$ vi dmarch.ini [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY ARCH_DEST = jy2 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY ARCH_DEST = jy3 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0
配置dmwatcher.ini
修改dmwatcher.ini配置守護程式，配置為全域性守護型別，使用自動切換模式。
[GRP1] DW_TYPE = GLOBAL #全域性守護型別 DW_MODE = AUTO #自動切換模式 生產建議設定成手動 DW_ERROR_TIME = 10 #遠端守護程式故障認定時間 INST_RECOVER_TIME = 60 #主庫守護程式啟動恢復的間隔時間 INST_ERROR_TIME = 10 #本地例項故障認定時間 INST_OGUID = 222222 #守護系統唯一OGUID值 INST_INI = /dm8/data/jy/dm.ini #dm.ini配置檔案路徑 INST_AUTO_RESTART = 1 #開啟例項的自動啟動功能(建議設定成0) INST_STARTUP_CMD = /dm8/bin/dmserver #命令列方式啟動 RLOG_SEND_THRESHOLD = 0 #指定主庫傳送日誌到備庫的時間閥值，預設關閉 RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閥值，預設關閉 [dmdba@dm8rw1 jy]$ vi dmwatcher.ini [GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 222222 INST_INI = /dm8/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm8/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0
配置備庫jy2
配置dm.ini檔案，配置以下引數
INSTANCE_NAME = jy2 PORT_NUM = 5236 #資料庫例項監聽埠 DW_INACTIVE_INTERVAL = 60 #接收守護程式訊息超時時間 ALTER_MODE_STATUS = 0 #不允許手工方式修改例項模式/狀態 ENABLE_OFFLINE_TS = 2 #不允許備庫OFFLINE表空間 MAL_INI = 1 #開啟MAL系統 ARCH_INI = 1 #開啟歸檔配置 RLOG_SEND_APPLY_MON = 64 #統計最近64次的日誌傳送資訊
配置dmmal.ini檔案
配置MAL系統，各主備庫的dmmal.ini配置必須完全一致，MAL_HOST使用內部網路IP，MAL_PORT與dm.ini中PORT_NUM使用不同的埠值，MAL_DW_PORT是各例項對應的守護程式之間，以及守護程式和監視器之間的通訊埠，配置如下：
MAL_CHECK_INTERVAL = 5 #MAL鏈路檢測時間間隔 MAL_CONN_FAIL_INTERVAL = 5 #判定MAL鏈路斷開的時間 [MAL_INST1] MAL_INST_NAME = jy1 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.213 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.213 #例項的對外服務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 = jy2 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.214 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.214 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [MAL_INST3] MAL_INST_NAME = jy3 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.215 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.215 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [dmdba@dm8rw2 jy]$ vi dmmal.ini MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = jy1 MAL_HOST = 10.138.130.213 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.213 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST2] MAL_INST_NAME = jy2 MAL_HOST = 10.138.130.214 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.214 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST3] MAL_INST_NAME = jy3 MAL_HOST = 10.138.130.215 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.215 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237
配置dmarch.ini
修改dmarch.ini，配置本地歸檔和即時歸檔。
除了本地歸檔外，其他歸檔配置項中的ARCH_DEST表示例項是Primary模式時，需要同步歸檔資料的目標例項名
當前例項jy2是備庫，守護系統配置完成後，可能在各種故障處理中，jy2切換為新的主庫，正常情況下，jy1會切換為新的備庫，需要向jy1和jy3同步資料，因此即時歸檔的ARCH_DEST分別配置為jy1和jy3。
[dmdba@dm8rw2 jy]$ vi dmarch.ini [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY ARCH_DEST = jy1 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY ARCH_DEST = jy3 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0
配置dmwatcher.ini
修改dmwatcher.ini配置守護程式，配置為全域性守護型別，使用自動切換模式。
[GRP1] DW_TYPE = GLOBAL #全域性守護型別 DW_MODE = AUTO #自動切換模式 生產建議設定成手動 DW_ERROR_TIME = 10 #遠端守護程式故障認定時間 INST_RECOVER_TIME = 60 #主庫守護程式啟動恢復的間隔時間 INST_ERROR_TIME = 10 #本地例項故障認定時間 INST_OGUID = 222222 #守護系統唯一OGUID值 INST_INI = /dm8/data/jy/dm.ini #dm.ini配置檔案路徑 INST_AUTO_RESTART = 1 #開啟例項的自動啟動功能(建議設定成0) INST_STARTUP_CMD = /dm8/bin/dmserver #命令列方式啟動 RLOG_SEND_THRESHOLD = 0 #指定主庫傳送日誌到備庫的時間閥值，預設關閉 RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閥值，預設關閉 [dmdba@dm8rw2 jy]$ vi dmwatcher.ini [GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 222222 INST_INI = /dm8/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm8/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0
配置備庫jy3
配置dm.ini檔案，配置以下引數
INSTANCE_NAME = jy3 PORT_NUM = 5236 #資料庫例項監聽埠 DW_INACTIVE_INTERVAL = 60 #接收守護程式訊息超時時間 ALTER_MODE_STATUS = 0 #不允許手工方式修改例項模式/狀態 ENABLE_OFFLINE_TS = 2 #不允許備庫OFFLINE表空間 MAL_INI = 1 #開啟MAL系統 ARCH_INI = 1 #開啟歸檔配置 RLOG_SEND_APPLY_MON = 64 #統計最近64次的日誌傳送資訊
配置dmmal.ini檔案
配置MAL系統，各主備庫的dmmal.ini配置必須完全一致，MAL_HOST使用內部網路IP，MAL_PORT與dm.ini中PORT_NUM使用不同的埠值，MAL_DW_PORT是各例項對應的守護程式之間，以及守護程式和監視器之間的通訊埠，配置如下：
MAL_CHECK_INTERVAL = 5 #MAL鏈路檢測時間間隔 MAL_CONN_FAIL_INTERVAL = 5 #判定MAL鏈路斷開的時間 [MAL_INST1] MAL_INST_NAME = jy1 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.213 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.213 #例項的對外服務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 = jy2 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.214 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.214 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [MAL_INST3] MAL_INST_NAME = jy3 #例項名，和dm.ini中的INSTANCE_NAME一致 MAL_HOST = 10.138.130.215 #MAL系統監聽TCP連線的IP地址 MAL_PORT = 5238 #MAL系統監聽TCP連線的埠 MAL_INST_HOST = 10.138.130.215 #例項的對外服務IP地址 MAL_INST_PORT = 5236 #例項的對外服務埠，和dm.ini中的PORT_NUM一致 MAL_DW_PORT = 5239 #例項對應的守護程式監聽TCP連線的埠 MAL_INST_DW_PORT = 5237 #例項監聽守護程式TCP連線的埠 [dmdba@dm8rw3 jy]$ vi dmmal.ini MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = jy1 MAL_HOST = 10.138.130.213 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.213 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST2] MAL_INST_NAME = jy2 MAL_HOST = 10.138.130.214 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.214 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST3] MAL_INST_NAME = jy3 MAL_HOST = 10.138.130.215 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.215 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237
配置dmarch.ini
修改dmarch.ini，配置本地歸檔和即時歸檔。
除了本地歸檔外，其他歸檔配置項中的ARCH_DEST表示例項是Primary模式時，需要同步歸檔資料的目標例項名
當前例項jy3是備庫，守護系統配置完成後，可能在各種故障處理中，jy3切換為新的主庫，正常情況下，jy1會切換為新的備庫，需要向jy1和jy2同步資料，因此即時歸檔的ARCH_DEST分別配置為jy1和jy2。
[dmdba@dm215 jy]$ vi dmarch.ini [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY ARCH_DEST = jy1 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY ARCH_DEST = jy2 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0
配置dmwatcher.ini
修改dmwatcher.ini配置守護程式，配置為全域性守護型別，使用自動切換模式。
[GRP1] DW_TYPE = GLOBAL #全域性守護型別 DW_MODE = AUTO #自動切換模式 生產建議設定成手動 DW_ERROR_TIME = 10 #遠端守護程式故障認定時間 INST_RECOVER_TIME = 60 #主庫守護程式啟動恢復的間隔時間 INST_ERROR_TIME = 10 #本地例項故障認定時間 INST_OGUID = 222222 #守護系統唯一OGUID值 INST_INI = /dm8/data/jy/dm.ini #dm.ini配置檔案路徑 INST_AUTO_RESTART = 1 #開啟例項的自動啟動功能(建議設定成0) INST_STARTUP_CMD = /dm8/bin/dmserver #命令列方式啟動 RLOG_SEND_THRESHOLD = 0 #指定主庫傳送日誌到備庫的時間閥值，預設關閉 RLOG_APPLY_THRESHOLD = 0 #指定備庫重演日誌的時間閥值，預設關閉 [dmdba@dm8rw3 jy]$ vi dmwatcher.ini [GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 222222 INST_INI = /dm8/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm8/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0
二:
主庫啟動到mount狀態設定oguid
[dmdba@dm8rw1 ~]$ dmserver /dm8/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop DM Database Server x64 V8 1-2-84-21.10.21-149328-10032-ENT 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 2022-10-21 file lsn: 25300 ndct db load 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. [dmdba@dm8rw1 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 3.177(ms) disql V8 SQL> sp_set_oguid(222222); DMSQL executed successfully used time: 66.753(ms). Execute id is 0. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 26788 26788 used time: 3.112(ms). Execute id is 1. SQL> select permanent_magic; LINEID PERMANENT_MAGIC ---------- --------------- 1 518889968 used time: 1.941(ms). Execute id is 2.
備庫jy2啟動到mount狀態設定oguid
[dmdba@dm8rw2 ~]$ dmserver /dm8/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop DM Database Server x64 V8 1-2-84-21.10.21-149328-10032-ENT 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 2022-10-21 file lsn: 26788 ndct db load 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. [dmdba@dm8rw2 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 5.278(ms) disql V8 SQL> sp_set_oguid(222222); DMSQL executed successfully used time: 114.383(ms). Execute id is 0. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 26788 26788 used time: 19.022(ms). Execute id is 1. SQL> select permanent_magic; LINEID PERMANENT_MAGIC ---------- --------------- 1 518889968 used time: 1.356(ms). Execute id is 2.
備庫jy3啟動到mount狀態設定oguid
[dmdba@dm8rw3 ~]$ dmserver /dm8/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop DM Database Server x64 V8 1-2-84-21.10.21-149328-10032-ENT 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 2022-10-21 file lsn: 26788 ndct db load 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. [dmdba@dm8rw3 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 5.145(ms) disql V8 SQL> sp_set_oguid(222222); DMSQL executed successfully used time: 110.938(ms). Execute id is 0. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 26788 26788 used time: 14.513(ms). Execute id is 1. SQL> select permanent_magic; LINEID PERMANENT_MAGIC ---------- --------------- 1 518889968 used time: 1.549(ms). Execute id is 2.
備庫jy2註冊服務用於啟動資料庫
[root@dm8rw2 ~]# /dm8/script/root/dm_service_installer.sh -dm_ini /dm8/data/jy/dm.ini -p jy2 -t dmserver
Created symlink from /etc/systemd/system/multi-user.target.wants/DmServicejy2.service to /usr/lib/systemd/system/DmServicejy2.service.
Finished to create the service (DmServicejy2)
備庫jy3註冊服務用於啟動資料庫
[root@dm8rw3 ~]# /dm8/script/root/dm_service_installer.sh -dm_ini /dm8/data/jy/dm.ini -p jy3 -t dmserver Created symlink from /etc/systemd/system/multi-user.target.wants/DmServicejy3.service to /usr/lib/systemd/system/DmServicejy3.service. Finished to create the service (DmServicejy3)
三:
主庫以primary開啟
[dmdba@dm8rw1 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 2.961(ms) disql V8 SQL> alter database primary; executed successfully used time: 58.207(ms). Execute id is 0.
備庫jy2以standby 開啟
[dmdba@dm8rw2 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 2.221(ms) disql V8 SQL> alter database standby; executed successfully used time: 145.716(ms). Execute id is 0.
備庫jy3以standby 開啟
[dmdba@dm8rw3 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is normal, state is mount login used time : 2.978(ms) disql V8 SQL> alter database standby; executed successfully used time: 72.982(ms). Execute id is 0.
啟動命令列工具DIsql，登入備庫修改資料庫為Standby模式如果當前資料庫不是normal模式，需要先修改dm.ini中ALTER_MODE_STATUS值為1，允許修改資料庫模式，修改Standby模式成功後再改回為0。如果是normal模式，請忽略下面的第1步和第3步。
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); ----第1步 SQL>alter database standby; ----第2步 SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); ----第3步
四:
啟動守護程式
啟動各個主備庫上的守護程式：
主庫
[dmdba@dm8rw1 ~]$ dmwatcher /dm8/data/jy/dmwatcher.ini DMWATCHER[4.0] V8 DMWATCHER[4.0] IS READY show 2022-01-18 22:23:19 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART DW_STATUS DW_SUB_STATUS DW_CTL_STATUS GRP1 GLOBAL AUTO 222222 FALSE TRUE RECOVERY WAIT_SEND_ARCH VALID INST_OK NAME SVR_MODE SYS_STATUS RTYPE FSEQ FLSN CSEQ CLSN DW_STAT_FLAG OK JY1 PRIMARY OPEN TIMELY 4205 28044 4205 28044 4 ---------------------------------------------------------------------------
備庫jy2
[dmdba@dm8rw2 ~]$ dmwatcher /dm8/data/jy/dmwatcher.ini DMWATCHER[4.0] V8 DMWATCHER[4.0] IS READY show 2022-01-18 22:23:28 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART DW_STATUS DW_SUB_STATUS DW_CTL_STATUS GRP1 GLOBAL AUTO 222222 FALSE TRUE OPEN SUB_STATE_START VALID INST_OK NAME SVR_MODE SYS_STATUS RTYPE FSEQ FLSN CSEQ CLSN DW_STAT_FLAG OK JY2 STANDBY OPEN TIMELY 4204 28044 4204 28044 0 DATABASE(JY2) APPLY INFO: REDOS_PARALLEL_NUM (1) DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4205, 4205, 4205], (RLSN, SLSN, KLSN)[28044, 28044, 28044], N_TSK[0], TSK_MEM_USE[0] REDO_LSN_ARR: (28044) ---------------------------------------------------------------------------
備庫jy3
[dmdba@dm8rw3 ~]$ dmwatcher /dm8/data/jy/dmwatcher.ini DMWATCHER[4.0] V8 DMWATCHER[4.0] IS READY show 2022-01-18 22:23:35 --------------------------------------------------------------------------- GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART DW_STATUS DW_SUB_STATUS DW_CTL_STATUS GRP1 GLOBAL AUTO 222222 FALSE TRUE OPEN SUB_STATE_START VALID INST_OK NAME SVR_MODE SYS_STATUS RTYPE FSEQ FLSN CSEQ CLSN DW_STAT_FLAG OK JY3 STANDBY OPEN TIMELY 4204 28044 4204 28044 0 DATABASE(JY3) APPLY INFO: REDOS_PARALLEL_NUM (1) DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4205, 4205, 4205], (RLSN, SLSN, KLSN)[28044, 28044, 28044], N_TSK[0], TSK_MEM_USE[0] REDO_LSN_ARR: (28044) ---------------------------------------------------------------------------
也可以註冊守護程式服務來進行啟動
主庫
[root@dm8rw1 ~]# /dm8/script/root/dm_service_installer.sh -watcher_ini /dm8/data/jy/dmwatcher.ini -p jy1 -t dmwatcher Created symlink from /etc/systemd/system/multi-user.target.wants/DmWatcherServicejy1.service to /usr/lib/systemd/system/DmWatcherServicejy1.service. Finished to create the service (DmWatcherServicejy1) [root@dm8rw1 ~]# service DmWatcherServicejy1 start Redirecting to /bin/systemctl start DmWatcherServicejy1.service
備庫1
[root@dm8rw2 ~]# /dm8/script/root/dm_service_installer.sh -watcher_ini /dm8/data/jy/dmwatcher.ini -p jy2 -t dmwatcher Created symlink from /etc/systemd/system/multi-user.target.wants/DmWatcherServicejy2.service to /usr/lib/systemd/system/DmWatcherServicejy2.service. Finished to create the service (DmWatcherServicejy2) [root@dm8rw2 ~]# service DmWatcherServicejy2 start Redirecting to /bin/systemctl start DmWatcherServicejy2.service
備庫2
[root@dm8rw3 ~]# /dm8/script/root/dm_service_installer.sh -watcher_ini /dm8/data/jy/dmwatcher.ini -p jy3 -t dmwatcher Created symlink from /etc/systemd/system/multi-user.target.wants/DmWatcherServicejy3.service to /usr/lib/systemd/system/DmWatcherServicejy3.service. Finished to create the service (DmWatcherServicejy3) [root@dm8rw3 ~]# service DmWatcherServicejy3 start Redirecting to /bin/systemctl start DmWatcherServicejy3.service
配置監視器
由於主庫和即時備庫的守護程式配置為自動切換模式，因此這裡選擇配置確認監視器。和普通監視器相比，確認監視器除了相同的命令支援外，在主庫發生故障時，能夠自動通知即時備庫接管為新的主庫，具有自動故障處理的功能。
修改dmmonitor.ini配置確認監視器，其中MON_DW_IP中的IP和PORT和dmmal.ini中的MAL_HOST和MAL_DW_PORT配置項保持一致。
MON_DW_CONFIRM = 1 #確認監視器模式 MON_LOG_PATH = /dm8/data/log #監視器日誌檔案存放路徑 MON_LOG_INTERVAL = 60 #每隔60s定時記錄系統資訊到日誌檔案 MON_LOG_FILE_SIZE = 32 #每個日誌檔案最大32M MON_LOG_SPACE_LIMIT = 0 #不限定日誌檔案總佔用空間 [GRP1] MON_INST_OGUID = 222222 #組GRP1的唯一OGUID值 #以下配置為監視器到組GRP1的守護程式的連線資訊，以“IP:PORT”的形式配置 #IP對應dmmal.ini中的MAL_HOST，PORT對應dmmal.ini中的MAL_DW_PORT MON_DW_IP = 10.138.130.213:5238 MON_DW_IP = 10.138.130.214:5238 MON_DW_IP = 10.138.130.215:5238 [dmdba@dm216 data]$ vi 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 = 222222 MON_DW_IP = 10.138.130.213:5238 MON_DW_IP = 10.138.130.214:5238 MON_DW_IP = 10.138.130.215:5238
啟動監視器：
[dmdba@dm8rw4 data]$ dmmonitor /dm8/data/dmmonitor.ini [monitor] 2022-01-18 22:28:01: DMMONITOR[4.0] V8 [monitor] 2022-01-18 22:28:02: DMMONITOR[4.0] IS READY. [monitor] 2022-01-18 22:28:02: Received message from(JY1) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-01-18 22:28:02 OPEN OK JY1 OPEN PRIMARY VALID 3 28044 28044 [monitor] 2022-01-18 22:28:02: Received message from(JY3) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-01-18 22:28:02 OPEN OK JY3 OPEN STANDBY VALID 3 28044 28044 [monitor] 2022-01-18 22:28:02: Received message from(JY2) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-01-18 22:28:02 OPEN OK JY2 OPEN STANDBY VALID 3 28044 28044 show 2022-01-18 22:28:11 #================================================================================# GROUP OGUID MON_CONFIRM MODE MPP_FLAG GRP1 222222 TRUE AUTO FALSE < > DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT 10.138.130.213 5239 2022-01-18 22:28:10 GLOBAL VALID OPEN JY1 OK 1 1 OPEN PRIMARY DSC_OPEN TIMELY 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 10.138.130.213 5236 OK JY1 OPEN PRIMARY 0 0 TIMELY VALID 4207 28044 4207 28044 NONE < > DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT 10.138.130.214 5239 2022-01-18 22:28:10 GLOBAL VALID OPEN JY2 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY 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 10.138.130.214 5236 OK JY2 OPEN STANDBY 0 0 TIMELY VALID 4204 28044 4204 28044 NONE DATABASE(JY2) APPLY INFO FROM (JY1), REDOS_PARALLEL_NUM (1): DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4207, 4207, 4207], (RLSN, SLSN, KLSN)[28044, 28044, 28044], N_TSK[0], TSK_MEM_USE[0] REDO_LSN_ARR: (28044) < > DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT 10.138.130.215 5239 2022-01-18 22:28:10 GLOBAL VALID OPEN JY3 OK 1 1 OPEN STANDBY DSC_OPEN TIMELY 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 10.138.130.215 5236 OK JY3 OPEN STANDBY 0 0 TIMELY VALID 4204 28044 4204 28044 NONE DATABASE(JY3) APPLY INFO FROM (JY1), REDOS_PARALLEL_NUM (1): DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4207, 4207, 4207], (RLSN, SLSN, KLSN)[28044, 28044, 28044], N_TSK[0], TSK_MEM_USE[0] REDO_LSN_ARR: (28044) #================================================================================#
測試資料同步
主庫:
SQL> create table test(id number(10)); executed successfully used time: 198.382(ms). Execute id is 103. SQL> insert into test values(1); affect rows 1 SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 29574 29574 used time: 0.350(ms). Execute id is 501. used time: 1.182(ms). Execute id is 104. SQL> commit; executed successfully used time: 12.799(ms). Execute id is 105.
備庫jy2:
SQL> select * from test; LINEID ID ---------- -- 1 1 used time: 2.615(ms). Execute id is 202. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 29574 29574 used time: 5.798(ms). Execute id is 0.
備庫jy3:
SQL> select * from test; LINEID ID ---------- -- 1 1 used time: 2.691(ms). Execute id is 202. SQL> select file_LSN, cur_LSN from v$rlog; LINEID FILE_LSN CUR_LSN ---------- -------------------- -------------------- 1 29574 29574 used time: 5.949(ms). Execute id is 0.
介面說明
DM多種客戶端介面都支援讀寫分離叢集連線設定，以下說明客戶端連線伺服器時如何設定讀寫分離屬性，詳細可參考《DM8程式設計師手冊》。4
JDBC介面
在JDBC連線串中增加了兩個連線屬性：
n rwSeparate 是否使用讀寫分離系統，預設0；取值（0不使用，1使用）。
n rwPercent 分發到主庫的事務佔主備庫總事務的百分比，有效值0~100，預設值25。
dm.jdbc.driver.DmDriver
jdbc:dm://192.168.0.206:5236?rwSeparate=1&rwPercent=10
當需要進行系統擴容，希望系統執行不中斷，或者影響執行的時間儘可能短，可透過動態增加叢集節點的方式進行。下面舉例對讀寫分離叢集進行
動態增加節點。
配置環境說明
機器名 IP地址 初始狀態 作業系統 dm8rw5 10.138.130.226 主庫 jy4 redhat 7.8
資料準備
新備庫建立DmAPService用於dmrman來執行還原恢復
[root@dm8rw5 /]# /dm8/script/root/dm_service_installer.sh -t dmap Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service. Finished to create the service (DmAPService) [root@dm8rw5 /]# service DmAPService start Redirecting to /bin/systemctl start DmAPService.service
1.對主庫進行聯機備份操作：
SQL> backup database backupset '/dm8/jy1_full_bak_2'; executed successfully used time: 00:00:02.278. Execute id is 500.
2.將備份集複製到新備庫:
[dmdba@dm8rw1 dm8]$ scp -r jy1_full_bak_2 10.138.130.226:/dm8 The authenticity of host '10.138.130.226 (10.138.130.226)' can't be established. ECDSA key fingerprint is SHA256:6O8c9WEeEYPbL4ncdRR1RsrjxxmfzPq9Tkq4/6uLSP4. ECDSA key fingerprint is MD5:e1:73:3e:8d:79:be:5c:82:0f:c7:58:79:45:ad:df:86. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.138.130.226' (ECDSA) to the list of known hosts. dmdba@10.138.130.226's password: jy1_full_bak_2.bak 100% 15MB 23.0MB/s 00:00 jy1_full_bak_2_1.bak 100% 156KB 4.7MB/s 00:00 jy1_full_bak_2.meta 100% 85KB 6.0MB/s 00:00 [dmdba@dm8rw1 dm8]$
3.初始化新備庫資料庫
[dmdba@dm8rw5 ~]$ dminit path=/dm8/data db_name=jy instance_name=jy4 port_num=5236 page_size=8 charset=0 initdb V8 db version: 0x7000c file dm.key not found, use default license! License will expire on 2022-10-21 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL log file path: /dm8/data/jy/jy01.log log file path: /dm8/data/jy/jy02.log write to dir [/dm8/data/jy]. create dm database success. 2022-02-16 16:50:23
恢復備庫
[dmdba@dm8rw5 ~]$ dmrman CTLSTMT="restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_2'" dmrman V8 restore database '/dm8/data/jy/dm.ini' from backupset '/dm8/jy1_full_bak_2' 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:08][Remaining:00:00:00] restore successfully. time used: 00:00:08.834 [dmdba@dm8rw5 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_2'" dmrman V8 RECOVER DATABASE '/dm8/data/jy/dm.ini' FROM BACKUPSET '/dm8/jy1_full_bak_2' file dm.key not found, use default license! Database mode = 1, oguid = 222222 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL EP[0]'s cur_lsn[29992], file_lsn[29992] [Percent:100.00%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00] recover successfully! time used: 00:00:02.730 [dmdba@dm8rw5 ~]$ dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/jy/dm.ini' UPDATE DB_MAGIC" dmrman V8 RECOVER DATABASE '/dm8/data/jy/dm.ini' UPDATE DB_MAGIC file dm.key not found, use default license! Database mode = 1, oguid = 222222 Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL EP[0]'s cur_lsn[31068], file_lsn[31068] recover successfully! time used: 00:00:01.061
配置新備庫
配置dm.ini
在dm8rw5機器上配置備庫的例項名為jy4，dm.ini引數修改如下：
INSTANCE_NAME = jy #例項名總長度不能超過16 PORT_NUM = 5236 #資料庫例項監聽埠 DW_INACTIVE_INTERVAL = 60 #接收守護程式訊息超時時間 ALTER_MODE_STATUS = 0 #不允許手工方式修改例項模式/狀態 ENABLE_OFFLINE_TS = 2 #不允許備庫OFFLINE表空間 MAL_INI = 1 #開啟MAL系統 ARCH_INI = 1 #開啟歸檔配置 RLOG_SEND_APPLY_MON = 64 #統計最近64次的日誌重演資訊
複製一份原系統dmmal.ini檔案，並加上自己一項，最終配置如下：
MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = jy1 MAL_HOST = 10.138.130.213 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.213 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST2] MAL_INST_NAME = jy2 MAL_HOST = 10.138.130.214 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.214 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST3] MAL_INST_NAME = jy3 MAL_HOST = 10.138.130.215 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.215 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST4] MAL_INST_NAME = jy4 MAL_HOST = 10.138.130.226 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.226 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [dmdba@dm8rw5 jy]$ vi dmmal.ini MAL_CHECK_INTERVAL = 5 MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = jy1 MAL_HOST = 10.138.130.213 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.213 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST2] MAL_INST_NAME = jy2 MAL_HOST = 10.138.130.214 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.214 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST3] MAL_INST_NAME = jy3 MAL_HOST = 10.138.130.215 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.215 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237 [MAL_INST4] MAL_INST_NAME = jy4 MAL_HOST = 10.138.130.226 MAL_PORT = 5238 MAL_INST_HOST = 10.138.130.226 MAL_INST_PORT = 5236 MAL_DW_PORT = 5239 MAL_INST_DW_PORT = 5237
配置dmarch.ini
修改dmarch.ini，配置本地歸檔和即時歸檔。
[ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY ARCH_DEST = jy1 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY ARCH_DEST = jy2 [ARCHIVE_TIMELY3] ARCH_TYPE = TIMELY ARCH_DEST = jy3 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0 [dmdba@dm8rw5 jy]$ vi dmarch.ini [ARCHIVE_TIMELY1] ARCH_TYPE = TIMELY ARCH_DEST = jy1 [ARCHIVE_TIMELY2] ARCH_TYPE = TIMELY ARCH_DEST = jy2 [ARCHIVE_TIMELY3] ARCH_TYPE = TIMELY ARCH_DEST = jy3 [ARCHIVE_LOCAL1] ARCH_TYPE = LOCAL ARCH_DEST = /dm8/arch ARCH_FILE_SIZE = 128 ARCH_SPACE_LIMIT = 0
配置dmwatcher.ini
修改dmwatcher.ini配置守護程式，配置為全域性守護型別，使用自動切換模式。
[GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 222222 INST_INI = /dm8/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm8/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0 [dmdba@dm8rw5 jy]$ vi dmwatcher.ini [GRP1] DW_TYPE = GLOBAL DW_MODE = AUTO DW_ERROR_TIME = 10 INST_RECOVER_TIME = 60 INST_ERROR_TIME = 10 INST_OGUID = 222222 INST_INI = /dm8/data/jy/dm.ini INST_AUTO_RESTART = 1 INST_STARTUP_CMD = /dm8/bin/dmserver RLOG_SEND_THRESHOLD = 0 RLOG_APPLY_THRESHOLD = 0
註冊守護程式服務
[root@dm8rw5 ~]# /dm8/script/root/dm_service_installer.sh -watcher_ini /dm8/data/jy/dmwatcher.ini -p jy4 -t dmwatcher Created symlink from /etc/systemd/system/multi-user.target.wants/DmWatcherServicejy4.service to /usr/lib/systemd/system/DmWatcherServicejy4.service. Finished to create the service (DmWatcherServicejy4)
啟動備庫
以mount方式啟動備庫
[dmdba@dm8rw5 ~]$ dmserver /dm8/data/jy/dm.ini mount file dm.key not found, use default license! version info: develop DM Database Server x64 V8 1-2-84-21.10.21-149328-10032-ENT startup... Normal of FAST Normal of DEFAULT Normal of RECYCLE Normal of KEEP Normal of ROLL Database mode = 1, oguid = 222222 License will expire on 2022-10-21 file lsn: 31068 ndct db load 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
啟動命令列工具disql，登入備庫設定OGUID值。
[dmdba@dm8rw5 jy]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is primary, state is mount login used time : 3.364(ms) disql V8 SQL> sp_set_oguid(222222); sp_set_oguid(222222); [-720]:Dmwatcher is active, or current configuration(ALTER_MODE_STATUS) not allowed to alter database. used time: 7.912(ms). Execute id is 0. SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 8.997(ms). Execute id is 1. SQL> sp_set_oguid(222222); DMSQL executed successfully used time: 57.701(ms). Execute id is 2.
修改資料庫模式
修改資料庫為Standby模式:
SQL> alter database standby; executed successfully used time: 66.098(ms). Execute id is 0. SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 8.720(ms). Execute id is 3.
動態新增MAL配置
需要分別連線原系統中每個例項單獨執行：
1.允許手工修改伺服器的模式和狀態。
[dmdba@dm8rw1 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is primary, state is open login used time : 2.628(ms) disql V8 SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 36.203(ms). Execute id is 600. [dmdba@dm8rw2 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is standby, state is open login used time : 4.421(ms) disql V8 SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 140.819(ms). Execute id is 0. [dmdba@dm8rw3 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is standby, state is open login used time : 4.246(ms) disql V8 SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1); DMSQL executed successfully used time: 127.831(ms). Execute id is 0
2.退出原系統所有守護程式。
注意守護程式退出後，需要等待對應例項dm.ini中配置的DW_INACTIVE_INTERVAL時間後才允許後續操作，否則未超過故障認定時間，伺服器認為守護程式仍然處於活動狀態，不允許手工修改伺服器為mount狀態。
[root@dm8rw1 ~]# service DmWatcherServicejy1 stop Redirecting to /bin/systemctl stop DmWatcherServicejy1.service [root@dm8rw2 ~]# service DmWatcherServicejy2 stop Redirecting to /bin/systemctl stop DmWatcherServicejy2.service [root@dm8rw3 ~]# service DmWatcherServicejy3 stop Redirecting to /bin/systemctl stop DmWatcherServicejy3.service
3.修改資料庫狀態為MOUNT。
[dmdba@dm8rw1 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is primary, state is open login used time : 2.628(ms) disql V8 SQL> alter database mount; executed successfully used time: 47.587(ms). Execute id is 0. [dmdba@dm8rw2 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is standby, state is open login used time : 4.421(ms) disql V8 SQL> alter database mount; executed successfully used time: 29.109(ms). Execute id is 0. [dmdba@dm8rw3 ~]$ disql SYSDBA/SYSDBA@localhost:5236 Server[localhost:5236]:mode is standby, state is open login used time : 4.246(ms) disql V8 SQL> alter database mount; executed successfully used time: 37.954(ms). Execute id is 0.
4.重新修改ALTER_MODE_STATUS值為0，不允許手工修改伺服器的模式和狀態
jy1:
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 8.305(ms). Execute id is 602.
jy2:
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 8.565(ms). Execute id is 2.
jy3:
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0); DMSQL executed successfully used time: 8.420(ms). Execute id is 2.
5.動態增加mal中jy4的相關配置資訊
jy1:
SQL> SF_MAL_CONFIG(1,0);
DMSQL executed successfully
used time: 0.760(ms). Execute id is 603.
SQL> SF_MAL_INST_ADD('MAL_INST4','jy4','10.138.130.226',5238,'10.138.130.226',5236,5239,0,5237);
DMSQL executed successfully
used time: 1.085(ms). Execute id is 604.
SQL> SF_MAL_CONFIG_APPLY();
DMSQL executed successfully
used time: 0.816(ms). Execute id is 605.
SQL> SF_MAL_CONFIG(0,0);
DMSQL executed successfully
used time: 0.514(ms). Execute id is 606.
jy2:
SQL> SF_MAL_CONFIG(1,0);
DMSQL executed successfully
used time: 0.881(ms). Execute id is 3.
SQL> SF_MAL_INST_ADD('MAL_INST4','jy4','10.138.130.226',5238,'10.138.130.226',5236,5239,0,5237);
DMSQL executed successfully
used time: 1.306(ms). Execute id is 4.
SQL> SF_MAL_CONFIG_APPLY();
DMSQL executed successfully
used time: 0.827(ms). Execute id is 5.
SQL> SF_MAL_CONFIG(0,0);
DMSQL executed successfully
used time: 0.572(ms). Execute id is 6.
jy3:
SQL> SF_MAL_CONFIG(1,0);
DMSQL executed successfully
used time: 0.663(ms). Execute id is 3.
SQL> SF_MAL_INST_ADD('MAL_INST4','jy4','10.138.130.226',5238,'10.138.130.226',5236,5239,0,5237);
DMSQL executed successfully
used time: 1.168(ms). Execute id is 4.
SQL> SF_MAL_CONFIG_APPLY();
DMSQL executed successfully
used time: 0.815(ms). Execute id is 5.
SQL> SF_MAL_CONFIG(0,0);
DMSQL executed successfully
used time: 0.585(ms). Execute id is 6.
動態新增歸檔配置
分別連線原系統中的所有例項（此時處於MOUNT狀態），動態新增dmarch.ini中歸檔節點
jy1:
SQL> alter database add archivelog 'DEST=jy4, TYPE=TIMELY'; executed successfully used time: 1.596(ms). Execute id is 0.
jy2:
SQL> alter database add archivelog 'DEST=jy4, TYPE=TIMELY'; executed successfully used time: 1.199(ms). Execute id is 0.
jy3:
SQL> alter database add archivelog 'DEST=jy4, TYPE=TIMELY'; executed successfully used time: 1.357(ms). Execute id is 0.
修改監視器dmmonitor.ini
在dmmonitor.ini中新增新增的備庫jy4：
[dmdba@dm8rw4 ~]$ vi /dm8/data/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 = 222222 MON_DW_IP = 10.138.130.213:5239 MON_DW_IP = 10.138.130.214:5239 MON_DW_IP = 10.138.130.215:5239 MON_DW_IP = 10.138.130.226:5239
啟動所有守護程式以及監視器
分別啟動主庫和備庫（包括jy4）的所有守護程式，最後啟動監視器。
jy1:
[root@dm8rw1 ~]# service DmWatcherServicejy1 start Redirecting to /bin/systemctl start DmWatcherServicejy1.service
jy2:
[root@dm8rw2 ~]# service DmWatcherServicejy2 start Redirecting to /bin/systemctl start DmWatcherServicejy2.service
jy3:
[root@dm8rw3 ~]# service DmWatcherServicejy3 start Redirecting to /bin/systemctl start DmWatcherServicejy3.service
jy4:
[root@dm8rw5 ~]# service DmWatcherServicejy4 start Redirecting to /bin/systemctl start DmWatcherServicejy4.service [dmdba@dm8rw4 ~]$ dmmonitor /dm8/data/dmmonitor.ini [monitor] 2022-02-17 16:30:16: DMMONITOR[4.0] V8 [monitor] 2022-02-17 16:30:16: DMMONITOR[4.0] IS READY. [monitor] 2022-02-17 16:30:16: Received message from(JY1) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-02-17 16:30:16 OPEN OK JY1 OPEN PRIMARY VALID 6 32418 32418 [monitor] 2022-02-17 16:30:16: Received message from(JY3) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-02-17 16:30:16 OPEN OK JY3 OPEN STANDBY VALID 6 32418 32418 [monitor] 2022-02-17 16:30:16: Received message from(JY2) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-02-17 16:30:16 OPEN OK JY2 OPEN STANDBY VALID 6 32418 32418 [monitor] 2022-02-17 16:30:16: Received message from(JY4) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN 2022-02-17 16:30:16 OPEN OK JY4 OPEN STANDBY VALID 6 32418 32418
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2883665/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DM8配置讀寫分離叢集
- DM8配置讀寫分離叢集V4.0
- DM7搭建讀寫分離叢集
- 設定gbase叢集節點離線狀態
- 淺談高效能資料庫叢集——讀寫分離資料庫
- 搭建Redis哨兵叢集並使用RedisTemplate實現讀寫分離Redis
- DM8 DMDSC動態擴充套件節點套件
- 資料庫治理利器：動態讀寫分離資料庫
- 從節點崩了，還怎麼「主從讀寫分離」？
- docker+mysql叢集+讀寫分離+mycat管理+垂直分庫+負載均衡DockerMySql負載
- Mysql讀寫分離叢集的搭建且與MyCat進行整合MySql
- OpenResty + Lua 動態增加 Zuul 節點RESTZuul
- 搭建Redis簡易叢集實現主從複製和讀寫分離Redis
- 搭建Redis“主-從-從”模式叢集並使用 RedisTemplate 實現讀寫分離Redis模式
- Pisanix v0.2.0 釋出｜新增動態讀寫分離支援
- consul 多節點/單節點叢集搭建
- DM8搭建MPP叢集
- 讀懂這一篇，叢集節點不下線
- MongoDB分片叢集節點狀態stateStr:RECOVERING解決MongoDB
- 在 Istio 中實現 Redis 叢集的資料分片、讀寫分離和流量映象Redis
- 節點從Proxmox VE徹底撤離及再次加入叢集
- 4.2 叢集節點初步搭建
- Solaris叢集節點重啟
- HAC叢集新增新節點
- KingbaseES V8R6叢集運維案例之---license禁用讀寫分離模組運維
- Redis的讀寫分離Redis
- KunlunBase 讀寫分離方案
- Laravel讀寫分離原理Laravel
- discuz 配置讀寫分離(主寫從讀)
- MongoDB叢集搭建(包括隱藏節點，仲裁節點)MongoDB
- linux搭建kafka叢集，多master節點叢集說明LinuxKafkaAST
- MyCat分庫分表、讀寫分離
- 資料讀寫壓力大，讀寫分離
- ShardingSphere-proxy +PostgreSQL實現讀寫分離（靜態策略）SQL
- 達夢資料庫(DM8)大規模並行叢集MPP 2節點安裝部署資料庫並行
- HAC叢集更改IP（單節點更改、全部節點更改）
- ShardingSphere(七) 讀寫分離配置，實現分庫讀寫操作
- Oracle叢集軟體管理-新增和刪除叢集節點Oracle