summaryrefslogtreecommitdiffstats
path: root/contrib/check_oracle_tbs
diff options
context:
space:
mode:
authorStanley Hopcroft <stanleyhopcroft@users.sourceforge.net>2005-01-27 04:46:08 (GMT)
committerStanley Hopcroft <stanleyhopcroft@users.sourceforge.net>2005-01-27 04:46:08 (GMT)
commitfce1f09f770fc5bbc6d323865af709ff515c803c (patch)
tree1e997a0bc1ace6217cc0dab87b65de52bca8be20 /contrib/check_oracle_tbs
parentccf789e017547e1f71b5945b2e1d85ce5b9cb8e4 (diff)
downloadmonitoring-plugins-fce1f09f770fc5bbc6d323865af709ff515c803c.tar.gz
New /contrib plugin
git-svn-id: https://nagiosplug.svn.sourceforge.net/svnroot/nagiosplug/nagiosplug/trunk@1112 f882894a-f735-0410-b71e-b25c423dba1c
Diffstat (limited to 'contrib/check_oracle_tbs')
-rw-r--r--contrib/check_oracle_tbs206
1 files changed, 206 insertions, 0 deletions
diff --git a/contrib/check_oracle_tbs b/contrib/check_oracle_tbs
new file mode 100644
index 0000000..bcc4af8
--- /dev/null
+++ b/contrib/check_oracle_tbs
@@ -0,0 +1,206 @@
1#!/usr/local/bin/perl -w
2
3# (c)2003 John Koyle, RFP Depot, LLC.
4# This is free software use it however you would like.
5# Thanks to the folks at http://www.think-forward.com for the SQL query
6
7
8use strict;
9use DBI;
10use Getopt::Long 2.16;
11use lib "/usr/local/nagios/libexec";
12use utils qw(%ERRORS);
13
14
15#*******************************************************************************
16# Set user configureable options here.
17#
18# Global Oracle info set here rather than command line to avoid output in ps -ef
19# Make sure this script is mode 700 and owner of the nrpe user
20#
21#*******************************************************************************
22my $orasid = "";
23my $orauser = "";
24my $orapwd = "";
25
26
27if (!$ENV{ORACLE_HOME}) {
28 $ENV{ORACLE_HOME} = '/a01/app/oracle/product/9.2.0.1';
29}
30
31#*****************You shouldn't need to modify anything below here *************
32my $state = $ERRORS{'UNKNOWN'};
33my $answer = undef;
34
35my ($MAJOR_VERSION, $MINOR_VERSION) = q$Revision$ =~ /(\d+)\.(\d+)/;
36my $VERSION = sprintf("%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION);
37
38my $opt_debug; # -d|--debug
39my $opt_help; # -h|--help
40my $opt_version; # -V|--version
41my $opt_warn_space; # -w|--warn-space
42my $opt_crit_space; # -c|--crit-space
43
44my $help = <<MARK; # help statement
45
46check_oracle_tbs v$VERSION
47
48Checks the tablespaces in an Oracle database for available free space.
49Usage: check_oracle_tbs [-w <warn>] [-c <crit>]
50
51 -d, --debug Output debug to screen.
52 -h, --help Displays this help and exits.
53 -w, --warn-space=... Warning threshold % free (default 15)
54 -c, --crit-space=... Critical threshold % free (default 10)
55 -V, --version Output version information and exit.
56
57MARK
58
59## We want exact matches to the switches
60
61Getopt::Long::config('no_auto_abbrev', 'no_ignore_case');
62
63my $rc = GetOptions(
64 "debug|d" => \$opt_debug,
65 "help|h" => \$opt_help,
66 "w|warn-space=s" => \$opt_warn_space,
67 "c|crit-space=s" => \$opt_crit_space,
68 "V|version" => \$opt_version,
69 );
70
71
72#***********************************************************************
73# Process command-line switches
74#***********************************************************************
75
76if (! $rc || defined $opt_help)
77{
78 print STDERR $help;
79 exit (defined $opt_help ? 0 : 1);
80}
81
82if (defined $opt_version)
83{
84 print STDERR "check_oracle_tbs v$VERSION\n";
85 exit 0;
86}
87
88if (! defined $opt_warn_space)
89{
90 if(defined $opt_debug) {
91 print STDOUT "Warn space not defined, using 80%\n\n";
92 }
93 $opt_warn_space = 15;
94}
95
96if (! defined $opt_crit_space)
97{
98 if(defined $opt_debug) {
99 print STDOUT "Crit space not defined, using 90%\n\n";
100 }
101 $opt_crit_space = 10;
102}
103
104my $array_ref = executeSQL();
105
106foreach my $row (@$array_ref) {
107 my ( $tbs_name, $tot_mb, $free_mb, $free_pct, $used_pct, $fsfi) = @$row;
108 if ($opt_debug) { print STDOUT "Output: $tbs_name\t$tot_mb\t$free_mb\t$free_pct\t$used_pct\t$fsfi\n\n"; }
109 if ($used_pct > (100 - $opt_crit_space) && $tbs_name !~ /RBS/) {
110 $state = $ERRORS{'CRITICAL'};
111 $answer .= "$tbs_name = $used_pct\% ";
112 last;
113 }
114 if ($used_pct > (100 - $opt_warn_space) && $tbs_name !~ /RBS/) {
115 $state = $ERRORS{'WARNING'};
116 $answer .= "$tbs_name = $used_pct\% ";
117 }
118}
119
120if ($state != $ERRORS{'CRITICAL'} && $state != $ERRORS{'WARNING'}) {
121 $state = $ERRORS{'OK'};
122 $answer = "All Tablespaces OK";
123}
124
125if ($opt_debug && $state != $ERRORS{'OK'}) { print STDOUT "The following tablespaces are in question: $answer\n\n"; }
126
127foreach my $key (keys %ERRORS) {
128 if ($state==$ERRORS{$key}) {
129 print ("$key: $answer");
130 last;
131 }
132}
133exit $state;
134
135sub executeSQL
136{
137 my ($dbh, $sth, $results);
138
139 $dbh = openOracle();
140
141 eval {
142 $dbh->{RaiseError} = 1;
143 # This query is taken from this URL and used with permission: http://www.think-forward.com/sql/tspace.htm
144 $sth = $dbh->prepare(q{
145 select df.tablespace_name tspace,
146 df.bytes/(1024*1024) tot_ts_size,
147 sum(fs.bytes)/(1024*1024) free_ts_size,
148 round(sum(fs.bytes)*100/df.bytes) ts_pct,
149 round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1,
150 ROUND(100*SQRT(MAX(fs.bytes)/SUM(fs.bytes))*
151 (1/SQRT(SQRT(COUNT(fs.bytes)))) ,2) FSFI
152 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes
153 from dba_data_files
154 group by tablespace_name ) df
155 where fs.tablespace_name = df.tablespace_name
156 group by df.tablespace_name, df.bytes
157 });
158
159 $sth->execute();
160 $results = $sth->fetchall_arrayref();
161 $sth->finish;
162 $dbh->{RaiseError} = 0;
163 };
164
165 if ($@) {
166 closeOracle($dbh);
167 if($opt_debug) { print STDOUT "DB Failed Query: $@\n"; }
168 CleanupAndExit($ERRORS{'UNKNOWN'});
169 }
170
171 closeOracle($dbh);
172
173 return $results;
174}
175
176#------ Open the connection to the database and return the handle
177sub openOracle
178{
179 my ($dbh);
180
181 $dbh = DBI->connect("$orasid", "$orauser", "$orapwd", "Oracle");
182
183 if (!$dbh) {
184 if ($opt_debug) { print "ERROR: Could not connect to Oracle!\n\n"; }
185 CleanupAndExit($ERRORS{'UNKNOWN'});
186 }
187 if ($opt_debug) { print "Connected to Oracle SID $orasid\n\n"; }
188 return $dbh;
189}
190
191#------- Close the database connection
192sub closeOracle()
193{
194 my ($dbh) = @_;
195
196 $dbh->disconnect;
197}
198
199#------ Exit with the current return code
200sub CleanupAndExit
201{
202 my ($rc) = @_;
203
204 exit($rc);
205}
206