zabbix 监控oracle 各种脚本-归档日志

 

监控归档日志脚本

#!/bin/bash source ~/.bash_profile function check { sqlplus -S "/ as sysdba" <<  EOF spool /tmp/archived.log # spool 将执行的结果输出到文件当中 select * from v\ $flash_recovery_area_usage; spool off quit EOF };check &>/dev/null # 如果不使用oracle用户执行的话，是下面这个样子的 #!/bin/bash source ~/.bash_profile function check { sudo su - oracle << EOF sqlplus -S "/ as sysdba" spool /tmp/archived.log select * from v\ $flash_recovery_area_usage; spool off quit EOF };check &>/dev/null

 执行结果

取值脚本

#!/bin/bash # Get archive log and backup set usage # version: 1 # ctime: # mtime: check() { # json格式输出的item变量 printf "{\n" printf '\t' "\"data\":[" printf '\n\t\t{' printf "\"{#STATUS_NAME}\":\"ARCHIVED\"}" printf "\n\t]\n" printf "}\n" } archived() { # 过滤归档日志使用率 函数 status=`grep ^ARCHIVED /tmp/archived.log | awk '{print $3}'` echo "$status" } backup() { # 过滤备份集使用率函数 status=`grep ^BACKUP /tmp/archived.log | awk '{print $3}'` echo "$status" } $1

执行结果

UserParameter配置文件

[SZSqxf@ecs-da85 ~]$ cat /usr/ local/zabbix-agent/etc/zabbix_agentd.conf.d/archived_log.conf UserParameter=archived.check,/bin/bash /usr/ local/zabbix-agent/share/zabbix/alertscripts/archived_log.sh check UserParameter=archived.status[*],/bin/bash /usr/ local/zabbix-agent/share/zabbix/alertscripts/archived_log.sh archived "$1" UserParameter=backup.piece[*],/bin/bash /usr/ local/zabbix-agent/share/zabbix/alertscripts/archived_log.sh backup "$1"

在zabbix主界面配置监控自动发现

自动发现规则

监控项原型

定期删除归档日志脚本

#!/bin/bash su - oracle -c "rman target /" cat>/home/check/guidang<<EOF DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1'; DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-1'; exit EOF

配合计划任务执行即可，例如

00 10 * * 5 /home/SZSqxf/ora_guidang.sh

接下来是其他各种脚本不再做配置演示了思路都是一样的。

 

备份情况脚本

#!/bin/bash source ~/.bash_profile function check { sudo su - oracle << EOF sqlplus -S "/ as sysdba" spool /tmp/backup_status.log SELECT count(a.STATUS) FROM V\ $RMAN_STATUS a WHERE START_TIME >=trunc(sysdate) AND END_TIME <= trunc(sysdate+1) AND OPERATION = 'BACKUP'; SELECT distinct STATUS FROM V\ $RMAN_STATUS a WHERE START_TIME >=trunc(sysdate) AND END_TIME <= trunc(sysdate+1) AND OPERATION = 'BACKUP'; spool off quit EOF };check &>/dev/null

取值脚本

#!/bin/bash check() { #portarray=(`grep [a-Z] /tmp/database.log | awk '{print $1}'`) #length=${#portarray[@]} printf "{\n" printf '\t' "\"data\":[" #for ((i=0;i<$length;i++)) # do printf '\n\t\t{' printf "\"{#STATUS_NAME}\":\"BACKUP\"}" #if [ $i -lt $[$length-1] ];then #printf ',' # fi #done printf "\n\t]\n" printf "}\n" } back() { status=`grep [0-9] /tmp/backup_status.log | awk '{print $1}'` echo "$status" } stat(){ stat=`grep [a-Z] /tmp/backup_status.log | awk 'NR==3 {print $1}'` if [ "$stat" == COMPLETED ]; then echo "0" else echo "1" fi } $1 # 备份back 函数值大于0 为正常执行。 # 备份状态 stat函数 值为COMPLETED 输出 0 表示正常。不为COMPLETED 为1 表示执行失败。

监控普通表空间使用情况脚本

#!/bin/bash # tablespace usagep check source ~/.bash_profile function check { sudo su - oracle << EOF sqlplus -S "/ as sysdba" set linesize 200 set pagesize 200 spool /tmp/tablespace.log 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 off quit EOF };check &>/dev/null

表空间名称取值脚本

#!/bin/bash #zabbix discovery oracle tablespace table_spaces=(`cat /tmp/tablespace.log | 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"

表空间使用率取值脚本

#!/bin/bash # oracle tablespace check CEHCK_TYPE= $1 TABLESPACE_NAME= $2 function usagepre { grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{printf "%.f\n",($2-$3)/$2*100}' } function available { grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{printf $3*1024*1024}' } function check { if grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | 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

监控临时表空间脚本

#!/bin/bash # tablespace usagep check source ~/.bash_profile function check { sudo su - oracle << EOF sqlplus -S "/ as sysdba" set linesize 200 set pagesize 200 spool /tmp/temp_tablespace.log select c.tablespace_name, to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v\ $temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name order by tablespace_name ; spool off quit EOF };check &>/dev/null

表空间变量名脚本

#!/bin/bash #zabbix discovery oracle tablespace table_spaces=(`cat /tmp/temp_tablespace.log | grep TEMP | 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"

取值脚本

#!/bin/bash CEHCK_TYPE= $1 TABLESPACE_NAME= $2 function usagepre { grep "\b$TABLESPACE_NAME\b" /tmp/temp_tablespace.log | cut -d% -f1 | awk '{print $2}' } #function available { # grep "\b$TABLESPACE_NAME\b" /tmp/temp_tablespace.log | awk '{printf $3*1024*1024}' #} case $CEHCK_TYPE in pre) usagepre ;; #fre) # available ;; *) echo -e "Usage: $0 [pre] [TABLESPACE_NAME]" esac

 

监控存储过程表分区创建是否正常

#!/bin/bash source ~/.bash_profile function check { sudo su - oracle << EOF sqlplus -S "/ as sysdba" spool /tmp/p_create_smsdb.log select MAX(substr(A.PARTITION_NAME,3,8)) from DBA_TAB_PARTITIONS A WHERE A.TABLE_OWNER= 'SMSDB' and A.TABLE_NAME in (select distinct name from smsdb.configtable); spool off quit EOF };check &>/dev/null

输出结果

取值脚本

#!/bin/bash check() { #portarray=(`grep [a-Z] /tmp/database.log | awk '{print $1}'`) #length=${#portarray[@]} printf "{\n" printf '\t' "\"data\":[" #for ((i=0;i<$length;i++)) # do printf '\n\t\t{' printf "\"{#PARTITION_NAME}\":\"SMSDB\"}" #if [ $i -lt $[$length-1] ];then #printf ',' # fi #done printf "\n\t]\n" printf "}\n" } num=`date +%Y%m%d --date= '1 day'` date() { date=(`grep -v [a-Z] /tmp/p_create_smsdb.log | awk 'NR==3 {print $1}'`) #echo $date if [ $date -eq $num ]; then echo "0" else echo "1" fi } $1 # 如果日期为第二天输出为0，表示存储过程执行成功 # 如果日志不是第二天输出为1 表示存储过程执行失败 # zabbix 触发器规则就是 值不为0的时候告警

还有其他脚本就不一一贴出来了， 说下大体思路吧。

分区交换脚本思路

例如table1定期为4天数据交换到table2表中，我们利用select count(*) 语句查询table1中4天之前数据，如果结果为0表示数据交换成功。不为0则异常