#!/bin/ksh
#set -x
Set_Env()
{
HOSTNAME=`hostname`
HR2SPOOL=/tmp/spool4drmonh2.rep;
MAXSPOOL=/tmp/spool4drmonmax.rep;
DRSPOOLFILE=/tmp/spool4dr.rep;
MREPORT="/oracle/admin/scripts/logs/dr_monitor_${ORA_SID}_at_`date +%H`.log"
rm -f $MREPORT
REMOTE_SID=DPODSB01
#NOTIFY2="mike.culp@gmail.com,test.culp@gmail.com"
NOTIFY2="mike.culp@gmail.com"
}
Get_Info()
{
sqlplus -s /<<EOF
set echo off verify off head off feed off pages0 trimspool on;
spool $MAXSPOOL;
select thread#, max(sequence#)
from v\$archived_log val, v\$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
spool off;
set head on pages 100;
spool $HR2SPOOL;
select thread#, count(1) Count from v\$log_history where first_time>sysdate - 1 group by thread# order by 1;
spool off;
exit
EOF
}
Get_From_DR()
{
sqlplus -s sys/ora98dba@$REMOTE_SID as sysdba<<EOF
set echo off verify off head off feed off pages0 trimspool on;
spool $DRSPOOLFILE;
select thread#, max(sequence#)
from v\$archived_log val, v\$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# order by 1;
spool off;
exit
EOF
}
Compile_Data()
{
echo "" >> $MREPORT
echo "Following are the Current Log Numbers in Primary and DR" >> $MREPORT
echo "" >> $MREPORT
GTOTAL=0
echo "INST PRIMARY DR DIFF" >> $MREPORT
echo "~~~~~~~~~~~~~~~~~~~~~~~~" >> $MREPORT
cat $MAXSPOOL | while read TNO MNO
do
RMNO=`cat $DRSPOOLFILE | egrep -v "Lagging|Applied|Average" | grep " $TNO " | awk '{print $2}'`
let THEDIFF=$MNO-$RMNO
let GTOTAL=$GTOTAL+$THEDIFF;
echo "$TNO $MNO $RMNO $THEDIFF" >> $MREPORT
done
echo "" >> $MREPORT
echo "DR is lagging behind $GTOTAL logs from Primary " >> $MREPORT
echo "" >> $MREPORT
echo "Following are Log Generation in last one day " >> $MREPORT
cat $HR2SPOOL >> $MREPORT
echo "" >> $MREPORT
}
###########
# M A I N #
###########
if [ "$1 " = " " ]
then
echo "
Usage : DR_Mon.ksh <DBNAME>
"
exit
fi
DBNAME=$1
if [ `ps -ef | grep -v grep | grep pmon | grep -c ${DBNAME}` -eq 0 ]
then
print "\n\nDont see the database `tput smso`${DBANME}`tput rmso` Running... It may be running on local failover node. \n\n"
exit 1
else
export ORA_SID=`ps -ef|grep pmon|grep ${DBNAME}|awk '{print $8}'|sed 's/ora_pmon_//'`
fi
if [ "$ORA_SID " != " " ]
then
export NEW_ORACLE_SID=${ORA_SID}; . ~/.profile > /dev/null
fi
Set_Env;
Get_Info;
Get_From_DR;
Compile_Data;
cat $MREPORT | mailx -s "`date +%x_%X`:: $ORA_SID :DR Progress Detail" -r dg.consultants@mrcconsulting.com "$NOTIFY2"