Thursday, February 7, 2013

Shell script to check Oracle Tablespace usage



I searched a shell script to check Oracle Tablespace usage, most scripts returned use complex SQL statements and they don’t report usage accurately, because auto-extend or multiple data files was not taken into account for calculation. Actually, there is a built-in view “dba_tablespace_usage_metrics” for the purpose starting from Oracle 10g. 
The following script check the Oracle database availability or tablespace usage and measure the response time.The scripts output “key=value” format, which can be easily discovered by LLD in Zabbix.(with LLD, Zabbix can dynamically discover any number of items to monitor without adding the items manually )

Script sample output

db-time= 71
db-status=[OK]: Name:SYSAUX SizeMB:1024 Used%: 73 ; Name:SYSTEM SizeMB:1024 Used%: 72 ; Name:USERS SizeMB:5 Used%: 20 ; Name:TEMP SizeMB:2048 Used%: 2 ; Name:UNDOTBS1 SizeMB:2048 Used%: 1 ;  8 rows selected.



The Oracle login in the script should have permission to read the view or have “select_catalog_role” role granted.

Script detail

#!/bin/ksh
function checkdb {
TNSNAME=$1
OUSER=$2
OPASS=$3

ORACLE_HOME=${ORACLE_HOME:=/u01/app/oracle/product/11.2.0/client_1}
export ORACLE_HOME
t1="$(date +%s%N)"

rt=$($ORACLE_HOME/bin/sqlplus -S ${OUSER}/${OPASS}@${TNSNAME}<< _END
set heading off
set linesize 200
select
   'Name:'|| tablespace_name,
   'SizeMB:'||round(TABLESPACE_SIZE*8/1024)||' Used%:',
   round(used_percent),
   ';'
from
   dba_tablespace_usage_metrics
order by 3 desc;
exit;
_END)

t2="$(date +%s%N)"
echo "db-time= $((($t2 - $t1)/1000000))"
#remove blank lines,ignore UNDOTBS,get the numeric value by removing tab and spaces
tbpct=$(echo "$rt" | egrep -v '^$|UNDOTBS' | head -1 | sed 's/.*Used%:\(.*\);/\1/'  |  sed 's/[ \t]*//g')
#Critical condition: thresh-hold > 95 or non-numeric value returned
if [ $tbpct -gt 95 ] || [[ "$tbpct" != +(\d) ]] ; then
 echo "db-status=[CRITICAL]:" $rt
else
 echo "db-status=[OK]:" $rt
fi
}




No comments:

Post a Comment