我们需要把脚本放到oracle用户的crontab计划任务表中,让脚本在后台每5分钟执行一次,一定要保证计划任务能正常按计划执行,否则这可能会被监控欺骗(监控端获取到的数据一直保持不变)。
hmracdb1:~ # crontab -u oracle -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXXj0XHiH installed on Thu Mar 23 11:38:37 2017)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)
*/5 * * * * /home/oracle/bin/check_tablespace.sh 手动执行SQL语句获取表空间使用率
SQL> set linesize 200
select ff.s tablespace_name,
ff.b total,
(ff.b - fr.b)usage,
fr.b free,
round((ff.b - fr.b) / ff.b * 100) || '% ' usagep
from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_data_files
group by tablespace_name) ff,
(select tablespace_name s, sum(bytes) / 1024 / 1024 b
from dba_free_space
group by tablespace_name) fr
12 where ff.s = fr.s;
TABLESPACE_NAME TOTAL USAGE FREE USAGEP
------------------------------------------------------------ ---------- ---------- ---------- ------------------------------------------------------------------------------------
TEST11 50 1 49 2%
SYSAUX 2048 851.375 1196.625 42%
UNDOTBS1 2048 12.6875 2035.3125 1%
USERS 1024 1 1023 0%
SYSTEM 2048 689.625 1358.375 34%
UDB_SPACE 40960 9286.125 31673.875 23%
UNDOTBS2 2048 12.8125 2035.1875 1%
7 rows selected.
下面编写一个zabbix自动发现oracle表空间的脚本discovery_oracle_tablespace.sh
hmracdb1:~ # cat discovery_oracle_tablespace.sh
#!/bin/bash
#zabbix discovery oracle tablespace
table_spaces=(`cat /tmp/ora_tablespace.txt | sed -e "1,3d" -e "/^$/d" -e "/selected/d" | awk '{print $1}'`)
length=${#table_spaces[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i/dev/null;then
echo 1
else
echo 0
fi
}
case $CEHCK_TYPE in
pre)
usagepre ;;
fre)
available ;;
check)
check ;;
*)
echo -e "Usage: $0 [pre|fre|check] [TABLESPACE_NAME]"
esac
确认脚本输出的值为zabbix监控所取的数值
hmracdb1:~ # ./tablespace_check.sh pre SYSTEM
34
hmracdb1:~ # ./tablespace_check.sh fre SYSTEM
1424359424hmracdb1:~ # ./tablespace_check.sh check SYSTEM
1 将脚本都保存到$ZABBIX_HOME/scpirts目录下,然后配置UserParameter参数,增加监控key,key名称分别为discovery.oracle.tablespace、tablespace.check,在以下配置文件末尾增加两行
hmracdb1:/opt/zabbix/scripts # vim ../etc/zabbix_agentd.conf.d/userparameter_script.conf
UserParameter=discovery.oracle.tablespace