Zabbix的自定义键值和自动发现功能监控Oracle数据库的表空间

作者：花生_TL007 | 2024-03-22 07:02:26

踩

zabbix5.0.14配置oracle表空间自动发现规则

前面介绍了利用Orabbix监控了，参考zabbix通过Orabbix监控oracle数据库，这里我们原先的模板中进行了修改，使用自动发现功能实现监控tablespace的使用情况。

1. 在被监控的数据库机器上创建3个shell脚本

1.1 得到表空间的使用情况

[oracle@oracledb shellScripts]$ cat /home/oracle/shellScripts/query_tablespace_usage.sh 
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
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格式的）

[oracle@oracledb ~]$ cat /usr/local/zabbix-3.6.2/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 ']}'

　　执行脚本可以得到

[oracle@oracledb ~]$ /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列

[oracle@oracledb ~]$ cat /usr/local/zabbix-3.6.2/scripts/oracle_
oracle_check.sh      oracle_discovery.sh  
[oracle@oracledb ~]$ 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文件末尾添加：

UserParameter=ora.tab.discovery,/usr/local/zabbix-3.6.2/scripts/oracle_discovery.sh
UserParameter=tablespace[*],/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>&1

4. 重启zabbix_agentd,使配置生效

/etc/init.d/zabbix_agentd restart

　　可以在zabbix服务端看是否得到客户端传来的数据

[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS autopercent]"
94.55
[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS maxmb]"
70
[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS used]"
66
[root@lanmp bin]# pwd
/usr/local/zabbix-3.2.6/bin

5. 添加发现规则

之前安装过orabbix,直接在Template_Oracle下添加发现规则

5.1 创建发现规则

模板-->Template_Oracle-->创建自动发现规则

名称：tablespacediscovery

键值：ora.tab.discovery

数据更新间隔：60