Oracle FRA/Flash Recovery Area monitoring using Python scripting

Dear Readers,

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.

Prerequisites:

  • 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.

Actual code:


# 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[3]
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:

fra.py

Hope u will find this post very useful.

Cheers

Regards,
Adityanath

9 replies

  1. Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.

  2. These are actually great ideas in concerning blogging.

  3. 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!

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