DM8 DSC集群实时主备搭建

1、环境准备

主库DSC集群公网ip：192.168.1.34/35    私有ip：192.168.10.134/135

备库ip：192.168.1.33

2、对DSC集群数据库全备

1)主库做全备

[dmdba@dmdsc01 bin]$ disql sysdba/dameng123

BACKUP DATABASE TO WEEKLY_FULL_BAK BACKUPSET

'/dm/dmbak/db_fullbak_01' DEVICE TYPE DISK BACKUPINFO 'db_fullbak' MAXPIECESIZE 300 COMPRESSED LEVEL 1 PARALLEL 2;

2）检查备份是否有效

cd /dm/dmdbms/bin

./dmrman  dcr_ini=/dm/dsc/config/dmdcr.ini

RMAN> check backupset '/dm/dmbak/db_fullbak_01';

check backupset '/dm/dmbak/db_fullbak_01';

[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 

check backupset successfully.

time used: 264.470(ms)

3）将备份拷贝至备机机器

scp  -r /dm/dmbak/db_fullbak_01 192.168.1.33:/dm/dmbak

3、备机安装数据库

1）在备库安装数据库软件

#创建用户和组

groupadd dinstall

useradd  -g dinstall -m -d /home/dmdba -s /bin/bash  dmdba

passwd dmdba

#创建相关目录并授权

mkdir -p /dm/dmdbms

mkdir -p /dm/dmdata

mkdir -p /dmarch

mkdir -p /dmbak

chown -R dmdba:dinstall /dm

chown -R dmdba:dinstall /dmarch

chown -R dmdba:dinstall  /dmbak

#配置dmdba用户环境变量

su - dmdba

cd ~

vi .bash_profile

export DM_HOME=/dm8/dmdbms

export PATH=$PATH:$DM_HOME/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin

#使环境变量配置生效

source .bash_profile

#安装数据库软件

su - dmdba

cd /mnt

./DMInstall.bin -i

2）在备机初始化数据库

[dmdba@Kylin03 bin]$ dminit path=/dm/dmdata  CASE_SENSITIVE=1 CHARSET=0 db_name=DW01 instance_name=DW01 port_num=5236 LOG_SIZE=512 SYSDBA_PWD=dameng123  PAGE_SIZE=32

initdb V8

db version: 0x7000c

file dm.key not found, use default license!

License will expire on 2024-01-10

Normal of FAST

Normal of DEFAULT

Normal of RECYCLE

Normal of KEEP

Normal of ROLL

 log file path: /dm/dmdata/DW01/DW0101.log

 log file path: /dm/dmdata/DW01/DW0102.log

write to dir [/dm/dmdata/DW01].

create dm database success. 2023-07-30 00:19:53

注意，对比主库dm.ini文件修改备库的dm.ini文件。

4、在备机还原dsc集群备份的数据

1）检查备份集

cd /dm/dmdbms/dm8/bin

./dmrman

RMAN> check backupset '/dm/dmbak/db_fullbak_01';

check backupset '/dm/dmbak/db_fullbak_01';

[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 

check backupset successfully.

time used: 707.058(ms)

2）还原数据库

restore database '/dm/dmdata/DW01/dm.ini' from backupset '/dm/dmbak/db_fullbak_01' TASK THREAD 2;

recover database '/dm/dmdata/DW01/dm.ini' from backupset '/dm/dmbak/db_fullbak_01';

recover database '/dm/dmdata/DW01/dm.ini' update db_magic;

5、修改配置文件

1）主库配置：

修改dm.ini参数如下：

DSC节点一：

INSTANCE_NAME = DSC0 

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 次的日志发送信息

DSC节点二：

INSTANCE_NAME = DSC1 

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和dm.ini放在同一路径下），两个DSC节点内容必须一致。

cat >>dmmal.ini<<EOF

MAL_CHECK_INTERVAL=87

MAL_CONN_FAIL_INTERVAL=180

MAL_SYS_BUF_SIZE=600

MAL_BUF_SIZE=300

MAL_VPOOL_SIZE=500

MAL_COMPRESS_LEVEL=0

[MAL_INST0]

MAL_INST_NAME=DSC0

MAL_HOST=192.168.1.34

MAL_PORT=5736

MAL_INST_HOST=192.168.10.134

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

[MAL_INST1]

MAL_INST_NAME=DSC1

MAL_HOST=192.168.1.35

MAL_PORT=5737

MAL_INST_HOST=192.168.10.135

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

[MAL_INST2]

MAL_INST_NAME=DW01

MAL_HOST=192.168.1.33

MAL_PORT=5738

MAL_INST_HOST=192.168.10.133

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

EOF

配置dmarch.ini，配置本地归档和实时归档。

DSC节点一：

ARCH_WAIT_APPLY = 0

ARCH_LOCAL_SHARE = 1

ARCH_LOCAL_SHARE_CHECK =0

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST =+DMDATA/DSC0/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

[ARCH_REMOTE1]

ARCH_TYPE = REMOTE

ARCH_DEST = DSC1

ARCH_INCOMING_PATH =+DMDATA/DSC1/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME    #实时归档类型

ARCH_DEST = DW01    #实时归档目标实例名

DSC节点二：

ARCH_WAIT_APPLY = 0

ARCH_LOCAL_SHARE = 1

ARCH_LOCAL_SHARE_CHECK =0

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL

ARCH_DEST =+DMDATA/DSC1/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

[ARCH_REMOTE1]

ARCH_TYPE = REMOTE

ARCH_DEST = DSC0

ARCH_INCOMING_PATH =+DMDATA/DSC0/arch

ARCH_FILE_SIZE = 128

ARCH_SPACE_LIMIT = 0

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME    #实时归档类型

ARCH_DEST = DW01    #实时归档目标实例名

配置 dmwatcher.ini。

DSC节点一：

[GRP1]

DW_TYPE = GLOBAL #全局守护类型

DW_MODE = MANUAL #手动切换模式

DW_ERROR_TIME = 120 #远程守护进程故障认定时间

INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间

INST_ERROR_TIME = 120 #本地实例故障认定时间

INST_OGUID = 453331 #守护系统唯一OGUID值

INST_INI = /dm/dsc/config/dsc0_config/dm.ini #dm.ini  配置文件路径

DCR_INI = /dm/dsc/config/dmdcr.ini

INST_AUTO_RESTART = 0 #打开实例的自动启动功能

INST_STARTUP_CMD = /dm/dmdbms/dm8/bin/DmServiceDSC start #命令行方式启动

RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阀值，默认关闭

RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阀值，默认关闭

DSC节点二：

[GRP1]

DW_TYPE = GLOBAL #全局守护类型

DW_MODE = MANUAL #手动切换模式

DW_ERROR_TIME = 120 #远程守护进程故障认定时间

INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间

INST_ERROR_TIME = 120 #本地实例故障认定时间

INST_OGUID = 453331 #守护系统唯一OGUID值

INST_INI = /dm/dsc/config/dsc1_config/dm.ini #dm.ini  配置文件路径

DCR_INI = /dm/dsc/config/dmdcr.ini

INST_AUTO_RESTART = 0 #打开实例的自动启动功能

INST_STARTUP_CMD = /dm/dmdbms/dm8/bin/DmServiceDSC start #命令行方式启动

RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阀值，默认关闭

RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阀值，默认关闭

2）备库配置：

配置dm.ini,修改参数如下：

INSTANCE_NAME = DW01 

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和dm.ini放在同一路径下）

MAL_CHECK_INTERVAL=87

MAL_CONN_FAIL_INTERVAL=180

MAL_SYS_BUF_SIZE=600

MAL_BUF_SIZE=300

MAL_VPOOL_SIZE=500

MAL_COMPRESS_LEVEL=0

[MAL_INST0]

MAL_INST_NAME=DSC0

MAL_HOST=192.168.1.34

MAL_PORT=5736

MAL_INST_HOST=192.168.10.134

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

[MAL_INST1]

MAL_INST_NAME=DSC1

MAL_HOST=192.168.1.35

MAL_PORT=5737

MAL_INST_HOST=192.168.10.135

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

[MAL_INST2]

MAL_INST_NAME=DW01

MAL_HOST=192.168.1.33

MAL_PORT=5738

MAL_INST_HOST=192.168.10.133

MAL_INST_PORT=5236

MAL_DW_PORT=52141

MAL_INST_DW_PORT=5276

配置dmarch.ini

[ARCHIVE_REALTIME]

ARCH_TYPE = REALTIME    #实时归档类型

ARCH_DEST = DSC0/DSC1    #实时归档目标实例名

[ARCHIVE_LOCAL1]

ARCH_TYPE = LOCAL #本地归档类型

ARCH_DEST = /dm/dmarch #本地归档文件存放路径

ARCH_FILE_SIZE = 2048       #单位 Mb ，本地单个归档文件最大值

ARCH_SPACE_LIMIT = 204800    #单位 Mb ，0  表示无限制，范围 1024~4294967294M

配置 dmwatcher.ini

[GRP1]

DW_TYPE = GLOBAL #全局守护类型

DW_MODE = MANUAL #手动切换模式

DW_ERROR_TIME = 120 #远程守护进程故障认定时间

INST_RECOVER_TIME = 60 #主库守护进程启动恢复的间隔时间

INST_ERROR_TIME = 120 #本地实例故障认定时间

INST_OGUID = 453331 #守护系统唯一OGUID值

INST_INI = /dm/dmdata/DW01/dm.ini #dm.ini  配置文件路径

INST_AUTO_RESTART = 0 #打开实例的自动启动功能

INST_STARTUP_CMD = /dm/dmdbms/dm8/bin/dmserver #命令行方式启动

RLOG_SEND_THRESHOLD = 0 #指定主库发送日志到备库的时间阀值，默认关闭

RLOG_APPLY_THRESHOLD = 0 #指定备库重演日志的时间阀值，默认关闭

6、搭建主备

1）修改数据库服务默认启动到mount状态并启动（主备库所有节点修改）

[dmdba@dmdsc01 bin]$ grep "START_MODE=mount" DmServiceDSC

START_MODE=mount

#以mount方式分别启动DSC及备库

DmServiceDSC start

2）修改数据库主备模式

使用disql连接dsc数据库执行(在dsc其中一个节点执行)：

SQL> sp_set_oguid(453331);

SQL> alter database primary;

使用disql连接备库数据库执行：

SQL> sp_set_oguid(453331);

SQL> alter database standby;

3）注册守护进程服务并启动：

#主库节点1

/dm/dmdbms/dm8/script/root/dm_service_installer.sh -t dmwatcher -watcher_ini /dm/dsc/config/dsc0_config/dmwatcher.ini -p DSC0

#主库节点2：

/dm/dmdbms/dm8/script/root/dm_service_installer.sh -t dmwatcher -watcher_ini /dm/dsc/config/dsc1_config/dmwatcher.ini -p DSC1

#备库

/dm/dmdbms/dm8/script/root/dm_service_installer.sh -t dmwatcher -watcher_ini /dm/dmdata/DW01/dmwatcher.ini -p DW01

#主备分别启动守护进程

./DmWatcherService start

4）配置监视器

vi /dm/dmdata/DW01/dmmonitor_manual.ini

MON_DW_CONFIRM = 1 #确认监视器模式

MON_LOG_PATH = /dm/dmdbms/dm8/log/ #监视器日志文件存放路径

MON_LOG_INTERVAL = 0 #每隔xxs 定时记录系统信息到日志文件

MON_LOG_FILE_SIZE = 32 #每个日志文件最大 32M

MON_LOG_SPACE_LIMIT = 0 #不限定日志文件总占用空间

[GRP1]

MON_INST_OGUID = 453331    #组 GRP1的唯一OGUID值

MON_DW_IP = 192.168.1.34:52141/192.168.1.35:52141

MON_DW_IP = 192.168.1.33:52141

#登录监视器，查看主备运行情况

[dmdba@Kylin03 ~]$ dmmonitor /dm/dmdata/DW01/dmmonitor_manual.ini

[monitor]         2023-07-30 17:54:34: DMMONITOR[4.0] V8

[monitor]         2023-07-30 17:54:35: DMMONITOR[4.0] IS READY.

[monitor]         2023-07-30 17:54:35: 收到守护进程(DSC0)消息

                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN            

                  2023-07-30 23:54:35  OPEN           OK        DSC0             OPEN        PRIMARY   VALID    38       179718          179718          

[monitor]         2023-07-30 17:54:35:

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

GET MONITOR CONNECT INFO FROM DMWATCHER(DSC0), THE FIRST LINE IS SELF INFO.

DW_CONN_TIME         MON_CONFIRM    MID            MON_IP                   MON_VERSION                                                     

2023-07-30 23:54:35  FALSE          114541909      ::ffff:192.168.1.33      DMMONITOR[4.0] V8

                                              

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

[monitor]         2023-07-30 17:54:35: 收到守护进程(DW01)消息

                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN            

                  2023-07-30 17:54:35  OPEN           OK        DW01             OPEN        STANDBY   VALID    38       179717          179717          

[monitor]         2023-07-30 17:54:36: 收到守护进程(DSC1)消息

                  WTIME                WSTATUS        INST_OK   INAME            ISTATUS     IMODE     RSTAT    N_OPEN   FLSN            CLSN            

                  2023-07-30 17:54:35  STARTUP        OK        DSC0             OPEN        PRIMARY   VALID    38       179718          179719          

show

2023-07-30 17:55:05

#================================================================================#

GROUP            OGUID       MON_CONFIRM     MODE            MPP_FLAG  

GRP1             453331      FALSE           MANUAL          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.1.34        52141        2023-07-30 23:55:05  GLOBAL    VALID     OPEN           DSC0             OK        2     2     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.10.134      5236       OK        DSC0             OPEN        PRIMARY   0          0            REALTIME  VALID    102945          179727          102946          179728          NONE                  

192.168.10.135      5236       OK        DSC1             OPEN        PRIMARY   1          0            REALTIME  VALID    94431           179728          94431           179728          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.1.33        52141        2023-07-30 17:55:05  GLOBAL    VALID     OPEN           DW01             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.10.133      5236       OK        DW01             OPEN        STANDBY   0          0            REALTIME  VALID    97715           179727          97715           179727          NONE                  

DATABASE(DW01) APPLY INFO FROM (DSC0), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:

DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[102945, 102945, 102946], (RLSN, SLSN, KLSN)[179727, 179727, 179728], N_TSK[0], TSK_MEM_USE[512]

REDO_LSN_ARR: (179727)

DSC_SEQNO[1], (RSEQ, SSEQ, KSEQ)[94430, 94430, 94431], (RLSN, SLSN, KLSN)[179727, 179727, 179728], N_TSK[0], TSK_MEM_USE[512]

REDO_LSN_ARR: (179727)

#================================================================================#

5）验证

主库创建表：

[dmdba@dmdsc01 bin]$ disql sysdba/dameng123

服务器[LOCALHOST:5236]:处于主库打开状态

登录使用时间 : 10.071(ms)

disql V8

SQL> create table test2 as select * from dba_tables;

SQL> select count(*) from test2;

行号     COUNT(*)            

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

1          103

备库验证：

[dmdba@Kylin03 ~]$ disql sysdba/dameng123

服务器[LOCALHOST:5236]:处于备库打开状态

登录使用时间 : 3.697(ms)

disql V8

SQL> select count(*) from test2;

行号     COUNT(*)            

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

1          103

达梦在线服务平台：https://eco.dameng.com