Oracle DR - RPO Calculator (OCI & ON-Prem)

Requirement -

Calculate RPO (Recovery Point Objective) in minutes for DR. Instead of getting Log gap alerts for a miss of 1 or 2 archives due to network congestion, I have decided to write a script which triggers alerts based on the RPO.

Dependencies -

SYSDBA access required to fetch the info from DR.
TNS Entries - Assumed every environment at Primary has the first tns entry of Primary DB server.
- Assumed every environment has _DR/_DG/_ST extensions for the tns entries of DR server.

Shell Script

#!/bin/bash
DRDIR=/home/oracle/shell_scr/drlag
mkdir -p $DRDIR
grep '=$' $ORACLE_HOME/network/admin/tnsnames.ora | grep ^[A-Z] | tr "=" "\t" > $DRDIR/INPFILE.out
pass=`cat /home/oracle/shell_scr/.pass`
DRDIR=/home/oracle/shell_scr/drlag
for DB in `cat $DRDIR/INPFILE.out`; do
DBNAME=$DB
#echo "DB name is $DBNAME"
#echo "Pass is $pass"
#echo "In SCN loop"
sqlplus -s "sys/${pass}@${DBNAME} as sysdba" < set head off
set pages 0
spool $DRDIR/${DBNAME}_scn.log
select to_char(current_scn) from v\$database;
spool off
EOF
scnnum=`cat $DRDIR/${DBNAME}_scn.log | head -1`
#echo "PRIM value : ${scnnum}"
#echo "Proceeding to Timestamp loop"
primtns=`grep '=$' $ORACLE_HOME/network/admin/tnsnames.ora | grep ^[A-Z] | tr "=" "\t" | grep -Evi 'st|dr|dg' | head -1`
sqlplus -s "sys/${pass}@${primtns} as sysdba" < set pages 0
set head off
spool $DRDIR/${DBNAME}_time.log
select to_char(scn_to_timestamp(${scnnum}),'YYYY-MM-DD HH24:MI:SS') as timestamp from dual;
spool off
EOF
done
cd $DRDIR
STBY_time=`ls -ltrh *time.log | grep -Ei '_st|dr' | awk {'print $9'} | head -1 | xargs cat | head -1`
PRIM_time=`ls -ltrh *time.log | grep -Evi '_st|dr' | awk {'print $9'} | head -1 | xargs cat | head -1`
echo "PRIM TIME : $PRIM_time"
echo "STBY TIME : $STBY_time"
echo ""
st_ss=`date --date "$STBY_time" +%s`
pr_ss=`date --date "$PRIM_time" +%s`
echo "PR SS : $pr_ss"
echo "ST SS : $st_ss"
echo ""
timgap=`expr $pr_ss - $st_ss`
echo ""
echo " Time Gap : $timgap Seconds"
echo ""
echo ""
if [ $timgap -ge 900 -a $timgap -le 1159 ];
then
echo "Warning - DR Log gap exceeded 15 minutes RPO"
elif [ $timgap -ge 1200 -a $timgap -le 1799 ];
then
echo "Critical - DR Log gap exceeded 20 minutes RPO"
elif [ $timgap -ge 1800 ];
then
echo "Disaster - DR Log gap exceed 30 minutes RPO"
else
echo "No Datalag"
fi

Sample Output

P.S. - Make necessary changes to the script to adapt to your environment if the above dependencies which suits my environment doesn't work for you.
If you are stuck you can directly reach me @ vnairaj@gmail.com, I can help you.