contrib/check_oracle_tbs

   1 #!/usr/local/bin/perl -w
   2
   3 # (c)2004 John Koyle, RFP Depot, LLC.
   4 # This is free software use it however you would like.
   5
   6 use strict;
   7 use DBI;
   8 use Getopt::Long 2.16;
   9 use lib "/usr/local/nagios/libexec";
  10 use utils qw(%ERRORS);
  11
  12
  13 #*******************************************************************************
  14 # Set user configureable options here.
  15 #
  16 # Global Oracle info set here rather than command line to avoid output in ps -ef
  17 # Make sure this script is mode 700 and owner of the nrpe user
  18 #
  19 #*******************************************************************************
  20 my $orasid = "";
  21 my $orauser = "";
  22 my $orapwd = "";
  23
  24
  25 if (!$ENV{ORACLE_HOME}) {
  26         $ENV{ORACLE_HOME} = '/u01/app/oracle/product/9.2';
  27 }
  28
  29 #*******************************************************************************
  30 my $state = $ERRORS{'UNKNOWN'};
  31 my $answer = undef;
  32
  33 my ($MAJOR_VERSION, $MINOR_VERSION) = q$Revision: 1134 $ =~ /(\d+)\.(\d+)/;
  34 my $VERSION = sprintf("%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION);
  35
  36 my $opt_debug;                  # -d|--debug
  37 my $opt_help;                   # -h|--help
  38 my $opt_version;                # -V|--version
  39 my $opt_warn_space;             # -w|--warn-space
  40 my $opt_crit_space;             # -c|--crit-space
  41
  42
  43
  44 my $help = <<MARK;      # help statement
  45
  46 check_oracle_tbs v$VERSION
  47
  48 Checks the tablespaces in an Oracle database for available free space.
  49 Usage: 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
  57 MARK
  58
  59 ## We want exact matches to the switches
  60
  61 Getopt::Long::config('no_auto_abbrev', 'no_ignore_case');
  62
  63
  64 my $rc = GetOptions(
  65                 "debug|d"               => \$opt_debug,
  66                 "help|h"                => \$opt_help,
  67                 "w|warn-space=s"        => \$opt_warn_space,
  68                 "c|crit-space=s"        => \$opt_crit_space,
  69                 "V|version"             => \$opt_version,
  70                    );
  71
  72
  73 #***********************************************************************
  74 # Process command-line switches
  75 #***********************************************************************
  76
  77 if (! $rc || defined $opt_help)
  78 {
  79     print STDERR $help;
  80     exit (defined $opt_help ? 0 : 1);
  81 }
  82
  83 if (defined $opt_version)
  84 {
  85     print STDERR "check_oracle_tbs v$VERSION\n";
  86     exit 0;
  87 }
  88
  89 if (! defined $opt_warn_space)
  90 {
  91     if(defined $opt_debug) {
  92         print STDOUT "Warn space not defined, using 80%\n\n";
  93     }
  94     $opt_warn_space = 15;
  95 }
  96
  97 if (! defined $opt_crit_space)
  98 {
  99     if(defined $opt_debug) {
 100         print STDOUT "Crit space not defined, using 90%\n\n";
 101     }
 102     $opt_crit_space = 10;
 103 }
 104
 105 my $array_ref = executeSQL();
 106
 107 # Don't match certain tablespaces.
 108 foreach my $row (@$array_ref) {
 109     my ( $tbs_name, $free_mb, $tot_mb, $free_pct) = @$row;
 110     if ($opt_debug) { print STDOUT "Output: $tbs_name\t$tot_mb\t$free_mb\t$free_pct\n\n"; }
 111     if ($free_pct < $opt_crit_space && $tbs_name !~ /RBS/ && $tbs_name !~ /PERFSTAT/ && $tbs_name !~ /UNDOTBS/) {
 112         $state = $ERRORS{'CRITICAL'};
 113         $answer .= "Critical: $tbs_name = $free_pct\% ";
 114         last;
 115     }
 116     if ($free_pct < $opt_warn_space && $tbs_name !~ /RBS/ && $tbs_name !~ /PERFSTAT/ && $tbs_name !~ /UNDOTBS/) {
 117         $state = $ERRORS{'WARNING'};
 118         $answer .= "Warning: $tbs_name = $free_pct\% ";
 119     }
 120 }
 121
 122 if ($state != $ERRORS{'CRITICAL'} && $state != $ERRORS{'WARNING'}) {
 123     $state = $ERRORS{'OK'};
 124     $answer = "All Tablespaces OK";
 125 }
 126
 127 if ($opt_debug && $state != $ERRORS{'OK'}) { print STDOUT "The following tablespaces are in question: $answer\n\n"; }
 128
 129 foreach my $key (keys %ERRORS) {
 130         if ($state==$ERRORS{$key}) {
 131                 print ("$key: $answer");
 132                 last;
 133         }
 134 }
 135 exit $state;
 136
 137 #------------------SUBS-------------------------------------------------------
 138 sub executeSQL
 139 {
 140     my ($dbh, $sth, $results);
 141
 142     $dbh = openOracle();
 143
 144     eval {
 145       $dbh->{RaiseError} = 1;
 146       $sth = $dbh->prepare(q{
 147          select ts.tablespace_name,
 148                 trunc(sum(ts.free_b)/1024/1024) free,
 149                 trunc(sum(ts.max_b)/1024/1024)  total,
 150                 trunc( sum(ts.free_b)/sum(ts.max_b)*1000) / 10 as pct_free
 151           from
 152                 (select a.file_id,
 153                         a.tablespace_name,
 154                         decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free) free_b,
 155                         a.maxsize max_b
 156                    from (select file_id,
 157                                 tablespace_name,
 158                                 autoextensible,
 159                                 bytes,
 160                                 decode(autoextensible,'YES',maxbytes,bytes) maxsize
 161                            from dba_data_files) a,
 162                         (select file_id,
 163                                 tablespace_name,
 164                                 sum(bytes) free
 165                            from dba_free_space
 166                        group by file_id, tablespace_name) b
 167                   where a.file_id=b.file_id(+)) ts
 168        group by tablespace_name
 169          });
 170
 171       $sth->execute();
 172       $results = $sth->fetchall_arrayref();
 173       $sth->finish;
 174       $dbh->{RaiseError} = 0;
 175     };
 176
 177     if ($@) {
 178       closeOracle($dbh);
 179       if($opt_debug) { print STDOUT "DB Failed Query: $@\n"; }
 180       CleanupAndExit($ERRORS{'UNKNOWN'});
 181     }
 182
 183     closeOracle($dbh);
 184
 185     return $results;
 186 }
 187
 188 #------ Open the connection to the database and return the handle
 189 sub openOracle
 190 {
 191    my ($dbh);
 192
 193    $dbh = DBI->connect("$orasid", "$orauser", "$orapwd", "Oracle");
 194
 195    if (!$dbh) {
 196       if ($opt_debug) { print "ERROR: Could not connect to Oracle!\n\n"; }
 197       CleanupAndExit($ERRORS{'UNKNOWN'});
 198    }
 199    if ($opt_debug) { print "Connected to Oracle SID $orasid\n\n"; }
 200    return $dbh;
 201 }
 202
 203 #------- Close the database connection
 204 sub closeOracle()
 205 {
 206    my ($dbh) = @_;
 207
 208    $dbh->disconnect;
 209 }
 210
 211 #------ Exit with the current return code
 212 sub CleanupAndExit
 213 {
 214         my ($rc) = @_;
 215
 216         exit($rc);
 217 }
 218