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表空间告警邮件截图:
邮件通知表空间告警
责任编辑:姜华 来源: 今日头条 ZabbixOracle数据库
(责任编辑:时尚)
棠记控股(08305.HK)预计年度亏损不少于50万港元 毛利严重下降
2020年1月份工业生产者出厂价格同比上涨0.1% 农副产品类价格持平
云南腾冲市财政局通过“六抓六着力”工作举措 推进挂钩村扶贫攻坚工作
四川省发改委雷开平副主任与苹果公司全球副总裁Rory Sexton一行四人座谈
央行:不得变相强制收集消费者金融信息 消费者权益保护工作应纳入考核