一、准备好达梦数据库安装包，以及key文件(若无key文件也能安装对应的修改脚本即可)
DMInstall.bin dm.key
达梦开发版下载地址：https://package.dameng.com/eco/adapter/DM8/202110/dm8_20211025_HWarm_centos7_64_ent.zip
1.下载后解压，再将dm8_20211025_HWarm_centos7_64_ent_8.1.2.84.iso进行解压后得到DMInstall.bin文件。
2.上传到服务器 /home/dm路径
二、将以下sql和脚本上传到/root目录下,执行chmod 755 mojor.sql dmo.sh pd.sh install.sh opt.sh
完成后直接执行./dmo.sh，即可安装成功。
如存储、备份、归档文件路径与脚本不同，需做出对应的修改。
mojor.sql
--配置归档
alter database mount;
alter database archivelog;
alter database add archivelog 'DEST=/dbdata/data/arch, TYPE=LOCAL, FILE_SIZE=256, SPACE_LIMIT=25600';
alter database open;
--配置在线日志
alter database resize logfile 'DAMENG01.log' to 2048;
alter database resize logfile 'DAMENG02.log' to 2048;
alter database add logfile '/dbdata/data/DAMENG/DAMENG03.log' size 2048;
alter database add logfile '/dbdata/data/DAMENG/DAMENG04.log' size 2048;
--配置作业
SP_INIT_JOB_SYS(1); --开启作业
call SP_CREATE_JOB('BACKUP_INC',1,0,'',0,0,'',0,'增量备份');
call SP_JOB_CONFIG_START('BACKUP_INC');
call SP_JOB_SET_EP_SEQNO('BACKUP_INC', 0);
call SP_ADD_JOB_STEP('BACKUP_INC', 'BAKUP_INC', 6, '11020000/dbdata/data/bak|/dbdata/data/bak', 1, 3, 0, 0, NULL, 0);
call SP_ADD_JOB_STEP('BACKUP_INC', 'BACKUP_INC_2', 6, '01020000/dbdata/data/bak', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('BACKUP_INC', 'BACKUP_INC', 1, 2, 1, 63, 0, '23:30:00', NULL, '2021-03-30 10:56:45', NULL, '');
call SP_JOB_CONFIG_COMMIT('BACKUP_INC');
call SP_CREATE_JOB('BAKUP_FULL',1,0,'',0,0,'',0,'全量备份');
call SP_JOB_CONFIG_START('BAKUP_FULL');
call SP_JOB_SET_EP_SEQNO('BAKUP_FULL', 0);
call SP_ADD_JOB_STEP('BAKUP_FULL', 'BAKUP_FULL', 6, '01020000/dbdata/data/bak', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('BAKUP_FULL', 'BAKUP_FULL', 1, 2, 1, 64, 0, '23:30:00', NULL, '2021-03-30 10:53:40', NULL, '');
call SP_JOB_CONFIG_COMMIT('BAKUP_FULL');
call SP_CREATE_JOB('DEL_ARCH',1,0,'',0,0,'',0,'删除一个月前的归档');
call SP_JOB_CONFIG_START('DEL_ARCH');
call SP_JOB_SET_EP_SEQNO('DEL_ARCH', 0);
call SP_ADD_JOB_STEP('DEL_ARCH', 'DEL_ARCH', 0, 'SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30)', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DEL_ARCH', 'DEL_ARCH', 1, 1, 1, 0, 0, '23:55:00', NULL, '2021-03-30 10:59:20', NULL, '');
call SP_JOB_CONFIG_COMMIT('DEL_ARCH');
call SP_CREATE_JOB('DEL_BAK',1,0,'',0,0,'',0,'除一个月前的备份');
call SP_JOB_CONFIG_START('DEL_BAK');
call SP_JOB_SET_EP_SEQNO('DEL_BAK', 0);
call SP_ADD_JOB_STEP('DEL_BAK', 'DEL_BAK', 0, 'SP_DB_BAKSET_REMOVE_BATCH(NULL,SYSDATE-30)', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DEL_BAK', 'DEL_BAK', 1, 1, 1, 0, 0, '23:50:00', NULL, '2021-03-30 10:58:06', NULL, '');
call SP_JOB_CONFIG_COMMIT('DEL_BAK');
--优化数据库
/*
**************************************************************************
* *
* Auto parameter adjustment 2.1 *
* [December 30, 2020 ]
* Written by Mr. Huanghaiming *
* Take effect after restart dmserver
* *
**************************************************************************
*/
declare
mem_per int:=100; --默认所有的内存归达梦数据库使用，如实际不能100%可用，可以调整此参数
v_mem_mb int;
v_cpus int;
tname varchar(100);
MEMORY_POOL int;
MEMORY_N_POOLS int;
MEMORY_TARGET int;
BUFFER INT;
MAX_BUFFER INT;
RECYCLE int;
CACHE_POOL_SIZE int;
BUFFER_POOLS int;
RECYCLE_POOLS int;
SORT_BUF_SIZE int;
SORT_BUF_GLOBAL_SIZE INT;
DICT_BUF_SIZE INT;
SESS_POOL_SIZE INT;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
TASK_THREADS INT;
IO_THR_GROUPS INT;
CNT INT;
begin
CNT :=0;
SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
print v_cpus;
print v_mem_mb;
v_mem_mb := v_mem_mb * (mem_per/100.0);
v_mem_mb=round(v_mem_mb,-3);
print v_mem_mb;
IF v_mem_mb <= 2000 THEN
return;
END IF;
IF v_mem_mb > 512000 THEN
v_mem_mb :=v_mem_mb*0.8;
END IF;
MEMORY_TARGET=round(cast(v_mem_mb * 0.12 as int),-3);
TASK_THREADS :=4;
IO_THR_GROUPS :=4;
IF v_cpus < 8 THEN
TASK_THREADS :=4;
IO_THR_GROUPS :=2;
END IF;
IF v_cpus >= 64 THEN
v_cpus := 64;
TASK_THREADS :=16;
IO_THR_GROUPS :=8;
END IF;
BUFFER := round(cast(v_mem_mb * 0.4 as int),-3);
MAX_BUFFER := BUFFER;
RECYCLE :=cast(v_mem_mb * 0.04 as int);
IF v_mem_mb < 70000 THEN
with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into BUFFER_POOLS from t1 where rn > v_mem_mb/800 order by 1;
PRINT 'BUFFER_POOLS ' || BUFFER_POOLS;
--设置根据内存情况RECYCLE_POOLS参数
with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into RECYCLE_POOLS from t1 where rn > v_mem_mb/800/3 order by 1;
PRINT 'RECYCLE_POOLS ' || RECYCLE_POOLS;
ELSE
BUFFER_POOLS := 101;
RECYCLE_POOLS := 41;
END IF;
--修改内存池
IF v_mem_mb >= 16000 THEN
IF v_mem_mb= 16000 THEN
MEMORY_POOL := 1500;
SORT_BUF_GLOBAL_SIZE := 1000;
ELSE
MEMORY_POOL := 2000;
SORT_BUF_GLOBAL_SIZE := 2000;
END IF;
CACHE_POOL_SIZE := 1024;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 2000;
SESS_POOL_SIZE := 16;
RLOG_POOL_SIZE := 1024;
HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
HJ_BUF_SIZE :=250;
HAGR_BUF_SIZE :=250;
RECYCLE :=round(RECYCLE,-3);
IF v_mem_mb > 64000 THEN
CACHE_POOL_SIZE := 2048;
RLOG_POOL_SIZE := 2048;
SORT_FLAG = 1;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE=50;
SORT_BUF_GLOBAL_SIZE= cast(v_mem_mb * 0.02 as int);
SESS_POOL_SIZE := 32;
HJ_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.15625 as int);
HAGR_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.04 as int);
HJ_BUF_SIZE :=512;
HAGR_BUF_SIZE :=512;
END IF;
DICT_BUF_SIZE := 50;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
SORT_BUF_GLOBAL_SIZE :=round(SORT_BUF_GLOBAL_SIZE,-3);
RECYCLE :=round(RECYCLE,-3);
ELSE
MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
CACHE_POOL_SIZE := 200;
RLOG_POOL_SIZE := 256;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 500;
DICT_BUF_SIZE := 50;
SESS_POOL_SIZE =16;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
HJ_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
HAGR_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
HJ_BUF_SIZE := GREAT(cast(v_mem_mb * 0.00625 as int),50);
HAGR_BUF_SIZE :=GREAT(cast(v_mem_mb * 0.00625 as int),50);
END IF;
tname :='BAK_DMINI_' || to_char(sysdate,'yymmdd');
execute IMMEDIATE 'select count(*) from USER_ALL_TABLES where table_name= ?' into CNT using tname;
IF exists(select 1 from v$instance where MODE$ in('NORMAL','PRIMARY')) THEN
IF CNT=0 THEN
execute IMMEDIATE 'CREATE TABLE BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' as select *,sysdate uptime from v$dm_ini';
ELSE
execute IMMEDIATE 'INSERT INTO BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' select *,sysdate uptime from v$dm_ini';
END IF;
END IF;
MEMORY_N_POOLS :=MEMORY_POOL/200;
print 'MEMORY_N_POOLS ' || MEMORY_N_POOLS;
--修改cpu相关参数
SP_SET_PARA_VALUE(2,'WORKER_THREADS',v_cpus);
SP_SET_PARA_VALUE(2,'TASK_THREADS',TASK_THREADS);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',IO_THR_GROUPS);
--修改内存池相关参数
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', mem_per);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', MEMORY_POOL);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', MEMORY_TARGET);
if exists(select 1 from v$dm_ini where para_name='MEMORY_N_POOLS') then
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', MEMORY_N_POOLS);
end if;
--修改缓冲区相关参数
SP_SET_PARA_VALUE(2,'BUFFER', BUFFER);
SP_SET_PARA_VALUE(2,'MAX_BUFFER', MAX_BUFFER);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', BUFFER_POOLS);
SP_SET_PARA_VALUE(2,'RECYCLE', RECYCLE);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', RECYCLE_POOLS);
--修改HASH相关参数
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', HJ_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', HJ_BUF_SIZE );
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',HAGR_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', HAGR_BUF_SIZE );
--修改排序相关参数
SP_SET_PARA_VALUE(2,'SORT_FLAG',SORT_FLAG);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',SORT_BLK_SIZE);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', SORT_BUF_SIZE);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', SORT_BUF_GLOBAL_SIZE);
--修改其他内存参数
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', RLOG_POOL_SIZE);
SP_SET_PARA_VALUE(2,'SESS_POOL_SIZE', SESS_POOL_SIZE);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', CACHE_POOL_SIZE);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', DICT_BUF_SIZE);
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,'SVR_LOG', 0);
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);
SP_SET_PARA_VALUE(2,'COMPATIBLE_MODE',4);
select MEMORY_TARGET+BUFFER+RECYCLE+HJ_BUF_GLOBAL_SIZE+HAGR_BUF_GLOBAL_SIZE+CACHE_POOL_SIZE
+DICT_BUF_SIZE+SORT_BUF_GLOBAL_SIZE+RLOG_POOL_SIZE;
exception
when others then
raise_application_error (-20001,substr( ' 执行失败, '||SQLCODE||' '||SQLERRM||' '||dbms_utility.format_error_backtrace , 1, 400));
end;
/脚本如下：
dmo.sh 初始准备工作以及shell执行
!/bin/bash
#安装依赖
apt install -y tcl expect wget
#添加用户
groupadd dinstall
useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
#配置密码
/root/pd.sh
#创建路径赋权
chmod 777 /home/dm/DMInstall.bin
#创建存储、备份、归档文件路径并赋权
#如与脚本中路径不相同则需修改创建，初始化数据存放路径，注册服务，优化sql做出相应的修改
mkdir -pv /dbdata/data/{arch,bak}
chown -R dmdba:dinstall /dbdata/data
#安装DM
su dmdba -c "/home/dm/DMInstall.bin -i"
/root/install.sh
#创建DmAPService服务并启动
/home/dmdba/dmdbms/script/root/root_installer.sh
##初始化数据存放路径
su dmdba -c "cd /home/dmdba/dmdbms/bin & ./dminit path=/dbdata/data/ CASE_SENSITIVE=0 PAGE_SIZE=32 CHARSET=1 length_in_char=1"
#注册服务
/home/dmdba/dmdbms/script/root/dm_service_installer.sh -t dmserver -p dmserver -dm_ini /dbdata/data/DAMENG/dm.ini
/home/dmdba/dmdbms/bin/DmServicedmserver start
echo "DM数据库单机安装已完成"
/root/opt.sh
pd.sh 设置dmdba用户密码
#!/usr/bin/expect
set password "AA123456"
spawn passwd dmdba
expect "新的 密码：" { send "$password\n" }
sleep 5
expect "重新输入新的 密码：" { send "$password\n" }
install.sh 使用expect 自动交互安装数据库中需填写的选项(需注意是否有key文件)
#!/usr/bin/expect
set keyurl "/home/dm/dm.key"
set indb "/home/dmdba/dmdbms"
spawn su dmdba -c "/home/dm/DMInstall.bin -i"
expect "*\[C\/c\]:" { send "c\n" }
sleep 5
#如果没有key文件，屏蔽下两行
expect "*\[Y\/y\]:" { send "y\n" }
expect "请输入Key文件的路径地址\*" { send "$keyurl\n" }
expect "*\[Y\/y\]:" { send "y\n" }
expect "请选择设置时区\*" { send "21\n" }
sleep 1
expect "*\[1 典型安装\]" { send "1\n" }
expect "请选择安装目录\*" { send "$indb\n" }
expect "是否确认安装路径\*" { send "y\n" }
expect "是否确认安装?\*" { send "y\r" }
interact
opt.sh 通过disql工具执行优化sql
#!/usr/bin/expect
cd /home/dmdba/dmdbms/bin
spawn ./disql SYSDBA/SYSDBA \`/root/mojor.sql.sql
sleep 3
send "exit\r"欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）