达梦DBA常用管理脚本

• 用户管理

--修改用户密码、授权
alter user SYSDBA IDENTIFIED BY "dameng123";   --修改系统默认用户的密码
create user TEST IDENTIFIED BY "dameng123";   --增加管理员用户
GRANT resource TO DMDBA;                       --分配resource角色给TEST用户 

--创建表空间、增加表空间数据文件
create tablespace "TEST" datafile '/opt/dm8/data/DAMENG/TEST.DBF' size 32 autoextend on next 100 maxsize 20480 CACHE = NORMAL;
alter tablespace "TEST" add datafile '/opt/dm8/data/DAMENG/TEST2.DBF' size 32 autoextend on next 100 maxsize 20480;

--创建用户指定表空间
create user test IDENTIFIED BY "dameng123"default tablespace TEST;
GRANT resource to test;

• 归档及备份

--开启归档
alter database mount;
alter database archivelog;
--添加归档指定路径及大小
alter database add archivelog 'dest=/opt/dm8/arch,type=local,file_size=1024,space_limit=102400';
alter database open;

--全量备份
backup database full to dmbak_full_01 bakfile '/opt/dm8/bak/dmbak_full_01.bak' compressed;
--基于备份集增量备份
backup database increment with backupdir '/opt/dm8/bak' to dmbak_increment_00 bakfile '/opt/dm8/bak/dmbak_increment_00.bak' compressed;

--使用作业备份
---开启代理作业
SP_INIT_JOB_SYS(1);
--定时每周六运行，进行全量备份
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_FULE_BAK_TIMELY','STEP_FULL_BAK',5,'01000/dm7/data/bak',0,0,0,0,'/dm7/data/job.log',1); 
call SP_ADD_JOB_SCHEDULE('JOB_FULE_BAK_TIMELY','SCHEDULE_FULL_BAK',1,2,1,64,0,'00:05:56',NULL,'2020-05-20 22:22:22',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/dm7/data/bak|/dm7/data/dm7',0,0,0,0,'/dm7/data/job.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_INCREMENT_BAK_TIMELY_NIGHT','SCHEDULE_INCREMENT_BAK',1,1,1,0,0,'02:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_INCREMENT_BAK_TIMELY_NIGHT');
--定时每周日运行，删除前30天的备份，包括全量和增量
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);',0,0,0,0,'/dm7/data/job.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_BAK_TIMELY','SCHEDULE_DEL_BAK',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_BAK_TIMELY');

--定时每周日运行，删除前30天的归档日志
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,'SP_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE-30);',0,0,0,0,'/dm7/data/job.log',1);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_ARCH_TIMELY','SCHEDULE_DEL_ARCH',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_ARCH_TIMELY');

• 统计信息

--创建视图，统计所有的模式和表
CREATE OR REPLACE VIEW V_ALL_TAB As 
SELECT 
    A.NAME SCH_NAME,
    B.NAME TAB_NAME 
FROM  
    SYS.SYSOBJECTS A JOIN SYS.SYSOBJECTS B 
ON
    A.ID = B.SCHID
AND A.TYPE$ =  'SCH'
AND A.NAME NOT IN ('SYS','CTISYS','SYSAUDITOR','SYSSSO','SYSJOB','SYSDBA')
AND B.SUBTYPE$ = 'UTAB'
AND B.PID = -1
AND B.NAME NOT LIKE '%$AUX' ORDER BY 1,2;

--创建记录统计信息的表
CREATE TABLE T_STAT_TAB
(
SCHE_NAME VARCHAR(20),
TAB_NAME VARCHAR(50),
TAB_CNT INT,
STAT_TIME DATETIME(0) DEFAULT SYSDATE
);
--存储过程，统计所有表的数量，或者指定用户下的表的数量
CREATE OR REPLACE PROCEDURE P_STAT_TABLE (SCHEMA_NAME VARCHAR(50)) AS
    V_CNT INT; 
BEGIN
     IF UCASE(SCHEMA_NAME)== 'ALL' THEN
         SCHEMA_NAME := '';
     END IF;
     FOR REC IN (SELECT SCH_NAME,TAB_NAME FROM V_ALL_TAB WHERE SCH_NAME LIKE '%'||SCHEMA_NAME||'%') LOOP
         BEGIN
             EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "'||REC.SCH_NAME||'"."'||REC.TAB_NAME||'"' INTO V_CNT;
         EXCEPTION WHEN OTHERS THEN
             V_CNT := -1;
         END;
         EXECUTE IMMEDIATE 'INSERT INTO T_STAT_TAB(SCH_NAME,TAB_NAME,TAB_CNT)
             VALUES('''||REC.SCH_NAME||''','''||REC.TAB_NAME||''','||V_CNT||')';
     END LOOP;
     COMMIT;
END;
P_START_TABLE('ALL');
--定时每周日运行，统计本周所有表数据
call SP_CREATE_JOB('JOB_START_TABLE_COUNT_TIMELY',1,0,'',0,0,'',0,'定时统计表数据量');
call SP_JOB_CONFIG_START('JOB_START_TABLE_COUNT_TIMELY');
call SP_ADD_JOB_STEP('JOB_START_TABLE_COUNT_TIMELY','STEP_START_TABLE_COUNT',0,'P_START_TABLE("ALL");',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOB_START_TABLE_COUNT_TIMELY','SCHEDULE_START_TABLE_COUNT',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOB_START_TABLE_COUNT_TIMELY');
--创建统计每周总量和增量
CREATE OR REPLACE VIEW V_SEL_TAB_SUM
AS
SELECT *,TOTAL_ROWS - PRV_TOTAL_ROWS INCREMENT_ROWS
FROM(
    SELECT STAT_TIME,TOTAL_ROWS,LEAD(TOTAL_ROWS) OVER(ORDER BY STAT_TIME DESC) PRV_TOTAL_ROWS
    FROM (
        SELECT LEFT(STAT_TIME,10) STAT_TIME,SUM(TAB_CNT) TOTAL_ROWS
        FROM T_STAT_TAB
        GROUP BY LEFT(STAT_TIME,10)
        )
    )
WHERE PRV_TOTAL_ROWS IS NOT NULL;

--查看每周总量和增量
SELECT * FROM V_SEL_TAB_SUM;

--统计每个表每周总量和增量
CREATE OR REPLACE VIEW V_SEL_TAB_INCR
AS
SELECT STAT_TIME,TAB_ROWS,PRV_TAB_ROWS,TAB_ROWS - PRV_TAB_ROWS INCREMENT_ROWS,SCHE_NAME,TAB_NAME
FROM(
    SELECT STAT_TIME,TAB_ROWS,LEAD(TAB_ROWS) OVER(PARTITION BY SCHE_NAME,TAB_NAME ORDER BY STAT_TIME DESC) PRV_TAB_ROWS,SCHE_NAME,TAB_NAME
    FROM (
        SELECT LEFT(STAT_TIME,10) STAT_TIME,TAB_CNT TAB_ROWS,SCHE_NAME,TAB_NAME
        FROM T_STAT_TAB
        )
    )
WHERE PRV_TAB_ROWS IS NOT NULL;
--查看每个表每周总量和增量
SELECT * FROM V_SEL_TAB_INCR;

–查看指定表每周总量和增量
SELECT * FROM V_SEL_TAB_INCR WHERE TAB_NAME='STAT_TAB';
–查看每个表最新周总量和增量
SELECT * FROM V_SEL_TAB_INCR
WHERE STAT_TIME=(
    SELECT MAX(STAT_TIME)
    FROM V_SEL_TAB_INCR)
ORDER BY TAB_ROWS DESC;
–查看增量较大的表数据信息
SELECT * FROM V_SEL_TAB_INCR ORDER BY INCREMENT_ROWS DESC NULLS LAST;
–统计数据库存储空间和表空间存储空间
CREATE TABLE T_STAT_DB_SIZE
(
TBS_NAME VARCHAR(20),
TOTAL_SIZE_MB INT,
STAT_TIME DATETIME(0) DEFAULT SYSDATE
);

CREATE OR REPLACE PROCEDURE P_STAT_SIZE AS 
BEGIN
INSERT INTO T_STAT_DB_SIZE(TAB_NAME,TOTAL_SIZE_MB)
    SELECT 'DB' TBS_NAME,TOTAL_SIZE*PAGE/1024/1024 FROM V$DATABASE;
INSERT INTO T_STAT_DB_SIZE(TBS_NAME,TOTAL_SIZE_MB)
    SELECT ISNULL(NAME,'ALL_TBS') NAME,SUM(TOTAL_SIZE*PAGE/1024/1024) TOTAL_SIZE_MB
    FROM V$TABLESPACE 
    GROUP BY ROLLUP(NAME);
END;
CALL SP_STAT_SIZE;
–定时每周日运行，统计本周表空间存储
call SP_CREATE_JOB('JOP_STAT_DB_SIZE_TIMELY',1,0,'',0,0,'',0,'定时统计存储容量');
call SP_JOB_CONFIG_START('JOP_STAT_DB_SIZE_TIMELY');
call SP_ADD_JOB_STEP('JOP_STAT_DB_SIZE_TIMELY','STEP_STAT_DB_SIZE',0,'P_STAT_SIZE',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOP_STAT_DB_SIZE_TIMELY','SCHEDULE_STAT_DB_SIZE',1,2,1,1,0,'00:00:00',NULL,'2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOP_STAT_DB_SIZE_TIMELY');
—统计每个表空间增量
CREATE OR REPLACE VIEW V_SEL_DB_INCR
AS 
--统计每个表空间增量
SELECT STAT_TIME,TBS_NAME,TOTAL_SIZE_MB,PRV_TOTAL_SIZE_MB,
    TOTAL_SIZE_MB - PRV_TOTAL_SIZE_MB INCREMENT_ROWS
FROM (
    SELECT STAT_TIME,TBS_NAME,TOTAL_SIZE_MB,
        LEAD(TOTAL_SIZE_MB) OVER(PARTITION BY TBS_NAME ORDER BY STAT_TIME DESC) PRV_TOTAL_SIZE_MB
    FROM(
        SELECT LEFT(STAT_TIME,19) STAT_TIME,TBS_NAME,TOTAL_SIZE_MB
        FROM T_STAT_DB_SIZE)
    )
WHERE PRV_TOTAL_SIZE_MB IS NOT NULL;
--统计每个表空间增量
SELECT * FROM V_SEL_TAB_INCR;
--统计会话并发情况
CREATE TABLE T_STAT_SESS_COUNT
(
SESS_TYPE VARCHAR(20),
SESS_COUNT INT,
SESS_TIME DATETIME(0)
);
--统计会话并发情况
CREATE OR REPLACE PROCEDURE P_STAT_SESS(STAT_CNT INT) AS 
BEGIN
INSERT INTO T_STAT_SESS_COUNT
    SELECT 'SESS' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(CREATE_TIME,19) SESS_TIME
    FROM V$SESSION_HISTORY
    WHERE CREATE_TIME > (
        SELECT IFNULL(MAX(SESS_TIME),'2020-01-01 01:01:01')
        FROM T_STAT_SESS_COUNT
        WHERE SESS_TYPE = 'SESS')
    GROUP BY LEFT(CREATE_TIME,19)
    HAVING COUNT(*) > STAT_CNT;

INSERT INTO T_STAT_SESS_COUNT
    SELECT 'SQL' SESS_TYPE,COUNT(*) SESS_COUNT,LEFT(START_TIME,19) SESS_TIME
    FROM V$SQL_HISTORY
    WHERE START_TIME > (
        SELECT IFNULL(MAX(SESS_TIME),'2020-01-01 01:01:01')
        FROM T_STAT_SESS_COUNT
        WHERE SESS_TYPE = 'SQL')
    GROUP BY LEFT(START_TIME,19)
    HAVING COUNT(*) > STAT_CNT;
END;
CALL P_STAT_SESS(2);
--定时每小时运行，统计时间段内并发情况
call SP_CREATE_JOB('JOP_STAT_SESS_COUNT_TIMELY',1,0,'',0,0,'',0,'定时统计会话并发量');
call SP_JOB_CONFIG_START('JOP_STAT_SESS_COUNT_TIMELY');
call SP_ADD_JOB_STEP('JOP_STAT_SESS_COUNT_TIMELY','STEP_STAT_SESS_SCOUNT',0,'CALL P_STAT_SESS(5);',0,0,0,0,'',1);
call SP_ADD_JOB_SCHEDULE('JOP_STAT_SESS_COUNT_TIMELY','SCHEDULE_STAT_SESS_COUNT',1,1,1,0,60,'00:00:00','23:59:59','2020-05-20 22:22:22',NULL,'');
call SP_JOB_CONFIG_COMMIT('JOP_STAT_SESS_COUNT_TIMELY');
--查询会话并发统计，按并发量排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SESS' ORDER BY SESS_COUNT DESC;
--查询会话并发统计，按并发时间排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SESS' ORDER BY SESS_TIME DESC;
--按照分钟统计会话并发
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SESS'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;
--查询SQL并发统计，按并发量排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SQL' ORDER BY SESS_COUNT DESC;
--查询SQL并发统计，按并发时间排序。
SELECT * FROM T_STAT_SESS_COUNT WHERE SESS_TYPE = 'SQL' ORDER BY SESS_TIME DESC;
--按照分钟统计SQL并发
SELECT LEFT(SESS_TIME,16),SUM(SESS_COUNT)
FROM T_STAT_SESS_COUNT
WHERE SESS_TYPE='SQL'
GROUP BY LEFT(SESS_TIME,16)
ORDER BY 2 DESC ,1 DESC;

• 调整优化基础参数

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);
--修改内存
SP_SET_PARA_VALUE(2,'BUFFER_POOLS',101);
SP_SET_PARA_VALUE(2,'BUFFER',                cast(v_mem_mb * 0.6 as INT));
SP_SET_PARA_VALUE(2,'MAX_BUFFER',            cast(v_mem_mb * 0.7 as INT));
SP_SET_PARA_VALUE(2,'HJ_BUF_GLOBAL_SIZE',    cast(v_mem_mb * 0.18 as INT));
SP_SET_PARA_VALUE(2,'HJ_BUF_SIZE',            cast(v_mem_mb * 0.0018 as INT));
SP_SET_PARA_VALUE(2,'HAGR_BUF_GLOBAL_SIZE', cast(v_mem_mb * 0.12 as INT));
SP_SET_PARA_VALUE(2,'HAGR_BUF_SIZE',        cast(v_mem_mb * 0.0024 as INT));
END;