Zabbix实战-简易教程--DB类--Oracle(主从同步、归档备份以及DB备份状态监控)

作者：@skyflask
转载本文请注明出处：https://www.cnblogs.com/skyflask/articles/8004468.html

---

目录

 一、背景需求
二、脚本编写
三、自定义key和测试
四、制作模板
五、触发器设置
六、效果图
七、脚本地址和参考文献

 一、背景需求

由于数据库是整个公司的核心，所以数据完整和备份是重中之重。本文简单阐述oracle主从同步、归档备份以及DB备份状态监控。

二、脚本编写

oracle DB备份状态脚本：

[root@bgp-bjzw-zabbix-server02 rpm]# cat bk_db.sh

#!/bin/bash
#. /home/oracle/.bash_profile
frq=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off timing off
select case when (mod(trunc(max(end_time))-trunc(min(end_time)),7)=0) then 'W' when (mod(trunc(max(end_time))-trunc(min(end_time)),7)>0) then 'D' end  from v\\$rman_status where object_type like 'DB%' and end_time>sysdate-8;
exit;
EOSQL`
frq=${frq: -1}

if [ "$frq" == "D" ]; then
db_bk=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off timing off
select count(*) from v\\$rman_status where object_type like  'DB%' and status='COMPLETED' and trunc(end_time)=trunc(sysdate);
exit;
EOSQL`

elif [ "$frq" == "W" ]; then
db_bk=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off timing off
select count(*) from v\\$rman_status where object_type like 'DB%' and status='COMPLETED' and end_time>sysdate-7;
exit;
EOSQL`
fi
db_bk=${db_bk: -1}

if [ $db_bk -gt 0 ]; then
echo "DB Backup OK"
else 
echo "DB Backup Problem"
fi

 

oracle主从同步状态：

[root@bgp-bjzw-zabbix-server02 rpm]# cat sysnc_status.sh
#!/bin/sh
for SID in $(ps -fu oracle | grep pmon | grep -v grep | cut -d_ -f3- | sort); do
export ORACLE_SID=$SID
row=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select count(*) from v\\$dataguard_stats where name in ('transport lag', 'apply lag') and value is not null;
exit;
EOSQL`
if [ $row -eq 2 ];
then
t_lag=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select to_number(substr(value,2,2))*1440+to_number(substr(value,5,2))*60+to_number(substr(value,8,2)) from v\\$dataguard_stats where name ='transport lag';
exit;
EOSQL`

a_lag=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select to_number(substr(value,2,2))*1440+to_number(substr(value,5,2))*60+to_number(substr(value,8,2)) from v\\$dataguard_stats where name ='apply lag';
exit;
EOSQL`

if [ $t_lag -gt 60 ];
then
t_lag=`echo $t_lag|tr -d " "` 
echo "Problem:$SID Transport Lag $t_lag Minutes"
else 
echo "$SID Archive log transport OK"
fi
if [ $a_lag -gt 120 ];
then
a_lag=`echo $a_lag|tr -d " "`
echo "Problem:$SID Standby not Apply Logs for $a_lag Minutes"
else 
echo "$SID Archive log apply OK"
fi
else 
echo "Problem:$SID Standby  Disconnected from Primary"
fi
done

 

oracle 归档备份状态：

[root@bgp-bjzw-zabbix-server02 rpm]# cat bk_arc.sh
#!/bin/bash
#. /home/oracle/.bash_profile
arc_bk=`sqlplus -silent '/ as sysdba' << EOSQL
whenever sqlerror exit sql.sqlcode
set pagesize 0 feedback off verify off heading off echo off
select count(*) from v\\$rman_status where object_type='ARCHIVELOG' and status='COMPLETED' and trunc(end_time)=trunc(sysdate);
exit;
EOSQL`

arc_bk=${arc_bk#*.}

if [ $arc_bk -gt 0 ]; then
echo "Archivelog Backup OK"
else
echo "Archivelog Backup Problem"
fi

 

三、自定义key和测试

UserParameter=custom.bkdb.status[*],sudo su - oracle -c /etc/zabbix/externalscripts/bk_db.sh
UserParameter=custom.bkarc.status[*],sudo su - oracle -c /etc/zabbix/externalscripts/bk_arc.sh
UserParameter=custom.sysnc.status[*],sudo su - oracle -c /etc/zabbix/externalscripts/sysnc_status.sh

 测试结果如下：

[root@centos6564-73-131 ~]# zabbix_get  -s192.168.72.160 -k custom.bkdb.status 
DB Backup OK
[root@centos6564-73-131 ~]# zabbix_get  -s192.168.72.160 -k custom.bkarc.status
Archivelog Backup OK
[root@centos6564-73-131 ~]# zabbix_get  -s192.168.72.160 -k custom.sysnc.status
paydbrac1 sync Ok

 

四、制作模板

模板和上篇监控多表空间汇总到一起，作为监控oracle。

模板地址请参照：http://www.cnblogs.com/skyflask/articles/8004184.html

 

 

五、触发器设置

 

 

六、效果图

 

七、脚本地址和参考文献

参照Zabbix实战-简易教程--DB类--Oracle(多表空间)