欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-02 21:26:40
转自 https://blog.51cto.com/8858975/1401988监控脚本(注:这里没用Sendmail工具发送邮件,如果用的话需要修改)$cat check_oracle_dg_delay.sh #!/bin/shsource ~/.bash_profile#定义变量v_datet ......

转自 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" <<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/1785464