Oracle延时DG,Oracle 11g DATAGUARD 同步延时监控脚本

转自 https://blog.51cto.com/8858975/1401988

监控脚本(注：这里没用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" <

--#####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/1785464