sscript to send mail when tablespace free < 15%

From: herta <herta.vandeneynde_at_gmail.com>
Date: Sun, 16 Sep 2007 22:57:20 -0000
Message-ID: <1189983440.395881.104140@w3g2000hsg.googlegroups.com>

After seeing our tablespaces filling up time and again, here's the script I wrote to monitor their free space. Works for both fixed size and autoextensible tablespaces.

Thought I'd share.

Kind regards,

Herta

#!/bin/bash
#+
# Description
# ===========
# script to monitor the free tablespace of the running databases
# sends a mail listing the tablespaces and their free% if free% < 15
#
# assumes that
# - ORACLE_HOME has been defined
# - the script is run from an account with dba privs
#
# Modification History
# ====================
# Date | Name | Modification Description
# ------------+------------------------

+---------------------------------------


# 14-Sep-2007 | Herta Van den Eynde    | initial version

#             |                        |

#_

#+
# initializations
# BFN = this script's basename
# DBI = database instance
# DBL = database instance list
# FS = free space
# MR = mail message recipient
# MS = mail message subject
# REC = record
# TOF = temporary output file
#_

typeset -i FS=0
MR="my.mail_at_comp.dom"
BFN=`basename $0`
TOF1=/SYS/LOC/ORAadm/log/${BFN%.*}.1.`date +%Y%m%d` TOF2=/SYS/LOC/ORAadm/log/${BFN%.*}.2.`date +%Y%m%d`

#+
# redirect output to $TOF1
#_

exec 3>&1
exec > $TOF1

#+
# get list of active databases
#_

DBL=$(ps auxw | grep smon | egrep -v -e ASM -e grep | while read REC; do
  echo ${REC##oracle*_}
done)

#+
# get tablespace_name + free %)
# if tablespace has no free extents, it will not show up in
dba_free_space
#
# if extensible = 'NO' then free% =
# current_free * 100 / current_size
# if extensible = 'YES' then free% =
# ( max_size - ( current_size - current_free )) * 100 / max_size
#_

for DBI in $DBL; do
  export ORACLE_SID=$DBI
  SO=$( sqlplus -s << EOF

      /as sysdba
      SET FEEDBACK OFF HEADING OFF
      SPOOL $TOF2
      SELECT
          ddf.tablespace_name
        , ddf.extensible
        , ROUND( GREATEST( NVL( dfs.current_free, 0 ), ddf.max_size -

( ddf.current_size - NVL( dfs.current_free, 0 ))) * 100 / GREATEST( ddf.current_size, ddf.max_size ), 0 ) "free%"

      FROM
          ( SELECT
                tablespace_name
              , MAX( autoextensible ) as extensible
              , ROUND( SUM ( bytes )/1024/1024, 2) as current_size
              , ROUND( SUM ( maxbytes )/1024/1024, 2) as max_size
            FROM
                dba_data_files
            GROUP BY
                tablespace_name
            ORDER BY
                tablespace_name
          ) ddf
        , ( SELECT
                tablespace_name
              , ROUND( SUM( bytes )/1024/1024, 2 ) as current_free
            FROM
                dba_free_space dfs
            GROUP BY
                tablespace_name
            ORDER BY
                tablespace_name
          ) dfs
      WHERE
        dfs.tablespace_name = ddf.tablespace_name(+);
      SPOOL OFF

EOF
  ) # end of SO

  cat $TOF2 | while read TS E FS ; do
    if [ -n "$TS" ]; then

      if [ $FS -lt 15 ]; then
        echo "database $DBI tablespace $TS extensible $E free% $FS"
      fi

    fi
  done
  echo
done

#+
# terminate output to $TOF1
#_

exec 1>&3 3>&-

#+
# send mail based on test results
#_

MH="Tablespace check failed on `hostname -s` - checked $( echo $DBL | wc -w ) dbs"

if [ -s $TOF ]; then
  MH="Tablespace check failed on `hostname -s` - checked $( echo $DBL | wc -w ) dbs"
else
  MH="Tablespace check OK on `hostname -s` - checked $( echo $DBL | wc -w ) dbs"
fi

mail -s "$MH" "$MR" < $TOF1

#+
# clean up and end processing
#_

if [ -n $TOF1 ]; then
  find `dirname $TOF1` -name "${BFN%.*}.*" -atime +28 -exec rm {} \; fi

exit Received on Sun Sep 16 2007 - 17:57:20 CDT