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