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