前面介绍了利用Orabbix监控了参考zabbix通过Orabbix监控oracle数据库这里我们原先的模板中进行了修改使用自动发现功能实现监控tablespace的使用情况。
1. 在被监控的数据库机器上创建3个shell脚本
1.1 得到表空间的使用情况
[oracleoracledb shellScripts]$ cat /home/oracle/shellScripts/query_tablespace_usage.sh
source /home/oracle/.bash_profile
sqlplus -s zabbix/zabbix > /tmp/tablespace.log<
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
col "Size (M)" for a15
col "Used (M)" for a15
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),99,999,990) "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,999,999,999) "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 990.00) "Used %"
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.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),99,999,999) "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,999,999,999) "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), 990.00) "Used %" 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
ORDER BY 7;
EOF
通过这个脚本我们可以得到表空间的使用情况并记录在/tmp/tablespace.log中
1.2 通过脚本取得表空间的名字并转换成json格式的(因为zabbix的自动发现功能获取的数据类型是JSON格式的)
[oracleoracledb ~]$ cat /usr/local/zabbix-3.6.2/scripts/oracle_discovery.sh
#!/bin/bash
TABLESPACEcat /tmp/tablespace.log |awk {print$2}|awk NR>3{print}
COUNTecho "$TABLESPACE" |wc -l
INDEX0
echo {"data":[
echo "$TABLESPACE" | while read LINE; do
echo -n {"{#TABLENAME}":"$LINE"}
INDEXexpr $INDEX 1
if [ $INDEX -lt $COUNT ]; then
echo ,
fi
done
echo ]}
执行脚本可以得到
[oracleoracledb ~]$ /usr/local/zabbix-3.6.2/scripts/oracle_discovery.sh
{"data":[
{"{#TABLENAME}":"COSTCTL_TBS_TEMP"},
{"{#TABLENAME}":"PDMS_DATA_TEMP"},
{"{#TABLENAME}":"UNDOTBS1"},
.....................
....................
{"{#TABLENAME}":"TBLSPACE_PAYMENT"},
{"{#TABLENAME}":"TBLSPACE_CLAIM"},
{"{#TABLENAME}":"TBLSPACE_PAYMENTIDX"},
{"{#TABLENAME}":"TEMP"},
{"{#TABLENAME}":"TBLSPACE_TEMP02"},
{"{#TABLENAME}":"SYSTEM"}]}
1.3 创建脚本获取/tmp/tablespace.log中的最后3列
[oracleoracledb ~]$ cat /usr/local/zabbix-3.6.2/scripts/oracle_
oracle_check.sh oracle_discovery.sh
[oracleoracledb ~]$ cat /usr/local/zabbix-3.6.2/scripts/oracle_check.sh
#!/bin/bash
EQ_DATA"$2"
ZBX_REQ_DATA_TAB"$1"
SOURCE_DATA/tmp/tablespace.log
case $2 in
maxmb)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk {print $5*1024*1024};;
used)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk {print $6*1024*1024};;
autopercent)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk {print $7};;
*)
echo $ERROR_WRONG_PARAM; exit 1;;
esac
exit 0
注意给/usr/local/zabbix-3.6.2/scripts这个目录执行权限
2. zabbixagent配置文件添加自定义监控key
在/usr/local/zabbix-3.6.2/etc/zabbix_agentd.conf文件末尾添加
UserParameterora.tab.discovery,/usr/local/zabbix-3.6.2/scripts/oracle_discovery.sh
UserParametertablespace[*],/usr/local/zabbix-3.6.2/scripts/oracle_check.sh $1 $2
3. 将query_tablespace_usage.sh放到系统自动任务中每隔一分钟执行。
*/1 * * * * /home/oracle/shellScripts/query_tablespace_usage.sh > /home/oracle/shellScripts/oracle_cron.sh.log 2>lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS autopercent]"
94.55
[rootlanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS maxmb]"
70
[rootlanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS used]"
66
[rootlanmp bin]# pwd
/usr/local/zabbix-3.2.6/bin
5. 添加发现规则
之前安装过orabbix,直接在Template_Oracle下添加发现规则
5.1 创建发现规则
模板-->Template_Oracle-->创建自动发现规则
名称tablespacediscovery
键值ora.tab.discovery
数据更新间隔60
5.2 给发现规则创建监控项原型
名称{#TABLENAME}autopercent
键值tablespace[{#TABLENAME},autopercent]
新的应用集tablespace
同样的方法创建剩下的两个监控原型
注意autopercent的单位是浮点型
5.3 创建图形原型
名称{#TABLENAME}monitor
整体的发现规则
过一分钟左右就会产生监控到的数据了
图形界面
