Oracle Database 监控：zabbix（二）
# 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 => ,
amb01 => {
dsn => 'DBI:Oracle:host=192.168.40.40;port=1521;sid=hxdb1',
query_list => ,
sleep => 60,
},
},
}
# 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
http://s1.运维网.com/images/20180919/1537325795928200.png
http://s1.运维网.com/images/20180919/1537325884358075.png
http://s1.运维网.com/images/20180919/1537325890605673.png
http://s1.运维网.com/images/20180919/1537325897527065.png
http://s1.运维网.com/images/20180919/1537327469968618.png
http://s1.运维网.com/images/20180919/1537327479718649.png
http://s1.运维网.com/images/20180919/1537327488255766.png
http://s1.运维网.com/images/20180919/1537327495382168.png
页:
[1]