Sunday, May 24, 2020

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.

Thursday, May 21, 2020

Oracle Database Firewall - 12.2.0.12.0




OGYatra 2019 - Memories - Session by ME @Vizag

Oracle Groundbreakers Yatra (July 25,2019) – Vizag - What a Success! We would like to take this opportunity to express heartfelt thanks to all Delegates, Speakers, AIOUG and Oracle Team for active participation and support. We hope you had fun, and we look forward to seeing you at the next event. Please check following links for more pics I got an opportunity to present on Oracle HA & 19c new features in this event. Amazing crowd and well received session. Below is the link of OGYatra event from our page - All India Oracle Users Group



https://www.facebook.com/aioug/posts/3099313406777759

Here is me presenting the session -



See you soon in OGYatra2020, Call for papers is open!