Check_recovery.sh program to run sqlplus and return its values remotely.

#/bin/ksh -x

NOTIFY_LIST=”pmehta@1800flowers.com”

SOURCE_ARCH_DEST=/db5/archlog/800P

TARGET_SID=UOC

SOURCE_SSH=/usr/local/bin/ssh

TARGET_SSH=/usr/local/bin/ssh

SOURCE_HOST=nyuocdbprod01s

TARGET_HOST=uocdbprod21

TARGET_ORACLE_HOME=/oracle/app/oracle/product/10.1

TARGET_ENVFILE=/oracle/app/oracle/oraprocs/oracle_env_UOC

SOURCE_ORACLE_HOME=/oracle/app/oracle/product/10.1

SOURCE_ENVFILE=/export/home/oracle/oraprocs/oracle_env_10g_UOC

MY_SQL=”select max(SEQUENCE#) from v$log_history;”

MY_SQL1=”select count(1) from v$log_history where first_time >= (sysdate – (45/1440));”

TARGET_SEQ=`$TARGET_SSH $TARGET_HOST “. $TARGET_ENVFILE; $TARGET_ORACLE_HOME/bin/sqlplus -S / as sysdba <set pagesize 0 feedback off verify off heading off echo off

$MY_SQL

END”`

echo “TARGET_SEQ = $TARGET_SEQ”

SOURCE_SEQ=`$SOURCE_SSH $SOURCE_HOST “. $SOURCE_ENVFILE; $SOURCE_ORACLE_HOME/bin/sqlplus -S / as sysdba <set pagesize 0 feedback off verify off heading off echo off

$MY_SQL

END”`

echo “SOURCE_SEQ = $SOURCE_SEQ”

NO_LOGS_45MIN=`$SOURCE_SSH $SOURCE_HOST “. $SOURCE_ENVFILE; $SOURCE_ORACLE_HOME/bin/sqlplus -S / as sysdba <set pagesize 0 feedback off verify off heading off echo off

$MY_SQL1

END”`

echo “NO_LOGS_45MIN = $NO_LOGS_45MIN”

DIFFER=`expr $SOURCE_SEQ – $TARGET_SEQ`

echo “DIFFER=$DIFFER”

if [ $DIFFER -gt 0 ] && [ $NO_LOGS_45MIN -gt 0 ]

then

echo “Recovery of $TARGET_SID STBY on $TARGET_HOST is lagging by $DIFFER logs, But Log generation in last 45 minutes is $NO_

LOGS_45MIN : No Page sent” | mailx -s “$TARGET_SID – Standby Recovery Alert” $NOTIFY_LIST

exit

fi

if [ $DIFFER -gt 18 ]

then

echo “Database $TARGET_SID on $TARGET_HOST IS LAGGING BY $DIFFER LOGS `date`” |mailx $NOTIFY_LIST

fi