6 # This Nagios plugin was created to check Oracle status
10 PROGPATH
=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
11 REVISION
="@NP_VERSION@"
18 echo " $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
19 echo " $PROGNAME --db <ORACLE_SID>"
20 echo " $PROGNAME --login <ORACLE_SID>"
21 echo " $PROGNAME --connect <ORACLE_SID>"
22 echo " $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
23 echo " $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
24 echo " $PROGNAME --oranames <Hostname>"
25 echo " $PROGNAME --help"
26 echo " $PROGNAME --version"
30 print_revision
$PROGNAME $REVISION
34 echo "Check Oracle status"
36 echo "--tns SID/IP Address"
37 echo " Check remote TNS server"
39 echo " Check local database (search /bin/ps for PMON process) and check"
40 echo " filesystem for sgadefORACLE_SID.dbf"
42 echo " Attempt a dummy login and alert if not ORA-01017: invalid username/password"
44 echo " Attempt a login and alert if an ORA- error is returned"
46 echo " Check local database for library and buffer cache hit ratios"
47 echo " ---> Requires Oracle user/password and SID specified."
48 echo " ---> Requires select on v_$sysstat and v_$librarycache"
50 echo " Check local database for tablespace capacity in ORACLE_SID"
51 echo " ---> Requires Oracle user/password specified."
52 echo " ---> Requires select on dba_data_files and dba_free_space"
53 echo "--oranames Hostname"
54 echo " Check remote Oracle Names server"
56 echo " Print this help screen"
58 echo " Print version and license information"
60 echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
61 echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
62 echo "tnsnames.ora file is locatable and is properly configured."
64 echo "When checking local database status your ORACLE_SID is case sensitive."
66 echo "If you want to use a default Oracle home, add in your oratab file:"
67 echo "*:/opt/app/oracle/product/7.3.4:N"
95 print_revision
$PROGNAME $REVISION
99 print_revision
$PROGNAME $REVISION
104 # Hunt down a reasonable ORACLE_HOME
105 if [ -z "$ORACLE_HOME" ] ; then
107 for oratab
in /var
/opt
/oracle
/oratab
/etc
/oratab
109 [ ! -f $oratab ] && continue
111 while read SID ORACLE_HOME junk;
113 if [ "$SID" = "$2" -o "$SID" = "*" ] ; then
118 [ -n "$ORACLE_HOME" ] && break
122 [ -z "$ORACLE_HOME" -a -d $PROGPATH/oracle
] && ORACLE_HOME
=$PROGPATH/oracle
124 if [ "$cmd" != "--db" ]; then
125 if [ -z "$ORACLE_HOME" -o ! -d "$ORACLE_HOME" ] ; then
126 echo "Cannot determine ORACLE_HOME for sid $2"
130 PATH
=$PATH:$ORACLE_HOME/bin
131 LD_LIBRARY_PATH
=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
132 export ORACLE_HOME PATH LD_LIBRARY_PATH
137 tnschk2
=` echo $tnschk | grep -c OK`
138 if [ ${tnschk2} -eq 1 ] ; then
139 tnschk3
=` echo $tnschk | sed -e 's/.*(//' -e 's/).*//'`
140 echo "OK - reply time ${tnschk3} from $2"
143 echo "No TNS Listener on $2"
148 namesctl status
$2 |
awk '
149 /Server has been running for:/ {
151 for (i = 6; i <= NF; i++) {
157 msg = "CRITICAL: " $0
158 status = '$STATE_CRITICAL'
166 pmonchk
=`ps -ef | grep -v grep | grep -c "ora_pmon_${2}$"`
167 if [ ${pmonchk} -ge 1 ] ; then
168 echo "${2} OK - ${pmonchk} PMON process(es) running"
170 #if [ -f $ORACLE_HOME/dbs/sga*${2}* ] ; then
171 #if [ ${pmonchk} -eq 1 ] ; then
172 #utime=`ls -la $ORACLE_HOME/dbs/sga*$2* | cut -c 43-55`
173 #echo "${2} OK - running since ${utime}"
177 echo "${2} Database is DOWN"
182 loginchk
=`sqlplus dummy/user@$2 < /dev/null`
183 loginchk2
=` echo $loginchk | grep -c ORA-01017`
184 if [ ${loginchk2} -eq 1 ] ; then
185 echo "OK - dummy login connected"
188 loginchk3
=` echo "$loginchk" | grep "ORA-" | head -1`
189 echo "CRITICAL - $loginchk3"
194 connectchk
=`sqlplus $2 < /dev/null`
195 connectchk2
=` echo $connectchk | grep -c ORA-`
196 if [ ${connectchk2} -eq 0 ] ; then
197 echo "OK - login successful"
200 connectchk3
=` echo "$connectchk" | grep "ORA-" | head -1`
201 echo "CRITICAL - $connectchk3"
206 if [ ${5} -gt ${6} ] ; then
207 echo "UNKNOWN - Warning level is less then Crit"
210 result
=`sqlplus -s ${3}/${4}@${2} << EOF
212 set numf '9999999.99'
213 select (1-(pr.value/(dbg.value+cg.value)))*100
214 from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg
215 where pr.name='physical reads'
216 and dbg.name='db block gets'
217 and cg.name='consistent gets';
220 if [ -n "`echo $result | grep ORA-`" ] ; then
221 error
=` echo "$result" | grep "ORA-" | head -1`
222 echo "CRITICAL - $error"
226 buf_hr
=`echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}'`
227 buf_hrx
=`echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}'`
228 result
=`sqlplus -s ${3}/${4}@${2} << EOF
230 set numf '9999999.99'
231 select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
232 from v\\$librarycache lc;
235 if [ -n "`echo $result | grep ORA-`" ] ; then
236 error
=` echo "$result" | grep "ORA-" | head -1`
237 echo "CRITICAL - $error"
241 lib_hr
=`echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}'`
242 lib_hrx
=`echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}'`
244 if [ $buf_hr -le ${5} -o $lib_hr -le ${5} ] ; then
245 echo "${2} CRITICAL - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
248 if [ $buf_hr -le ${6} -o $lib_hr -le ${6} ] ; then
249 echo "${2} WARNING - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
252 echo "${2} OK - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
257 if [ ${6} -lt ${7} ] ; then
258 echo "UNKNOWN - Warning level is more then Crit"
261 result
=`sqlplus -s ${3}/${4}@${2} << EOF
263 set numf '9999999.99'
264 select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
266 select tablespace_name,sum(bytes)/1024/1024 total
267 from dba_data_files group by tablespace_name) A
269 ( select tablespace_name,sum(bytes)/1024/1024 free
270 from dba_free_space group by tablespace_name) B
271 ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='${5}';
274 if [ -n "`echo $result | grep ORA-`" ] ; then
275 error
=` echo "$result" | grep "ORA-" | head -1`
276 echo "CRITICAL - $error"
280 ts_free
=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($1)}'`
281 ts_total
=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($2)}'`
282 ts_pct
=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($3)}'`
283 ts_pctx
=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print $3}'`
284 if [ "$ts_free" -eq 0 -a "$ts_total" -eq 0 -a "$ts_pct" -eq 0 ] ; then
285 echo "No data returned by Oracle - tablespace $5 not found?"
288 if [ "$ts_pct" -ge ${6} ] ; then
289 echo "${2} : ${5} CRITICAL - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
292 if [ "$ts_pct" -ge ${7} ] ; then
293 echo "${2} : ${5} WARNING - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
296 echo "${2} : ${5} OK - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"