Tablespace Health Check Monitoring Script - Linux

 Generic Tablespace
Health Check Monitoring Script

Generic Tablespace health check monitoring script for Unix/Linux and Solaris Environment.

#!/bin/sh

# ##############################################################################################

# Generic Tablespace Health Check Monitoring Script

#

# ===============================================================================

# Usage:

#

# Run the script to any where at Oracle Server (Linux/Solaris)

# Rus as Oracle User

# Eg : ./mon.sh <Low Threshold> <High Threshold>

#      ./mon.sh 5 10

#

# Prepared by : Amit Kumar Srivastava      15-May-2016

#               OCM11g

# ===============================================================================

# ##############################################################################################

#

#

# Variables for High and Low Threshold

# #########################################

hth=${2}

lth=${1}

# #########################

# Setting ORACLE_SID

# #########################

for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

   do

    export ORACLE_SID

# #########################

# Setting ORACLE_HOME

# #########################

  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`

  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:

if [ -f /etc/oratab ]

  then

  ORATAB=/etc/oratab

  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`

  export ORACLE_HOME

## If OS is Solaris:

elif [ -f /var/opt/oracle/oratab ]

  then

  ORATAB=/var/opt/oracle/oratab

  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`

  export ORACLE_HOME

fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:

if [ -z "${ORACLE_HOME}" ]

 then

  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail

-1`

  export ORACLE_HOME

fi

# ###############################

# Generate DB Tablespace details

# ###############################

TBSDATA=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF

set linesize 190 pages 0 echo off feedback off

spool /tmp/tablespace_usage.log

select tablespace_name||':'||round(pct_free) from (

SELECT  a.tablespace_name tablespace_name,

       ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,

       ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,

       ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free

FROM  ( SELECT  f.tablespace_name,

               SUM(f.bytes) bytes_alloc,

               SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        FROM DBA_DATA_FILES f

        GROUP BY tablespace_name) a,

      ( SELECT  f.tablespace_name,

               SUM(f.bytes)  bytes_free

        FROM DBA_FREE_SPACE f

        GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name (+)

UNION

SELECT h.tablespace_name,

       ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),

       ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),

       ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)

FROM   sys.V_\$TEMP_SPACE_HEADER h, sys.V_\$TEMP_EXTENT_POOL p

WHERE  p.file_id(+) = h.file_id

AND    p.tablespace_name(+) = h.tablespace_name

GROUP BY h.tablespace_name)

where  pct_free < 15;

spool off

exit;

EOF

)

# ######################################

# Notifying Tablespace threshold value

# ######################################

echo '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^'

echo 'Tablespace Monitoring for Database : '$ORACLE_SID  $lth ' -- ' $hth

echo '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^'

for tbsname in `awk  '{print $1}' /tmp/tablespace_usage.log`

do

z=$tbsname

t=`echo $z|grep -v '^$'|cut -f1 -d ':'`

v=`echo $z|grep -v '^$'|cut -f2 -d ':'`

 if [ "$v" -le $lth ];

  then

    echo 'Database ' $ORACLE_SID' : '$t  ' tablespace reached to below critical threshold is '$v

    echo 'SET YOUR NOTIFICATION MAIL'

 else

    echo 'Database ' $ORACLE_SID' : '$t  ' tablespace reached to below warning threshold is '$v

    echo 'SET YOUR NOTIFICATION MAIL'

 fi

done

#rm -f /tmp/tablespace_usage.log

done