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.

[sourcecode language=”bash”]

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

[/sourcecode]

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.

[sourcecode language=”bash”]

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

[/sourcecode]

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.

[sourcecode language=”bash”]

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

[/sourcecode]

Resources

Leave a Comment

Your email address will not be published. Required fields are marked *