之前通过orabbix可以监控oracle很多对象,具体可以参考:
http://blog.itpub.net/24486203/viewspace-1788791/
1.在被监控端创建脚本，此脚本用于获取表空间及空间使用信息,此脚本由oracle用户下创建(这里我将system及sysaux 监控去除掉了)．这个zabbix用户为之前orabbix所创建用户
[root@ scripts]# cat oracle_cron.sh
#!/bin/bash
source /home/oracle/.bash_profile
sqlplus -s zabbix/zabbix > /tmp/tablespace.log<<EOF
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
NVL(a.bytes, 0) "Size (M)",
NVL(a.bytes - NVL(f.bytes, 0), 0) "Used (M)",
round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),2) "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 d.tablespace_name not in ('SYSAUX','SYSTEM') 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",
NVL(a.bytes, 0) "Size (M)",
NVL(t.bytes,0) "Used (M)",
round(NVL(t.bytes / a.bytes * 100, 0),2) "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
sed -i '$d' /tmp/tablespace.log
sed -i '$d' /tmp/tablespace.log
sed -i 's/,//g' /tmp/tablespace.log
手动运行脚本结果如下
[oracle@ scripts]$ cat /tmp/tablespace.log
Status Name Type Extent Size (M) Used (M) Used %
---------- ------------------------- ---------- --------------- ---------- ---------- ----------
ONLINE USERS PERMANENT LOCAL 1073741824 11272192 1.05
ONLINE TEMP TEMPORARY LOCAL 4294967296 97517568 2.27
ONLINE UNDOTBS1 UNDO LOCAL 4294967296 1579155456
33.46
2.利用脚本(oracle_discovery.sh)对取出数据文件(tablespace.log)进行格式化(因为zabbix自动发现数据类型是jason类型),取出Name那一列．
[root@ ~]# cat /etc/zabbix/scripts/oracle_discovery.sh
#!/bin/bash
TABLESPACE=`cat /tmp/tablespace.log |awk '{print$2}'|awk 'NR>3{print}'`
COUNT=`echo "$TABLESPACE" |wc -l`
INDEX=0
echo '{"data":['
echo "$TABLESPACE" | while read LINE; do
echo -n '{"{#TABLENAME}":"'$LINE'"}'
INDEX=`expr $INDEX + 1`
if [ $INDEX -lt $COUNT ]; then
echo ','
fi
done
echo ']}'
3.利用脚本(oracle_check.sh)获取数据文件(tablespace.log)后三列
[root@ scripts]# cat oracle_check.sh
#!/bin/bash
EQ_DATA="$2"
ZBX_REQ_DATA_TAB="$1"
SOURCE_DATA=/tmp/tablespace.log
case $2 in
maxmb) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $5}';;
used) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $6}';;
autopercent) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $7}';;
*) echo $ERROR_WRONG_PARAM; exit 1;;
esac
exit 0
4.设置脚本权限(oracle_check.sh,oracle_discovery.sh)
chmod o+x oracle_check.sh
chmod o+x oracle_discovery.sh
取值测试
cd /etc/zabbix/scripts/
[root@ scripts]# ./oracle_check.sh USERS maxmb
1073741824
[root@ scripts]# ./oracle_check.sh USERS used
11272192
[root@ scripts]# ./oracle_check.sh USERS autopercent
1.05
5.zabbixagent配置文件添加自定义监控key
echo "UserParameter=ora.tab.discovery,/etc/zabbix/scripts/oracle_discovery.sh">>/etc/zabbix/zabbix_agentd.conf
echo 'UserParameter=tablespace[*],/etc/zabbix/scripts/oracle_check.sh $1 $2'>>/etc/zabbix/zabbix_agentd.conf
6.配置计划任务，重启zabbix_agentd服务
*/1 * * * * /u01/scripts/oracle_cron.sh > /u01/scripts/oracle_cron.sh.log 2>&1
/etc/init.d/zabbix_agentd restart
7.在zabbix_server端添加discovery模板，之前安装过orabbix,直接在Template_Oracle下添加discovery rule
点击create discovery rule,填写值如下
· Name: tablespacediscovery
Type:Zabbix agent
Key:ora.tab.discovery (这个自定义键值名称)
UserParameter=ora.tab.discovery,/etc/zabbix/scripts/oracle_discovery.sh
在tablespacediscovery创建三个Item prototypes如下
Name:{#TABLENAME}autopercent
Type:zabbix agent
Key:tablespace[{#TABLESPACENAME},autopercent]
Name:{#TABLENAME}maxmb
Key:tablespace{#TABLENAME,maxmb}
Name:{#TABLENAME}used
Key: tablespace{#TABLENAME,used}
定义Graph prototypes,注意添加的时候选择Add prototype
Name: TABLESPACE-{#TABLENAME}
建立触发器(表空间使用比例达%95报警)
Name: TableSpace {#TABLENAME} alarm
Expression:
{Template_Oracle:tablespace[{#TABLENAME},autopercent].last()}>95
最后我们看一下效果图
