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