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
--------------------------------------------
/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;