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.
- 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
- Host Name
- Instance Name
- DB Name
- DB size in GB
- Archive log Mode
- 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.
# 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 password = pieces hostname = pieces port = pieces service = pieces 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:
Hope u will find this post very useful.