#!/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"