达梦数据库DM7部署方案

1. 创建数据目录

mkdir /dmdata

chown -R dmdba:dinstall /dmdata

su - dmdba

mkdir /dmdata/TYYW2

mkdir /dmdata/TYYW2/arch

mkdir /dmdata/TYYW2/bak

修改/etc/security/limits.conf增加如下内容：

----------------------------------

dmdba  soft      nproc      4096

dmdba  hard      nproc      4096

dmdba  soft      nofile     65536

dmdba  hard      nofile     65536

dmdba  soft      core       unlimited

----------------------------------

修改/home/dmdba/.bash_profile增加如下内容：

export PATH=$PATH:/home/dmdba/dmdbms/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/dmdba/dmdbms/bin

export JAVA_HOME=/home/dmdba/dmdbms/jdk

--修改归档并进行备份测试

ALTER DATABASE MOUNT;

ALTER DATABASE ARCHIVELOG;

--添加归档，指定最大大小为100G

ALTER DATABASE ADD ARCHIVELOG ‘DEST=/dmdata/TYYW2/arch,TYPE=LOCAL,FILE_SIZE=128,SPACE_LIMIT=128000;

ALTER DATABASE OPEN;

--定时每周六运行，进行全量备份

call SP_CREATE_JOB(‘JOB_FULL_BAK_TIMELY‘,1,0,‘‘,0,0,‘‘,0,‘定时全量备份‘);

call SP_JOB_CONFIG_START(‘JOB_FULL_BAK_TIMELY‘);

call SP_ADD_JOB_STEP(‘JOB_FULL_BAK_TIMELY‘, ‘STEP_FULL_BAK‘, 5, ‘01000/dmdata/TYYW2/bak‘, 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘JOB_FULL_BAK_TIMELY‘, ‘SCHEDULE_FULL_BAK‘, 1, 2, 1, 64, 0, ‘00:05:56‘, NULL, ‘2016-04-07 21:05:57‘, NULL, ‘‘);

call SP_JOB_CONFIG_COMMIT(‘JOB_FULL_BAK_TIMELY‘);

--定时每天运行，进行增量备份(晚上,周六除外)

call SP_CREATE_JOB(‘JOB_INCREMENT_BAK_TIMELY_NIGHT‘,1,0,‘‘,0,0,‘‘,0,‘定时增量备份‘);

call SP_JOB_CONFIG_START(‘JOB_INCREMENT_BAK_TIMELY_NIGHT‘);

call SP_ADD_JOB_STEP(‘JOB_INCREMENT_BAK_TIMELY_NIGHT‘, ‘STEP_INCREMENT_BAK‘, 5, ‘11000/dmdata/TYYW2/bak|/dmdata/TYYW2/bak‘, 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘JOB_INCREMENT_BAK_TIMELY_NIGHT‘, ‘SCHEDULE_INCREMENT_BAK‘, 1, 2, 1, 63, 0, ‘01:05:56‘, NULL, ‘2016-04-07 21:05:57‘, NULL, ‘‘);

call SP_JOB_CONFIG_COMMIT(‘JOB_INCREMENT_BAK_TIMELY_NIGHT‘);

call SP_CREATE_JOB(‘JOB_DEL_BAK_TIMELY‘,1,0,‘‘,0,0,‘‘,0,‘定时删除备份‘);

call SP_JOB_CONFIG_START(‘JOB_DEL_BAK_TIMELY‘);

call SP_ADD_JOB_STEP(‘JOB_DEL_BAK_TIMELY‘, ‘STEP_DEL_BAK‘, 0, ‘SP_BATCH_DEL_BAK(‘‘DAMENG‘‘, ‘‘‘‘, 1, sysdate-30, -1);‘, 1, 2, 0, 0, NULL, 0); --BAK1备份

--call SP_ADD_JOB_STEP(‘JOB_DEL_BAK_TIMELY‘, ‘STEP_DEL_BAK‘, 0, ‘SP_DB_BAKSET_REMOVE_BATCH(NULL,SYSDATE-30);‘, 1, 2, 0, 0, NULL, 0); --BAK2备份，备份集方式的备份

call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_BAK_TIMELY‘, ‘SCHEDULE_DEL_BAK‘, 1, 2, 1, 1, 0, ‘00:05:56‘, NULL, ‘2016-04-07 21:05:57‘, NULL, ‘‘);

call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_BAK_TIMELY‘);

call SP_CREATE_JOB(‘JOB_DEL_ARCH_TIMELY‘,1,0,‘‘,0,0,‘‘,0,‘定时删除备份‘);

call SP_JOB_CONFIG_START(‘JOB_DEL_ARCH_TIMELY‘);

call SP_ADD_JOB_STEP(‘JOB_DEL_ARCH_TIMELY‘, ‘STEP_DEL_ARCH‘, 0, ‘SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30);‘, 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE(‘JOB_DEL_ARCH_TIMELY‘, ‘SCHEDULE_DEL_ARCH‘, 1, 2, 1, 1, 0, ‘00:05:56‘, NULL, ‘2016-04-07 21:05:57‘, NULL, ‘‘);

call SP_JOB_CONFIG_COMMIT(‘JOB_DEL_ARCH_TIMELY‘);

--修改调整基础参数

declare

v_mem_mb int;

v_cpus int;

begin

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;

--修改线程数

IF v_cpus < 8  THEN v_cpus := 8;  END IF;

IF v_cpus > 64 THEN v_cpus := 64; END IF;

SP_SET_PARA_VALUE(2,‘WORKER_THREADS‘,v_cpus);

SP_SET_PARA_VALUE(2,‘TASK_THREADS‘,v_cpus/2);

--修改内存

SP_SET_PARA_VALUE(2,‘MAX_OS_MEMORY‘,       90);

SP_SET_PARA_VALUE(2,‘MEMORY_POOL‘,         cast(v_mem_mb * 0.015 as int));

SP_SET_PARA_VALUE(2,‘BUFFER‘,              cast(v_mem_mb * 0.5 as int));

SP_SET_PARA_VALUE(2,‘MAX_BUFFER‘,          cast(v_mem_mb * 0.5 as int));

SP_SET_PARA_VALUE(2,‘BUFFER_POOLS‘,        67);--7,11,19,37,67,101 (4G-128G)

SP_SET_PARA_VALUE(2,‘RECYCLE‘,            cast(v_mem_mb * 0.032 as int));

SP_SET_PARA_VALUE(2,‘RECYCLE_POOLS‘,       11);

SP_SET_PARA_VALUE(2,‘HJ_BUF_GLOBAL_SIZE‘,  cast(v_mem_mb * 0.0625 as int));

SP_SET_PARA_VALUE(2,‘HJ_BUF_SIZE‘,         cast(v_mem_mb * 0.004 as int));

SP_SET_PARA_VALUE(2,‘HAGR_BUF_GLOBAL_SIZE‘,cast(v_mem_mb * 0.05 as int));

SP_SET_PARA_VALUE(2,‘HAGR_BUF_SIZE‘,       cast(v_mem_mb * 0.004 as int));

SP_SET_PARA_VALUE(2,‘SESS_POOL_SIZE‘,      cast(v_mem_mb * 0.004 as int));

SP_SET_PARA_VALUE(2,‘CACHE_POOL_SIZE‘,     cast(v_mem_mb * 0.016 as int));--SQL缓冲池大小，以兆为单位。

SP_SET_PARA_VALUE(2,‘USE_PLN_POOL‘,        1); --启用执行计划缓存，默认开启

SP_SET_PARA_VALUE(2,‘OLAP_FLAG‘,           2); --统计分析型库设置为1

SP_SET_PARA_VALUE(2,‘SVR_LOG‘,             0); --关闭SQL日志记录

SP_SET_PARA_VALUE(2,‘SORT_BUF_SIZE‘,       10);--数据量大，且排序sql较多，可调大该参数，单位Mb

SP_SET_PARA_VALUE(2,‘TEMP_SIZE‘,           1024);--默认创建的临时表空间大小，以兆为单位

SP_SET_PARA_VALUE(2,‘MAX_SESSIONS‘,        500); --最大会话连接数

SP_SET_PARA_VALUE(2,‘MAX_SESSION_STATEMENT‘, 1000); --单个会话上允许同时打开的语句句柄最大数

SP_SET_PARA_VALUE(2,‘DICT_BUF_SIZE‘,       50); --字典缓冲区大小，以兆为单位

end;

-- 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL

SP_SET_PARA_VALUE(2,‘ COMPATIBLE_MODE‘,        2);

ALTER  USER SYSDBA IDENTIFIED BY "Dameng_admin";  --修改系统默认用户的密码

CREATE USER DMDBA  IDENTIFIED BY "Dameng_dba"; --增加管理员用户

GRANT DBA TO DMDBA;

--根据项目和用户实际情况定义一个缩写

CREATE USER TYYWDBA IDENTIFIED BY "Tyyw2_dba"; --增加用户管理员用户

GRANT DBA TO TYYWDBA;

--根据实际情况创建一个用户默认的表空间

CREATE TABLESPACE TS_TYYW2 DATAFILE ‘TS_TYYW2_01.DBF‘ SIZE 128;

ALTER TABLESPACE TS_TYYW2 ADD DATAFILE ‘TS_TYYW2_02.DBF‘ SIZE 128;

--根据迁移或者用户需求以及项目定义一个普通用户

CREATE USER TYYW2 IDENTIFIED BY "TYYW2_1234" DEFAULT TABLESPACE TS_TYYW2;  --增加普通用户，并使用默认表空间

GRANT RESOURCE,PUBLIC TO TYYW2;