代码拉取完成，页面将自动刷新
同步操作将从 hnyuanzj/DMShellInstall 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
[BEGIN] 2022/12/20 0:38:30
-mp --mem_percent 优化数据库时物理内存占比 [80]
-di --dm_iso_name 达梦数据库安装镜像名称
-kp --key_path 达梦数据库密钥路径
数据守护(DataWatch):
-rp --root_passwd root 用户密码
-dhn --wc_hostname 主机名，以逗号隔开，例如 -dhn dw01,dw02
-dpi --wc_public_ip 业务IP，以逗号隔开，例如 -dpi 10.211.55.101,10.211.55.102
-dmi --wc_mal_ip MAL IP，以逗号隔开，例如 -dmi 1.1.1.1,1.1.1.2
-dmoi --dw_monitor_ip 监视器主机 IP
-dgn --dw_group_name 数据守护组名 [GRP1]
-mpn --mal_port_num MAL 监听端口号 [5336],取值范围：1024~65535
-mdpn --mal_dw_port_num 守护进程端口号 [5436],取值范围：1024~65535
-midpn --mal_inst_dw_port_num 实例监听守护进程端口号 [5536],取值范围：1024~65535
-oi --os_iso 是否需要挂载操作系统 ISO 镜像，脚本自动配置 YUM 源 [Y]
DSC集群(DMDSC):
-rp --root_passwd root 用户密码
-dhn --wc_hostname 主机名，以逗号隔开，例如 -dhn dsc01,dsc02
-dpi --wc_public_ip 业务IP，以逗号隔开，例如 -dpi 10.211.55.101,10.211.55.102
-dmi --wc_mal_ip MAL IP，以逗号隔开，例如 -dmi 1.1.1.1,1.1.1.2
-ddn --dsc_db_name 数据库DB_NAME，默认值：DSC
-den --dsc_ep_name 数据库每个节点的实例名前缀，默认值：DSC
-dcd --dcr_disk dcr磁盘名字：/dev/sdb(多链路聚合的磁盘名字：dmdcr01)，只能传入一块磁盘
-vod --vote_disk vote磁盘名字：/dev/sdc(多链路聚合的磁盘名字：dmvote01)，只能传入一块磁盘
-lod --log_disk redo日志磁盘名字：/dev/sdd,/dev/sde(多链路聚合的磁盘名字：dmlog01,dmlog02),可以是一块盘，也可以是多块盘，如果没有划分单独的redo磁盘，可以不写
-ard --arch_disk 归档日志磁盘名字：/dev/sdf,/dev/sdg(多链路聚合的磁盘名字：dmarch01,dmarch02),可以是一块盘，也可以是多块盘，如果没有划分单独的arch磁盘，可以不写
-dad --data_disk 数据盘名字：/dev/sdh,/dev/sdi(多链路聚合的磁盘名字：dmdata01,dmdata02),可以是一块盘，也可以是多块盘，盘数必须大于等于 1
-apd --asm_pwd asm实例密码,默认值：Welcome1
-cdp --css_dcr_ep_port css通信端口,默认值：[9341],取值范围：1024~65534
-adp --asm_dcr_ep_port asm通信端口,默认值：[9351],取值范围：1024~65534
-ddp --db_dcr_ep_port db通信端口,默认值：[9741],取值范围：1024~65534
-amp --asvrmal_mal_port mal系统通信端口,默认值：[7236],取值范围：1024~65534
-mtp --multipath 是否需要配置multipath多链路聚合，脚本默认不配置 [N]
-oi --os_iso 是否需要挂载操作系统 ISO 镜像，脚本自动配置 YUM 源 [Y]
注意：本脚本仅用于新服务器上实施部署数据使用，请严禁在已运行数据库的主机上执行，以免发生数据丢失或者损坏，造成不可挽回的损失！！！
[root@centos6 soft]# ls
a.sh dm8_20221121_x86_rh6_64.iso DMShellInstall
[root@centos6 soft]# ./DMShellInstall -di dm8_20221121_x86_rh6_64.iso -d /opt/dmdbms \
> -dhn dw01,dw02,dw03 -rp 123456 \
> -dpi 192.168.2.21,192.168.2.22,192.168.2.23 -dmi 172.16.2.21,172.16.2.22,172.16.2.23 \
> -mpn 12345 -mdpn 12346 -midpn 12347 \
> -oi N
███████ ████ ████ ████████ ██ ██ ██ ██ ██ ██ ██
░██░░░░██ ░██░██ ██░██ ██░░░░░░ ░██ ░██ ░██░██ ░██ ░██ ░██
░██ ░██░██░░██ ██ ░██░██ ░██ █████ ░██ ░██░██ ███████ ██████ ██████ ██████ ░██ ░██
░██ ░██░██ ░░███ ░██░█████████░██████ ██░░░██ ░██ ░██░██░░██░░░██ ██░░░░ ░░░██░ ░░░░░░██ ░██ ░██
░██ ░██░██ ░░█ ░██░░░░░░░░██░██░░░██░███████ ░██ ░██░██ ░██ ░██░░█████ ░██ ███████ ░██ ░██
░██ ██ ░██ ░ ░██ ░██░██ ░██░██░░░░ ░██ ░██░██ ░██ ░██ ░░░░░██ ░██ ██░░░░██ ░██ ░██
░███████ ░██ ░██ ████████ ░██ ░██░░██████ ███ ███░██ ███ ░██ ██████ ░░██ ░░████████ ███ ███
░░░░░░░ ░░ ░░ ░░░░░░░░ ░░ ░░ ░░░░░░ ░░░ ░░░ ░░ ░░░ ░░ ░░░░░░ ░░ ░░░░░░░░ ░░░ ░░░
请选择达梦数据库部署类型：单机[si]/数据守护[dw]/dsc集群[dsc]
dw
达梦数据库安装部署类型: datawatch
校验 192.168.2.21,192.168.2.22,192.168.2.23 地址，请等待！！！
校验 172.16.2.21,172.16.2.22,172.16.2.23 地址，请等待！！！
主库安装 expect 软件用于 root 用户互信：
已加载插件：fastestmirror, security
设置安装进程
Determining fastest mirrors
OS-YUM | 4.0 kB 00:00 ...
解决依赖关系
--> 执行事务检查
---> Package expect.x86_64 0:5.44.1.15-5.el6_4 will be 安装
--> 处理依赖关系 libtcl8.5.so()(64bit)，它被软件包 expect-5.44.1.15-5.el6_4.x86_64 需要
--> 执行事务检查
---> Package tcl.x86_64 1:8.5.7-6.el6 will be 安装
--> 完成依赖关系计算
依赖关系解决
=====================================================================================================================================================================================================================
软件包 架构 版本 仓库 大小
=====================================================================================================================================================================================================================
正在安装:
expect x86_64 5.44.1.15-5.el6_4 OS-YUM 256 k
为依赖而安装:
tcl x86_64 1:8.5.7-6.el6 OS-YUM 1.9 M
事务概要
=====================================================================================================================================================================================================================
Install 2 Package(s)
总下载量：2.2 M
Installed size: 4.9 M
下载软件包：
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总计 10 MB/s | 2.2 MB 00:00
运行 rpm_check_debug
执行事务测试
事务测试成功
执行事务
正在安装 : 1:tcl-8.5.7-6.el6.x86_64 1/2
正在安装 : expect-5.44.1.15-5.el6_4.x86_64 2/2
Verifying : expect-5.44.1.15-5.el6_4.x86_64 1/2
Verifying : 1:tcl-8.5.7-6.el6.x86_64 2/2
已安装:
expect.x86_64 0:5.44.1.15-5.el6_4
作为依赖被安装:
tcl.x86_64 1:8.5.7-6.el6
完毕！
所有节点 root 用户配置互信：
# 192.168.2.21 SSH-2.0-OpenSSH_5.3
no hostkey alg
# 192.168.2.21 SSH-2.0-OpenSSH_5.3
# 192.168.2.21 SSH-2.0-OpenSSH_5.3
# 192.168.2.22 SSH-2.0-OpenSSH_5.3
no hostkey alg
# 192.168.2.22 SSH-2.0-OpenSSH_5.3
# 192.168.2.22 SSH-2.0-OpenSSH_5.3
# 192.168.2.23 SSH-2.0-OpenSSH_5.3
no hostkey alg
# 192.168.2.23 SSH-2.0-OpenSSH_5.3
# 192.168.2.23 SSH-2.0-OpenSSH_5.3
spawn scp -rq /root/.ssh root@192.168.2.21:~
root@192.168.2.21's password:
spawn ssh-copy-id -i root@192.168.2.21
root@192.168.2.21's password:
Now try logging into the machine, with "ssh 'root@192.168.2.21'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
spawn scp -rq /root/.ssh root@192.168.2.22:~
root@192.168.2.22's password:
spawn ssh-copy-id -i root@192.168.2.22
root@192.168.2.22's password:
Now try logging into the machine, with "ssh 'root@192.168.2.22'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
spawn scp -rq /root/.ssh root@192.168.2.23:~
root@192.168.2.23's password:
spawn ssh-copy-id -i root@192.168.2.23
root@192.168.2.23's password:
Now try logging into the machine, with "ssh 'root@192.168.2.23'", and check in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
拷贝脚本以及安装包到节点：192.168.2.22
dwnode.sh 100% 435 0.4KB/s 00:00
DMShellInstall 100% 133KB 133.3KB/s 00:00
dm8_20221121_x86_rh6_64.iso 100% 959MB 73.8MB/s 00:13
拷贝脚本以及安装包到节点：192.168.2.23
dwnode.sh 100% 435 0.4KB/s 00:00
DMShellInstall 100% 133KB 133.3KB/s 00:00
dm8_20221121_x86_rh6_64.iso 100% 959MB 73.8MB/s 00:13
达梦数据库开始安装：
#==============================================================#
打印系统信息
#==============================================================#
服务器时间:
2022年 12月 20日 星期二 07:18:33 CST
操作系统版本:
CentOS release 6.10 (Final)
cpu信息:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 142
Model name: Intel(R) Core(TM) i7-8565U CPU @ 1.80GHz
Stepping: 12
CPU MHz: 1992.001
BogoMIPS: 3984.00
Hypervisor vendor: VMware
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 8192K
NUMA node0 CPU(s): 0
内存信息:
total used free shared buffers cached
Mem: 2870 1301 1568 0 14 1119
-/+ buffers/cache: 167 2703
Swap: 8191 0 8191
total used free shared buffers cached
Mem: 2.8G 1.3G 1.5G 264K 14M 1.1G
-/+ buffers/cache: 167M 2.6G
Swap: 8.0G 0B 8.0G
挂载信息:
UUID=cec08ef8-7698-4f29-85ed-18b15971b9dd / ext4 defaults 1 1
UUID=bbbb2b30-9dbc-4f48-a37c-fec3926d4423 /boot ext4 defaults 1 2
UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/sr0 /mnt/cdrom iso9660 defaults 0 0
目录信息:
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 91G 2.3G 84G 3% /
tmpfs 1.5G 0 1.5G 0% /dev/shm
/dev/sda1 190M 39M 142M 22% /boot
/dev/sr0 3.8G 3.8G 0 100% /mnt/cdrom
.host:/ 200G 108G 93G 54% /mnt/hgfs
#==============================================================#
关闭 SWAP 功能
#==============================================================#
total used free shared buffers cached
Mem: 2939404 1327028 1612376 264 15252 1146580
-/+ buffers/cache: 165196 2774208
Swap: 0 0 0
#UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
#==============================================================#
禁用防火墙
#==============================================================#
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则： [确定]
iptables：正在卸载模块： [确定]
ip6tables：将 chains 设置为 ACCEPT 策略：filter [确定]
ip6tables：清除防火墙规则： [确定]
：正在卸载模块： [确定]
iptables：未运行防火墙。
#==============================================================#
禁用 selinux
#==============================================================#
SELINUX=disabled
SELINUXTYPE=targeted
#==============================================================#
配置主机名
#==============================================================#
kernel.hostname = dw01
centos6
#==============================================================#
配置时间同步
#==============================================================#
2022年 12月 20日 星期二 07:18:33 CST
no crontab for root
#==============================================================#
禁用透明大页 & 禁用NUMA & 开启 I/0 schedule
#==============================================================#
index=0
kernel=/vmlinuz-2.6.32-754.el6.x86_64
args="ro rd_NO_LUKS KEYBOARDTYPE=pc KEYTABLE=us rd_NO_MD crashkernel=auto LANG=zh_CN.UTF-8 rd_NO_LVM rd_NO_DM rhgb quiet numa=off transparent_hugepage=never elevator=deadline"
#==============================================================#
创建 DMDBA 用户以及安装目录
#==============================================================#
更改用户 dmdba 的密码 。
passwd： 所有的身份验证令牌已经成功更新。
dmdba:x:54321:54321::/home/dmdba:/bin/bash
uid=54321(dmdba) gid=54321(dinstall) 组=54321(dinstall),54322(dmdba)
#==============================================================#
配置系统参数
#==============================================================#
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
error: "vm.numa_stat" is an unknown key
vm.overcommit_memory = 0
#==============================================================#
配置用户限制
#==============================================================#
dmdba - nice 0
dmdba - as unlimited
dmdba - fsize unlimited
dmdba - nproc 131072
dmdba - nofile 131072
dmdba - core unlimited
dmdba - data unlimited
root - nice 0
root - as unlimited
root - fsize unlimited
root - nproc 131072
root - nofile 131072
root - core unlimited
root - data unlimited
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth include system-auth
account required pam_nologin.so
account include system-auth
password include system-auth
session required pam_selinux.so close
session required pam_loginuid.so
session optional pam_console.so
session required pam_selinux.so open
session required pam_namespace.so
session optional pam_keyinit.so force revoke
session include system-auth
-session optional pam_ck_connector.so
session required pam_limits.so
session required /lib64/security/pam_limits.so
#==============================================================#
配置语言中文
#==============================================================#
export LANG=zh_CN.UTF-8
#==============================================================#
配置用户环境变量
#==============================================================#
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export MALLOC_ARENA_MAX=1
export DM_HOME=/opt/dmdbms
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
export PS1="[`whoami`@`hostname`:"'\w]$ '
alias ds='disql SYSDBA/SYSDBA:5236 '
alias dsql='disql SYSDBA/SYSDBA:5236 \`'
alias dssql='disql -S SYSDBA/SYSDBA:5236 \`'
alias dmlog='cd $DM_HOME/log'
#==============================================================#
挂载达梦安装镜像
#==============================================================#
总用量 959M
-r-xr-xr-x. 1 root root 2.7M 11月 21 16:25 DM8 Install.pdf
-r-xr-xr-x. 1 root root 957M 11月 21 16:27 DMInstall.bin
#==============================================================#
安装达梦数据库软件
#==============================================================#
解压安装程序.........
2022-12-20 07:19:00
[INFO] 安装达梦数据库...
2022-12-20 07:19:00
[INFO] 安装 基础 模块...
2022-12-20 07:19:04
[INFO] 安装 服务器 模块...
2022-12-20 07:19:05
[INFO] 安装 客户端 模块...
2022-12-20 07:19:08
[INFO] 安装 驱动 模块...
2022-12-20 07:19:09
[INFO] 安装 手册 模块...
2022-12-20 07:19:10
[INFO] 安装 服务 模块...
2022-12-20 07:19:11
[INFO] 移动日志文件。
2022-12-20 07:19:11
[INFO] 更改安装目录权限完成。
2022-12-20 07:19:12
[INFO] 正在启动DmAPService服务...
2022-12-20 07:19:27
[INFO] 启动DmAPService服务成功。
2022-12-20 07:19:27
[INFO] 安装达梦数据库完成。
#==============================================================#
初始化达梦数据库
#==============================================================#
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-11-21
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dmdata/DAMENG/DAMENG01.log
log file path: /dmdata/DAMENG/DAMENG02.log
write to dir [/dmdata/DAMENG].
create dm database success. 2022-12-20 07:19:34
#==============================================================#
注册实例服务
#==============================================================#
移动服务脚本文件(/opt/dmdbms/bin/DmServiceDAMENG 到 /etc/rc.d/init.d/DmServiceDAMENG)
创建服务(DmServiceDAMENG)完成
#==============================================================#
启停数据库
#==============================================================#
后台启动数据库：
Starting DmServiceDAMENG: [ OK ]
DmServiceDAMENG (pid 5687) is running.
查询数据库基础参数信息：
密钥过期时间：2023-11-21
数据库参数项 数据库参数值
------------------------------- -----------------------------------
实例名 GRP1_DW_01
DM Database Server x64 V8 --03134283950-20221121-175072-20024
簇大小 32
页大小 32
大小写敏感 1
字符集 1
varchar是否以字符为单位 0
#==============================================================#
创建归档和备份脚本
#==============================================================#
创建数据库归档脚本：
-rw-r--r--. 1 dmdba dinstall 378 12月 20 07:20 /home/dmdba/scripts/conf_arch.sql
创建数据库备份脚本：
-rw-r--r--. 1 dmdba dinstall 780 12月 20 07:20 /home/dmdba/scripts/conf_fullbackup.sql
-rw-r--r--. 1 dmdba dinstall 1.5K 12月 20 07:20 /home/dmdba/scripts/conf_incrbackup.sql
创建 DMDBA 用户脚本，密码 SYSDBA ：
-rw-r--r--. 1 dmdba dinstall 696 12月 20 07:20 /home/dmdba/scripts/ct_dbuser.sql
#==============================================================#
创建达梦数据库优化脚本
#==============================================================#
创建数据库参数配置脚本：
-rw-r--r--. 1 dmdba dinstall 14K 12月 20 07:20 /home/dmdba/scripts/conf_para.sql
创建数据库优化结果查询脚本：
-rw-r--r--. 1 dmdba dinstall 2.2K 12月 20 07:20 /home/dmdba/scripts/query_dm.sql
#==============================================================#
在 192.168.2.21 创建备份
#==============================================================#
密钥过期时间：2023-11-21
NAME DESCRIBE
-------- -------------------------------------------------------------------------------
bak_full 周六全量备份，并删除30天之前的备份。
bak_inc 周日到周五做增量备份,如果失败,清除8天前备份,做全量备份
NAME COMMAND
--------- ---------------------------------------------------------------------------------------------------------
bak_clear CALL SF_BAKSET_BACKUP_DIR_ADD('DISK','/dmbak/DAMENG'); CALL SP_DB_BAKSET_REMOVE_BATCH('DISK',SYSDATE-30);
#==============================================================#
优化数据库基础参数
#==============================================================#
密钥过期时间：2023-11-21
修改cpu相关参数:
SP_SET_PARA_VALUE(2,'WORKER_THREADS',1);
SP_SET_PARA_VALUE(2,'TASK_THREADS',4);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',2);
修改内存池相关参数:
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY',80);
SP_SET_PARA_VALUE(2,'MEMORY_POOL',100);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS',1);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET',0);
修改缓冲区相关参数:
SP_SET_PARA_VALUE(2,'BUFFER',1000);
SP_SET_PARA_VALUE(2,'MAX_BUFFER',1000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS',3);
SP_SET_PARA_VALUE(2,'RECYCLE',80);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS',2);
修改fast_pool相关参数:
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES',3000);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES',1000);
修改内存检测参数为1:
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK',1);
非DSC环境将ENABLE_FREQROOTS设置为1,注意DM7 V$instance视图没有dsc_role字段,DM7这部分可以删掉:
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
修改HASH相关参数:
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE',50);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE',50);
修改排序相关参数:
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE',10);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE',500);
修改其他内存参数:
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE',256);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE',200);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE',50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET',16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET',16384);
修改实例相关参数:
SP_SET_PARA_VALUE(2,'USE_PLN_POOL',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(2,'TEMP_SIZE',1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT',102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS',1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT',20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER',0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
修改优化器相关参数:
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
开启并行PURGE:
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
开启手动并行:
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作:
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
开启SQL 注入HINT功能:
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
开启数据异步追踪:
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
开启操作系统认证:
sp_set_para_value(2,'ENABLE_LOCAL_OSAUTH',1);
重启数据库，优化参数生效
Stopping DmServiceDAMENG: [ OK ]
Starting DmServiceDAMENG: [ OK ]
#==============================================================#
创建DMDBA用户，密码：SYSDBA
#==============================================================#
密钥过期时间：2023-11-21
USERNAME
----------
SYSAUDITOR
SYSSSO
SYSDBA
DMDBA
SYS
#==============================================================#
查询数据库优化结果：
#==============================================================#
密钥过期时间：2023-11-21
PARA_NAME DEFAULT_VALUE PARA_VALUE
--------------------- ------------- ----------
MAX_OS_MEMORY 100 80
MAX_SESSION_STATEMENT 10000 20000
TRX_VIEW_MODE 1 1
MAX_SESSIONS 10000 1500
IO_THR_GROUPS 8 2
ADAPTIVE_NPLN_FLAG 3 0
OPTIMIZER_MODE 1 1
TOP_DIS_HASH_FLAG 1 1
TOP_ORDER_OPT_FLAG 0 0
VIEW_PULLUP_FLAG 0 1
USE_PLN_POOL 1 1
TASK_THREADS 16 4
WORKER_THREADS 16 1
SESS_POOL_TARGET 16384 16384
SESS_POOL_SIZE 64 64
VM_POOL_TARGET 16384 16384
DICT_BUF_SIZE 50 50
HAGR_BUF_SIZE 500 50
HAGR_BUF_GLOBAL_SIZE 5000 1000
HJ_BUF_SIZE 500 50
HJ_BUF_GLOBAL_SIZE 5000 1000
SORT_FLAG 0 0
SORT_BUF_GLOBAL_SIZE 1000 500
SORT_BLK_SIZE 1 1
SORT_BUF_SIZE 20 10
MAX_BUFFER 1000 1000
RECYCLE_POOLS 3 1
RECYCLE 300 80
BUFFER_POOLS 9 3
BUFFER 1000 1000
MEMORY_MAGIC_CHECK 1 1
MEMORY_TARGET 15000 0
MEMORY_N_POOLS 1 1
MEMORY_POOL 500 100
PK_WITH_CLUSTER 0 0
ENABLE_MONITOR 1 1
SVR_LOG 0 1
DATETIME_FMT_MODE 0 0
COMPATIBLE_MODE 0 0
CLOB_LIKE_MAX_LEN 10240 10240
ENABLE_ENCRYPT 0 0
REDOS_PARALLEL_NUM 1 1
RLOG_POOL_SIZE 256 256
CACHE_POOL_SIZE 100 200
TEMP_SPACE_LIMIT 0 102400
TEMP_SIZE 10 1024
OLAP_FLAG 2 2
#==============================================================#
数据守护主库脱机备份
#==============================================================#
正常关闭主数据库：
Stopping DmServiceDAMENG: [ OK ]
主库进行脱机 dmrman 备份：
dmrman V8
BACKUP DATABASE '/dmdata/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dmbak/BACKUP_FILE_01'
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[39191], file_lsn[39191]
Processing backupset /dmbak/BACKUP_FILE_01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
backup successfully!
time used: 00:00:03.227
主库查看脱机备份文件：
总用量 22604
drwxr-xr-x. 2 dmdba dinstall 4096 12月 20 07:20 .
drwxrwxr-x. 4 dmdba dinstall 4096 12月 20 07:20 ..
-rw-r--r--. 1 dmdba dinstall 23042048 12月 20 07:20 BACKUP_FILE_01.bak
-rw-r--r--. 1 dmdba dinstall 91648 12月 20 07:20 BACKUP_FILE_01.meta
#==============================================================#
配置节点：192.168.2.22
#==============================================================#
节点 192.168.2.22 开始执行配置：
███████ ████ ████ ████████ ██ ██ ██ ██ ██ ██ ██
░██░░░░██ ░██░██ ██░██ ██░░░░░░ ░██ ░██ ░██░██ ░██ ░██ ░██
░██ ░██░██░░██ ██ ░██░██ ░██ █████ ░██ ░██░██ ███████ ██████ ██████ ██████ ░██ ░██
░██ ░██░██ ░░███ ░██░█████████░██████ ██░░░██ ░██ ░██░██░░██░░░██ ██░░░░ ░░░██░ ░░░░░░██ ░██ ░██
░██ ░██░██ ░░█ ░██░░░░░░░░██░██░░░██░███████ ░██ ░██░██ ░██ ░██░░█████ ░██ ███████ ░██ ░██
░██ ██ ░██ ░ ░██ ░██░██ ░██░██░░░░ ░██ ░██░██ ░██ ░██ ░░░░░██ ░██ ██░░░░██ ░██ ░██
░███████ ░██ ░██ ████████ ░██ ░██░░██████ ███ ███░██ ███ ░██ ██████ ░░██ ░░████████ ███ ███
░░░░░░░ ░░ ░░ ░░░░░░░░ ░░ ░░ ░░░░░░ ░░░ ░░░ ░░ ░░░ ░░ ░░░░░░ ░░ ░░░░░░░░ ░░░ ░░░
达梦数据库开始安装：
#==============================================================#
打印系统信息
#==============================================================#
服务器时间:
2022年 12月 20日 星期二 07:23:22 CST
操作系统版本:
CentOS release 6.10 (Final)
cpu信息:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 142
Model name: Intel(R) Core(TM) i7-8565U CPU @ 1.80GHz
Stepping: 12
CPU MHz: 1992.001
BogoMIPS: 3984.00
Hypervisor vendor: VMware
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 8192K
NUMA node0 CPU(s): 0
内存信息:
total used free shared buffers cached
Mem: 2870 1238 1632 0 9 1038
-/+ buffers/cache: 189 2680
Swap: 8191 0 8191
total used free shared buffers cached
Mem: 2.8G 1.2G 1.6G 260K 9.9M 1.0G
-/+ buffers/cache: 189M 2.6G
Swap: 8.0G 0B 8.0G
挂载信息:
UUID=cec08ef8-7698-4f29-85ed-18b15971b9dd / ext4 defaults 1 1
UUID=bbbb2b30-9dbc-4f48-a37c-fec3926d4423 /boot ext4 defaults 1 2
UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/sr0 /mnt/cdrom iso9660 defaults 0 0
目录信息:
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 91G 2.3G 84G 3% /
tmpfs 1.5G 0 1.5G 0% /dev/shm
/dev/sda1 190M 39M 142M 22% /boot
/dev/sr0 3.8G 3.8G 0 100% /mnt/cdrom
.host:/ 200G 108G 93G 54% /mnt/hgfs
#==============================================================#
关闭 SWAP 功能
#==============================================================#
total used free shared buffers cached
Mem: 2939404 1261844 1677560 260 10132 1063796
-/+ buffers/cache: 187916 2751488
Swap: 0 0 0
#UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
#==============================================================#
禁用防火墙
#==============================================================#
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则： [确定]
iptables：正在卸载模块： [确定]
ip6tables：将 chains 设置为 ACCEPT 策略：filter [确定]
ip6tables：清除防火墙规则： [确定]
：正在卸载模块： [确定]
iptables：未运行防火墙。
#==============================================================#
禁用 selinux
#==============================================================#
SELINUX=disabled
SELINUXTYPE=targeted
#==============================================================#
配置主机名
#==============================================================#
kernel.hostname = dw02
centos6
#==============================================================#
配置时间同步
#==============================================================#
2022年 12月 20日 星期二 07:23:22 CST
no crontab for root
#==============================================================#
禁用透明大页 & 禁用NUMA & 开启 I/0 schedule
#==============================================================#
index=0
kernel=/vmlinuz-2.6.32-754.el6.x86_64
args="ro rd_NO_LUKS KEYBOARDTYPE=pc KEYTABLE=us rd_NO_MD crashkernel=auto LANG=zh_CN.UTF-8 rd_NO_LVM rd_NO_DM rhgb quiet numa=off transparent_hugepage=never elevator=deadline"
#==============================================================#
创建 DMDBA 用户以及安装目录
#==============================================================#
更改用户 dmdba 的密码 。
passwd： 所有的身份验证令牌已经成功更新。
dmdba:x:54321:54321::/home/dmdba:/bin/bash
uid=54321(dmdba) gid=54321(dinstall) 组=54321(dinstall),54322(dmdba)
#==============================================================#
配置系统参数
#==============================================================#
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
error: "vm.numa_stat" is an unknown key
vm.overcommit_memory = 0
#==============================================================#
配置用户限制
#==============================================================#
dmdba - nice 0
dmdba - as unlimited
dmdba - fsize unlimited
dmdba - nproc 131072
dmdba - nofile 131072
dmdba - core unlimited
dmdba - data unlimited
root - nice 0
root - as unlimited
root - fsize unlimited
root - nproc 131072
root - nofile 131072
root - core unlimited
root - data unlimited
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth include system-auth
account required pam_nologin.so
account include system-auth
password include system-auth
session required pam_selinux.so close
session required pam_loginuid.so
session optional pam_console.so
session required pam_selinux.so open
session required pam_namespace.so
session optional pam_keyinit.so force revoke
session include system-auth
-session optional pam_ck_connector.so
session required pam_limits.so
session required /lib64/security/pam_limits.so
#==============================================================#
配置语言中文
#==============================================================#
export LANG=zh_CN.UTF-8
#==============================================================#
配置用户环境变量
#==============================================================#
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export MALLOC_ARENA_MAX=1
export DM_HOME=/opt/dmdbms
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
export PS1="[`whoami`@`hostname`:"'\w]$ '
alias ds='disql SYSDBA/SYSDBA:5236 '
alias dsql='disql SYSDBA/SYSDBA:5236 \`'
alias dssql='disql -S SYSDBA/SYSDBA:5236 \`'
alias dmlog='cd $DM_HOME/log'
#==============================================================#
挂载达梦安装镜像
#==============================================================#
总用量 959M
-r-xr-xr-x. 1 root root 2.7M 11月 21 16:25 DM8 Install.pdf
-r-xr-xr-x. 1 root root 957M 11月 21 16:27 DMInstall.bin
#==============================================================#
安装达梦数据库软件
#==============================================================#
解压安装程序.........
2022-12-20 07:23:48
[INFO] 安装达梦数据库...
2022-12-20 07:23:48
[INFO] 安装 基础 模块...
2022-12-20 07:23:53
[INFO] 安装 服务器 模块...
2022-12-20 07:23:53
[INFO] 安装 客户端 模块...
2022-12-20 07:23:56
[INFO] 安装 驱动 模块...
2022-12-20 07:23:58
[INFO] 安装 手册 模块...
2022-12-20 07:23:58
[INFO] 安装 服务 模块...
2022-12-20 07:24:00
[INFO] 移动日志文件。
2022-12-20 07:24:00
[INFO] 更改安装目录权限完成。
2022-12-20 07:24:00
[INFO] 正在启动DmAPService服务...
2022-12-20 07:24:16
[INFO] 启动DmAPService服务成功。
2022-12-20 07:24:16
[INFO] 安装达梦数据库完成。
#==============================================================#
初始化达梦数据库
#==============================================================#
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-11-21
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dmdata/DAMENG/DAMENG01.log
log file path: /dmdata/DAMENG/DAMENG02.log
write to dir [/dmdata/DAMENG].
create dm database success. 2022-12-20 07:24:22
#==============================================================#
注册实例服务
#==============================================================#
移动服务脚本文件(/opt/dmdbms/bin/DmServiceDAMENG 到 /etc/rc.d/init.d/DmServiceDAMENG)
创建服务(DmServiceDAMENG)完成
#==============================================================#
启停数据库
#==============================================================#
后台启动数据库：
Starting DmServiceDAMENG: [ OK ]
DmServiceDAMENG (pid 5502) is running.
查询数据库基础参数信息：
密钥过期时间：2023-11-21
数据库参数项 数据库参数值
------------------------------- -----------------------------------
实例名 GRP1_DW_02
DM Database Server x64 V8 --03134283950-20221121-175072-20024
簇大小 32
页大小 32
大小写敏感 1
字符集 1
varchar是否以字符为单位 0
#==============================================================#
创建归档和备份脚本
#==============================================================#
创建数据库归档脚本：
-rw-r--r--. 1 dmdba dinstall 378 12月 20 07:24 /home/dmdba/scripts/conf_arch.sql
创建数据库备份脚本：
-rw-r--r--. 1 dmdba dinstall 780 12月 20 07:24 /home/dmdba/scripts/conf_fullbackup.sql
-rw-r--r--. 1 dmdba dinstall 1.5K 12月 20 07:24 /home/dmdba/scripts/conf_incrbackup.sql
创建 DMDBA 用户脚本，密码 SYSDBA ：
-rw-r--r--. 1 dmdba dinstall 696 12月 20 07:24 /home/dmdba/scripts/ct_dbuser.sql
#==============================================================#
创建达梦数据库优化脚本
#==============================================================#
创建数据库参数配置脚本：
-rw-r--r--. 1 dmdba dinstall 14K 12月 20 07:24 /home/dmdba/scripts/conf_para.sql
创建数据库优化结果查询脚本：
-rw-r--r--. 1 dmdba dinstall 2.2K 12月 20 07:24 /home/dmdba/scripts/query_dm.sql
#==============================================================#
优化数据库基础参数
#==============================================================#
密钥过期时间：2023-11-21
修改cpu相关参数:
SP_SET_PARA_VALUE(2,'WORKER_THREADS',1);
SP_SET_PARA_VALUE(2,'TASK_THREADS',4);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',2);
修改内存池相关参数:
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY',80);
SP_SET_PARA_VALUE(2,'MEMORY_POOL',100);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS',1);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET',0);
修改缓冲区相关参数:
SP_SET_PARA_VALUE(2,'BUFFER',1000);
SP_SET_PARA_VALUE(2,'MAX_BUFFER',1000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS',3);
SP_SET_PARA_VALUE(2,'RECYCLE',80);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS',2);
修改fast_pool相关参数:
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES',3000);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES',1000);
修改内存检测参数为1:
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK',1);
非DSC环境将ENABLE_FREQROOTS设置为1,注意DM7 V$instance视图没有dsc_role字段,DM7这部分可以删掉:
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
修改HASH相关参数:
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE',50);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE',50);
修改排序相关参数:
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE',10);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE',500);
修改其他内存参数:
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE',256);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE',200);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE',50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET',16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET',16384);
修改实例相关参数:
SP_SET_PARA_VALUE(2,'USE_PLN_POOL',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(2,'TEMP_SIZE',1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT',102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS',1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT',20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER',0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
修改优化器相关参数:
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
开启并行PURGE:
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
开启手动并行:
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作:
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
开启SQL 注入HINT功能:
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
开启数据异步追踪:
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
开启操作系统认证:
sp_set_para_value(2,'ENABLE_LOCAL_OSAUTH',1);
重启数据库，优化参数生效
Stopping DmServiceDAMENG: [ OK ]
Starting DmServiceDAMENG: [ OK ]
#==============================================================#
查询数据库优化结果：
#==============================================================#
密钥过期时间：2023-11-21
PARA_NAME DEFAULT_VALUE PARA_VALUE
--------------------- ------------- ----------
MAX_OS_MEMORY 100 80
MAX_SESSION_STATEMENT 10000 20000
TRX_VIEW_MODE 1 1
MAX_SESSIONS 10000 1500
IO_THR_GROUPS 8 2
ADAPTIVE_NPLN_FLAG 3 0
OPTIMIZER_MODE 1 1
TOP_DIS_HASH_FLAG 1 1
TOP_ORDER_OPT_FLAG 0 0
VIEW_PULLUP_FLAG 0 1
USE_PLN_POOL 1 1
TASK_THREADS 16 4
WORKER_THREADS 16 1
SESS_POOL_TARGET 16384 16384
SESS_POOL_SIZE 64 64
VM_POOL_TARGET 16384 16384
DICT_BUF_SIZE 50 50
HAGR_BUF_SIZE 500 50
HAGR_BUF_GLOBAL_SIZE 5000 1000
HJ_BUF_SIZE 500 50
HJ_BUF_GLOBAL_SIZE 5000 1000
SORT_FLAG 0 0
SORT_BUF_GLOBAL_SIZE 1000 500
SORT_BLK_SIZE 1 1
SORT_BUF_SIZE 20 10
MAX_BUFFER 1000 1000
RECYCLE_POOLS 3 1
RECYCLE 300 80
BUFFER_POOLS 9 3
BUFFER 1000 1000
MEMORY_MAGIC_CHECK 1 1
MEMORY_TARGET 15000 0
MEMORY_N_POOLS 1 1
MEMORY_POOL 500 100
PK_WITH_CLUSTER 0 0
ENABLE_MONITOR 1 1
SVR_LOG 0 1
DATETIME_FMT_MODE 0 0
COMPATIBLE_MODE 0 0
CLOB_LIKE_MAX_LEN 10240 10240
ENABLE_ENCRYPT 0 0
REDOS_PARALLEL_NUM 1 1
RLOG_POOL_SIZE 256 256
CACHE_POOL_SIZE 100 200
TEMP_SPACE_LIMIT 0 102400
TEMP_SIZE 10 1024
OLAP_FLAG 2 2
#==============================================================#
数据守护备库脱机恢复
#==============================================================#
正常关闭备数据库：
Stopping DmServiceDAMENG: [ OK ]
拷贝主库的备份文件并授权：
BACKUP_FILE_01.meta 100% 90KB 89.5KB/s 00:00
BACKUP_FILE_01.bak 100% 22MB 22.0MB/s 00:01
备库执行脱机数据库 dmrman 还原：
dmrman V8
RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM BACKUPSET '/dmbak/BACKUP_FILE_01'
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:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.646
备库执行 dmrman 数据库更新：
dmrman V8
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[39191], file_lsn[39191]
recover successfully!
time used: 00:00:01.008
#==============================================================#
数据守护主备配置参数文件
#==============================================================#
备库配置 dm.ini 参数：
ALTER_MODE_STATUS = 1 #Whether to permit database user to alter database mode and status by SQLs, 1: yes, 0: no
ENABLE_OFFLINE_TS = 2 #Whether tablespace can be offline
MAL_INI = 1 #dmmal.ini
ARCH_INI = 1 #dmarch.ini
备库配置 dmmal.ini 参数：
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_DW_01
MAL_HOST = 172.16.2.21
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.21
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST2]
MAL_INST_NAME = GRP1_DW_02
MAL_HOST = 172.16.2.22
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.22
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST3]
MAL_INST_NAME = GRP1_DW_03
MAL_HOST = 172.16.2.23
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.23
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
备库配置 dmarch.ini 参数：
ARCH_WAIT_APPLY = 1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dmarch/DAMENG
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 102400
[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_01
[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_03
备库配置 dmwatcher.ini 参数：
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_ERROR_TIME = 10
INST_OGUID = 496433
INST_INI = /dmdata/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /opt/dmdbms/bin/DmServiceDAMENG start
备库配置 dmmonitor.ini 参数：
MON_DW_CONFIRM = 0
MON_LOG_PATH = /opt/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 2048
[GRP1]
MON_INST_OGUID = 496433
MON_DW_IP = 172.16.2.21:12346
MON_DW_IP = 172.16.2.22:12346
MON_DW_IP = 172.16.2.23:12346
备库注册 dmwatcher 服务：
移动服务脚本文件(/opt/dmdbms/bin/DmWatcherServiceGRP1 到 /etc/rc.d/init.d/DmWatcherServiceGRP1)
创建服务(DmWatcherServiceGRP1)完成
备库启动到 mount 状态：
Starting DmServiceDAMENG: [ OK ]
备库修改模式：
密钥过期时间：2023-11-21
备库启动守护进程：
Starting DmWatcherServiceGRP1: [ OK ]
Connection to 192.168.2.22 closed.
节点 192.168.2.22 配置完成！
#==============================================================#
配置节点：192.168.2.23
#==============================================================#
节点 192.168.2.23 开始执行配置：
███████ ████ ████ ████████ ██ ██ ██ ██ ██ ██ ██
░██░░░░██ ░██░██ ██░██ ██░░░░░░ ░██ ░██ ░██░██ ░██ ░██ ░██
░██ ░██░██░░██ ██ ░██░██ ░██ █████ ░██ ░██░██ ███████ ██████ ██████ ██████ ░██ ░██
░██ ░██░██ ░░███ ░██░█████████░██████ ██░░░██ ░██ ░██░██░░██░░░██ ██░░░░ ░░░██░ ░░░░░░██ ░██ ░██
░██ ░██░██ ░░█ ░██░░░░░░░░██░██░░░██░███████ ░██ ░██░██ ░██ ░██░░█████ ░██ ███████ ░██ ░██
░██ ██ ░██ ░ ░██ ░██░██ ░██░██░░░░ ░██ ░██░██ ░██ ░██ ░░░░░██ ░██ ██░░░░██ ░██ ░██
░███████ ░██ ░██ ████████ ░██ ░██░░██████ ███ ███░██ ███ ░██ ██████ ░░██ ░░████████ ███ ███
░░░░░░░ ░░ ░░ ░░░░░░░░ ░░ ░░ ░░░░░░ ░░░ ░░░ ░░ ░░░ ░░ ░░░░░░ ░░ ░░░░░░░░ ░░░ ░░░
达梦数据库开始安装：
#==============================================================#
打印系统信息
#==============================================================#
服务器时间:
2022年 12月 20日 星期二 07:26:17 CST
操作系统版本:
CentOS release 6.10 (Final)
cpu信息:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 1
On-line CPU(s) list: 0
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 142
Model name: Intel(R) Core(TM) i7-8565U CPU @ 1.80GHz
Stepping: 12
CPU MHz: 1992.001
BogoMIPS: 3984.00
Hypervisor vendor: VMware
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 8192K
NUMA node0 CPU(s): 0
内存信息:
total used free shared buffers cached
Mem: 2870 1238 1632 0 9 1038
-/+ buffers/cache: 189 2681
Swap: 8191 0 8191
total used free shared buffers cached
Mem: 2.8G 1.2G 1.6G 260K 9.9M 1.0G
-/+ buffers/cache: 189M 2.6G
Swap: 8.0G 0B 8.0G
挂载信息:
UUID=cec08ef8-7698-4f29-85ed-18b15971b9dd / ext4 defaults 1 1
UUID=bbbb2b30-9dbc-4f48-a37c-fec3926d4423 /boot ext4 defaults 1 2
UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/sr0 /mnt/cdrom iso9660 defaults 0 0
目录信息:
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 91G 2.3G 84G 3% /
tmpfs 1.5G 0 1.5G 0% /dev/shm
/dev/sda1 190M 39M 142M 22% /boot
/dev/sr0 3.8G 3.8G 0 100% /mnt/cdrom
.host:/ 200G 108G 93G 54% /mnt/hgfs
#==============================================================#
关闭 SWAP 功能
#==============================================================#
total used free shared buffers cached
Mem: 2939404 1261512 1677892 260 10164 1063792
-/+ buffers/cache: 187556 2751848
Swap: 0 0 0
#UUID=84e57345-d09b-44e5-8240-2becf00e7c15 swap swap defaults 0 0
#==============================================================#
禁用防火墙
#==============================================================#
iptables：将链设置为政策 ACCEPT：filter [确定]
iptables：清除防火墙规则： [确定]
iptables：正在卸载模块： [确定]
ip6tables：将 chains 设置为 ACCEPT 策略：filter [确定]
ip6tables：清除防火墙规则： [确定]
：正在卸载模块： [确定]
iptables：未运行防火墙。
#==============================================================#
禁用 selinux
#==============================================================#
SELINUX=disabled
SELINUXTYPE=targeted
#==============================================================#
配置主机名
#==============================================================#
kernel.hostname = dw03
centos6
#==============================================================#
配置时间同步
#==============================================================#
2022年 12月 20日 星期二 07:26:17 CST
no crontab for root
#==============================================================#
禁用透明大页 & 禁用NUMA & 开启 I/0 schedule
#==============================================================#
index=0
kernel=/vmlinuz-2.6.32-754.el6.x86_64
args="ro rd_NO_LUKS KEYBOARDTYPE=pc KEYTABLE=us rd_NO_MD crashkernel=auto LANG=zh_CN.UTF-8 rd_NO_LVM rd_NO_DM rhgb quiet numa=off transparent_hugepage=never elevator=deadline"
#==============================================================#
创建 DMDBA 用户以及安装目录
#==============================================================#
更改用户 dmdba 的密码 。
passwd： 所有的身份验证令牌已经成功更新。
dmdba:x:54321:54321::/home/dmdba:/bin/bash
uid=54321(dmdba) gid=54321(dinstall) 组=54321(dinstall),54322(dmdba)
#==============================================================#
配置系统参数
#==============================================================#
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
error: "vm.numa_stat" is an unknown key
vm.overcommit_memory = 0
#==============================================================#
配置用户限制
#==============================================================#
dmdba - nice 0
dmdba - as unlimited
dmdba - fsize unlimited
dmdba - nproc 131072
dmdba - nofile 131072
dmdba - core unlimited
dmdba - data unlimited
root - nice 0
root - as unlimited
root - fsize unlimited
root - nproc 131072
root - nofile 131072
root - core unlimited
root - data unlimited
auth [user_unknown=ignore success=ok ignore=ignore default=bad] pam_securetty.so
auth include system-auth
account required pam_nologin.so
account include system-auth
password include system-auth
session required pam_selinux.so close
session required pam_loginuid.so
session optional pam_console.so
session required pam_selinux.so open
session required pam_namespace.so
session optional pam_keyinit.so force revoke
session include system-auth
-session optional pam_ck_connector.so
session required pam_limits.so
session required /lib64/security/pam_limits.so
#==============================================================#
配置语言中文
#==============================================================#
export LANG=zh_CN.UTF-8
#==============================================================#
配置用户环境变量
#==============================================================#
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export MALLOC_ARENA_MAX=1
export DM_HOME=/opt/dmdbms
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
export PS1="[`whoami`@`hostname`:"'\w]$ '
alias ds='disql SYSDBA/SYSDBA:5236 '
alias dsql='disql SYSDBA/SYSDBA:5236 \`'
alias dssql='disql -S SYSDBA/SYSDBA:5236 \`'
alias dmlog='cd $DM_HOME/log'
#==============================================================#
挂载达梦安装镜像
#==============================================================#
总用量 959M
-r-xr-xr-x. 1 root root 2.7M 11月 21 16:25 DM8 Install.pdf
-r-xr-xr-x. 1 root root 957M 11月 21 16:27 DMInstall.bin
#==============================================================#
安装达梦数据库软件
#==============================================================#
解压安装程序.........
2022-12-20 07:26:43
[INFO] 安装达梦数据库...
2022-12-20 07:26:44
[INFO] 安装 基础 模块...
2022-12-20 07:26:48
[INFO] 安装 服务器 模块...
2022-12-20 07:26:48
[INFO] 安装 客户端 模块...
2022-12-20 07:26:52
[INFO] 安装 驱动 模块...
2022-12-20 07:26:54
[INFO] 安装 手册 模块...
2022-12-20 07:26:54
[INFO] 安装 服务 模块...
2022-12-20 07:26:56
[INFO] 移动日志文件。
2022-12-20 07:26:56
[INFO] 更改安装目录权限完成。
2022-12-20 07:26:56
[INFO] 正在启动DmAPService服务...
2022-12-20 07:27:12
[INFO] 启动DmAPService服务成功。
2022-12-20 07:27:12
[INFO] 安装达梦数据库完成。
#==============================================================#
初始化达梦数据库
#==============================================================#
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-11-21
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dmdata/DAMENG/DAMENG01.log
log file path: /dmdata/DAMENG/DAMENG02.log
write to dir [/dmdata/DAMENG].
create dm database success. 2022-12-20 07:27:17
#==============================================================#
注册实例服务
#==============================================================#
移动服务脚本文件(/opt/dmdbms/bin/DmServiceDAMENG 到 /etc/rc.d/init.d/DmServiceDAMENG)
创建服务(DmServiceDAMENG)完成
#==============================================================#
启停数据库
#==============================================================#
后台启动数据库：
Starting DmServiceDAMENG: [ OK ]
DmServiceDAMENG (pid 5500) is running.
查询数据库基础参数信息：
密钥过期时间：2023-11-21
数据库参数项 数据库参数值
------------------------------- -----------------------------------
实例名 GRP1_DW_03
DM Database Server x64 V8 --03134283950-20221121-175072-20024
簇大小 32
页大小 32
大小写敏感 1
字符集 1
varchar是否以字符为单位 0
#==============================================================#
创建归档和备份脚本
#==============================================================#
创建数据库归档脚本：
-rw-r--r--. 1 dmdba dinstall 378 12月 20 07:27 /home/dmdba/scripts/conf_arch.sql
创建数据库备份脚本：
-rw-r--r--. 1 dmdba dinstall 780 12月 20 07:27 /home/dmdba/scripts/conf_fullbackup.sql
-rw-r--r--. 1 dmdba dinstall 1.5K 12月 20 07:27 /home/dmdba/scripts/conf_incrbackup.sql
创建 DMDBA 用户脚本，密码 SYSDBA ：
-rw-r--r--. 1 dmdba dinstall 696 12月 20 07:27 /home/dmdba/scripts/ct_dbuser.sql
#==============================================================#
创建达梦数据库优化脚本
#==============================================================#
创建数据库参数配置脚本：
-rw-r--r--. 1 dmdba dinstall 14K 12月 20 07:27 /home/dmdba/scripts/conf_para.sql
创建数据库优化结果查询脚本：
-rw-r--r--. 1 dmdba dinstall 2.2K 12月 20 07:27 /home/dmdba/scripts/query_dm.sql
#==============================================================#
优化数据库基础参数
#==============================================================#
密钥过期时间：2023-11-21
修改cpu相关参数:
SP_SET_PARA_VALUE(2,'WORKER_THREADS',1);
SP_SET_PARA_VALUE(2,'TASK_THREADS',4);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',2);
修改内存池相关参数:
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY',80);
SP_SET_PARA_VALUE(2,'MEMORY_POOL',100);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS',1);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET',0);
修改缓冲区相关参数:
SP_SET_PARA_VALUE(2,'BUFFER',1000);
SP_SET_PARA_VALUE(2,'MAX_BUFFER',1000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS',3);
SP_SET_PARA_VALUE(2,'RECYCLE',80);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS',2);
修改fast_pool相关参数:
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES',3000);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES',1000);
修改内存检测参数为1:
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK',1);
非DSC环境将ENABLE_FREQROOTS设置为1,注意DM7 V$instance视图没有dsc_role字段,DM7这部分可以删掉:
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
修改HASH相关参数:
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE',50);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',500);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE',50);
修改排序相关参数:
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE',10);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE',500);
修改其他内存参数:
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE',256);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE',200);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE',50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET',16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET',16384);
修改实例相关参数:
SP_SET_PARA_VALUE(2,'USE_PLN_POOL',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(2,'TEMP_SIZE',1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT',102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS',1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT',20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER',0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
修改优化器相关参数:
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
开启并行PURGE:
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
开启手动并行:
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作:
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
开启SQL 注入HINT功能:
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
开启数据异步追踪:
SP_SET_PARA_VALUE(1,'SVR_LOG',1);
开启操作系统认证:
sp_set_para_value(2,'ENABLE_LOCAL_OSAUTH',1);
重启数据库，优化参数生效
Stopping DmServiceDAMENG: [ OK ]
Starting DmServiceDAMENG: [ OK ]
#==============================================================#
查询数据库优化结果：
#==============================================================#
密钥过期时间：2023-11-21
PARA_NAME DEFAULT_VALUE PARA_VALUE
--------------------- ------------- ----------
MAX_OS_MEMORY 100 80
MAX_SESSION_STATEMENT 10000 20000
TRX_VIEW_MODE 1 1
MAX_SESSIONS 10000 1500
IO_THR_GROUPS 8 2
ADAPTIVE_NPLN_FLAG 3 0
OPTIMIZER_MODE 1 1
TOP_DIS_HASH_FLAG 1 1
TOP_ORDER_OPT_FLAG 0 0
VIEW_PULLUP_FLAG 0 1
USE_PLN_POOL 1 1
TASK_THREADS 16 4
WORKER_THREADS 16 1
SESS_POOL_TARGET 16384 16384
SESS_POOL_SIZE 64 64
VM_POOL_TARGET 16384 16384
DICT_BUF_SIZE 50 50
HAGR_BUF_SIZE 500 50
HAGR_BUF_GLOBAL_SIZE 5000 1000
HJ_BUF_SIZE 500 50
HJ_BUF_GLOBAL_SIZE 5000 1000
SORT_FLAG 0 0
SORT_BUF_GLOBAL_SIZE 1000 500
SORT_BLK_SIZE 1 1
SORT_BUF_SIZE 20 10
MAX_BUFFER 1000 1000
RECYCLE_POOLS 3 1
RECYCLE 300 80
BUFFER_POOLS 9 3
BUFFER 1000 1000
MEMORY_MAGIC_CHECK 1 1
MEMORY_TARGET 15000 0
MEMORY_N_POOLS 1 1
MEMORY_POOL 500 100
PK_WITH_CLUSTER 0 0
ENABLE_MONITOR 1 1
SVR_LOG 0 1
DATETIME_FMT_MODE 0 0
COMPATIBLE_MODE 0 0
CLOB_LIKE_MAX_LEN 10240 10240
ENABLE_ENCRYPT 0 0
REDOS_PARALLEL_NUM 1 1
RLOG_POOL_SIZE 256 256
CACHE_POOL_SIZE 100 200
TEMP_SPACE_LIMIT 0 102400
TEMP_SIZE 10 1024
OLAP_FLAG 2 2
#==============================================================#
数据守护备库脱机恢复
#==============================================================#
正常关闭备数据库：
Stopping DmServiceDAMENG: [ OK ]
拷贝主库的备份文件并授权：
BACKUP_FILE_01.meta 100% 90KB 89.5KB/s 00:00
BACKUP_FILE_01.bak 100% 22MB 22.0MB/s 00:01
备库执行脱机数据库 dmrman 还原：
dmrman V8
RESTORE DATABASE '/dmdata/DAMENG/dm.ini' FROM BACKUPSET '/dmbak/BACKUP_FILE_01'
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:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.632
备库执行 dmrman 数据库更新：
dmrman V8
RECOVER DATABASE '/dmdata/DAMENG/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 2, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[39191], file_lsn[39191]
recover successfully!
time used: 00:00:01.007
#==============================================================#
数据守护主备配置参数文件
#==============================================================#
备库配置 dm.ini 参数：
ALTER_MODE_STATUS = 1 #Whether to permit database user to alter database mode and status by SQLs, 1: yes, 0: no
ENABLE_OFFLINE_TS = 2 #Whether tablespace can be offline
MAL_INI = 1 #dmmal.ini
ARCH_INI = 1 #dmarch.ini
备库配置 dmmal.ini 参数：
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_DW_01
MAL_HOST = 172.16.2.21
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.21
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST2]
MAL_INST_NAME = GRP1_DW_02
MAL_HOST = 172.16.2.22
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.22
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST3]
MAL_INST_NAME = GRP1_DW_03
MAL_HOST = 172.16.2.23
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.23
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
备库配置 dmarch.ini 参数：
ARCH_WAIT_APPLY = 1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dmarch/DAMENG
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 102400
[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_01
[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_02
备库配置 dmwatcher.ini 参数：
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_ERROR_TIME = 10
INST_OGUID = 496433
INST_INI = /dmdata/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /opt/dmdbms/bin/DmServiceDAMENG start
备库配置 dmmonitor.ini 参数：
MON_DW_CONFIRM = 0
MON_LOG_PATH = /opt/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 2048
[GRP1]
MON_INST_OGUID = 496433
MON_DW_IP = 172.16.2.21:12346
MON_DW_IP = 172.16.2.22:12346
MON_DW_IP = 172.16.2.23:12346
备库注册 dmwatcher 服务：
移动服务脚本文件(/opt/dmdbms/bin/DmWatcherServiceGRP1 到 /etc/rc.d/init.d/DmWatcherServiceGRP1)
创建服务(DmWatcherServiceGRP1)完成
备库启动到 mount 状态：
Starting DmServiceDAMENG: [ OK ]
备库修改模式：
密钥过期时间：2023-11-21
备库启动守护进程：
Starting DmWatcherServiceGRP1: [ OK ]
Connection to 192.168.2.23 closed.
节点 192.168.2.23 配置完成！
#==============================================================#
数据守护主备配置参数文件
#==============================================================#
主库配置 dm.ini 参数：
ALTER_MODE_STATUS = 1 #Whether to permit database user to alter database mode and status by SQLs, 1: yes, 0: no
ENABLE_OFFLINE_TS = 2 #Whether tablespace can be offline
MAL_INI = 1 #dmmal.ini
ARCH_INI = 1 #dmarch.ini
主库配置 dmmal.ini 参数：
MAL_CHECK_INTERVAL = 5
MAL_CONN_FAIL_INTERVAL = 5
[MAL_INST1]
MAL_INST_NAME = GRP1_DW_01
MAL_HOST = 172.16.2.21
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.21
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST2]
MAL_INST_NAME = GRP1_DW_02
MAL_HOST = 172.16.2.22
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.22
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
[MAL_INST3]
MAL_INST_NAME = GRP1_DW_03
MAL_HOST = 172.16.2.23
MAL_PORT = 12345
MAL_INST_HOST = 192.168.2.23
MAL_INST_PORT = 5236
MAL_DW_PORT = 12346
MAL_INST_DW_PORT = 12347
主库配置 dmarch.ini 参数：
ARCH_WAIT_APPLY = 1
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dmarch/DAMENG
ARCH_FILE_SIZE = 1024
ARCH_SPACE_LIMIT = 102400
[ARCHIVE_REALTIME1]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_02
[ARCHIVE_REALTIME2]
ARCH_TYPE = REALTIME
ARCH_DEST = GRP1_DW_03
主库配置 dmwatcher.ini 参数：
[GRP1]
DW_TYPE = GLOBAL
DW_MODE = MANUAL
DW_ERROR_TIME = 10
INST_ERROR_TIME = 10
INST_OGUID = 496433
INST_INI = /dmdata/DAMENG/dm.ini
INST_AUTO_RESTART = 1
INST_STARTUP_CMD = /opt/dmdbms/bin/DmServiceDAMENG start
主库配置 dmmonitor.ini 参数：
MON_DW_CONFIRM = 0
MON_LOG_PATH = /opt/dmdbms/log
MON_LOG_INTERVAL = 60
MON_LOG_FILE_SIZE = 32
MON_LOG_SPACE_LIMIT = 2048
[GRP1]
MON_INST_OGUID = 496433
MON_DW_IP = 172.16.2.21:12346
MON_DW_IP = 172.16.2.22:12346
MON_DW_IP = 172.16.2.23:12346
主库注册 dmwatcher 服务：
移动服务脚本文件(/opt/dmdbms/bin/DmWatcherServiceGRP1 到 /etc/rc.d/init.d/DmWatcherServiceGRP1)
创建服务(DmWatcherServiceGRP1)完成
主库启动到 mount 状态：
Starting DmServiceDAMENG: [ OK ]
主库修改模式：
密钥过期时间：2023-11-21
主库启动守护进程：
Starting DmWatcherServiceGRP1: [ OK ]
恭喜您，达梦数据守护已经安装完成！
[root@centos6 soft]# su - dmdba
[dmdba@dw01:~]$ dmmonitor /dmdata/DAMENG/dmmonitor_GRP1.ini
[monitor] 2022-12-20 07:26:37: DMMONITOR[4.0] V8
[monitor] 2022-12-20 07:26:38: DMMONITOR[4.0] IS READY.
[monitor] 2022-12-20 07:26:38: 收到守护进程(GRP1_DW_01)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-12-20 07:26:38 OPEN OK GRP1_DW_01 OPEN PRIMARY VALID 4 39353 39353
[monitor] 2022-12-20 07:26:38:
#--------------------------------------------------------------------------------#
GET MONITOR CONNECT INFO FROM DMWATCHER(GRP1_DW_01), THE FIRST LINE IS SELF INFO.
DW_CONN_TIME MON_CONFIRM MID MON_IP MON_VERSION
2022-12-20 07:26:38 FALSE 1349081708 ::ffff:172.16.2.21 DMMONITOR[4.0] V8
#--------------------------------------------------------------------------------#
[monitor] 2022-12-20 07:26:38: 收到守护进程(GRP1_DW_03)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-12-20 07:29:38 OPEN OK GRP1_DW_03 OPEN STANDBY VALID 4 39352 39352
[monitor] 2022-12-20 07:26:38: 收到守护进程(GRP1_DW_02)消息
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-12-20 07:29:20 OPEN OK GRP1_DW_02 OPEN STANDBY VALID 4 39352 39352
login
用户名:SYSDBA
密码:
[monitor] 2022-12-20 07:26:51: 登录监视器成功!
switchover GRP1_DW_02
[monitor] 2022-12-20 07:27:04: 开始切换实例GRP1_DW_02
[monitor] 2022-12-20 07:27:04: 通知守护进程GRP1_DW_01切换SWITCHOVER状态
[monitor] 2022-12-20 07:27:05: 守护进程(GRP1_DW_01)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2022-12-20 07:27:05: 切换守护进程GRP1_DW_01为SWITCHOVER状态成功
[monitor] 2022-12-20 07:27:05: 通知守护进程GRP1_DW_02切换SWITCHOVER状态
[monitor] 2022-12-20 07:27:05: 守护进程(GRP1_DW_02)状态切换 [OPEN-->SWITCHOVER]
[monitor] 2022-12-20 07:27:06: 切换守护进程GRP1_DW_02为SWITCHOVER状态成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_01开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_01执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02开始执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02执行SP_SET_GLOBAL_DW_STATUS(0, 6)语句成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_01开始执行ALTER DATABASE MOUNT语句
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_01执行ALTER DATABASE MOUNT语句成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02开始执行SP_APPLY_KEEP_PKG()语句
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02执行SP_APPLY_KEEP_PKG()语句成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02开始执行ALTER DATABASE MOUNT语句
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_02执行ALTER DATABASE MOUNT语句成功
[monitor] 2022-12-20 07:27:06: 实例GRP1_DW_01开始执行ALTER DATABASE STANDBY语句
[monitor] 2022-12-20 07:27:08: 实例GRP1_DW_01执行ALTER DATABASE STANDBY语句成功
[monitor] 2022-12-20 07:27:08: 实例GRP1_DW_02开始执行ALTER DATABASE PRIMARY语句
[monitor] 2022-12-20 07:27:08: 实例GRP1_DW_02执行ALTER DATABASE PRIMARY语句成功
[monitor] 2022-12-20 07:27:08: 通知实例GRP1_DW_02修改所有归档状态无效
[monitor] 2022-12-20 07:27:08: 修改所有实例归档为无效状态成功
[monitor] 2022-12-20 07:27:08: 实例GRP1_DW_01开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_01执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_02开始执行ALTER DATABASE OPEN FORCE语句
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_02执行ALTER DATABASE OPEN FORCE语句成功
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_01开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_01执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_02开始执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句
[monitor] 2022-12-20 07:27:09: 实例GRP1_DW_02执行SP_SET_GLOBAL_DW_STATUS(6, 0)语句成功
[monitor] 2022-12-20 07:27:09: 通知守护进程GRP1_DW_01切换OPEN状态
[monitor] 2022-12-20 07:27:09: 守护进程(GRP1_DW_01)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2022-12-20 07:27:10: 切换守护进程GRP1_DW_01为OPEN状态成功
[monitor] 2022-12-20 07:27:10: 通知守护进程GRP1_DW_02切换OPEN状态
[monitor] 2022-12-20 07:27:10: 守护进程(GRP1_DW_02)状态切换 [SWITCHOVER-->OPEN]
[monitor] 2022-12-20 07:27:11: 切换守护进程GRP1_DW_02为OPEN状态成功
[monitor] 2022-12-20 07:27:11: 通知组(GRP1)的守护进程执行清理操作
[monitor] 2022-12-20 07:27:11: 清理守护进程(GRP1_DW_01)请求成功
[monitor] 2022-12-20 07:27:12: 清理守护进程(GRP1_DW_02)请求成功
[monitor] 2022-12-20 07:27:12: 清理守护进程(GRP1_DW_03)请求成功
[monitor] 2022-12-20 07:27:12: 实例GRP1_DW_02切换成功
2022-12-20 07:27:12
#================================================================================#
GROUP OGUID MON_CONFIRM MODE MPP_FLAG
GRP1 496433 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
172.16.2.22 12346 2022-12-20 07:29:53 GLOBAL VALID OPEN GRP1_DW_02 OK 1 1 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.2.22 5236 OK GRP1_DW_02 OPEN PRIMARY 0 0 REALTIME VALID 4809 39522 4809 39522 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
172.16.2.21 12346 2022-12-20 07:27:11 GLOBAL VALID OPEN GRP1_DW_01 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME INVALID
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.2.21 5236 OK GRP1_DW_01 OPEN STANDBY 0 0 REALTIME INVALID 4807 39364 4807 39364 NONE
DATABASE(GRP1_DW_01) APPLY INFO FROM (GRP1_DW_02), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4807, 4807, 4807], (RLSN, SLSN, KLSN)[39364, 39364, 39364], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (39364)
<<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
172.16.2.23 12346 2022-12-20 07:30:11 GLOBAL VALID OPEN GRP1_DW_03 OK 1 1 OPEN STANDBY DSC_OPEN REALTIME INVALID
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.2.23 5236 OK GRP1_DW_03 OPEN STANDBY 0 0 REALTIME INVALID 4787 39364 4787 39364 NONE
DATABASE(GRP1_DW_03) APPLY INFO FROM (GRP1_DW_01), REDOS_PARALLEL_NUM (1), WAIT_APPLY[FALSE]:
DSC_SEQNO[0], (RSEQ, SSEQ, KSEQ)[4807, 4807, 4807], (RLSN, SLSN, KLSN)[39364, 39364, 39364], N_TSK[0], TSK_MEM_USE[0]
REDO_LSN_ARR: (39364)
#================================================================================#
[monitor] 2022-12-20 07:27:13: 守护进程(GRP1_DW_02)状态切换 [OPEN-->RECOVERY]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-12-20 07:29:55 RECOVERY OK GRP1_DW_02 OPEN PRIMARY VALID 5 39522 39523
[monitor] 2022-12-20 07:27:16: 守护进程(GRP1_DW_02)状态切换 [RECOVERY-->OPEN]
WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN
2022-12-20 07:29:58 OPEN OK GRP1_DW_02 OPEN PRIMARY VALID 5 39523 39524
exit
[dmdba@dw01:~]$ exit
logout
[root@centos6 soft]# init 0
[root@centos6 soft]# Connection closing...Socket close.
Connection closed by foreign host.
Disconnected from remote host(192.168.2.21) at 00:51:50.
Type `help' to learn how to use Xshell prompt.
[C:\~]$
Connecting to 192.168.2.21:22...
[END] 2022/12/20 0:53:05
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手