1. 操作系统检查项
   1.1 检查透明大页是否关闭

grep -i huge /proc/meminfo
cat /sys/kernel/mm/transparent_hugepage/enabled

1.2 系统空间使用率

df -h

1.3 局部负载

ps -ef |grep dms
top -Hp 对应DMPID

1.4 内存负载

vmstat 1 11（根据实际情况修改秒数）

1.5 磁盘负载

sar -d 1 11（根据实际情况修改秒数）

2. 数据库检查项
   2.1 数据库服务日志检查

数据库服务日志记录了数据库服务启动，刷检查点，写归档、刷盘等一系列实例的运行过程，按月生成，一般默认命名为 dm_实例名称_月份.log。针对该日志的分析，可了解以上过程的运行状态，通过检查点刷盘，也可以分析业务的繁忙程度，作为调整内存、检查点参数的依据。通过 grep 筛选检查具有 ERROR 或者 FATAL 关键字的日志
tail -500f dm_DMSERVER_XXX.log

[/dm8/log]cat XXX.log | grep ERROR
[/dm8/log]cat XXX.log | grep FATAL

DMAP 进程日志
dmap 日志是记录 DMAP 插件的运情况的日志，DMAP 日志的位置和数据库运行日志的位置相同，命名方式为 dm_DMAP_日期.log，如下图所示：

数据库备份日志
数据库备份日志，是记录了是数据库备份情况的日志，主要用于记录数据库备份的过程信息，判断备份是否完成的日志，日志存放的位置和数据库运行日志相同，命名格式为 dm_BAKES_日期.log，如下图所示：

数据守护集群日志检查
数据守护集群，由至少两台数据库服务器组成相比单实例数据库多出了守护进程日志需要检查，并且集群中的每一个节点的数据库日志，守护进程日志都要检查。
数据库守护进程日志：
数据守护进程日志，主要记录守护进程对数据库进程运行情况的监控信息和守护集群之间的内部通讯信息，守护进程日志的命名方式为 dm_watcher_日期.log，

2.2 数据库状态收集

数据库基本信息收集

select ‘版本’ as 名称,SVR_VERSION as 信息 from v$instance union all
select ‘数据库名’ as 名称,CUR_DATABASE() union all
select ‘实例名’,INSTANCE_NAME from v$instance union all
select ‘永久魔术值’,to_char(PERMANENT_MAGIC()) union all
select ‘簇大小’,SF_GET_EXTENT_SIZE ()||‘页’ union all
select ‘页大小’,PAGE()/1024||‘K’ union all
select ‘字符集’,DECODE(UNICODE,‘0’,‘GB18030’,‘1’,‘UTF-8’,‘2’,‘EUC-KR’) union all
select ‘大小写’,DECODE(SF_GET_CASE_SENSITIVE_FLAG,‘0’,‘不敏感’,‘1’,‘敏感’) union all
select ‘空格填充’,DECODE(BLANK_PAD_MODE,‘0’,‘否’,‘1’,‘是’) union all
select ‘日志大小’,MAX(RLOG_SIZE/1024/1024)||‘MB’ from v$rlogfile union all
select ‘归档状态’,DECODE(ARCH_MODE,‘Y’,‘启用’,‘N’,‘未启用’) from v$database union all
select ‘创建时间’, cast(CREATE_TIME as varchar(50)) from v$database;

实例基本信息

SELECT B.SVR_VERSION AS “数据库版本”, C.DB_MAGIC AS “数据库MAGIC”,A.NAME AS “数据库名”,B.INSTANCE_NAME AS “实例名”,B.STATUS$ AS “系统状态”,B.MODE$ AS “模式”,DECODE(A.ARCH_MODE,‘Y’,‘是’,‘N’,‘否’) AS “是否归档”,B.START_TIME AS “服务启动时间” FROM V$DATABASE A,V$INSTANCE B,V$RLOG C;

查询LICENSE到期时间

SELECT SERIES_NO AS “序列号”,CHECK_CODE AS “校验码”,DECODE(SERVER_SERIES,‘P’,‘个人版’,‘S’,‘标准版’,‘E’,‘企业版’,‘A’,‘安全版’,‘D’,‘开发版’) as “系列名称”,DECODE(SERVER_TYPE,‘1’,‘正式版’,‘2’,‘测试版’,‘3’,‘试用版’) as “版本类型”,EXPIRED_DATE as “到期时间”,AUTHORIZED_CUSTOMER AS “最终用户”,PROJECT_NAME AS “项目名称”,OS_TYPE AS “授权操作系统”,CPU_TYPE AS “授权CPU类型”,MAX_CPU_NUM AS “授权CPU个数”,DECODE(CLUSTER_TYPE,‘0000’,‘无’,‘0001’,‘DSC’,‘0010’,‘RWC’,‘0011’,‘RWC、DSC’,‘0100’,‘MPP’,‘0101’,‘MPP、DSC’,‘0110’,‘MPP、RWC’,‘0111’,‘MPP、RWC、DSC’,‘1000’,‘DW’,‘1001’,‘DW、DSC’,‘1010’,‘DW、RWC’,‘1011’,‘DW、RWC、DSC’,‘1100’,‘DW、MPP’,‘1101’,‘DW、MPP、DSC’,‘1110’,‘DW、MPP、RWC’,‘1111’,‘DW、MPP、RWC、DSC’) as “授权集群”,EXPIRED_DATE AS “有效期”,CONCURRENCY_USER_NUMBER AS “并发数” FROM V$LICENSE;

所有参数信息
SELECT PARA_NAME , PARA_VALUE ,
MIN_VALUE , MAX_VALUE , MPP_CHK ,SESS_VALUE , FILE_VALUE, DESCRIPTION , PARA_TYPE
FROM V$DM_INI

查询REDO信息
SELECT CLIENT_PATH AS “日志名”,PATH AS “路径”,(RLOG_SIZE/1024/1024) AS “文件大小M”,CREATE_TIME AS “创建时间” FROM V$RLOGFILE;

归档日志检查

SELECT CUR_LSN AS “当前LSN”,FILE_LSN AS “已经刷到盘上的LSN”,FLUSHING_PAGES AS “正在刷盘总页数”,(TOTAL_SPACE/1024/1024) AS “归档日志总空间M”,(FREE_SPACE/1024/1024) AS “归档日志剩余空间M” FROM V$RLOG;

查询表空间信息

DECLARE
K INT:=(SELECT cast(PAGE()/1024 as varchar));

BEGIN
SELECT
F.“PATH” 数据文件 ,
F.CLIENT_PATH,
G.NAME 所属表空间,
F.TOTAL_SIZE*K/1024 ||‘M’ 文件大小 ,
(CASE F.AUTO_EXTEND WHEN ‘1’ THEN ‘是’ ELSE ‘否’ END) 自动扩容,
(CASE F.NEXT_SIZE WHEN ‘0’ THEN ‘默认值’ ELSE F.NEXT_SIZE||‘M’ END) 下次扩容值,
F.MAX_SIZE||‘M’ 文件最大值
FROM SYS.“V$DATAFILE” F LEFT JOIN SYS.V$TABLESPACE G ON F.GROUP_ID=G.ID;
END;
/

数据文件信息

SELECT PATH as 文件路径,(TOTAL_SIZEPAGE/1024/1024)as 文件大小MB,(FREE_SIZEPAGE/1024/1024)as 剩余大小MB,(CAST((TOTAL_SIZE-FREE_SIZE)*100/TOTAL_SIZE AS NUMERIC(2,0))||‘%’) as 使用比例,DECODE(AUTO_EXTEND,‘0’,‘关闭’,‘1’,‘打开’) as 自动扩展,NEXT_SIZE as 扩充尺寸MB,MAX_SIZE as 扩充上限MB,CREATE_TIME as 创建时间,MODIFY_TIME as 修改时间 FROM V$DATAFILE;

ASM检查

SELECT GROUP_ID , GROUP_NAME ,N_DISK , AU_SIZE , EXTENT_SIZE,TOTAL_SIZE , FREE_SIZE ,TOTAL_FILE_NUM FROM V$ASMGROUP;

SELECT GROUP_ID , DISK_ID ,
DISK_NAME , DISK_PATH , SIZE ,FREE_AUNO , CREATE_TIME , MODIFY_TIME
FROM V$ASMDISK;

SELECT FILE_ID , TYPE , PATH ,SIZE_BYTES , SIZE_TOTAL , CREATE_TIME , MODIFY_TIME,GROUP_ID , DISK_ID ,
DISK_AUNO , AU_OFFSET
FROM V$ASMFILE;

会话统计信息
SELECT STATE AS 状态,CLNT_IP AS 连接IP,COUNT(*)AS 数量 FROM V$SESSIONS GROUP BY STATE,CLNT_IP ORDER BY STATE;

当前会话信息

SELECT SESS_ID AS “会话ID”,DECODE(STATE,‘CREATE’,‘创建’,‘STARTUP’,‘启动’,‘IDLE’,‘空闲’,‘ACTIVE’,‘活动’,‘WAIT’,‘等待’,‘UNKNOWN’,‘未知’) AS “会话状态”,CREATE_TIME AS “会话创建时间”,TRX_ID AS “事务ID”,SQL_TEXT AS “SQL”,USER_NAME AS “当前用户”,CURR_SCH AS “当前模式”,CLNT_TYPE AS “连接类型”,DECODE(AUTO_CMT,‘Y’,‘是’,‘N’,‘否’) AS “是否自动提交”,DECODE(DDL_AUTOCMT,‘Y’,‘是’,‘N’,‘否’) AS “DDL是否自动提交”,APPNAME AS “连接程序名”,CLNT_IP AS “客户机IP”,CLNT_HOST AS “客户机名”,OSNAME AS “客户机系统” FROM V$SESSIONS;

等待事件历史

SELECT THREAD_ID AS “线程ID”,TRX_ID AS “事务ID”,WAIT_CLASS AS “等待类型号”,WAIT_OBJECT AS “等待对象”,WAIT_START AS “等待开始时间”,TRUNC(WAIT_TIME,6)/1000000 AS “等待时间” FROM V$WAIT_HISTORY;

查询死锁历史

SELECT SESS_ID AS “会话ID”,TRX_ID AS “事务ID”,SQL_TEXT AS “产生死锁的SQL”,HAPPEN_TIME AS “死锁发生时间” FROM V$DEADLOCK_HISTORY;

锁情况汇总

SELECT STATTIME , WT_TRXID ,
BLK_TRXID , BLOCKED , WT_TABLE , WT_SESS , BLK_SESS ,
WT_USER_NAME , BLK_USER_NAME ,
SQL_TEXT , CLNT_IP , SS
FROM (WITH TRX_TAB AS
(SELECT O1.NAME,
L1.TRX_ID
FROM V$LOCK L1,
SYSOBJECTS O1
WHERE L1.TABLE_ID = O1.ID
AND O1.ID <> 0),
TRX_SESS AS
(SELECT L.TRX_ID WT_TRXID,
L.ROW_IDX BLK_TRXID,
L.BLOCKED,
(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID = L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,
S2.SESS_ID BLK_SESS,
S1.USER_NAME WT_USER_NAME,
S2.USER_NAME BLK_USER_NAME,
S1.SQL_TEXT,
S1.CLNT_IP,
DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L,
V$SESSIONS S1,
V$SESSIONS S2
WHERE L.TRX_ID = S1.TRX_ID
AND L.ROW_IDX = S2.TRX_ID)
SELECT SYSDATE STATTIME,
*
FROM TRX_SESS
WHERE BLOCKED = 1)

阻塞情况
SELECT WT_SQL , CLNT_IP , SS , WT_TRXID , BLK_TRXID
FROM (WITH LOCKS
AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME
FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
LOCK_TR
AS (SELECT TRX_ID WT_TRXID, TID BLK_TRXID
FROM LOCKS
WHERE BLOCKED = 1),
RES
AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
T1.SQL_TEXT WT_SQL
FROM LOCK_TR S, LOCKS T1, LOCKS T2
WHERE T1.LTYPE = ‘OBJECT’
AND T1.TABLE_ID <> 0
AND T2.LTYPE = ‘OBJECT’
AND T2.TABLE_ID <> 0
AND S.WT_TRXID = T1.TRX_ID
AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID
FROM RES)

历史故障

SELECT SESS_ID AS “会话ID”,TRX_ID AS “事务ID”,DECODE(SU_FLAG,‘U’,‘用户异常’,‘S’,‘系统异常’,‘P’,‘语法异常’) AS “错误类型”,SQL_TEXT AS “错误的SQL”,ECPT_CODE AS “错误提示”,ERR_TIME AS “产生时间” FROM V$RUNTIME_ERR_HISTORY;

查询备份集

select sf_bakset_backup_dir_add(‘DISK’,‘/home/dmdba/backup’);按实际备份目录为准
SELECT BACKUP_PATH 备份目录,OBJECT_NAME 备份类型,BACKUP_TIME 备份时间 FROM V$BACKUPSET;

备份具体信息查询
SELECT DEVICE_TYPE , BACKUP_ID ,
PARENT_ID , BACKUP_NAME ,
BACKUP_PATH , decode(TYPE,0,‘基备份’,1,‘增量备份’,2,‘表备份’,3,‘归档备份’) , decode(LEVEL,0,‘联机备份’,1,‘脱机备份’) , decode(RANGE#,1,‘库备份’,2,‘表空间备份’,3,‘表级备份’,4,‘归档备份’) , OBJECT_NAME ,
OBJECT_ID , BASE_NAME , BACKUP_TIME ,DESC# , ENCRYPT_TYPE ,
COMPRESS_LEVEL , WITHOUT_LOG ,
USE_PWR , PKG_SIZE , BEGIN_LSN,END_LSN , BKP_NUM ,
DBF_NUM , PARALLEL_NUM , decode(BAKSET_TYPE,0,‘普通备份集（NORMAL）’,1,‘DDL_CLONE备份集（DDL_CLONE）’,2,‘联机拷贝备份集（COPY）’),MPP_FLAG , MIN_TRX_START_LSN , MIN_EXEC_VER , CUMULATIVE,MIN_DCT_VER , DDL_CLONE,是否有效
FROM (select top 50 *,SF_BAKSET_CHECK(DEVICE_TYPE,BACKUP_PATH) 是否有效 from V$BACKUPSET order by BACKUP_TIME desc );

查询用户信息

SELECT USER_ID AS “用户ID”,USERNAME AS “用户名”,ACCOUNT_STATUS AS “状态”,DEFAULT_TABLESPACE AS “默认表空间”,CREATED AS “创建时间”,LOCK_DATE AS “锁定开始时间”,EXPIRY_DATE AS “密码有效期截止” FROM DBA_USERS;

对象汇总

SELECT DECODE(OBJECT_TYPE,‘SCH’,‘模式’,‘TABLE’,‘表’,‘VIEW’,‘视图’,‘MATERIALIZED VIEW’,‘物化视图’,‘PROCEDURE’,‘存储过程’,‘FUNCTION’,‘函数’,‘SEQUENCE’,‘序列’,‘TRIGGER’,‘触发器’,‘PACKAGE’,‘包’,‘PACKAGE BODY’,‘包主体’,‘CLASS’,‘类’,‘SYNONYM’,‘同义词’,‘DOMAIN’,‘域’,‘TYPE’,‘自定义类型’,‘INDEX’,‘索引’,‘CONSTRAINT’,‘约束’,‘SEQUENCE’,‘序列’,‘TABLE PARTITION’,‘分区表’) AS 对象名称,OWNER AS 所属用户,COUNT(*)AS 数量 FROM DBA_OBJECTS WHERE OWNER NOT IN (‘SYS’,‘SYSTEM’,‘SYSAUDITOR’,‘SYSJOB’,‘SYSSSO’,‘CTISYS’) GROUP BY OBJECT_TYPE,OWNER ORDER BY OWNER ASC,OBJECT_TYPE DESC;

自增列信息

SELECT owner , table_name ,
COL_NAME
FROM (select b.owner,
b.table_name,
a.name COL_NAME
from SYS.SYSCOLUMNS a,
dba_tables b ,
sys.sysobjects c
where a.INFO2=1
and a.id =c.id
and c.name = b.table_name)

TOP 10 数据量表统计
–CALL SP_CREATE_SYSTEM_PACKAGES(1); --创建系统包
–CALL SP_DB_STAT_INIT (); --对库上所有表及索引 生成统计信息
select * from (SELECT TABLE_NAME AS 表名,OWNER AS 所属用户,TABLESPACE_NAME AS 所属表空间,NUM_ROWS AS 行数 FROM DBA_TABLES WHERE OWNER NOT IN (‘SYS’,‘SYSTEM’,‘SYSAUDITOR’,‘SYSJOB’,‘SYSSSO’,‘CTISYS’) AND TABLESPACE_NAME != ‘TEMP’ order by 4 desc) where rownum<10 ;

SELECT owner, segment_name,
tablespace_name , size_mb , num_rows
FROM (SELECT top 10 d.owner,
d.segment_name,
d.tablespace_name,
SUM(d.bytes) / 1024 / 1024 size_mb,
(select num_rows from dba_tables nb where table_name=d.segment_name and nb.owner=d.owner) num_rows
FROM dba_segments d
GROUP BY d.owner,
d.segment_name,
d.tablespace_name
ORDER BY size_mb DESC)

TOP 20 慢SQL
SELECT SEQ_NO, SQL_ID,
SESS_ID, SESS_SEQ, TRX_ID
THREAD_ID,TOP_SQL_TEXT,
SEC_SQL_TEXT, THRD_SQL_TEXT,START_TIME, TIME_USED,
IS_OVER, EXEC_ID, VM,STKFRM, STK_LEVEL,
BYTES_DYNAMIC_ALLOCED, BYTES_DYNAMIC_FREED,CUR_SQL_NODE, MAL_ID,
N_LOGIC_READ, N_PHY_READ,
AFFECTED_ROWS, HARD_PARSE_FLAG,
MPP_EXEC_ID
FROM (SELECT TOP 20 * FROM V$SQL_HISTORY a where a.TOP_SQL_TEXT not like ‘%textarea%’ ORDER BY TIME_USED DESC)

TOP 20 消耗内存SQL

SELECT SESS_ID ,SQL_ID
SQL_TEXT,
MEM_USED_BY_K,
FINISH_TIME ,N_RUNS ,SEQNO,
TRX_ID ,SESS_SEQ
FROM (SELECT TOP 20 * FROM V$SYSTEM_LARGE_MEM_SQLS a where a.SQL_TEXT not like ‘%textarea%’ ORDER BY MEM_USED_BY_K DESC)

TOP 20 MTAB语句

SELECT EXEC_ID,SQL_TEXT,
STK_LEVEL , CUR_SQL_NODE , OP_SEQ_NO,OP_TYPE$ , MTAB_USED_BY_M , MEM_USED_BY_M , MMT_FILE_ALLOC ,MMT_PAGES_USED , MTAB_TYPE,MAL_ID , MPP_EXEC_ID
FROM (SELECT TOP 20 * FROM V$MTAB_USED_HISTORY a where a.SQL_TEXT not like ‘%textarea%’)

TOP20 SORT 语句
SELECT EXEC_ID ,SQL_TEXT
STK_LEVEL , CUR_SQL_NODE , OP_SEQ_NO , OP_TYPE$ , N_PAGES ,
N_MERGE_SORT , SORT_BYTES ,
SORT_ROWS , MAL_ID , MPP_EXEC_ID
FROM (SELECT TOP 20 * FROM V$SORT_HISTORY a where a.SQL_TEXT not like ‘%textarea%’)

AWR 信息收集

SELECT * FROM SYS.WRM$_SNAPSHOT;
CALL SYS.AWR_REPORT_HTML(1,2,‘/dm8/backup’,‘AWR.HTML’);

清理所有的历史快照信息

DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY;

JOB情况

SELECT ID , NAME , ENABLE , USERNAME , CREATETIME ,
MODIFYTIME , DESCRIBE , LAST_TIME , NEXT_TIME , WHAT
FROM (SELECT A.ID,
A.NAME,
A.“ENABLE”,
A.USERNAME,
A.CREATETIME,
A.MODIFYTIME,
A.DESCRIBE,
B.LAST_DATE || ’ ’ || B.LAST_SEC LAST_TIME,
B.NEXT_DATE || ’ ’ || B.NEXT_SEC NEXT_TIME,
B.WHAT
FROM SYSJOB.SYSJOBS A,
SYSJOB.DBA_JOBS B
WHERE A.ID = B.JOB)

JOB运行情况

SELECT EXEC_ID , NAME ,
STEPNAME , START_TIME , END_TIME,ERRTYPE , ERRCODE ,
ERRINFO
FROM (SELECT EXEC_ID,
NAME,
STEPNAME,
START_TIME,
END_TIME,
ERRTYPE,
ERRCODE,
ERRINFO
FROM (SELECT EXEC_ID,
NAME,
STEPNAME,
START_TIME,
END_TIME,
ERRTYPE,
ERRCODE,
ERRINFO,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY EXEC_ID DESC) RN
FROM SYSJOB.SYSSTEPHISTORIES2)
WHERE RN <= 10)

DBLINK汇总

SELECT OWNER , DB_LINK ,
USERNAME , HOST , CREATED
FROM DBA_DB_LINKS