前言
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分钟执行一次,如部署过程有疑问,可随时与作者联系!
©版权声明:本文为天凯DBS的原创文章,转载请附上原文出处链接及本声明,否则将追究法律责任。
原文链接:https://dbs-service.cn/a/152.html