Dear Readers,

In my previous posts, I discussed about tablespace/FRA/RMAN backup/Patchset level monitoring using python. But none of these blog posts explained about connecting to multiple Oracle database using same program. Many of my blog followers requested me to write a code which connects to multiple databases & executes some query.

So here comes my new python code for creating DB inventory, which will pull some important details about each of databases in the list.

Note: This code is ready to use & only works on Oracle DB with versions 18+ . In case you have some databases below version 18 in your environment you can use this script.

Prerequisites:

• python binaries with version 3
• cx_Oracle module
• You will create a file named conn_details.txt. This file will contain connection details about all databases in your environment which includes Username, Password, Hostname, Port & service name. This user should have necessary privileges on DB to run queries mentioned in your program. I am using below file for my demo.
• Program file, db_inventory.py or db_inventory12.py & conn_details.txt should be present in same folder

What this program does:

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

• DB Type
• Version
• Host Name
• Instance Name
• DB Name
• DB size in GB
• Archive log Mode
• Flashback
• Startup Time

Note: This program also handles exceptions such as file does not exists or any ORA-errors received while running queries on any of databases.

Actual code:

# Script Name: db_inventory.py
# Description: creating DB inventory by connecting mutiple oracle DB's. Oracle DB version should be 18 or +
# Usage: python db_inventory.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 = ("DBType","Version","hostname","InstanceName","DBName","DBSize","ArchivelogMode","Flashback","StartupTime")
print(head)
for line in handle:
line = line.rstrip()
pieces = line.split(",")
username = pieces[0]
password = pieces[1]
hostname = pieces[2]
port = pieces[3]
service = pieces[4]
try:
con = cx_Oracle.connect(username,password,hostname + ':' + port + '/' + service)
cursor=con.cursor()
for result in cursor.execute("select distinct 'Oracle,'||i.version_full||','||host_name||','||instance_name||','||d.name||','||(a.total_size + nvl(b.total_size,0))||','||log_mode||','||FLASHBACK_ON||','||STARTUP_TIME from v$database d, v$instance i,(select round(sum(bytes)/1024/1024/1024) total_size from dba_data_files) a ,(select round(sum(bytes)/1024/1024/1024) total_size from dba_temp_files) b"):
result=str(result)
print(style.YELLOW + result.format(head) + style.RESET)
except cx_Oracle.DatabaseError as err:
print(style.RED +"Script execution failed for :"+service+'=>', err )
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:

In case query fails to connect one of databases in the list, sample output is given below:

Here comes result for script designed if you have databases below version 18:

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:

db_inventory

Hope u will find this post very useful.

Cheers

Regards,
Adityanath