1 #!/bin/sh
2 #
3 # latigid010@yahoo.com
4 # 01/06/2000
5 #
6 # This Nagios plugin was created to check remote or local TNS
7 # status and check local Database status.
8 #
9 # Add the following lines to your object config file (i.e. commands.cfg)
10 # command[check-tns]=/usr/local/nagios/libexec/check_ora 1 $ARG$
11 # command[check-oradb]=/usr/local/nagios/libexec/check_ora 2 $ARG$
12 #
13 #
14 # Usage:
15 # To check TNS Status: ./check_ora 1 <Oracle Sid or Hostname/IP address>
16 # To Check local database: ./check_ora 2 <ORACLE_SID>
17 #
18 # I have the script checking for the Oracle PMON process and
19 # the sgadefORACLE_SID.dbf file.
20 #
21 #
22 # If you have any problems check that you have the $ORACLE_HOME
23 # enviroment variable set, have $ORACLE_HOME/bin in your PATH, and
24 # dont forget about your tnsnames.ora file. when checking Local
25 # Database status your ORACLE_SID is case sensitive.
26 #
28 PROGNAME=`basename $0`
29 PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
30 REVISION=`echo '$Revision$' | sed -e 's/[^0-9.]//g'`
32 . $PROGPATH/utils.sh
35 print_usage() {
36 echo "Usage:"
37 echo " $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
38 echo " $PROGNAME --db <ORACLE_SID>"
39 echo " $PROGNAME --login <ORACLE_SID>"
40 echo " $PROGNAME --cache <USER> <PASS> <INST> <CRITICAL> <WARNING>"
41 echo " $PROGNAME --tablespace <USER> <PASS> <INST> <TABLESPACE> <CRITICAL> <WARNING>"
42 echo " $PROGNAME --oranames <Hostname>"
43 echo " $PROGNAME --help"
44 echo " $PROGNAME --version"
45 }
47 print_help() {
48 print_revision $PROGNAME $REVISION
49 echo ""
50 print_usage
51 echo ""
52 echo "Check remote or local TNS status and check local Database status"
53 echo ""
54 echo "--tns=SID/IP Address"
55 echo " Check remote TNS server"
56 echo "--db=SID"
57 echo " Check local database (search /bin/ps for PMON process and check"
58 echo " filesystem for sgadefORACLE_SID.dbf"
59 echo "--login=SID"
60 echo " Attempt a dummy login and alert if not ORA-01017: invalid username/password"
61 echo "--cache"
62 echo " Check local database for library and buffer cache hit ratios"
63 echo " ---> Requires Oracle user/password and SID specified."
64 echo " ---> Requires select on v_$sysstat and v_$librarycache"
65 echo "--tablespace"
66 echo " Check local database for tablespace capacity in ORACLE_SID"
67 echo " ---> Requires Oracle user/password specified."
68 echo " ---> Requires select on dba_data_files and dba_free_space"
69 echo "--oranames=Hostname"
70 echo " Check remote Oracle Names server"
71 echo "--help"
72 echo " Print this help screen"
73 echo "--version"
74 echo " Print version and license information"
75 echo ""
76 echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
77 echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
78 echo "tnsnames.ora file is locatable and is properly configured."
79 echo ""
80 echo "When checking Local Database status your ORACLE_SID is case sensitive."
81 echo ""
82 support
83 }
85 case "$1" in
86 1)
87 cmd='--tns'
88 ;;
89 2)
90 cmd='--db'
91 ;;
92 *)
93 cmd="$1"
94 ;;
95 esac
97 # Information options
98 case "$cmd" in
99 --help)
100 print_help
101 exit $STATE_OK
102 ;;
103 -h)
104 print_help
105 exit $STATE_OK
106 ;;
107 --version)
108 print_revision $PLUGIN $REVISION
109 exit $STATE_OK
110 ;;
111 -V)
112 print_revision $PLUGIN $REVISION
113 exit $STATE_OK
114 ;;
115 esac
117 # Hunt down a reasonable ORACLE_HOME
118 if [ -z "$ORACLE_HOME" ] ; then
119 # Adjust to taste
120 for oratab in /var/opt/oracle/oratab /etc/oratab
121 do
122 [ ! -f $oratab ] && continue
123 ORACLE_HOME=`IFS=:
124 while read SID ORACLE_HOME junk;
125 do
126 if [ "$SID" = "$2" ] ; then
127 echo $ORACLE_HOME;
128 exit;
129 fi;
130 done < $oratab`
131 [ -n "$ORACLE_HOME" ] && break
132 done
133 fi
134 # Last resort
135 [ -z "$ORACLE_HOME" -a -d $PROGPATH/oracle ] && ORACLE_HOME=$PROGPATH/oracle
137 if [ -z "$ORACLE_HOME" -o ! -d "$ORACLE_HOME" ] ; then
138 echo "Cannot determine ORACLE_HOME for sid $2"
139 exit $STATE_UNKNOWN
140 fi
141 PATH=$PATH:$ORACLE_HOME/bin
142 LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
143 export ORACLE_HOME PATH LD_LIBRARY_PATH
145 case "$cmd" in
146 --tns)
147 tnschk=` tnsping $2`
148 tnschk2=` echo $tnschk | grep -c OK`
149 if [ ${tnschk2} -eq 1 ] ; then
150 tnschk3=` echo $tnschk | sed -e 's/.*(//' -e 's/).*//'`
151 echo "OK - reply time ${tnschk3} from $2"
152 exit $STATE_OK
153 else
154 echo "No TNS Listener on $2"
155 exit $STATE_CRITICAL
156 fi
157 ;;
158 --oranames)
159 namesctl status $2 | awk '
160 /Server has been running for:/ {
161 msg = "OK: Up"
162 for (i = 6; i <= NF; i++) {
163 msg = msg " " $i
164 }
165 status = '$STATE_OK'
166 }
167 /error/ {
168 msg = "CRITICAL: " $0
169 status = '$STATE_CRITICAL'
170 }
171 END {
172 print msg
173 exit status
174 }'
175 ;;
176 --db)
177 pmonchk=`ps -ef | grep -v grep | grep ${2} | grep -c pmon`
178 if [ ${pmonchk} -ge 1 ] ; then
179 echo "${2} OK - ${pmonchk} PMON process(es) running"
180 exit $STATE_OK
181 #if [ -f $ORACLE_HOME/dbs/sga*${2}* ] ; then
182 #if [ ${pmonchk} -eq 1 ] ; then
183 #utime=`ls -la $ORACLE_HOME/dbs/sga*$2* | cut -c 43-55`
184 #echo "${2} OK - running since ${utime}"
185 #exit $STATE_OK
186 #fi
187 else
188 echo "${2} Database is DOWN"
189 exit $STATE_CRITICAL
190 fi
191 ;;
192 --login)
193 loginchk=`sqlplus dummy/user@$2 < /dev/null`
194 loginchk2=` echo $loginchk | grep -c ORA-01017`
195 if [ ${loginchk2} -eq 1 ] ; then
196 echo "OK - dummy login connected"
197 exit $STATE_OK
198 else
199 loginchk3=` echo "$loginchk" | grep "ORA-" | head -1`
200 echo "CRITICAL - $loginchk3"
201 exit $STATE_CRITICAL
202 fi
203 ;;
204 --cache)
205 if [ ${5} -gt ${6} ] ; then
206 echo "UNKNOWN - Warning level is less then Crit"
207 exit $STATE_UNKNOWN
208 fi
209 result=`sqlplus -s ${2}/${3}@${4} << EOF
210 set pagesize 0
212 select (1-(pr.value/(dbg.value+cg.value)))*100 \
213 from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg \
214 where pr.name = 'physical reads' \
215 and dbg.name='db block gets' \
216 and cg.name='consistent gets'; `
218 buf_hr=`echo $result | awk '{print int($1)}'`
219 result=`sqlplus -s ${2}/${3}@${4} << EOF
220 set pagesize 0
222 select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100 \
223 from v\\$librarycache lc ; `
225 lib_hr=`echo $result | awk '{print int($1)}'`
227 if [ $buf_hr -le ${5} -o $lib_hr -le ${5} ] ; then
228 echo "${3} : ${4} CRITICAL - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
229 exit $STATE_CRITICAL
230 fi
231 if [ $buf_hr -le ${6} -o $lib_hr -le ${6} ] ; then
232 echo "${3} : ${4} WARNING - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
233 exit $STATE_WARNING
234 fi
235 echo "${3} : ${4} OK - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
237 exit $STATE_OK
238 ;;
239 --tablespace)
240 if [ ${6} -lt ${7} ] ; then
241 echo "UNKNOWN - Warning level is more then Crit"
242 exit $STATE_UNKNOWN
243 fi
244 result=`sqlplus -s ${2}/${3}@${4} << EOF
245 set pagesize 0
247 select b.free,a.total,100 - trunc(b.free/a.total * 1000) / 10 prc \
248 from ( \
249 select tablespace_name,sum(bytes)/1024/1024 total \
250 from dba_data_files group by tablespace_name) A, \
251 ( select tablespace_name,sum(bytes)/1024/1024 free \
252 from dba_Free_space group by tablespace_name) B \
253 where a.tablespace_name=b.tablespace_name and a.tablespace_name='${5}'; `
255 ts_free=`echo $result | awk '{print int($1)}'`
256 ts_total=`echo $result | awk '{print int($2)}'`
257 ts_pct=`echo $result | awk '{print int($3)}'`
258 if [ $ts_pct -ge ${6} ] ; then
259 echo "${4} : ${5} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ]"
260 exit $STATE_CRITICAL
261 fi
262 if [ $ts_pct -ge ${7} ] ; then
263 echo "${4} : ${5} WARNING - $ts_pct% used [ $ts_free / $ts_total MB available ]"
264 exit $STATE_WARNING
265 fi
266 echo "${4} : ${5} OK - $ts_pct% used [ $ts_free / $ts_total MB available ]"
267 exit $STATE_OK
268 ;;
269 *)
270 print_usage
271 exit $STATE_UNKNOWN
272 esac