dataguard check report

# Below are Data Guard Reports --------------
0 */2 * * * /opt/oracle/DBAENGG/scripts/ofr_dg_check_nw.ksh 2>&1


pbpmccdbaora1:/home/oracle>>cat /opt/oracle/DBAENGG/scripts/ofr_dg_check_nw.ksh
#!/usr/bin/ksh
. $HOME/.setup_PMCGRD20.ksh
#export ORACLE_HOME=/opt/oracle/PWHRMN20/product/dbms/10.2.0
export PATH=$ORACLE_HOME/bin:$PATH
#export TNS_ADMIN=/opt/oracle/TNS
SCRIPTDIR=`dirname "$0"`
FILENAME=`basename "$0"`
ORALOGIN=sys
ORAPASSWD=ic0lumbus#
EORAPASSWD=pd0jpm412
InValidDB=0
#MAILLIST="na_pb_bd_eq_fo_oracle_team@jpmorgan.com,ib.core.svcs.oracle.mbox@jpmorgan.com"
MAILLIST1="ib.core.svcs.oracle.mbox@jpmorgan.com"
MAILLIST="ib_global_dba@jpmchase.com"
LOG_FILE=/tmp/ofr_dbstdby.txt
LOG_FILE2=/tmp/ofr_dbstdby2.txt
LOG_TMP=/tmp/stdby_nw.999
LOG_TMP1=/tmp/stdby_nw.8888
>$LOG_TMP
>$LOG_TMP1
>$LOG_FILE
>$LOG_FILE2
for ORATABLN in `cat /opt/oracle/DBAENGG/dbmap/oratab.stdby`
do
  case $ORATABLN in
        \#*)            ;;      #comment-line
          *)
     unset DB_NAME DB_STDBY ORAPASSWD APP
     if [ "`echo $ORATABLN | awk -F: '{print $5}' -`" = "Y" ] ; then
            DB_NAME=`echo $ORATABLN | awk -F: '{print $1}' -`
            DB_STDBY=`echo $ORATABLN | awk -F: '{print $2}' -`
            LOB=`echo $ORATABLN | awk -F: '{print $3}' -`
            ORAPASSWD=`echo $ORATABLN | awk -F: '{print $4}' -`
            APP=`echo $ORATABLN | awk -F: '{print $6}' -`
if [ "${DB_NAME}" = "PWHGRD20" ] ; then
ORALOGIN=dba_bear
ORAPASSWD=ydarw1n#
else
ORALOGIN=sys
#if [ "${LOB}" = "PB" ]; then
#ORAPASSWD=ic0lumbus#
#else
#ORAPASSWD=pd0jpm412
#fi
fi
if [ "${DB_NAME}" = "PCRDAR20" ] ; then
ORALOGIN=sys
ORAPASSWD=ic0lumbus#
fi
if [ "${LOB}" = "APACEQ" ]; then
ORALOGIN=dbsnmp
sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_NAME <<EOF01A
set heading off feedback off
set newpage none
set line  132
spool $LOG_TMP
select rtrim(ltrim(thread#))||':'||ltrim(rtrim(max(sequence#)))||':'
from v\$log where ARCHIVED='YES' group by  thread#;
spool off
exit;
EOF01A
elif [ "${DB_NAME}" = "IFXP01" ] ; then
ORALOGIN=dbsnmp
sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_NAME <<EOF01A
set heading off feedback off
set newpage none
set line  132
spool $LOG_TMP
select rtrim(ltrim(thread#))||':'||ltrim(rtrim(max(sequence#)))||':'
from v\$log where ARCHIVED='YES' group by  thread#;
spool off
exit;
EOF01A
elif [ "${DB_NAME}" = "PGPSP01" ] ; then
ORALOGIN=dbsnmp
sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_NAME <<EOF01A
set heading off feedback off
set newpage none
set line  132
spool $LOG_TMP
select rtrim(ltrim(thread#))||':'||ltrim(rtrim(max(sequence#)))||':'
from v\$log where ARCHIVED='YES' group by  thread#;
spool off
exit;
EOF01A
else
sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_NAME as sysdba <<EOF01A
set heading off feedback off
set newpage none
set line  132
spool $LOG_TMP
select rtrim(ltrim(thread#))||':'||ltrim(rtrim(max(sequence#)))||':'
from v\$log where ARCHIVED='YES' group by  thread#;
spool off
exit;
EOF01A
fi
if [ $? -ne 0 ] ; then
 echo $DB_NAME ':' $DB_STDBY ':' 'Error Primary' >> $LOG_TMP1
else
for standbyln in `cat $LOG_TMP`
do
   prim_thread=`echo $standbyln | awk -F: '{print $1}' -`
   prim_sequence=`echo $standbyln | awk -F: '{print $2}' -`
sqlplus -s -L $ORALOGIN/$ORAPASSWD@$DB_STDBY as sysdba <<EOF01B
set heading off feedback off
set linesize 150
set pagesize 200
column LOB format a8 head 'LOB#'
column APP format a35 head 'APP#'
column pthread format a10 head 'Thread#'
column dname format a20 head 'Primary'
column sname format a20 head 'Standby'
column pseq format a10 head 'Prim_Seq#'
column dseq format 999999999 head 'Stdby_Seq#'
column diff format 999999999 head 'Diff'
spool $LOG_TMP1 append
select '$LOB' LOB,'$APP' APP,'$DB_NAME' dname,'$DB_STDBY' sname,'$prim_thread' pthread,rtrim('$prim_sequence') pseq,max(sequence#) dseq ,
'$prim_sequence' -  max(sequence#) diff from v\$archived_log stdby where applied='YES' and  stdby.thread#='$prim_thread'
group by '$LOB',':','$APP',':','$DB_NAME',':','$DB_STDBY',':','$prim_thread',':','$prim_sequence';
spool off
exit;
EOF01B
if [ $? -ne 0 ] ; then
 echo $DB_NAME ':' $DB_STDBY ':' 'Error Connecting Standby' >> $LOG_TMP1
fi
done
fi
     fi;;
  esac
done
echo " ------------------------------------------------------------------------------------------------"> $LOG_FILE
echo -e "| Oracle Standby Check Report \t \t  `date '+%Y/%m/%d %H:%M:%S'`           |">> $LOG_FILE
echo " ------------------------------------------------------------------------------------------------">> $LOG_FILE
echo -e "LOB  \tAPP                   \t   Primary       \t Standby     \t  Thread# \t Prim.Seq# \t Stdby.Seq# \t Diff* \n">> $LOG_FILE
cat $LOG_TMP1 >> $LOG_FILE
echo " =================================================================================================">> $LOG_FILE
echo Script Name :  $SCRIPTDIR/$FILENAME >> $LOG_FILE
if [ $InValidDB -gt 0 ]; then
   echo 'Use Following Sql Scripts For More Details : ' >> $LOG_FILE
   echo '   1) /opt/oracle/DBATOOLS/sql/dataguard/show_phy_apply.sql' >> $LOG_FILE
   mail -s "Oracle:Report Standby Database Check" $MAILLIST < $LOG_FILE
 else
   mail -s "ORA Exception REPORT: Standby Database Check" $MAILLIST1 -c $MAILLIST < $LOG_FILE
fi
exit 0


pbpmccdbaora1:/home/oracle>>cat /opt/oracle/DBAENGG/dbmap/oratab.stdby
PMCRCS20:BCRRCS20:PB:0R4JpmPB:Y:RACS2
BCRGDW20:PMCGDW20:PB:0R4JpmPB:Y:GCRS
PMCBVD20:BCRBVD20:PB:0R4JpmPB:Y:GENEVA
PMCPRV20:BCRPRV20:PB:0R4JpmPB:Y:CSR_DASHBOARD
PMCPRV30:BCRPRV30:PB:0R4JpmPB:Y:CSR_DASHBOARD
PMCMRG20:BCRMRG20:PB:0R4JpmPB:Y:GPB_MARGIN
PMCPBS20:BCRPBS20:PB:0R4JpmPB:Y:GENEVA_PBSD
PCRODS20:PMCODS20:PB:0R4JpmPB:Y:GENEVA
PCRSBL20:BMCSBL20:PB:0R4JpmPB:Y:SBL
PCRSBL30:BMCSBL30:PB:0R4JpmPB:Y:SBL
PMCSBL20:BCRSBL20:PB:0R4JpmPB:Y:SBL
<primary>:<standby>:<LOB>:<sys/dbsnmp password>:<Applicationname>