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 )
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
Note: Only a member of this blog may post a comment.