Monitoring Flash Recovery Area space using shell script

This script will check the flash recovery area and will shoot a mail if the space is over 80% 

filled up.

From oracle user:

[root@server1 ~]# vi /home/oracle/flashback.sql

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999

col round(space_used/1048576) heading "Space Used (MB)" format 99999

col round((space_used/space_limit)*100) heading " % used "  format 99999

col name format a30

set head off

select name, round(space_limit/1048576),round(space_used/1048576),round ((space_used/space_limit)*100) as "% used"

from  v$RECOVERY_FILE_DEST;

exit;

:wq

Now lets create the shell-script which will monitor the space usage of flash recovery area:-

From root user

[root@server1 ~]# vi /root/spacecheck.sh

su - oracle -c "sqlplus -S / as sysdba @/home/oracle/flashback.sql" > /home/oracle/test.txt

space=`cat /home/oracle/test.txt | awk '{print $4}'`

if

[ $space -gt 80 ]; then

mail -s "Attention!! Low space in Flash recovery area! " yourmailid@gmail.com

fi

exit 0

:wq

We schedule the script which will check in every 5 mins.

[root@server1 ~]crontab -e

*/5 * * * * /root/spacecheck.sh > /dev/null