1.数据库实例监控
1.1 数据库状态检查
select STATUS$ from V$INSTANCE; --数据库实例状态
1.2 慢sql及阻塞检查
SELECT
DS.SESS_ID "被阻塞的会话ID",
DS.SQL_TEXT "被阻塞的SQL",
DS.TRX_ID "被阻塞的事务ID",
(CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
DS.CREATE_TIME "开始阻塞时间",
SS.SESS_ID "占用锁的会话ID",
SS.SQL_TEXT "占用锁的SQL",
SS.CLNT_IP "占用锁的IP",
L.TID "占用锁的事务ID"
FROM
V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
SS.TRX_ID = L.TID
WHERE
L.BLOCKED = 1

1.3 数据库日志检查
检查运行日志中是否存在 错误信息 可以根据关键字进行 搜索
Grep -iE ‘error|fatal’ DM_xxxx.log
1.4 数据库空间情况
数据库磁盘占用空间、
select (select TOTAL_SIZE from V$DATABASE) (select page())/1024/1024 as 占用大小单位MB;
表空间信息
SELECT NAME AS "NAME",
CASE TYPE$ WHEN '1' THEN 'DB类型' WHEN '2' THEN '临时表空间' END AS "TYPE",
CASE STATUS$ WHEN '0' THEN '联机' WHEN '2' THEN '脱机' WHEN '3' THEN '脱机' WHEN '4' THEN '损坏'END AS "STATUS",
TOTAL_SIZEPAGE/1024/1024 AS "TOTALSIZE",
FILE_NUM AS "FILENUM"
FROM V$TABLESPACE;

1.5 统计执行时间最长的sql
SELECT SQL_TEXT,EXEC_TIME,FINISH_TIME FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

2.数据库备份检查
2.1 数据库全量、增量，数据文件、日志文件备份运行情况
SELECT SCHE."NAME" ,
SCHE."JOBID" ,
SCHE."TYPE" ,
SCHE."FREQ_INTERVAL" ,
SCHE."FREQ_SUB_INTERVAL",
SCHE."STARTTIME" ,
USERJOB.NEXT_DATE ,
STEPS."NAME" STEPSNAME ,
STEPS."SEQNO" STEPSSEQNO,
STEPS."TYPE" STEPSTYPE ,
STEPS.COMMAND WHAT ,
STEPS.SUCC_ACTION ,
STEPS.FAIL_ACTION
FROM SYSJOB.SYSJOBSCHEDULES SCHE
LEFT JOIN SYSJOB.USER_JOBS USERJOB
ON SCHE.JOBID = USERJOB.JOB
LEFT JOIN SYSJOB.SYSJOBSTEPS STEPS
ON SCHE.JOBID = STEPS.JOBID
WHERE SCHE."TYPE" != 0
AND SCHE.VALID == 'Y'
AND USERJOB.NEXT_DATE >= TO_CHAR(SYSDATE,'YYYY-MM-DD')
ORDER BY STEPS.JOBID,
STEPS.SEQNO ASC
2.2 确保生产的备份文件是正确的大小
Du -sh /dmbak

3.主备集群监控

3.1集群状态检查
登录集群监视器查看集群状态
./dmmonitor /dm8/dmdbms/bin/dmmonitor_nor.ini
show

3.2主备集群同步延迟查看
在备机上执行以下sql语句
select LAST_CMT_TIME -APPLY_CMT_TIME from V$RAPPLY_STAT;