达梦数据库--单机部署V1.6

最新推荐文章于 2024-03-05 09:51:00 发布

我TM秃了呀!!!

最新推荐文章于 2024-03-05 09:51:00 发布

阅读量830

点赞数

分类专栏：达梦数据库文章标签：数据库

本文链接：https://blog.csdn.net/qq_44646761/article/details/131670057

版权

达梦数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、环境准备
1、secadm导入签名，root安装数据库软件
2、磁盘分区
parted /dev/sdb #对磁盘进行分区
mklabel gpt #磁盘标签改为gpt
mkpart primary 0% 50% #100%容量分一个主分区
mkpart primary 50% 70%
mkpart primary 70% 100%
p #打印当前分区
q #退出

3、格式化磁盘、挂载磁盘
for((i=1;i<=3;i++))
do
/usr/sbin/mkfs.ext4 /dev/sdb$i
done
mkdir -p /dm{data,arch,bak}

4、做开机自动挂载磁盘
uuid1=blkid | grep sdb1 |awk '{print $2}' |cut -c 7-42
echo “UUID=$uuid1 /dmdata ext4 defaults,noatime 0 0” >> /etc/fstab
uuid2=blkid | grep sdb2 |awk '{print $2}' |cut -c 7-42
echo “UUID=$uuid2 /dmarch ext4 defaults,noatime 0 0” >> /etc/fstab
uuid3=blkid | grep sdb3 |awk '{print $2}' |cut -c 7-42
echo “UUID=$uuid3 /dmbak ext4 defaults,noatime 0 0” >> /etc/fstab
mount -a

5、修改主机名（dmdb+IP地址）
hostnamectl set-hostname dmdb && exec bash
6、配置环境变量
echo “export DM_HOME=/opt/dmdbms/bin” >>~/.bash_profile
echo “export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH:$HOME/bin” >>~/.bash_profile
echo “export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin” >>~/.bash_profile
echo “alias dmlog=‘cd /opt/dmdbms/bin/log’” >>~/.bash_profile
echo “alias dmbin=‘cd /opt/dmdbms/bin/bin’” >>~/.bash_profile
echo “alias dmdata=‘cd /dmdata/DAMENG’” >>~/.bash_profile
source ~/.bash_profile

二、初始化实例
1、进入到bin目录：
cd /opt/dmdbms/bin/bin
2、初始化实例
可通过 ./dminit help 查看相关参数信息
例如：端口号5236，字符集UTF-8，大小写不敏感，页大小16k（初始化需要向开发人员咨询该参数信息）
./dminit PATH=/dmdata PAGE_SIZE=16 CASE_SENSITIVE=N CHARSET=1
3、启动数据库
dm_services start
4、SYSDBA登陆修改REDO日志大小
alter database resize logfile ‘DAMENG01.log’ to 2048;
alter database resize logfile ‘DAMENG02.log’ to 2048;

三、ZYJ表空间
SYSDBA账户登录：
1、新建表空间或扩充MAIN表空间
-----新建表空间
create tablespace “TEST” datafile
‘/dmdata/DAMENG/TEST01.dbf’ size 10240,
‘/dmdata/DAMENG/TEST02.dbf’ size 10240,
‘/dmdata/DAMENG/TEST03.dbf’ size 10240
CACHE = NORMAL;
-----扩充MAIN表空间
alter tablespace “MAIN” resize datafile ‘MAIN.DBF’ to 10240;
alter tablespace “MAIN” add datafile ‘/dmdata/DAMENG/MAIN01.DBF’ size 10240;
alter tablespace “MAIN” add datafile ‘/dmdata/DAMENG/MAIN02.DBF’ size 10240;
alter tablespace “MAIN” add datafile ‘/dmdata/DAMENG/MAIN03.DBF’ size 10240;
alter tablespace “MAIN” add datafile ‘/dmdata/DAMENG/MAIN04.DBF’ size 10240;
alter tablespace “MAIN” add datafile ‘/dmdata/DAMENG/MAIN05.DBF’ size 10240;
四、ZYJ创建用户
1、创建用户并分配表空间
----SYSDBA用户登录
CREATE USER USER_NAME; —创建用户
GRANT DBA TO USER_NAME; —授予DBA角色
----SYSSSO用户登录：
ALTER USER USER_NAME IDENTIFIED BY “用户口令”; —设置密码
ALTER USER USER_NAME DEFAULT TABLESPACE “表空间名”; —设置用户默认表空间
ALTER USER USER_NAME DEFAULT INDEX TABLESPACE “表空间名”; —设置用户索引表空间

五、注意修改用户的资源限制（如果不修改7天后密码过期）
默认密码：SYSSSO/SYSSSO123，SYSDBA/SYSDBA123
----SYSSSO用户登录（先修改SYSSSO和SYSDBA）：
ALTER USER “SYSSSO” LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;
ALTER USER “SYSSSO” LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER “SYSSSO” LIMIT PASSWORD_LOCK_TIME UNLIMITED;
ALTER USER “SYSDBA” LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;
ALTER USER “SYSDBA” LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER “SYSDBA” LIMIT PASSWORD_LOCK_TIME UNLIMITED;

可通过下面语句查看剩余过期时间，如果为0代表没有过期时间。
select b.username, a.life_time from sysusers a right join all_users b on a.id=b.user_id;

六、开启归档日志
----SYSDBA用户登录
alter DATABASE MOUNT; —修改数据库配置状态
alter database archivelog; —修改为归档模式
alter database add archivelog ‘DEST=/dmarch, TYPE=LOCAL, FILE_SIZE=256, SPACE_LIMIT=204800’;
alter DATABASE OPEN; —修改数据库OPEN状态

七、定时作业备份，定时清除30天前过期备份
----创建代理环境
SP_INIT_JOB_SYS(1);
–全量备份
–全量备份：定时每周六00:30整进行
call SP_CREATE_JOB(‘bak_full’,1,0,‘’,0,0,‘’,0,‘’);
call SP_JOB_CONFIG_START(‘bak_full’);
call SP_ADD_JOB_STEP(‘bak_full’, ‘bak_full’, 6, ‘01000000/dmbak’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘bak_full’, ‘bak_full’, 1, 2, 1, 64, 0, ‘00:30:00’, NULL, ‘2020-06-25 22:43:59’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘bak_full’);
–增量备份
–增量备份：定时每周一、二、三、四、五、日晚23:30点进行
call SP_CREATE_JOB(‘bak_inc’,1,0,‘’,0,0,‘’,0,‘’);
call SP_JOB_CONFIG_START(‘bak_inc’);
call SP_ADD_JOB_STEP(‘bak_inc’, ‘bak_inc’, 6, ‘11000000/dmbak|/dmbak’, 1, 0, 2, 6, NULL, 0);
call SP_ADD_JOB_STEP(‘bak_inc’, ‘switch_full’, 6, ‘01000000/dmbak’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘bak_inc’, ‘bak_inc’, 1, 2, 1, 63, 0, ‘23:30:00’, NULL, ‘2020-06-21 11:15:00’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘bak_inc’);
–备份清理
–备份保留30天，清除30天前的备份，每天凌晨03点整进行
call SP_CREATE_JOB(‘bak_clear’,1,0,‘’,0,0,‘’,0,‘每天删除30天前的备份’);
call SP_JOB_CONFIG_START(‘bak_clear’);
call SP_ADD_JOB_STEP(‘bak_clear’, ‘del_bak’, 0, ‘SF_BAKSET_BACKUP_DIR_ADD(’‘DISK’‘,’‘/dmbak’‘);
CALL SP_DB_BAKSET_REMOVE_BATCH(’‘DISK’‘,SYSDATE-30);’, 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE(‘bak_clear’, ‘diaodu_del’, 1, 1, 1, 0, 0, ‘03:00:00’, NULL, ‘2020-06-25 22:54:03’, NULL, ‘’);
call SP_JOB_CONFIG_COMMIT(‘bak_clear’);

八、优化脚本
1、记录默认参数并且优化参数（SYSDBA账户登陆）：
create table t_dm_Ini_default as select * from v$dm_ini;

–修改调整基础参数
declare
mem_per int:=90;
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 $in s t an ce w h ere MO D E$ in(‘NORMAL’,‘PRIMARY’)) THEN
IF CNT=0 THEN
execute IMMEDIATE ‘CREATE TABLE BAK_DMINI_’ || to_char(sysdate,‘yymmdd’) || ’ as select *,sysdate uptime from v $KaTeX parse error: Expected group after '_' at position 64: \dotsINTO BAK_DMINI_̲' || to_char(sy\dots$ 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); 

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;
/

2、对比默认和调整后的参数
set pagesize 100;

select a.para_name,b.para_value,a.para_value default_value
from t_dm_Ini_default b,v$dm_ini a
where a.para_name=b.para_name and
a.para_name in (‘WORKER_THREADS’,
‘TASK_THREADS’, ‘IO_THR_GROUPS’, ‘MAX_OS_MEMORY’, ‘MEMORY_POOL’,
‘MEMORY_N_POOLS’, ‘MEMORY_TARGET’, ‘BUFFER’, ‘MAX_BUFFER’,
‘BUFFER_POOLS’, ‘RECYCLE’, ‘RECYCLE_POOLS’,‘HJ_BUF_GLOBAL_SIZE’,
‘HJ_BUF_SIZE’, ‘HAGR_BUF_GLOBAL_SIZE’, ‘HAGR_BUF_SIZE’,
‘SORT_FLAG’, ‘SORT_BLK_SIZE’, ‘SORT_BUF_SIZE’, ‘SORT_BUF_GLOBAL_SIZE’,
‘RLOG_POOL_SIZE’, ‘SESS_POOL_SIZE’, ‘CACHE_POOL_SIZE’, ‘DICT_BUF_SIZE’,
‘VM_POOL_TARGET’, ‘SESS_POOL_TARGET’, ‘USE_PLN_POOL’, ‘ENABLE_MONITOR’,
‘SVR_LOG’, ‘TEMP_SIZE’, ‘TEMP_SPACE_LIMIT’, ‘MAX_SESSIONS’,
‘MAX_SESSION_STATEMENT’,‘PK_WITH_CLUSTER’,‘ENABLE_ENCRYPT’,
‘CLOB_LIKE_MAX_LEN’, ‘OLAP_FLAG’, ‘VIEW_PULLUP_FLAG’,
‘OPTIMIZER_MODE’, ‘ADAPTIVE_NPLN_FLAG’, ‘TOP_DIS_HASH_FLAG’,
‘TOP_ORDER_OPT_FLAG’, ‘MEMORY_MAGIC_CHECK’,‘DATETIME_FMT_MODE’,
‘COMPATIBLE_MODE’,‘TRX_VIEW_MODE’,‘REDOS_PARALLEL_NUM’);

九、开启异步SQL日志追踪
disql SYSDBA/HEN_SYSDBA2021
SP_SET_PARA_VALUE(1,‘SVR_LOG’,1);

mkdir /dmdata/DAMENG/sqllog
echo ‘[SLOG_ALL]’ > /dmdata/DAMENG/sqllog.ini
echo ‘FILE_NUM = 20’ >> /dmdata/DAMENG/sqllog.ini
echo ‘SWITCH_MODE = 2’ >> /dmdata/DAMENG/sqllog.ini
echo ‘SWITCH_LIMIT = 512’ >> /dmdata/DAMENG/sqllog.ini
echo ‘ASYNC_FLUSH = 1’ >> /dmdata/DAMENG/sqllog.ini
echo ‘FILE_PATH = /dmdata/DAMENG/sqllog/’ >> /dmdata/DAMENG/sqllog.ini

dm_services restart

十、数据库启动与关闭
dm_services start
dm_services stop

我TM秃了呀!!!

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库--单机部署V1.6

一、环境准备1、secadm导入签名，root安装数据库软件2、磁盘分区parted /dev/sdb #对磁盘进行分区mklabel gpt #磁盘标签改为gptmkpart primary 0% 50% #100%容量分一个主分区p #打印当前分区q #退出3、格式化磁盘、挂载磁盘for((i=1;i<=3;i++))dodone4、做开机自动挂载磁盘uuid1=uuid2=uuid3=mount -a。
复制链接

扫一扫