Script: Check Flash Recovery Usage

I’ve been trying to monitor the archive log usage for our databases, and I came up with this little script.  I thought I would share in case it would help anyone, but also, please correct me if there is a better way or you see anything wrong.

Here’s the basic version.  You can change the database names in the for loop.

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in FN91DMO FN91CFG FN91DEV FN91TST
do
 export ORACLE_SID=$e
 sqlplus -s / as sysdba >> $tmpfile <<!
 set head off
 set feedback off
 select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
 from v\$flash_recovery_area_usage
 where PERCENT_SPACE_USED > 0;
 exit;
!
done
sed '/^$/d' $tmpfile
rm $tmpfile

This next version calls a script to get the database names.  For example, I have the databases stored in an XML file, and I built a perl script that would read the XML file and print the database names.

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in $(buildDBList)
do
 export ORACLE_SID=$e
 sqlplus -s / as sysdba >> $tmpfile <<!
 set head off
 set feedback off
 select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
 from v\$flash_recovery_area_usage
 where PERCENT_SPACE_USED > 0;
 exit;
!
done
sed '/^$/d' $tmpfile
rm $tmpfile

Finally, this version calls a script to check the status of the database.  If the database is down or temporarily off, it skips that database.

tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in $(buildDBList)
do
 export ORACLE_SID=$e
 checkDBStatus $e 2>&1 > /dev/null
 if [ $? -eq 0 ]; then
   sqlplus -s / as sysdba >> $tmpfile <<!
   set head off
   set feedback off
   select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
   from v\$flash_recovery_area_usage
   where PERCENT_SPACE_USED > 0;
   exit;
!
 fi
done
sed '/^$/d' $tmpfile
rm $tmpfile

Resources

• Oracle Database Reference: V$FLASH_RECOVERY_AREA_USAGE
• Oracle Database Backup and Recovery Basics: 3.5 Setting Up a Flash Recovery Area for RMAN