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
