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