Nagios 监控oracle数据库表空间

作者：Mike.Xu 发表于: January 7, 2009 3:28 PM
转载时请务必以超链接形式标明文章 原始出处和作者信息及本版权声明。
链接：http://www.dbasky.net/archives/2009/01/nagios-oracle.html
 1：前提条件是nagios 服务正常运行，然后手动添加脚本模块，脚本的代码如下：
my $dbuser = 'system';
my $dbpass = '×××××';

注意修改自己的数据库DB的帐号
#!/usr/bin/perl
use strict;
use DBI;
use Getopt::Long;

use lib "/usr/local/nagios/libexec";
use utils qw(%ERRORS $TIMEOUT);

$ENV{"ORACLE_HOME"}="/opt/oracle/10.2.0/db/";
$ENV{"ORACLE_BASE"}="/opt/oracle/10.2.0/";
$ENV{"LD_LIBRARY_PATH"}="/opt/oracle/10.2.0/db/lib" ;
$ENV{"JAVA_HOME"}="/opt/oracle/10.2.0/db/jdk";
$ENV{"PATH"}="/opt/oracle/10.2.0/db/bin:/opt/oracle/10.2.0/db/jdk/bin";
$ENV{"NLS_LANG"}="AMERICAN_AMERICA.AL32UTF8";
my $host = dbserver;
my $sid  = nuage;
my $port = 1521;
my $dbuser = 'system';
my $dbpass = '×××××';
my $tablespace = texttable;
my $alertpct = texttable;
my $critpct = texttable;

my $dbname = nuage;
my $tbname = nuagetable;
my $total  = nuage;
my $used  = nuage;
my $pct_used  =nuage ;
my $pct_used_max = 0;
my $total = nuage;
my $rc = nuage;
my $exit_code = nuage;
my $output = nuage;
my $perfout = nuage;
my $outputok = nuage;
my $WARN_FREE=4000;
my $CRIT_FREE=2000;

 

sub check_options{
        Getopt::Long::Configure ("bundling");
        GetOptions(
        'H:s'   => \$host,
        'P:i'   => \$port,
        'D:s'   => \$sid,
#        'T:s'   => \$tablespace,
        'c:i'   => \$critpct,
        'w:i'   => \$alertpct
        );

}

sub usage {
        print "@_\n" if @_;
        print "usage : check_ora_table_space.pl -H <host> -D <sid> -P <port> -T <tablespace> -w <pctwarn>  -c <pctcrit>\n";
        exit (-1);
}

sub error {
        print "@_\n" if @_;
        exit (2);
}

 

###main####

check_options();

my $dbh = DBI->connect( "dbi:Oracle:host=$host;port=$port;sid=$sid", $dbuser, $dbpass, { PrintError => 0, AutoCommit => 1, RaiseError => 0 } )
        || &error ("cannot connect to $dbname: $DBI::errstr\n");

my  $sth = $dbh->prepare(<<EOF
select ts tablespace_name,
       sum(mbytes) total,
       sum(abytes) - sum(fbytes) USED,
       (sum(abytes) - sum(fbytes)) / sum(mbytes) * 100 PCT_USED
  from (select tablespace_name ts,
               sum(bytes) / (1024 * 1024) mbytes,
               sum(bytes) / (1024 * 1024) abytes,
               0 fbytes
          from dba_data_files
         group by tablespace_name
        union
        select tablespace_name ts,
               0 mbytes,
               0 abytes,
               sum(bytes) / (1024 * 1024) fbytes
          from dba_free_space
         group by tablespace_name)
group by ts
EOF
)

    || &error("Cannot prepare request : $DBI::errstr\n");

$sth->execute
        || &error("Cannot execute request : $DBI::errstr\n");

while (my ($tbname, $total, $used, $pct_used) = $sth->fetchrow)
{
        my  $pct_used=int($pct_used);
        my  $used=int($used);
        #print STDOUT "$sid $tbname table size: ". $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)";
        #print "table space $answer\n";


         #my ($rc) = $dbh->disconnect
         #|| &error ("Cannot disconnect from database : $dbh->errstr\n") ;
        if (($pct_used > $alertpct) && ($total - $used < $WARN_FREE) ) {

                if ( ($pct_used > $critpct) && (  $total - $used < $CRIT_FREE ) ) {
                        $exit_code = 2;
                        $output.="$sid $tbname table size: ". $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)";
                        $perfout.="$tbname=".$used."MB ";
                } else {
                        $exit_code = ($exit_code > 1) ? $exit_code :1 ;
                        $output.="$sid $tbname table size: ". $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)";
                        $perfout.="$tbname=".$used."MB ";
                }
        } else {
                $exit_code = ($exit_code >0) ?  $exit_code:0;
                $outputok.="$sid $tbname table size: ". $total . " MB Used:" . int($used) . " MB (" . int($pct_used) . "%)";
                $perfout.="$tbname=".$used."MB ";
        }
}

$rc = $dbh->disconnect
        || &error ("Cannot disconnect from database : $dbh->errstr\n");

if(!defined($output)){ $output=$outputok; }
print "$output. |$perfout \n";
exit ($exit_code);
    2：然后运行脚本：
[root@server ] ./check_oracle_space.pl -H SZVSA031 -D SVED -P 1521  -w 90 -c 95