● 用户管理
–修改用户密码、授权
alter user SYSDBA IDENTIFIED BY “SG-RDB123”; --修改系统默认用户的密码
create user TEST IDENTIFIED BY “SG-RDB123”; --增加管理员用户
GRANT resource TO DMDBA; --分配resource角色给TEST用户
–创建表空间、增加表空间数据文件
create tablespace “TEST” datafile ‘/opt/dm8/data/SG-RDB/TEST.DBF’ size 32 autoextend on next 100 maxsize 20480 CACHE = NORMAL;
alter tablespace “TEST” add datafile ‘/opt/dm8/data/SG-RDB/TEST2.DBF’ size 32 autoextend on next 100 maxsize 20480;
–创建用户指定表空间
create user test IDENTIFIED BY “SG-RDB123"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(“SG-RDB”,”",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_SIZEPAGE/1024/1024 FROM VTABLESPACE
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 VSQL_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 VSYSTEMINFO;
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;
