In my previous post, I discussed about tablespace monitoring using python. Here comes my new python code to check FRA/Flash recovery area usage details in Oracle DB.
Note: This code is ready to use. But you can definitely refer it & modify wherever as per your need.
- python binaries with version 3
- cx_Oracle module
What this program does:
This program needs following inputs from user side:
- SYSTEM password
- TNS entry to connect to DB.
At the end of execution, it provides following details about FRA in database.
- FRA location
- Total area allocated in GB
- Free space left in GB
- FRA space used in %
Note: If FRA used space is more than 80% will be highlighted in RED color, while others will be in GREEN color. Also it will standard message in case FRA is not set.
# Script Name: fra.py # Description: FRA usage details in an Oracle DB # Usage: python fra.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' RESET = '\033[0m' # Prompt the user for a password without echoing password = getpass.getpass(prompt = 'Enter SYSTEM password: ', stream=None) # Enter TNS string to connect db_name = input("Enter DB name: ") # Code for getting FRA usage details in an Oracle DB try: con = cx_Oracle.connect("SYSTEM",password,db_name) cursor=con.cursor() head = ("FRA","TotalGB","FreeGB","Full%") for result in cursor.execute("SELECT NAME,(SPACE_LIMIT / 1024 / 1024 / 1024) Total_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS Free_GB,ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS Full_Percent FROM V$RECOVERY_FILE_DEST"): result=str(result) pieces = result.split() pieces = pieces free_percent = float(pieces[:-1]) if free_percent > 80: print(head) print(style.RED + result.format(head) + style.RESET) else: print(head) print(style.GREEN + result.format(head) + style.RESET) break else: print(style.GREEN + "FRA is not set. Kindly check ASM/Filesystem usage details."+ style.RESET) except cx_Oracle.DatabaseError as err: print("Script execution failed: ", err)
Here comes the result:
I am re-running code after fixing this alert. Here comes the new result:
ALL GREEN!!! HAPPY!!!!
Also we get below message in case FRA is not enabled on your DB:
You can download sample files from below location:
Hope u will find this post very useful.
Categories: Administration, automation, backup & recovery, Feature, Monitoring, python, Scripts
Very good Adi good one for monitoring
Thanks for visiting and appreciate your feedback.:-)
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.
These are actually great ideas in concerning blogging.
I used to be able to find good info from your blog posts.
Python and Oracle… Sign me in!! (really need to learn python)
That’s really helpful .. Can you add one more line. Alert on email if fra more than 80%
Appreciate the feedback!!!
I will cover email notifications in my next post.
Wow, marvelous blog layout! How long have you been blogging for?
you made blogging look easy. The overall look of your website is wonderful, as well
as the content!
Excellent article! We will be linking to this particularly great content on our website. Keep up the great writing.
I constantly spent my half an hour to read this weblog’s
content everyday along with a mug of coffee.