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

主机添加模板

等待一会后查看监控结果：

监控指标图形展示：

这个的告警我就不去模拟了，因为都是生产环境的，下图为4月份的TEMP表空间告警邮件截图：

Tags：oracle 用户 表