Oracle Database 监控：zabbix（二）

西大

[root@amb01 ~]# vi /opt/zdba/conf/config.pl
{
  zabbix => {
    host    => '192.168.40.35', --zabbix server
    port    => 10051,
    timeout => 30,
  },
  daemon => {
    sleep      => 60,
    split_logs => 1
  },
  db => {
    default => {
      user       => 'zabbix',
      pass       => 'zabbix',
      query_list => 'query.props.pl',
      sleep      => 60,
      retry_step => 1,
    },
    list   => [qw|amb01|],
    amb01 => {
      dsn        => 'DBI:Oracle:host=192.168.40.40;port=1521;sid=hxdb1',
      query_list => [qw|query.props.pl|],
      sleep      => 60,
    },
  },
}
[root@amb01 ~]# vi /opt/zdba/conf/query.props.pl
  list => [
        'dbversion',            'dbuptime',
        'dbsizetotal',          'dbsizeused',
        'dblogswitch',          'dbredosize',
        ......略
  ],
    dbversion => {
    query => q{
        select banner
        from v$version
        where banner like '%Oracle Database%'
    },
  },
   dbuptime => {
    query => q{
        select (sysdate - startup_time) * 86400
        from v$instance
    },
  },
   dbsizetotal => {
    query => q{
        select sum(bytes) from dba_data_files
    },
  },
  dbsizeused => {
    query => q{
        select sum((a.bytes-f.bytes)) from dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
        where d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
        and not (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
    },
  },
  dblogswitch => {
    query => q{
        select count(*)
        from v$log_history
        where to_char(first_time) >= to_char(sysdate)
    },
  },
   dbredosize => {
    query => q{
        select value from v$sysstat
        where name='redo size'
    },
  },
......略
  discovery => {
    rule => {
      tablespaces => {
        query => q{
            select name ts from v$tablespace
        },
        keys => ['TS'],
      },
    },
    item => {
      ts_usage_pct => {
        query => q{
            select d.tablespace_name ts,
       to_char (nvl ((a.bytes - nvl (f.bytes, 0)) / a.bytes * 100, 0),
                '990.00'
               ) pct
  from sys.dba_tablespaces d,
       (select   tablespace_name, sum (bytes) bytes
            from dba_data_files
        group by tablespace_name) a,
       (select   tablespace_name, sum (bytes) bytes
            from dba_free_space
        group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = f.tablespace_name(+)
   and not (d.extent_management like 'LOCAL' and d.contents like 'TEMPORARY')
union all
select d.tablespace_name ts,
       to_char (nvl (t.bytes / a.bytes * 100, 0), '990.00') pct
  from sys.dba_tablespaces d,
       (select   tablespace_name, sum (bytes) bytes
            from dba_temp_files
        group by tablespace_name) a,
       (select   tablespace_name, sum (bytes_cached) bytes
            from v$temp_extent_pool
        group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = t.tablespace_name(+)
   and d.extent_management like 'LOCAL'
   and d.contents like 'TEMPORARY'
        },
        keys => { TS => 'PCT' }
      },
      ts_usage_bytes => {
        query => q{
            select
    d.tablespace_name ts
  , nvl(a.bytes - nvl(f.bytes, 0), 0) bytes
from
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_data_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes) bytes
      from dba_free_space
      group by tablespace_name
    ) f
where
      d.tablespace_name = a.tablespace_name(+)
  and d.tablespace_name = f.tablespace_name(+)
  and not (
    d.extent_management like 'LOCAL'
    and
    d.contents like 'TEMPORARY'
  )
union all
select
    d.tablespace_name ts
  , nvl(t.bytes, 0) bytes
from
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_temp_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes_cached) bytes
      from v$temp_extent_pool
      group by tablespace_name
    ) t
where
      d.tablespace_name = a.tablespace_name(+)
  and d.tablespace_name = t.tablespace_name(+)
  and d.extent_management like 'LOCAL'
  and d.contents like 'TEMPORARY'
        },
        keys => { TS => 'BYTES' }
      },
    },
  },
}　　zabbix server item