达梦数据库主备架构配置

最新推荐文章于 2024-09-11 19:10:03 发布

richluo1

最新推荐文章于 2024-09-11 19:10:03 发布

阅读量562

点赞数

文章标签：数据库架构 java

本文链接：https://blog.csdn.net/richluo1/article/details/126491351

版权

DM主备架构
vim /etc/sysctl.conf
1、添加以下内容
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
kernel.core_pattern = /data/core.%p
vm.swappiness=10
vm.dirty_background_ratio = 0
vm.min_free_kbytes = 2097152
1.1、使参数生效，执行
sysctl -p
2、新建数据库管理用户和组
groupadd dinstall -g 2001
useradd -G dinstall -m -d /home/dmdba -s /bin/bash -u 2001 dmdba
passwd dmdba
Dameng123
vim /etc/security/limits.conf
dmdba soft nice 0
dmdba hard nice 0
dmdba soft as unlimited
dmdba hard as unlimited
dmdba soft fsize unlimited
dmdba hard fsize unlimited
dmdba soft nproc 65536
dmdba hard nproc 65536
dmdba soft nofile 65536
dmdba hard nofile 65536
dmdba soft core unlimited
dmdba hard core unlimited
dmdba soft data unlimited
dmdba hard data unlimited
vim /home/dmdba/.bash_profile
export DM_HOME=/dm8/dmdbms/ ----看环境的要求而定
export PATH=$PATH:$DM_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
2.1、root用户挂载数据库软件的iso文件
mkdir -p /dmiso
mount -o loop dm8_20200930_x86_rh6_64_ent_8.1.1.134.iso /dmiso

2.2、使用 dminit 工具初始化实例或者使用dbca.sh 图形化初始化数据库
--执行以下命令
dminit path=/dm8/dmdbms/data page_size=32 extent_size=32 charset=1 log_size=2048 db_name=DM instance_name=DM SYSDBA_PWD="Dameng123" SYSAUDITOR_PWD="Dameng123"
root 用户下切换到/dm8/dmdbms/script/root/ 注册服务，图形化初始化免
cd /dm8/dmdbms/script/root/
./dm_service_installer.sh -t dmserver -p DM -dm_ini /dm8/dmdbms/data/DAMENG/dm.ini
systemctl start DmServiceDM.service
3、数据库打开归档
alter database mount;
alter database add archivelog 'dest=/dm8/dmdbms/data/arch/ ,TYPE=local,FILE_SIZE=1024,SPACE_LIMIT=102400';
alter database archivelog;
alter database open;

4、定制作业备份
4.1、登录数据库执行以下命令，创建作业系统表
SP_INIT_JOB_SYS(1);
--全量备份（每周六 23 点全备）：其中有1分钟后的一次性全备调度，执行完成后检查备份是否成功。
call SP_CREATE_JOB('bakfull',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('bakfull');
call SP_ADD_JOB_STEP('bakfull', 'bak1', 6, '01020000/dm8/dmdbms/data/bakfull', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakfull', 'std1', 1, 2, 1, 64, 0, '23:00:00', NULL, '2021-11-01 21:17:22', NULL, '');
call SP_ADD_JOB_SCHEDULE('bakfull', 'once', 1, 0, 0, 0, 0, NULL, NULL, sysdate+1/1440, NULL, '');
call SP_JOB_CONFIG_COMMIT('bakfull');
4.2、增量备份（每周除周六外每天 23 点增量备份）：
call SP_CREATE_JOB('bakincr',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('bakincr');
call SP_ADD_JOB_STEP('bakincr', 'bak2', 6, '41010000/dm8/dmdbms/data/bakfull|/dm8/dmdbms/data/bakfull', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('bakincr', 'std2', 1, 2, 1, 63, 0, '23:00:00', NULL, '2021-11-01 21:19:30', NULL, '');
call SP_JOB_CONFIG_COMMIT('bakincr');
4.3、备份定期删除（每天 23：30 删除 14 天前备份）：
call SP_CREATE_JOB('delbak',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('delbak');
call SP_ADD_JOB_STEP('delbak','bak1',0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/dm8/dmdbms/data/bakfull'');call sp_db_bakset_remove_batch(''DISK'',now()-14);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('delbak', 'del01', 1, 1, 1, 0, 0, '23:30:00', NULL, '2020-11-02 14:48:41', NULL, '');
call SP_JOB_CONFIG_COMMIT('delbak');
4.4、另外添加自动收集统计信息的任务（每天1点收集全库统计信息）
call SP_CREATE_JOB('statistics',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('statistics');
call SP_ADD_JOB_STEP('statistics', 'statistics1', 0, 'begin
for rs in (select ''sf_set_SESSION_para_value(''''HAGR_HASH_SIZE'''',(select cast(
case when max(table_rowcount(owner,table_name))<=(select max_value from v$dm_ini
where para_Name=''''HAGR_HASH_SIZE'''') and max(table_rowcount(owner,table_name))>=(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
max(table_rowcount(owner,table_name)) when max(table_rowcount(owner,table_name))<(
select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') then
(select min_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') else
(select max_value from v$dm_ini where para_Name=''''HAGR_HASH_SIZE'''') end as bigint)
from dba_tables where owner=''''''||NAME||''''''));''
sql1,''DBMS_STATS.GATHER_SCHEMA_STATS(''''''||NAME||'''''',100,TRUE,''''FOR ALL COLUMNS SIZE AUTO'''');''
sql2
from SYS.SYSOBJECTS where TYPE$=''SCH'' ) loop
execute immediate rs.sql1;
execute immediate rs.sql2;
end loop;
end;', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('statistics', 'statistics1', 1, 2, 1, 64, 0, '01:00:00', NULL, '2021-06-09 22:54:37', NULL, '');
call SP_JOB_CONFIG_COMMIT('statistics');

-5、配置sql日志
BUF_TOTAL_SIZE = 10240 #SQLs Log Buffer Total Size(K)(1024~1024000)
BUF_SIZE = 1024 #SQLs Log Buffer Size(K)(50~409600)
BUF_KEEP_CNT = 6 #SQLs Log buffer keeped count(1~100)

[SLOG_ALL]
FILE_PATH = ../log
PART_STOR = 1
SWITCH_MODE = 2
SWITCH_LIMIT = 512
ASYNC_FLUSH = 1
FILE_NUM = 5
ITEMS = 0
SQL_TRACE_MASK = 2:3:25
MIN_EXEC_TIME = 1500
USER_MODE = 0
USERS =

6、查看DmAPService是否启动，如果没有先启动 DMAP 服务，dmdba 到安装目录的 bin 下执行以下命令： systemctl status DmAPService.service
systemctl start DmAPService.service
systemctl stop DmServiceDM.service
--dmdba用户启动dmrman工具
dmrman use_ap=2
6.1、执行 backup 全库：
backup database '/dm8/dmdbms/data/DAMENG/dm.ini' backupset '/dm8/dmdbms/data/bakfull';
传到备库
scp -pr bakfull/ dmdba@192.168.192.3:/dm8/dmdbms/data/bakfull
6.2、dmdba用户备库启动dmrman工具
dmrman use_ap=2
restore database '/dm8/dmdbms/data/DAMENG/dm.ini' from backupset '/dm8/dmdbms/data/bakfull';
recover database '/dm8/dmdbms/data/DAMENG/dm.ini' from backupset '/dm8/dmdbms/data/bakfull';
recover database '/dm8/dmdbms/data/DAMENG/dm.ini' update db_magic;
7、主备两主机修改dm.dm_ini
INSTANCE_NAME = DM1
MAL_INI = 1
ARCH_INI = 1
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
--------------
INSTANCE_NAME = DM2
MAL_INI = 1
ARCH_INI = 1
ALTER_MODE_STATUS = 0
ENABLE_OFFLINE_TS = 2
8、主备机添加 dmarch.ini
8.1、主机添加
[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = DM2
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/dmdbms/data/arch/
ARCH_FILE_SIZE = 2048
ARCH_SPACE_LIMIT = 102400
8.2、备机添加
[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = DM2
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm8/dmdbms/data/arch/
ARCH_FILE_SIZE = 2048
ARCH_SPACE_LIMIT = 102400

9、主备机新建文件 dmmal.ini，执行以下命令
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 15
[MAL_INST1]
MAL_INST_NAME = DM1
MAL_HOST = 192.168.192.2 --主机IP根据环境给的配置
MAL_PORT = 7336
MAL_INST_HOST = 192.168.192.2 --主机IP根据环境给的配置
MAL_INST_PORT = 5236
MAL_DW_PORT = 7436 --DEM端口
MAL_INST_DW_PORT = 7536
[MAL_INST2]
MAL_INST_NAME = DM2
MAL_HOST = 192.168.192.3 --备机IP根据环境给的配置
MAL_PORT = 7336
MAL_INST_HOST = 192.168.192.3 --备机IP根据环境给的配置
MAL_INST_PORT = 5236
MAL_DW_PORT = 7436 --DEM端口
MAL_INST_DW_PORT = 7536

10、配置守护进程配置文件 dmwatcher.ini
[GRP_DW]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 30
INST_RECOVER_TIME = 60
INST_ERROR_TIME = 20
INST_OGUID = 453331
INST_INI = /dm8/dmdbms/data/DAMENG/dm.ini --根据环境配置
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /dm8/dmdbms/bin/dmserver --根据安装数据软件的位置配置
RLOG_APPLY_THRESHOLD = 0

11、以 mount 方式启动主备数据库实例（两台）
dmserver /dm8/dmdbms/data/DAMENG/dm.ini mount;
sp_set_oguid(453331);
alter database primary; --主库执行
alter database standby; --备库执行

12、启动守护进程
dmwatcher /dm8/dmdbms/data/DAMENG/dmwatcher.ini
--登录数据库查看数据库是否open，正常是open
13、主备两台配置监视器配置文件 dmmonitor.ini
MON_DW_CONFIRM = 1
MON_LOG_PATH = /dm8/dmdbms/data/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 200
MON_LOG_SPACE_LIMIT = 1024
[GRP_DW]
MON_INST_OGUID = 453331
MON_DW_IP = 192.168.192.2:7436
MON_DW_IP = 192.168.192.3:7436
14、执行监视器
dmmonitor /dm8/dmdbms/data/DAMENG/dmmonitor.ini

[monitor] 2022-08-23 16:38:15: DMMONITOR[4.0] V8
[monitor] 2022-08-23 16:38:16: DMMONITOR[4.0] IS READY.

[monitor] 2022-08-23 16:38:16: 收到守护进程(DM2)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-08-23 16:38:16 OPEN OK DM2 OPEN STANDBY NULL 7 150841 150841

[monitor] 2022-08-23 16:38:16: 收到守护进程(DM1)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-08-23 16:38:16 OPEN OK DM1 OPEN PRIMARY VALID 7 150845 150847

show global info
2022-08-23 16:38:28
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP_DW 453331 TRUE 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.192.2 7436 2022-08-23 16:38:28 GLOBAL VALID OPEN DM1 OK 1 1 OPEN PRIMARY DSC_OPEN REALTIME VALID

DW_IP MAL_DW_PORT WTIME WTYPE WCTLSTAT WSTATUS INAME INST_OK N_EP N_OK ISTATUS IMODE DSC_STATUS RTYPE RSTAT
192.168.192.3 7436 2022-08-23 16:38:28 GLOBAL VALID OPEN DM2 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME VALID