Zabbix监控Oracle数据库表空间并配置邮件告警图文教程

核心数据库10.100.0.20和21表空间已经修改完成，可以正常监控使用率和大小，并邮件告警通知。

修改Oracle数据库表空间监控

创建表空间信息收集脚本：

vim /home/oracle/scripts/check_tablespace.sh

脚本内容如下：

#!/bin/bash

# tablespace usagep check

source ~/.bash_profile

function check {

sqlplus -S "/ as sysdba" <<  EOF

set numwidth 20

set linesize 200

set pagesize 200

spool /tmp/ora_tablespace.txt

select aa.tablespace_name,

        round(bb.maxs, 2) "MAX(G)",

        round(aa.usd, 2) "USED(G)",

        round((bb.maxs - aa.usd) , 2) "FREE(G)",

        round(aa.usd / bb.maxs * 100, 2) "USED%"

   from (Select b.Tablespace_Name, (b.Bytes - Nvl(a.Bytes, 0)) "USD"

           From (select tablespace_name, sum(bytes) bytes

                   from Dba_Free_Space

                  group by tablespace_name) a,

                (select tablespace_name, sum(bytes) bytes

                   from Dba_Data_Files

                  group by tablespace_name) b

          Where b.tablespace_name = a.tablespace_name(+)

         -- and b.tablespace_name='MAXIMO_DATA'

         ) aa,

        (select tablespace_name,

                sum(case

                      when maxbytes = 0 then

                       user_bytes

                      else

                       maxbytes

                    end) "MAXS"

           from dba_data_files

         -- where tablespace_name='MAXIMO_DATA'

          group by tablespace_name

          order by tablespace_name) bb

  where aa.tablespace_name = bb.tablespace_name and aa.tablespace_name not like '%UNDO%'

  order by 5 desc, 1;

spool off

set linesize 100

set pagesize 100

spool /tmp/ora_autex.txt

select tablespace_name,autoextensible from dba_data_files;

spool off

quit

EOF

};check &>/dev/null

赋予执行权限

chmod +x /home/oracle/scripts/check_tablespace.sh

配置计划任务(oracle用户)

*/5 * * * * /home/oracle/scripts/check_tablespace.sh

手动执行脚本并查看结果

表空间脚本执行结果

自动发现脚本内容如下：

# 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<$length;i++))

do

    printf "\n\t\t{"

    printf "\"{#TABLESPACE_NAME}\":\"${table_spaces[$i]}\"}"

    if [ $i -lt $[$length-1] ];then

        printf ","

    fi

done

    printf "\n\t]\n"

printf "}\n"





执行结果

# ./discovery_oracle_tablespace.sh

{

        "data":[

                {"{#TABLESPACE_NAME}":"EMA"},

                {"{#TABLESPACE_NAME}":"SYSAUX"},

                {"{#TABLESPACE_NAME}":"MHWEB"},

                {"{#TABLESPACE_NAME}":"SYSTEM"},

                {"{#TABLESPACE_NAME}":"OGG"},

                {"{#TABLESPACE_NAME}":"USERS"},

                {"{#TABLESPACE_NAME}":"QDPTTBS"},

                {"{#TABLESPACE_NAME}":"UYUN"},

                {"{#TABLESPACE_NAME}":"LINKSCAPEH5DB"},

              {"{#TABLESPACE_NAME}":"WX_HUANGHE_TBS"}

        ]

}

监控项脚本内容如下：

# cat tablespace_check.sh

#!/bin/bash

# oracle tablespace check

CEHCK_TYPE=$1

TABLESPACE_NAME=$2



function usagepre {

    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf "%.f\n",$5}'

#    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf "%.f\n",$3/$2*100}'

}



function available {

    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf $4}'

#    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf ($2-$3)*1024*1024}'

}



function check {

    if grep "\b$TABLESPACE_NAME\b" /tmp/ora_autex.txt | awk '{print $2}' | uniq | grep "YES" &>/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

Agent参数配置：

# cat /etc/zabbix/zabbix_agentd.d/oracle_tablespace.conf

UserParameter=discovery.oracle.tablespace,/etc/zabbix/scripts/discovery_oracle_tablespace.sh

UserParameter=tablespace.check[*],/etc/zabbix/scripts/tablespace_check.sh $1 $2

重启zabbix-agent

主机添加模板