summaryrefslogtreecommitdiffstats
path: root/contrib/check_oracle_instance.pl
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/check_oracle_instance.pl')
-rw-r--r--contrib/check_oracle_instance.pl470
1 files changed, 0 insertions, 470 deletions
diff --git a/contrib/check_oracle_instance.pl b/contrib/check_oracle_instance.pl
deleted file mode 100644
index 3ba8d57a..00000000
--- a/contrib/check_oracle_instance.pl
+++ /dev/null
@@ -1,470 +0,0 @@
1#!/usr/bin/perl
2# $Id: check_oracle_instance.pl 10 2002-04-03 02:58:47Z sghosh $
3
4# Copyright (c) 2002 Sven Dolderer
5# some pieces of Code adopted from Adam vonNieda's oracletool.pl
6# (http://www.oracletool.com)
7#
8# You may distribute under the terms of either the GNU General Public
9# License or the Artistic License, as specified in the Perl README file,
10# with the exception that it cannot be placed on a CD-ROM or similar media
11# for commercial distribution without the prior approval of the author.
12
13# This software is provided without warranty of any kind.
14
15require 5.003;
16
17use strict;
18use Getopt::Long;
19
20# We need the DBI and DBD-Oracle Perl modules:
21require DBI || die "It appears that the DBI module is not installed! aborting...\n";
22require DBD::Oracle || die "It appears that the DBD::Oracle module is not installed! aborting...\n";
23
24use vars qw($VERSION $PROGNAME $logfile $debug $state $dbh $database $username $password $message $sql $cursor $opt_asession $opt_nsession $opt_tablespace $opt_nextents $opt_fextents $opt_aextents $privsok $warn $critical);
25
26'$Revision: 10 $' =~ /^.*(\d+.\d+) \$$/; # Use The Revision from RCS/CVS
27$VERSION = $1;
28$0 =~ m!^.*/([^/]+)$!;
29$PROGNAME = $1;
30#$debug="true";
31$logfile = "/tmp/check_oracle_instance.log";
32my %ERRORS = (UNKNOWN => -1, OK => 0, WARNING => 1, CRITICAL => 2);
33
34# Read cmdline opts:
35Getopt::Long::Configure('bundling', 'no_ignore_case');
36GetOptions (
37 "V|version" => \&version,
38 "h|help" => \&usage,
39 "u|user=s" => \$username,
40 "p|passwd=s" => \$password,
41 "c|connect=s" => \$database,
42 "a|active-sessions:s" => \$opt_asession,
43 "s|num-sessions:s" => \$opt_nsession,
44 "t|tablespaces:s" => \$opt_tablespace,
45 "n|num-extents:s" => \$opt_nextents,
46 "f|free-extents:s" => \$opt_fextents,
47 "x|allocate-extents" => \$opt_aextents
48 );
49($database && $username && $password) || die "mandatory parameters missing (try -h)\n";
50logit(" \$opt_asession = \"$opt_asession\"");
51logit(" \$opt_nsession = \"$opt_nsession\"");
52logit(" \$opt_tablespace = \"$opt_tablespace\"");
53logit(" \$opt_nextents = \"$opt_nextents\"");
54logit(" \$opt_fextents = \"$opt_fextents\"");
55logit(" \$opt_aextents = \"$opt_aextents\"");
56
57# so let's connect to the instance...
58$dbh = dbConnect($database,$username,$password);
59
60$message="$database: ";
61check_sessions($opt_nsession) if ($opt_nsession && $privsok);
62check_sessions($opt_asession,"active") if ($opt_asession && $privsok);
63check_tablespaces($opt_tablespace) if ($opt_tablespace && $privsok);
64check_nextents($opt_nextents) if ($opt_nextents && $privsok);
65check_fextents($opt_fextents) if ($opt_fextents && $privsok);
66check_aextents() if ($opt_aextents && $privsok);
67
68$message=$message . "ok. " . getDbVersion($dbh) unless ($state);
69print "$message\n";
70exit $state;
71
72
73sub usage {
74 copyright();
75 print "
76This plugin will check various things of an oracle database instance.
77
78Prerequisties are: a local oracle client,
79 perl > v5.003, and DBI and DBD::Oracle perl modules.
80
81Usage: $PROGNAME -u <user> -p <passwd> -c <connectstring>
82 [-a <w>/<c>] [-s <w>/<c>] [-t <w>/<c>] [-n <w>/<c>] [-f <w>/<c>] [-x]
83 $PROGNAME [-V|--version]
84 $PROGNAME [-h|--help]
85";
86 print "
87Options:
88 -u, --user=STRING
89 the oracle user
90 -p, --passwd=STRING
91 the oracle password
92 -c, --connect=STRING
93 the oracle connectstring as defined in tnsnames.ora
94 -a, --active-sessions=WARN/CRITICAL
95 check the number of active (user-)sessions
96 WARN(Integer): number of sessions to result in warning status,
97 CRITICAL(Integer): number of sessions to result in critical status
98 -s, --num-sessions=WARN/CRITICAL
99 check the total number of (user-)sessions
100 WARN(Integer): number of sessions to result in warning status,
101 CRITICAL(Integer): number of sessions to result in critical status
102 -t, --tablespaces=WARN/CRITICAL
103 check the percent of used space in every tablespace
104 WARN(Integer): percentage to result in warning status,
105 CRITICAL(Integer): percentage to result in critical status
106 -n, --num-extents=WARN/CRITICAL
107 check the number of extents of every object (excluding SYS schema)
108 WARN(Integer): number of extents to result in warning status,
109 CRITICAL(Integer): number of extents to result in critical status
110 -f, --free-extents=WARN/CRITICAL
111 check the number of free extents of every object: max_extents - #extents
112 WARN(Integer): number of free extents to result in warning status,
113 CRITICAL(Integer): number of free extents to result in critical status
114 -x, --allocate-extents
115 warn if an object cannot allocate a next extent.
116";
117 exit $ERRORS{"UNKNOWN"};
118}
119
120
121sub version {
122 copyright();
123 print "
124$PROGNAME $VERSION
125";
126 exit $ERRORS{"UNKNOWN"};
127}
128
129
130sub copyright {
131 print "The netsaint plugins come with ABSOLUTELY NO WARRANTY. You may redistribute
132copies of the plugins under the terms of the GNU General Public License.
133For more information about these matters, see the file named COPYING.
134Copyright (c) 2002 Sven Dolderer\n";
135}
136
137
138sub logit {
139 my $text = shift;
140 if ($debug) {
141 open (LOG,">>$logfile") || die "Cannot open log file \"$logfile\"!";
142 print LOG "$text\n";
143 close (LOG);
144 }
145}
146
147
148sub dbConnect {
149 logit("Enter subroutine dbConnect");
150
151 my $database = shift;
152 my $username = shift;
153 my $password = shift;
154
155# Attempt to make connection to the database..
156 my $data_source = "dbi:Oracle:$database";
157 $dbh = DBI->connect($data_source,$username,$password,{PrintError=>0});
158
159# Show an error message for these errors.
160# ORA-12224 - "The connection request could not be completed because the listener is not running."
161# ORA-01034 - "Oracle was not started up."
162# ORA-01090 - "Shutdown in progress - connection is not permitted""
163# ORA-12154 - "The service name specified is not defined correctly in the TNSNAMES.ORA file."
164# ORA-12505 - "TNS:listener could not resolve SID given in connect descriptor."
165# ORA-12545 - "TNS:name lookup failure."
166
167 unless ($dbh) {
168 logit(" Error message is ~$DBI::errstr~");
169 if ( $DBI::errstr =~ /ORA-01017|ORA-1017|ORA-01004|ORA-01005/ ) {
170 $message="Login error: ~$DBI::errstr~";
171 $state=$ERRORS{"UNKNOWN"};
172 } elsif ( $DBI::errstr =~ /ORA-12224/ ) {
173 $message= "You received an ORA-12224, which usually means the listener is down, or your connection definition in your tnsnames.ora file is incorrect. Check both of these things and try again.";
174 $state=$ERRORS{"CRITICAL"};
175 } elsif ( $DBI::errstr =~ /ORA-01034/ ) {
176 $message= "You received an ORA-01034, which usually means the database is down. Check to be sure the database is up and try again.";
177 $state=$ERRORS{"CRITICAL"};
178 } elsif ( $DBI::errstr =~ /ORA-01090/ ) {
179 $message= "You received an ORA-01090, which means the database is in the process of coming down.";
180 $state=$ERRORS{"CRITICAL"};
181 } elsif ( $DBI::errstr =~ /ORA-12154/ ) {
182 $message= "You received an ORA-12154, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose.";
183 $state=$ERRORS{"UNKNOWN"};
184 } elsif ( $DBI::errstr =~ /ORA-12505/ ) {
185 $message= "You received an ORA-12505, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose, or the database you are trying to connect to is not defined to the listener that is running on that node.";
186 $state=$ERRORS{"UNKNOWN"};
187 } elsif ( $DBI::errstr =~ /ORA-12545/ ) {
188 $message= "You received an ORA-12545, which probably means you have a mistake in your TNSNAMES.ORA file for the database that you chose. (Possibly the node name).";
189 $state=$ERRORS{"UNKNOWN"};
190 } else {
191 $message="Unable to connect to Oracle ($DBI::errstr)\n";
192 $state=$ERRORS{"UNKNOWN"};
193 }
194
195 } else {
196 logit(" Login OK.");
197
198 # check to be sure this user has "SELECT ANY TABLE" privilege.
199 logit(" checking for \"SELECT ANY TABLE\" privilege");
200 if (checkPriv("SELECT ANY TABLE") < 1) {
201 $message="user $username needs \"SELECT ANY TABLE\" privilege.";
202 $state=$ERRORS{"UNKNOWN"};
203 } else {
204 $privsok="yep";
205 $state=$ERRORS{"OK"};
206 }
207 }
208 return ($dbh);
209}
210
211
212sub getDbVersion {
213
214 logit("Enter subroutine getDbVersion");
215
216 my $dbh = shift;
217 my $oraversion;
218
219# Find out if we are dealing with Oracle7 or Oracle8
220 logit(" Getting Oracle version");
221 $sql = "select banner from v\$version where rownum=1";
222
223 $cursor = $dbh->prepare($sql) or logit("Error: $DBI::errstr");
224 $cursor->execute;
225 (($oraversion) = $cursor->fetchrow_array);
226 $cursor->finish;
227 logit(" Oracle version = $oraversion");
228 return $oraversion;
229}
230
231
232sub checkPriv {
233 logit("Enter subroutine checkPriv");
234 my ($privilege,$yesno);
235 $privilege = shift;
236 logit(" Checking for privilege \"$privilege\"");
237
238 $sql = "SELECT COUNT(*) FROM SESSION_PRIVS WHERE PRIVILEGE = '$privilege'";
239 $cursor=$dbh->prepare($sql);
240 $cursor->execute; $yesno = $cursor->fetchrow_array;
241 $cursor->finish;
242
243 return($yesno);
244}
245
246
247sub get_values {
248 logit("Enter subroutine get_values");
249 my ($args, $inverse, $abort);
250 $args = shift;
251 $inverse = shift;
252 if ($args =~ m!^(\d+)/(\d+)$!) {
253 $warn = $1;
254 $critical = $2;
255
256 # TODO: check for positive numbers!
257
258 if (! $inverse && $warn >= $critical) {
259 print "\"$args\": warning threshold must be less than critical threshold. aborting...\n";
260 $abort="yep";
261 }
262 if ($inverse && $warn <= $critical) {
263 print "\"$args\": warning threshold must be greater than critical threshold. aborting...\n";
264 $abort="yep";
265 }
266 } else {
267 print "\"$args\": invalid warn/critical thresholds. aborting...\n";
268 $abort="yep";
269 }
270 exit $ERRORS{"UNKNOWN"} if $abort;
271 logit (" args=$args, warn=$warn, critical=$critical");
272}
273
274
275sub check_sessions {
276 logit("Enter subroutine check_sessions");
277 my ($args, $add, $sqladd, $count);
278 $args = shift;
279 $add = shift || '#'; # Default: Number of sessions
280 $sqladd = "AND STATUS = 'ACTIVE'" if ($add eq "active");
281
282 get_values($args);
283
284 $sql = "SELECT COUNT(*) FROM V\$SESSION WHERE TYPE <> 'BACKGROUND' $sqladd";
285 $cursor=$dbh->prepare($sql);
286 $cursor->execute;
287 $count = $cursor->fetchrow_array;
288 $cursor->finish;
289 logit (" $add sessions is $count");
290
291 if ($count >= $critical) {
292 $message = $message . "$add sessions critical ($count) ";
293 $state=$ERRORS{"CRITICAL"};
294 } elsif ($count >= $warn) {
295 $message = $message . "$add sessions warning ($count) ";
296 $state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
297 }
298}
299
300
301sub check_tablespaces {
302 logit("Enter subroutine check_tablespaces");
303 my ($args, $tablespace, $pctused, $mymsg, $mywarn, $mycritical);
304 $args = shift;
305 $mymsg = "Tablespace usage ";
306
307 get_values($args);
308
309 $sql = "SELECT
310 DF.TABLESPACE_NAME \"Tablespace name\",
311 NVL(ROUND((DF.BYTES-SUM(FS.BYTES))*100/DF.BYTES),100) \"Percent used\"
312 FROM DBA_FREE_SPACE FS,
313 (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY
314 TABLESPACE_NAME ) DF
315 WHERE FS.TABLESPACE_NAME (+) = DF.TABLESPACE_NAME
316 GROUP BY DF.TABLESPACE_NAME, DF.BYTES
317 ORDER BY 2 DESC";
318
319 $cursor=$dbh->prepare($sql);
320 $cursor->execute;
321 while (($tablespace, $pctused) = $cursor->fetchrow_array) {
322 logit (" $tablespace - $pctused% used");
323 if ($pctused >= $critical) {
324 unless ($mycritical) {
325 $mymsg = $mymsg . "critical: ";
326 $mycritical="yep";
327 }
328 $mymsg = $mymsg . "$tablespace ($pctused%) ";
329 $state=$ERRORS{"CRITICAL"};
330 } elsif ($pctused >= $warn) {
331 unless ($mywarn) {
332 $mymsg = $mymsg . "warning: ";
333 $mywarn="yep";
334 }
335 $mymsg = $mymsg . "$tablespace ($pctused%) ";
336 $state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
337 }
338 }
339 $cursor->finish;
340 $message = $message . $mymsg . " " if ($mycritical || $mywarn);
341}
342
343
344sub check_nextents {
345 logit("Enter subroutine check_nextents");
346 my ($args, $owner, $objname, $objtype, $extents, $mymsg, $mywarn, $mycritical);
347 $args = shift;
348 $mymsg = "#Extents ";
349
350 get_values($args);
351
352 $sql = "SELECT
353 OWNER \"Owner\",
354 SEGMENT_NAME \"Object name\",
355 SEGMENT_TYPE \"Object type\",
356 COUNT(*) \"Extents\"
357 FROM DBA_EXTENTS WHERE OWNER <> 'SYS'
358 GROUP BY SEGMENT_TYPE, SEGMENT_NAME, TABLESPACE_NAME, OWNER
359 HAVING COUNT(*) >= $warn
360 ORDER BY 4 DESC";
361
362 $cursor=$dbh->prepare($sql);
363 $cursor->execute;
364 while (($owner, $objname, $objtype, $extents) = $cursor->fetchrow_array) {
365 if ($extents >= $critical) {
366 unless ($mycritical) {
367 $mymsg = $mymsg . "critical: ";
368 $mycritical="yep";
369 }
370 $mymsg = $mymsg . "$owner.$objname($objtype)=$extents ";
371 $state=$ERRORS{"CRITICAL"};
372 } elsif ($extents >= $warn) {
373 unless ($mywarn) {
374 $mymsg = $mymsg . "warning: ";
375 $mywarn="yep";
376 }
377 $mymsg = $mymsg . "$owner.$objname($objtype)=$extents ";
378 $state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
379 }
380 }
381 $cursor->finish;
382 $message = $message . $mymsg . " " if ($mycritical || $mywarn);
383}
384
385
386sub check_fextents {
387 logit("Enter subroutine check_fextents");
388 my ($args, $owner, $objname, $objtype, $extents, $maxextents, $freextents, $mymsg, $mywarn, $mycritical);
389 $args = shift;
390 $mymsg = "Free extents ";
391
392 get_values($args, "inverse");
393
394 $sql = "SELECT
395 OWNER \"Owner\",
396 SEGMENT_NAME \"Object name\",
397 SEGMENT_TYPE \"Object type\",
398 EXTENTS \"Extents\",
399 MAX_EXTENTS \"Max extents\",
400 MAX_EXTENTS - EXTENTS \"Free extents\"
401 FROM DBA_SEGMENTS
402 WHERE (EXTENTS + $warn) >= MAX_EXTENTS
403 AND SEGMENT_TYPE != 'CACHE'
404 ORDER BY 6";
405
406 $cursor=$dbh->prepare($sql);
407 $cursor->execute;
408 while (($owner, $objname, $objtype, $extents, $maxextents, $freextents) = $cursor->fetchrow_array) {
409 if ($freextents <= $critical) {
410 unless ($mycritical) {
411 $mymsg = $mymsg . "critical: ";
412 $mycritical="yep";
413 }
414 $mymsg = $mymsg . "$owner.$objname($objtype)=$extents ";
415 $state=$ERRORS{"CRITICAL"};
416 } elsif ($freextents <= $warn) {
417 unless ($mywarn) {
418 $mymsg = $mymsg . "warning: ";
419 $mywarn="yep";
420 }
421 $mymsg = $mymsg . "$owner.$objname($objtype)=$extents/$maxextents ";
422 $state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
423 }
424 }
425 $cursor->finish;
426 $message = $message . $mymsg . " " if ($mycritical || $mywarn);
427}
428
429
430sub check_aextents {
431 logit("Enter subroutine check_aextents");
432 my ($args, $owner, $objname, $objtype, $tablespace_name, $mymsg, $mywarn);
433 my (@tablespaces);
434
435 # Get a list of all tablespaces
436 $sql = "SELECT TABLESPACE_NAME
437 FROM DBA_TABLESPACES ORDER BY TABLESPACE_NAME";
438 $cursor = $dbh->prepare($sql);
439 $cursor->execute;
440 while ($tablespace_name = $cursor->fetchrow_array) {
441 push @tablespaces, $tablespace_name;
442 }
443 $cursor->finish;
444
445 # Search every tablespace for objects which cannot allocate a next extent.
446 foreach $tablespace_name(@tablespaces) {
447 logit (" checking tablespace $tablespace_name");
448 $sql = "SELECT
449 OWNER \"Owner\",
450 SEGMENT_NAME \"Object name\",
451 SEGMENT_TYPE \"Object type\"
452 FROM DBA_SEGMENTS
453 WHERE TABLESPACE_NAME = '$tablespace_name'
454 AND NEXT_EXTENT > (SELECT NVL(MAX(BYTES),'0') FROM DBA_FREE_SPACE
455 WHERE TABLESPACE_NAME = '$tablespace_name')";
456 $cursor = $dbh->prepare($sql);
457 $cursor->execute;
458 while (($owner, $objname, $objtype) = $cursor->fetchrow_array) {
459 logit (" found: $owner.$objname($objtype)");
460 unless ($mywarn) {
461 $mymsg = $mymsg . "warning: ";
462 $mywarn="yep";
463 }
464 $mymsg = $mymsg . "$owner.$objname($objtype) ";
465 $state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
466 }
467 $cursor->finish;
468 }
469 $message = $message . $mymsg . "cannot allocate a next extent. " if $mywarn;
470}