Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【oracle】oracle监控指标

13 Aug 2019 » zabbix

oracle监控指标

目的:归纳Oracle监控指标,用于监控数据库的日常运行。

数据库的存活情况

保证Oracle的实例是否存活。

SELECT   to_char(case when inst_cnt > 0 then 1 else 0 end,'FM99999999999999990') retvalue
FROM     (select count(*) inst_cnt FROM v$instance 
WHERE    status = 'OPEN' AND logins = 'ALLOWED' AND database_status = 'ACTIVE');

数据库的运行时间

查看数据库自上一次重启以来,运行的时间。

SELECT   to_char((sysdate-startup_time)*86400, 'FM99999999999999990') retvalue
FROM     v$instance;

数据库的可用连接数

保证Oracle数据库有可用的连接数,具体的参数和processes有关。

select trim(limit_value-current_utilization) from gv$resource_limit
where resource_name='processes';

RMAN备份情况

保证Oracle数据库的RMAN备份是否正常。其中返回值为0,代表正常。V$RMAN_STATUS监控RMAN备份情况的视图。

select trim(decode (sum(ESTADO),0,0,null,-1,1)) FROM ( 
SELECT TO_NUMBER (
            DECODE (STATUS,
                    'COMPLETED', 0,
                    'RUNNING', 0,
                    'COMPLETED WITH WARNINGS', 1,
                    'COMPLETED WITH ERRORS', 2,
                    3))
            "ESTADO",
            TO_CHAR (OPERATION)
         || '('
         || TO_CHAR (NVL (OBJECT_TYPE, '-'))
         || ') - '
         || TO_CHAR (STATUS)
         || ' - DURACION: '
         || TO_CHAR (ROUND ( (END_TIME - START_TIME) * 24 * 60, 2))
         || ' MIN'
            "DETALLE"
    FROM V$RMAN_STATUS
   WHERE START_TIME > SYSDATE -1
         AND COMMAND_ID = (  SELECT MAX (COMMAND_ID) FROM V$RMAN_STATUS)
ORDER BY ESTADO DESC, START_TIME);

Oracle数据库表空间

目的:监控数据库表空间的使用率,超过一定的阈值报警。&1,需要传参数,具体的表空间名称。

SELECT   TRIM(TRUNC ((10000 * (x.used)) / y.mbytes) / 100)  "pct_used"
FROM     (SELECT   b.tablespace_name NAME,
         (SUM (b.BYTES) / COUNT (DISTINCT a.file_id || '.' || a.block_id) - SUM (DECODE (a.BYTES, NULL, 0, a.BYTES)) / COUNT (DISTINCT b.file_id)) used,
         (SUM (DECODE (a.BYTES, NULL, 0, a.BYTES)) / COUNT (DISTINCT b.file_id)) free
FROM     SYS.dba_data_files b, SYS.dba_free_space a
WHERE    a.tablespace_name= UPPER('&1')
AND      a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, b.tablespace_name) x,
         (SELECT   c.tablespace_name NAME,
         (SUM (NVL (DECODE (c.maxbytes, 0, c.BYTES, c.maxbytes),
         c.BYTES))) mbytes
FROM     SYS.dba_data_files c
GROUP BY c.tablespace_name) y
WHERE    x.NAME = y.NAME;
--AND    x.name<>'UNDOTBS1';

数据库会话的锁的情况

DBA_WAITERS会显示所有session等待锁的情况。通过查询知道是否有session有锁的情况。

select COUNT(*)  from v$session  ,DBA_WAITERS   
where Seconds_in_wait/60>=1 
AND STATE='WAITING' 
AND SID=HOLDING_SESSION;

数据库会话的阻塞情况

gv$session_blockers展示session的阻塞情况。超过60s的监控出来,并且报警。

select count(*)
from gv$session_blockers a, v$session v
where a.blocker_sid=v.sid
and v.username is not null
and exists(select 1 from v$session v2
           where a.sid=v2.sid and v2.username is not null and SECONDS_IN_WAIT/60>=1);