Validating Archives Between Production and Standby Databases

Validating Archives Between Production and Standby Databases

On the Production Database

#!/bin/ksh

#
# ---------------------------------------------------------------------------------------------------------------------------------
#

ORACLE_HOME=/<mount-point>/oracle/product/8.1.7
LD_LIBRARY_PATH=/<mount-point>/oracle/product/8.1.7/lib:/usr/ucblib:/usr/openwin/lib
JAVA_HOME=/<mount-point>/oracle/product/jre/1.1.8
ORACLE_SID=<SID>
ORACLE_SBIN=/<mount-point>/oracle/product/8.1.7/sbin
ORACLE_ALERT=/<mount-point>/oracle/admin/sudbp/bdump

PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin

host=`hostname`

if [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]\
|| [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$ORACLE_SBIN" ]\
|| [ -z "$PATH" ] || [ -z "$host" ]; then 
        echo "$0 aborted - variable not defined"
        mailx -s "$0 aborted - variable not defined" $email < /dev/null
        exit 1
fi

USAGE="$0: Incorrect arguments, Usage: $0 <\"email1 email2 ... emailn\">" 

if [ `whoami` != 'oracle' ]; then    
        echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
        mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
        exit 1
elif [ -z "$1" ]; then
        echo "$USAGE"
        mailx -s "$USAGE" "emergency@xyz.com" < /dev/null
        exit 1
fi

email=$1

echo "Executing $0 on $host"

#
# ---------------------------------------------------------------------------------------------------------------------------------
#

$ORACLE_HOME/bin/sqlplus internal<<!
set head off term off echo off feedback off show off trim off trims off verify off linesize 132 escape on;
spool /u01/app/oracle/product/8.1.5/sbin/logs/archives_e4501.log;
select 'SEQUENCE '||to_char(sequence#)||' DATE '||to_char(first_time,'yyyy/mm/dd hh:mi:ss') 
from v\$log_history where sequence# = (select max(sequence#)-1 from
v\$log_history);
spool off;
exit
!

cat $ORACLE_SBIN/logs/archives_production.log | grep "SEQUENCE" | grep -v grep | grep -v SQL\> | awk '{ print $1,$2,$3,$4,$5 }' > $ORACLE_SBIN/logs/archives_production.log

if [ -s $ORACLE_SBIN/logs/archives_production.log ]; then
        /usr/bin/rcp -p $ORACLE_SBIN/logs/archives_production.log <Standby Database Hostname>:/<mount-point>/oracle/product/8.1.7/sbin/logs/archives_production.log
else
        sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs"
        echo $sub
        mailx -s "$sub" $email < "Error"
        exit 1
fi

exit 0 

On the Standby Database

#!/bin/ksh

#
# ---------------------------------------------------------------------------------------------------------------------------------
#

ORACLE_HOME=/<mount-point>/oracle/product/8.1.7
LD_LIBRARY_PATH=/<mount-point>/oracle/product/8.1.7/lib:/usr/ucblib:/usr/openwin/lib
JAVA_HOME=/<mount-point>/oracle/product/jre/1.1.8
ORACLE_SID=<SID>
ORACLE_SBIN=/<mount-point>/oracle/product/8.1.7/sbin
ORACLE_ALERT=/<mount-point>/oracle/admin/sudbp/bdump

PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin

host=`hostname`

if [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]\
|| [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$ORACLE_SBIN" ]\
|| [ -z "$PATH" ] || [ -z "$host" ]; then 
        echo "$0 aborted - variable not defined"
        mailx -s "$0 aborted - variable not defined" $email < /dev/null
        exit 1
fi

USAGE="$0: Incorrect arguments, Usage: $0 <\"email1 email2 ... emailn\">" 

if [ `whoami` != 'oracle' ]; then    
        echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
        mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
        exit 1
elif [ -z "$1" ]; then
        echo "$USAGE"
        mailx -s "$USAGE" "emergency@xyz.com" < /dev/null
        exit 1
fi

email=$1

echo "Executing $0 on $host"

#
# ---------------------------------------------------------------------------------------------------------------------------------
#

$ORACLE_HOME/bin/sqlplus internal<<!
set head off term off echo off feedback off show off trim off trims off verify off linesize 132 escape on;
spool $ORACLE_SBIN/logs/archives_standby.log;
select 'SEQUENCE '||to_char(sequence#)||' DATE '||to_char(first_time,'yyyy/mm/dd hh:mi:ss') 
from v\$log_history where sequence# = (select max(sequence#) from v\$log_history);
spool off;
exit
!

cat $ORACLE_SBIN/logs/archives_standby.log | grep "SEQUENCE" | grep -v grep | grep -v SQL\> | awk '{ print $2 }' > $ORACLE_SBIN/logs/archives_standby.log

if [ -s $ORACLE_SBIN/logs/archives_production.log ] -a [ -s $ORACLE_SBIN/logs/archives_standby.log ]; then
 export prod=`/bin/cat $ORACLE_SBIN/logs/archives_production.log`
 export stby=`/bin/cat $ORACLE_SBIN/logs/archives_standby.log`
 export diffs=$(($prod-$stby))
 if [ $diffs -gt 2 ]; then 
         sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs ($diffs)"
         echo $sub
         mailx -s "$sub" $email < /dev/null
         exit 1
 fi
else
        sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs"
        echo $sub
        mailx -s "$sub" $email < /dev/null
        exit 1
fi

exit 0