Generic Tablespace
Health Check Monitoring Script
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
