Script to monitor Oracle ASM diskgroup

Script to monitor Oracle ASM diskgroup and drop mail if threshold crossed

--------------------------------------------
/u00/oracle/SCRIPTS/asm/asm_diskgroup.sh
--------------------------------------------
set -x
export ORACLE_SID=<<DB_SID>>
export ORACLE_BASE=/u00/oracle
export ORACLE_HOME=/u00/oracle/product/10205
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

${ORACLE_HOME}/bin/sqlplus  "sys/India123@ASM_Testing.india as sysdba" << EOF
@/u00/oracle/SCRIPTS/asm/asm_diskgroup_status_Testing.sql
EOF

cat /u00/oracle/SCRIPTS/asm/asm_diskgroup_status_Testing.html > /u00/oracle/SCRIPTS/asm/asm_status_final.html

t1=7
t2=`grep "no rows selected" /u00/oracle/SCRIPTS/asm/asm_status_final.html|wc -l`
echo $t1
echo $t2
if [ "$t1" == "$t2" ]
then
echo "no mail"
else
export MAILTO="er.vaibhav1425@gmail.com"
export SUBJECT="ASM DISKGROUP STATUS REPORT(>85%)- "
TIME=$(date +"%d-%m-%Y")
export CONTENT="/u00/oracle/SCRIPTS/asm/asm_status_final.html"
(
echo "To: $MAILTO"
echo "Subject: $SUBJECT $TIME"
echo "MIME-Version: 1.0"
echo 'Content-Type: multipart/mixed; boundary="-q1w2e3r4t5"'
echo
echo '---q1w2e3r4t5'
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
cat $CONTENT
echo '---q1w2e3r4t5'
) | /usr/sbin/sendmail -r Oracle_asm@testing.com $MAILTO
fi
set +x

--------------------------------------------

<<END>>

--------------------------------------------

/u00/oracle/SCRIPTS/asm/asm_diskgroup_status_Testing.sql

--------------------------------------------

SPOOL /u00/oracle/.JLR_SCRIPTS/asm/asm_diskgroup_status_Testing.html

COLUMN spool_time NEW_VALUE _spool_time NOPRINT

 SELECT TO_CHAR (SYSDATE, 'DD Mon YYYY') spool_time

  FROM DUAL;

CLEAR COLUMNS BREAKS COMPUTES

SET markup html on -

table  'WIDTH="95%" align="center" BORDER="1"'

SET feedback off

COLUMN dbname                 FORMAT a25           HEADING 'DATABASE NAME' ENTMAP off

COLUMN group_name             FORMAT a25           HEADING 'DISK GROUP NAME' ENTMAP off

COLUMN state                  FORMAT a11           HEADING 'STATUS'  ENTMAP off

COLUMN total_mb               FORMAT 999,999,999   HEADING 'TOTAL SIZE (GB)' ENTMAP off

COLUMN used_mb                FORMAT 999,999,999   HEADING 'USED SIZE (GB)' ENTMAP off

COLUMN pct_used               FORMAT 999.99        HEADING 'PERCENTAGE USED' ENTMAP off

set lines 300

set pages 300

SET feedback      ON

prompt DB_NAME - QA(<<Server_details>>) ASM DISKGROUP Status 

--<--- Your wish in prompt

SELECT

    c.db_name                                   dbname

  ,  dg.name                                     group_name

  , dg.state                                    state

  , dg.total_mb/1024                                 total_gb

  , (dg.total_mb - dg.free_mb)/1024                     used_gb

  , ROUND((1- (dg.free_mb / dg.total_mb))*100, 2)  pct_used

FROM

    v$asm_diskgroup dg,v$asm_client c

WHERE

     dg.group_number =

 c.group_number and dg.total_mb != 0 and ROUND((1- (free_mb / total_mb))*100, 2) >=85 and dg.name not like '%REDO%' and c.db_name not like '%ASM%' and c.db_name not like '%mgmtdb%' ORDER BY  pct_used desc;

SET feedback      ON

SET heading       ON

SET verify        ON

SET wrap          OFF

SET trimspool     OFF

SET serveroutput  OFF

SET escape        OFF

SET MARKUP HTML OFF

SET TERMOUT ON

Spool off;