Validating Archives Between Production and Standby Databases

I wrote these Solaris Unix scripts for a book project about 10 years ago. They are dated but I have used them myself recently to construct useful monitoring scripting in Linux.

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 &ltStandby 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

Recovery Script on Standby Database

#!/bin/ksh

export ORACLE_SID=<SID>
export ORACLE_HOME=/<mount-point-1>/oracle/product/8.1.7
PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=lib:/usr/ucblib

svrmgrl << EOF
connect internal
recover standby database
auto
exit

Changing Strings in Files Using the sed Editor

Be extremely careful using a script such as shown below where global changes can be made.

#!/bin/ksh
for file in $* do
	cp $file $file.old
	sed -e 's/find/replace/g' < $file > $file
done