mardi 5 avril 2016

Shell script to check the ASM diskgroup space in oracle and send a mail

I am trying to write a shell script to monitor ASM instance in ORACLE and mail the respective people in mail list. I have written the following script.

#/bin/sh    
#set -x
    USER=xxx
    PASS=yyy
    CC_OFFSHORE="xxx@hang.com , yyy@hang.com , zzz@hang.com"
    CC_TEAM="$CC_OFFSHORE , special_recipient@hang.com"
    . /home/oracle/.TESTenv             #(NOT MANDATORY. DEPENDS ON YOUR ENVIRONMENT)
    sqlplus -s $USER/$PASS << EOF
    spool /home/oracle/SABARISH/RETVAL.log
    set linesize 140
    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
    col group_number format 999
    col diskgroup  format a20
    col total_mb  format 999,999,999
    col free_mb  format 999,999,999
    col tot_used format 999,999,999
    col pct_used format 999
    col pct_free format 999
    select group_number,
           name diskgroup,
           total_mb,
           free_mb,
           total_mb-free_mb tot_used,
           pct_used,
           pct_free
      from (select group_number,name,total_mb,free_mb,
                 round(((total_mb-nvl(free_mb,0))/decode(total_mb,0,1,total_mb))*100) pct_used,
                 round((free_mb/total_mb)*100) pct_free
          from v\$asm_diskgroup
          where total_mb >0
          order by pct_free
         )
    /
    spool off;
    EOF

    while read -r values
    do
        USED_PCT=$(echo $values | awk '{print $6}')
        DISKGROUP_NAME=$(echo $values | awk '{print $2}')
        WARNING_LIMIT=60
        CRITICAL_LIMIT=70
        if [ ${USED_PCT} -ge ${WARNING_LIMIT} ] && [ ${USED_PCT} -lt ${CRITICAL_LIMIT} ]
        then
            echo "WARNING ALERT. $DISKGROUP_NAME disk has used $USED_PCT%" | mailx -s "(WARNING ALERT $USED_PCT% used)" $CXC_OFFSHORE
        elif [ ${USED_PCT} -ge ${CRITICAL_LIMIT} ]
        then
            echo "CRITICAL ALERT.$DISKGROUP_NAME disk has used $USED_PCT%" | mailx -s "(CRITICAL ALERT $USED_PCT% used)" $CXC_TEAM
        fi
    done < /home/oracle/SABARISH/RETVAL.log

The above script is working perfect.

All I need to do is add few more details to the script. Before executing the ASM instance query, we need to check whether the DB is up and running.

And check whether the password for the particular user has expired or active.

And then run the ASM query. If both the above constraints fail, then it shouldn't execute the ASM instance query. It should exit.

How do I achieve it?

Aucun commentaire:

Enregistrer un commentaire