# SCRIPT WILL GENERATE OUTPUT AS BELOW 
# [oracle@ ~]$ ./dg_archive_gap_listing.sh
# Primary_Arc_No=62769 but Stby_Arc_No=62754
#

#!/bin/bash
#set Oracle environment for Sql*Plus
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=PROD
export PATH=$PATH:$ORACLE_HOME/bin

#set working directory. script is located here..
cd /home/oracle/scripts

#Problem statement is constructed in message variable
MESSAGE=""

#hostname of the primary DB.. used in messages..
HOST_NAME=`hostname`


#Get Data guard information to Unix shell variables...

MESSAGE=`
sqlplus -s sys/pegasyspl as sysdba <<EOSQL
set pages 0 feed off
set serverout on size 1000000;
Var Rc Number;
Declare
  Local_Arc		Number:=0;
  Stby_Arc		Number:=0;
  Dr_Arc 		Number:=0;
  Stby_Apply		Number:=0;
  Dr_Apply              Number:=0;
  Msg			Varchar2(4000):='';
Begin
  :Rc:=0;
  Select Archived_Seq# Into Local_Arc
    From V\\$Archive_Dest_Status Where Dest_Id=1;
  Select Archived_Seq#, Applied_Seq#
    Into Stby_Arc, Stby_Apply 
    From V\\$Archive_Dest_Status Where Dest_Id=2;
  Select Archived_Seq#, Applied_Seq#
    Into Dr_Arc, Dr_Apply
    From V\\$Archive_Dest_Status Where Dest_Id=3;

  If Local_Arc > Stby_Arc + 10 Then
    Msg:=Msg||'Primary_Arc_No='||Local_Arc||' but Stby_Arc_No='||Stby_Arc||Chr(13) ;
    :Rc:=1;
  End If;

  If Stby_Arc > Stby_Apply + 10 Then  
    Msg:=Msg||'Stby_Arc_No='||Stby_Arc||' but Stby_Apply_No='||Stby_Apply||Chr(13) ;
    :Rc:=1;
  End If;

  If :Rc != 0 Then
     Dbms_Output.Put_Line(Msg);
  End If;

  If Dr_Arc > Dr_Apply + 10  Then
    Msg:=Msg||'Dr_Arc_No='||Dr_Arc||' but Dr_Apply_No='||Dr_Apply||Chr(13);
    :Rc:=1;
  End If;

  If :Rc != 0 Then 
     Dbms_Output.Put_Line(Msg); 
  End If;
End;
/
exit :rc
EOSQL`
echo $MESSAGE
if [ $? -ne 0 ]; then
  MESSAGE="Error on $HOST_NAME Standby -log APPLY- service!\n${MESSAGE}\nThis problem may cause the archive directories to get full!!!\n\n"
  echo -e $MESSAGE
fi