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

1 comment:

  1. while executing the script i am getting below error.

    ./spacecheck.sh[4]: 4: 0403-012 A test command parameter is not valid.

    ReplyDelete