UOS操作系统安装达梦数据库V8单机脚本

最新推荐文章于 2024-06-16 22:08:20 发布

LunateYX

最新推荐文章于 2024-06-16 22:08:20 发布

阅读量1.6k

点赞数

文章标签：数据库运维

本文链接：https://blog.csdn.net/lunateyx/article/details/123855061

版权

一、准备好达梦数据库安装包，以及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"

LunateYX

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
UOS操作系统安装达梦数据库V8单机脚本

一、准备好达梦数据库安装包，以及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.上传..
复制链接

扫一扫