监控Oracle表空间

编写自动发现规则，监控Oracle表空间的使用率、剩余空间；

1、编写获取Oracle表空间的shell脚本 check_tablespace.sh，放入到计划任务执行

#!/bin/bash
#
# Oracle tablespace usagep check
# Run this script in crond at oracle user

source ~/.bash_profile


# check tableSpace funtion
check {
sqlplus -S user/passwd@orcl << EOF
set linesize 200
set pagesize 200
spool /tmp/ora_tablespace.txt
select a.tablespace_name, total, free, (total - free) as usage
  from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 as free
          from dba_free_space
         group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
spool /tmp/ora_autex.txt
select tablespace_name,autoextensible from dba_data_files;
spool off
exit
EOF
}

# Execute command and log into file
check &> /dev/null

将脚本放入计划任务执行：

# check Oracle tablespace usagep and process by use zabbix
5 * * * * bash /opt/zabbix/share/script/check_tablespace.sh

2、编写生成自动发现规则模板的shell脚本discovery_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"

3、编写zabbix获取数据脚本zabbix_check_tablespace.sh

#!/bin/bash
#
# zabbix check oracle tablespace

CEHCK_TYPE=$1
TABLESPACE_NAME=$2

function usagepre {
    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf "%.f\n",($2-$3)/$2*100}'
}
function available {
    grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf $3*1024*1024}'
}
function check {
    if grep "\b$TABLESPACE_NAME\b" /tmp/ora_autex.txt | awk '{print $2}' | uniq | grep -i "YES" &>/dev/null;then
        echo "YES"
    else
        echo "NO"
    fi
}
case $CEHCK_TYPE in
    pre)
        usagepre ;;
    free)
        available ;;
    check)
        check ;;
    *)
        echo -e "Usage: $0 [pre|free|check] [TABLESPACE_NAME]"
esac

4、在zabbix_agentd上增加UserParamete

[zabbix@DB1 ~]$ cat /opt/zabbix/etc/zabbix_agentd.conf.d/oracle.conf
## tablespace
UserParameter=discovery.oracle.tablespace[*],/bin/bash /opt/zabbix/share/script/discovery_tablespace.sh
UserParameter=tablespace.check[*],bash /opt/zabbix/share/script/zabbix_check_tablespace.sh $1 $2

5、服务端测试键值（客户端需要先重启）

[root@zabbix ]#  zabbix_get -s 192.168.1.10 -p 10050 -k 'tablespace.check[pre,SYSTEM]'
43
[root@zabbix ]#  zabbix_get -s 192.168.1.10 -p 10050 -k 'tablespace.check[free,SYSTEM]'
15194.5
[root@zabbix ]#  zabbix_get -s 192.168.1.10 -p 10050 -k 'tablespace.check[check,SYSTEM]'
0

6、增加监控模板

6.1、添加自动发现规则

Oracle_TableSpace:{#TABLESPACE_NAME}使用率
tablespace.check[pre,{#TABLESPACE_NAME}]

6.2、添加自动发现规则下的监控项目--表空间使用率

Oracle_TableSpace:{#TABLESPACE_NAME}使用率
tablespace.check[pre,{#TABLESPACE_NAME}]

6.3、添加自动发现规则下监控规则--表空间剩余空间

Oracle_TableSpace:{#TABLESPACE_NAME}可用容量
tablespace.check[free,{#TABLESPACE_NAME}]

6.4、创建触发器，使用率大于80%则告警

7、效果图

资料下载

上传的资料包含监控模板、数据库连接数监控、表空间自动发现规则（监控），下载地址