1 #!/usr/local/bin/perl -w
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
8 use strict;
9 use DBI;
10 use Getopt::Long 2.16;
11 use lib "/usr/local/nagios/libexec";
12 use utils qw(%ERRORS);
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 #*******************************************************************************
22 my $orasid = "";
23 my $orauser = "";
24 my $orapwd = "";
27 if (!$ENV{ORACLE_HOME}) {
28 $ENV{ORACLE_HOME} = '/a01/app/oracle/product/9.2.0.1';
29 }
31 #*****************You shouldn't need to modify anything below here *************
32 my $state = $ERRORS{'UNKNOWN'};
33 my $answer = undef;
35 my ($MAJOR_VERSION, $MINOR_VERSION) = q$Revision$ =~ /(\d+)\.(\d+)/;
36 my $VERSION = sprintf("%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION);
38 my $opt_debug; # -d|--debug
39 my $opt_help; # -h|--help
40 my $opt_version; # -V|--version
41 my $opt_warn_space; # -w|--warn-space
42 my $opt_crit_space; # -c|--crit-space
44 my $help = <<MARK; # help statement
46 check_oracle_tbs v$VERSION
48 Checks the tablespaces in an Oracle database for available free space.
49 Usage: check_oracle_tbs [-w <warn>] [-c <crit>]
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.
57 MARK
59 ## We want exact matches to the switches
61 Getopt::Long::config('no_auto_abbrev', 'no_ignore_case');
63 my $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 );
72 #***********************************************************************
73 # Process command-line switches
74 #***********************************************************************
76 if (! $rc || defined $opt_help)
77 {
78 print STDERR $help;
79 exit (defined $opt_help ? 0 : 1);
80 }
82 if (defined $opt_version)
83 {
84 print STDERR "check_oracle_tbs v$VERSION\n";
85 exit 0;
86 }
88 if (! 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 }
96 if (! 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 }
104 my $array_ref = executeSQL();
106 foreach 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 }
120 if ($state != $ERRORS{'CRITICAL'} && $state != $ERRORS{'WARNING'}) {
121 $state = $ERRORS{'OK'};
122 $answer = "All Tablespaces OK";
123 }
125 if ($opt_debug && $state != $ERRORS{'OK'}) { print STDOUT "The following tablespaces are in question: $answer\n\n"; }
127 foreach my $key (keys %ERRORS) {
128 if ($state==$ERRORS{$key}) {
129 print ("$key: $answer");
130 last;
131 }
132 }
133 exit $state;
135 sub executeSQL
136 {
137 my ($dbh, $sth, $results);
139 $dbh = openOracle();
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 });
159 $sth->execute();
160 $results = $sth->fetchall_arrayref();
161 $sth->finish;
162 $dbh->{RaiseError} = 0;
163 };
165 if ($@) {
166 closeOracle($dbh);
167 if($opt_debug) { print STDOUT "DB Failed Query: $@\n"; }
168 CleanupAndExit($ERRORS{'UNKNOWN'});
169 }
171 closeOracle($dbh);
173 return $results;
174 }
176 #------ Open the connection to the database and return the handle
177 sub openOracle
178 {
179 my ($dbh);
181 $dbh = DBI->connect("$orasid", "$orauser", "$orapwd", "Oracle");
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 }
191 #------- Close the database connection
192 sub closeOracle()
193 {
194 my ($dbh) = @_;
196 $dbh->disconnect;
197 }
199 #------ Exit with the current return code
200 sub CleanupAndExit
201 {
202 my ($rc) = @_;
204 exit($rc);
205 }