1、监控脚本(注:这里没用Sendmail工具发送邮件,如果用的话需要修改)
$cat check_oracle_dg_delay.sh
#!/bin/sh source ~/.bash_profile #定义变量 v_datetime=`date +"%Y-%m-%d %H:%M:%S"` v_username='oracle' v_hostname=`hostname` #获取IP地址 v_ip=`/sbin/ifconfig bond0|grep 'inet addr'|awk '{print $2}'|cut -d: -f2` #发送人 v_sendmail='xxxxx@139.com' #接收人,多个联系人用空格分开 v_receivemail='XXXXX@139.com XXXXX@163.com' #路径 v_path="/home/${v_username}/scripts" #####define tns##### v_dbname='XXXXX' v_standby_tns='xxxxx_dg' #####报警阀值设置(单位:秒)##### v_delay_time_warning=1800 #####Oracle DG 延时获取开始##### sqlplus -S "/ as sysdba" <<EOF --#####primary database#### col delay_time for 9999999999; col tmp_delay_time new_val tmp_delay_time set timing off time off verify off heading off trimout on trimspool on linesize 180; spool ${v_path}/delay_time.log select (to_date(to_char(scn_to_timestamp(current_scn), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') - to_date(to_char(scn_to_timestamp ((select current_scn from v\$database@phystandby.localdomain)), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600 tmp_delay_time from v\$database; spool off; spool ${v_path}/delay_scn_and_time.log col standby_current_scn for 999999999999999; col primary_current_scn for 999999999999999; col standby_current_time for a30; col primary_current_time for a30; col delay_time for 9999999999; set heading on select current_scn primary_current_scn, (select current_scn from v\$database@phystandby.localdomain) standby_current_scn, to_char(scn_to_timestamp(current_scn), 'yyyy-mm-dd hh24:mi:ss') primary_current_time, to_char(scn_to_timestamp ((select current_scn from v\$database@phystandby.localdomain)), 'yyyy-mm-dd hh24:mi:ss') standby_current_time, (to_date(to_char(scn_to_timestamp(current_scn), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') - to_date(to_char(scn_to_timestamp ((select current_scn from v\$database@phystandby.localdomain)), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) * 24 * 3600 "delay_time(s)" from v\$database; spool off; exit EOF #####Oracle DG 延时获取结束##### #####Oracle DG 延时时长提取##### v_delay_time=`cat ${v_path}/delay_time.log|sed '/^$/d'|sed -e 's/[[:space:]][[:space:]]*/ /g'` #####监控代码开始##### if [ ${v_delay_time} -ge ${v_delay_time_warning} ] ; then echo "############################" > ${v_path}/oracle_dg_delay.log echo "USER: ${v_username}" >> ${v_path}/oracle_dg_delay.log echo "HOSTNAME: ${v_hostname}" >> ${v_path}/oracle_dg_delay.log echo "IP: ${v_ip}" >> ${v_path}/oracle_dg_delay.log echo "############################" >> ${v_path}/oracle_dg_delay.log cat ${v_path}/delay_scn_and_time.log >> ${v_path}/oracle_dg_delay.log ${v_path}/bsmtp -f ${v_sendmail} -h smtp.api.localdomain -s "${v_dbname} ORACLE DG DELAY ${v_delay_time}s WARNING!!! - ${v_datetime}" ${v_receivemail} < ${v_path}/oracle_dg_delay.log fi #####监控代码结束#####
2、配置crontab,每十分钟执行一次
$crontab -l
#oracle DG delay check */10 * * * * /home/oracle/scripts/check_oracle_dg_delay.sh >/dev/null 2>&1
转载于:https://blog.51cto.com/8858975/1401988