REQUIREMENT:

Write a shell script, which will trigger a mail alert, if the utilization of the asm diskgroup reached 90 percent.

SOLUTION:

1. Below is the shell script.

Make sure to update ORACLE_HOME, ORACLE_SID inside the shell script.

cat /export/home/oracle/asm_dg.sh

export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=PRODDB1
export PATH=$ORACLE_HOME/bin:$PATH
logfile=/export/home/oracle/asm_dg.log
sqlplus -s "/as sysdba" > /dev/null << EOF spool $logfile 
SET LINESIZE 150 
SET PAGESIZE 9999 
SET VERIFY off
COLUMN group_name 
FORMAT a25 HEAD 'DISKGROUP_NAME' 
COLUMN state FORMAT a11 HEAD 'STATE' 
COLUMN type FORMAT a6 HEAD 'TYPE' 
COLUMN total_mb FORMAT 999,999,999 HEAD 'TOTAL SIZE(GB)' 
COLUMN free_mb FORMAT 999,999,999 HEAD 'FREE SIZE (GB)' 
COLUMN used_mb FORMAT 999,999,999 HEAD 'USED SIZE (GB)' 
COLUMN pct_used FORMAT 999.99 HEAD 'PERCENTAGE USED' 

SELECT distinct name group_name , state state , type type , 
round(total_mb/1024) TOTAL_GB , round(free_mb/1024) free_gb , 
round((total_mb - free_mb) / 1024) used_gb , 
round((1- (free_mb / total_mb))*100, 2) pct_used from 
v$asm_diskgroup where round((1- (free_mb / total_mb))*100, 2) > 90 ORDER BY name;
spool off
exit
EOF
count=`cat $logfile|wc -l`
#echo $count
if [ $count  -ge 4 ];
 then
  mailx -s "ASM DISKGROUP REACHED 90% UTILIZATION" support@dbaclass.com < $logfile
fi

2. Give proper permission:

chmod 755 /export/home/oracle/asm_dg.sh

3. Configure in crontab:

0,15,30,45 * * * * /export/home/oracle/asm_dg.sh