Shell Programming and Scripting

Hi All,

OS:AIX 5.3 64 bits

I would like the below script to send alert mail with the message - "Standby logs falling behind Primary" to xyz@yahoo.com

Script
=====

#!/usr/bin/ksh

#-----------------------------------------------------------------------------
# Use SQL*Plus to query the MAX(SEQUENCE#) from both databases V$LOG_HISTORY # view. If the STANDBY appears to be falling behind, then send alert mail...
#-----------------------------------------------------------------------------
_OutFile=stdby_chk.out
sqlplus -s /nolog << __EOF__ > ${_OutFile} 2>&1
connect / as sysdba
set verify off

col logseq_on_standby new_value V_STDBY_LOGSEQ
select /*+ rule */ max(h.sequence#) logseq_on_standby
from v\$log_history h,
v\$parameter p
where h.thread# = to_number(decode(p.value,'0',1,p.value))
and p.name = 'thread';

col filecnt new_value V_STDBY_FILECNT
select count(*) filecnt
from v\$datafile;
exit;
__EOF__

ssh <username>@host.domain
export ORACLE_SID=<SIDNAME>
export ORACLE_HOME=<ORACLE_HOME_PATH>
export PATH=$ORACLE_HOME/bin:$PATH
_OutFile1=primary_chk.out
$ORACLE_HOME/bin/sqlplus -s /nolog << __EOF__ > ${_OutFile1} 2>&1
connect / as sysdba

col logseq_on_primary new_value V_PRIMARY_LOGSEQ
select /*+ rule */ max(h.sequence#) logseq_on_primary
from v\$log_history h,
v\$parameter p
where h.thread# = to_number(decode(p.value,'0',1,p.value))
and p.name = 'thread';

col filecnt new_value V_PRIMARY_FILECNT
select count(*) filecnt
from v\$datafile;
exit;
__EOF__
exit;

could anyone please share the exeperience in fulfilling this objective!!!

Thanks for your time!

Regards,

Hi,

Standby archive logs should be falling behind by 2 than Primary archive logs.

For example:

If standby archive logs are at -> 498
Primary archive logs are at -> 500

Then alert mail should be sent to xyz@yahoo.com with the message "standby logs are behind primary"

Thanks for your help and time!!

Regards,