Creating Oracle DR/Standby synchronization report using Python scripting

Dear Readers,

In my previous posts, I discussed about tablespace/FRA/RMAN backup/Patchset level monitoring/DB inventory using python.

Here comes my new python code for generating DR/Standby synchronization status report from the list of databases provided in the DB list.

Note: This code is ready to use. You may need to make changes in threshold values for archive lag as per your need.

Prerequisites:

  • python binaries with version 3
  • cx_Oracle module
  • Program file: dr_sync.py & conn_details.txt should be present in same folder
  • You will create a file named conn_details.txt. This file will contain connection details about all databases in your environment which includes SYS, SYS Password, TNS entry for Primary database followed by TNS entry for Standby database. I am using below file for my demo

.2

What this program does:

At the end of execution, it displays following details about each database mentioned in conn_details.txt.

  • DB Name
  • Maximum sequence# archived on primary database
  • Maximum sequence# appplied on standby database
  • Archive lag

Note:

This program also handles exceptions such as file does not exists or any ORA-errors received while running queries on any of databases. Such line will be printed in red color.
This program also prints a line in yellow color in case archive lag between primary & standby databases is more than 3.

Actual code:


# Script Name: dr_sync.py
# Description: Script to dispaly DR sync status
# Usage: python dr_sync.py
# Author: Adityanath Dewoolkar
# Version: 1

# importing required modules
import cx_Oracle
import getpass
import os

# For conditional coloring
os.system("")
class style():
RED = '\033[31m'
GREEN = '\033[32m'
YELLOW = '\033[33m'
RESET = '\033[0m'


list = "conn_details.txt"
try:
handle = open(list)
head = ("DBName","Primary","Standby","Difference")
print(head)
for line in handle:
line = line.rstrip()
pieces = line.split(",")
username = pieces[0]
password = pieces[1]
tns_prod = pieces[2]
tns_dr = pieces[3]
try:
con = cx_Oracle.connect(username,password,tns_prod,cx_Oracle.SYSDBA)
cursor=con.cursor()
for result1 in cursor.execute("select max(sequence#) from v$log where archived='YES' "):
result1 = str(result1)
result1 = result1[1:-2]
result1 = int(result1)
except:
result1="ERROR"
try:
con = cx_Oracle.connect(username,password,tns_dr,cx_Oracle.SYSDBA)
cursor=con.cursor()
for result2 in cursor.execute("select max(sequence#) from v$archived_log where applied='YES' "):
result2 = str(result2)
result2 = result2[1:-2]
result2 = int(result2)
diff = result1-result2
final = tns_prod,result1,result2,diff
final = str(final)
if diff > 3:
print(style.YELLOW + final.format(head) + style.RESET)
else:
print(style.GREEN + final.format(head) + style.RESET)
except:
result2="ERROR"
diff="ERROR"
final = tns_prod,result1,result2,diff
final = str(final)
print(style.RED + final.format(head) + style.RESET)
except :
print(style.RED + "Make sure conn_details.txt is present in this folder with names of backup output files"+ style.RESET)

Here comes the result: First line shows DR is in sync, so printed in green color. Second line shows script is unable to connect to DB so printed in red color & last line is printed in yellow color as archive lag is more than 3.

gifgit

In case, conn_details.txt is not present in same folder location for python script, it will show below exception message:

gifgit (1)

You can download sample files from below location:

DR_sync

Hope u will find this post very useful.

Cheers

Regards,
Adityanath

3 replies

  1. Hey there! Someone in my Facebook group shared this site with us so I came to give it a look. I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers! Exceptional blog and fantastic style and design.

  2. Greetings from Los angeles! I’m bored at work so I decided to browse your website on my iphone during lunch break. I enjoy the information you present here and can’t wait to take a look when I get home. I’m surprised at how quick your blog loaded on my cell phone .. I’m not even using WIFI, just 3G .. Anyways, very good site!

  3. Very efficiently written article. It will be useful to anyone who utilizes it, as well as myself. Keep up the good work – i will definitely read more posts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s