Dataguard: Shell script to List Archive gap between Primary & Standby

I was looking for a NAGIOS monitoring script which will list the archive gap between Primary and 2 of my Standby databases.

http://www.oracledbasupport.co.uk/wp-content/uploads/2010/09/dg_archive_gap_listing-1.txt

When compiled the script will return results as below

[oracle@scripts]$ ./dg_archive_gap.sh
Primary_Arc_No=61077 but Stby_Arc_No=61066

[oracle@scripts]$ ./dg_archive_gap.sh
Primary_Arc_No=61088 but Stby_Arc_No=61047
Stby_Arc_No=61067 but Stby_Apply_No=61047
Dr_Arc_No=61067 but Dr_Apply_No=61047

If you have multiple standby databases , please make sure you Add SQL stmts with “Dest_Id=X” , here 1 (Primary), 2 (Standby) & 3 (DR standby)

#!/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/sys 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(10) || ‘–‘;
: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(10) || ‘–‘;
: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(10)|| ‘–‘;
: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