zabbix自发现监控oracle表容量

之前通过Orabbix监控Oracle的数据，但是确无法监控表容量，以及添加的新表。

以下通过sql语句提取表数据做一个巡检，通过脚本提取巡检数据，达到监控表容量的作用

vim zabbix.sh

#!/bin/sh
source ~/.bash_profile
connection="system/Pmph\_k28n3 as sysdba"   # 连接数据库
echo "连接字符串:${connection}"
echo "--------begin------------"

sqlplus $connection @/root/zabbix.sql   ## 读取执行 指定sql文件
echo "--------end------------ "
exit;



############### 以下是巡检的sql文件  ##############

vim zabbix.sql


set head off
set linesize 20000
set echo off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on

COLUMN c_free_mb NEW_VALUE _c_free_mb NOPRINT
COLUMN c_total_mb NEW_VALUE _c_total_mb NOPRINT
COLUMN c_retvalue NEW_VALUE _c_retvalue NOPRINT
COLUMN c_dbfilesize NEW_VALUE _c_dbfilesize NOPRINT

select total_mb c_total_mb,free_mb c_free_mb from v$asm_diskgroup where GROUP_NUMBER = 1;
select to_char(sum(bytes/1024/1024/1024), 'FM99999999999999990') c_retvalue from dba_data_files;
SELECT to_char(sum(  NVL(a.bytes/1024/1024/10 - NVL(f.bytes/1024/1024/10, 0), 0)), 'FM99999999999999990') c_dbfilesize 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');

spool /root/zabbixxuanjian.log     ###    将 巡检文件 导出至 指定的位置，待会儿 zabbix 到这个文件取值    

prompt free_mb:&_c_free_mb
prompt total_mb:&_c_total_mb
prompt dbfilesize:&_c_retvalue
prompt dbsize:&_c_dbfilesize
prompt query:

SELECT * FROM (
select '- Tablespace ->',t.tablespace_name ktablespace,
       '- Type->',substr(t.contents, 1, 1) tipo,
       '- Used(MB)->',trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
       '- ActualSize(MB)->',trunc(d.tbs_size/1024/1024) ktbs_size,
       '- MaxSize(MB)->',trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
       '- FreeSpace(MB)->',trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
       '- Space->',trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
       '- Perc->',decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
from
  ( select SUM(bytes) tbs_size,
           SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
    from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
              from dba_data_files
    )
    group by tablespace_name
    ) d,
    ( select SUM(bytes) free_space,tablespace_name tablespace
    from dba_free_space
    group by tablespace_name

表容量取值

vim oracle_check.sh
 #!/bin/bash
EQ_DATA="$2"
ZBX_REQ_DATA_TAB="$1"
SOURCE_DATA=/root/zabbixxunjian.log
case $2 in
  maxmb)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $16}';;
  used)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $10}';;
  autopercent)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $NF}' ;;
  type)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $7}';;
  actualsize)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $13}' ;;
  freespace)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $19}' ;;
  space)
    grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $22}' ;;
  *)
    echo $ERROR_WRONG_PARAM; exit 1;;
esac
exit 0

表发现取值

vim oracle_discovery.sh

#!/bin/bash
TABLESPACE=$(cat /root/zabbixxunjian.log | awk '/Tablespace/ {print $4}')
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 ']}'

设置zabbix-agent 自定义键值

vim /etc/zabbix/zabbix_agentd.d/userparameter_oracle.conf


UserParameter=ora.tab.discovery,/home/oracle/task/oracle_discovery.sh
UserParameter=tablespace[*],/home/oracle/task/oracle_check.sh $1 $2
UserParameter=dbfilesize,cat /home/oracle/zabbixXJ.log  |awk -F ":" '/dbfilesize/ {print $NF}'
UserParameter=dbsize,cat /home/oracle/zabbixXJ.log  |awk -F ":" '/dbsize/ {print $NF}'
UserParameter=datadisk_free,cat /home/oracle/zabbixXJ.log | awk '/free_mb/ {print $NF}'
UserParameter=datadisk_total,cat /home/oracle/zabbixXJ.log | awk '/total_mb/ {print $NF}'



systemctl restart zabbix-agent.service # 重启zabbix 
zabbix_get -s 192.168.0.130 -k  ora.tab.discovery # 在服务端测试键值，返回以下数据
{"data":[
{"{#TABLENAME}":"USERS"},
{"{#TABLENAME}":"UNDOTBS2"},
{"{#TABLENAME}":"SYSTEM"},
{"{#TABLENAME}":"SYSAUX"},
{"{#TABLENAME}":"P_EXAM_ANSWERDETAIL"},
{"{#TABLENAME}":"UNDOTBS1"},
{"{#TABLENAME}":"EXAM2X_DATA"},
{"{#TABLENAME}":"PART_TK_STUDENT_TASK_ANSWER"},
{"{#TABLENAME}":"EXAM_PRODUCTION_SPACE"},
{"{#TABLENAME}":"EXAM_PRODUCTION_BIGSPACE"},
{"{#TABLENAME}":"TEMP"},
{"{#TABLENAME}":"EXAM_PRODUCTION_TEMP"},
{"{#TABLENAME}":"EXAM2X_TEMP"}]}

zabbix服务端导入模板

模板文件已上传：

https://download.youkuaiyun.com/download/qq_26129413/42453555