Creating Oracle DB inventory using Python scripting

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

6 replies

    • It will basically query all databases you mentioned in conn_details.txt.

      But in case you are looking for cdb along with pdb for size, you may need to make necessary changes in SQL query.

      Regards,
      Adi

  1. I think the admin of this web page is reallyworking hard in favor of his website, becausehere every data is quality based data.

  2. I just like the helpful information you supply to your articles. I’ll bookmark your blog and test once more right here regularly. I’m quite certain I’ll be told lots of new stuff proper right here! Good luck for the following!

  3. I have read some excellent stuff here. Certainly value bookmarking for revisiting.I surprise how a lot attempt you put to create this kind of excellentinformative web site.

Leave a Reply to Balaji Cancel 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