【Oracle Goldengate实时监控脚本设计】可触发短信实时告警

前言

        goldengate数据同步方案灵活高效，可满足各企业各行业的同步数据需求，但由于其高成本的维护，对专业性要求较高，另外ogg的进程受到源端表结构变更，异常操作，目标数据库环境等各方面限制，将会造成进程的挂起，这样会让同步中断，所以对goldengate进程的监控尤为重要！脚本能对进程挂起进行实时告警，起到及时处理故障的重大作用，下面脚本将通过多年的经验积累开发而成，主要是通过短信形式对进程异常进行告警，目前应用于各大行业，稳定高效。

1在LINUX下部署goldengate短信监控
– 创建短信表

create table dba_ftc.SEND_MSG

(

MSG_ID     NUMBER(20) not null,

IPADDRESS  VARCHAR2(200),

CREATETIME DATE,

CONTENT    VARCHAR2(1000)

)

– Create/Recreate primary, unique and foreign key constraints （增加主键）

alter table dba_ftc.SEND_MSG

add constraint PK_SEND_MSG primary key (MSG_ID)

using index ;

–ogg monitor script（OGG监控脚本，部署在LINUX的crontab任务下）:

#/bin/sh

. /home/oracle/.bash_profile

#获得OGG进程状态并写入临时日志文件

echo "info all" |/ogg/software/ggsci|tail -n 34|sed 's/^GG.*//' > /tmp/ogg_monitor.log

#捕获ABEND或STOPPED的ogg进程

ogg_monitor=`egrep 'ABEND|STOPPED' /tmp/ogg_monitor.log`

 if [ ! $ogg_monitor ]; then

            echo "IS NULL"

        else

/opt/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus sms_link/*******k@erptar3 << EOF

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'18988968178','${ogg_monitor}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13729826939','${ogg_monitor}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13926108245','${ogg_monitor}',10,sysdate from DUAL ;

commit;

exit

EOF

fi

#判断OGG延时情况

Lag_value=30

Time_value=30

while read line

do

echo $line 

        Lag_time=`echo $line |awk '{print $4}'|awk -F':' '{print $2}'`

        Time=`echo $line |awk '{print $5}'|awk -F':' '{print $2}'`

        if [[ $Lag_time -gt $Lag_value ]] || [[ $Time -gt $Time_value ]];then

/opt/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus sms_link/******k@erptar3 << EOF

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'18988968178','${line}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13729826939','${line}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13926108245','${line}',10,sysdate from DUAL ;

commit;

exit

EOF

        fi

done  </tmp/ogg_monitor.log

2.在WINDOWS下部署goldengate短信监控

$ogg_info="info all"|D:\ogg\software\ggsci

$ogg_monitor=echo $ogg_info[-34..-1]|select-string -pattern "^(MANAGER|REPLICAT).*(ABEND|STOPPED).*\s*$"

#$ogg_monitor=echo $ogg_info[-34..-1]|select-string -pattern "^(MANAGER|REPLICAT).*"

if(!$ogg_monitor){echo "is Null"} else{

$message="

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'18988968178','${ogg_monitor}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13729826939','${ogg_monitor}',10,sysdate from DUAL ;

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,'13926108245','${ogg_monitor}',10,sysdate from DUAL ;

commit;

exit

"

echo $message|D:\u01\product\11.2.0\dbhome_1\BIN\sqlplus.exe sms_link/******@erptar3

}

$ogg_info="info all"|D:\ogg\software\ggsci

$ogg_monitor=echo $ogg_info[-34..-1]|select-string -pattern "^(MANAGER|REPLICAT).*(ABEND|STOPPED).*\s*$"

#$ogg_monitor=echo $ogg_info[-34..-1]|select-string -pattern "^(MANAGER|REPLICAT).*"

if(!$ogg_monitor){echo "is Null"} else{

$message="

insert into mbs7_msg.msg_sendsms(SSID,STID,TEMPLETCODE,VERSIONCODE,CUSCODE,MOBILE,CONTENT,PRIORINDEX,CREATETIME)

select MBS7_MSG.SEQ_MSG_SENDSMS.NEXTVAL,0,'AlarmSms',0,0,m.phone,'10.1.8.209 '||'${ogg_monitor}',10,sysdate from mbs7_msg.msg_dbamonitor m;

commit;

exit

"

echo $message|D:\u01\product\11.2.0\dbhome_1\BIN\sqlplus.exe sms_link/*******@erptar3

}

外层调用

powershell D:\ogg\software\ogg_monitor.ps1

最后通过crontab或windows的调度任务调用即可，每15或30分钟执行一次，如部署过程有疑问，可随时与作者联系！

十年磨一剑，厚积薄发！

天凯科技-数据库技术支持
7*24小时提供数据库运维与优化服务，十年DBA实战经验，长期服务于国内关键行业，深受好评！
服务热线/微信：13926108245
本企业定期分享DBA实战处理经验，同时分享精品视频教程，更多资源请点击访问"天凯运维资讯"获得!