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

.

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.

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

You can download sample files from below location:

DR_sync

Hope u will find this post very useful.

Cheers

Regards,
Adityanath