門戶網(wǎng)站運(yùn)維監(jiān)控系列-如何監(jiān)控oracle數(shù)據(jù)庫表空間

cnweili

文章摘自http://www.centreon.com.cn/

用法： 命令  -H 主機(jī)  -D  數(shù)據(jù)庫  -P  端口  -w warning  -c critical
[root@SZVSXMON libexec]# ./check_oracle_space.pl -H SZVSA031 -D SVED -P 1521  -w 90 -c 95
SVED CWMLITE table size: 20 MB Used:9 MB (46%)SVED DRSYS table size: 20 MB Used:9 MB (48%)SVED EXAMPLE table size: 2000 MB Used:148 MB (7%)SVED INDX table size: 25 MB Used:0 MB (0%)SVED ODM table size: 20 MB Used:9 MB (46%)SVED SYSTEM table size: 2000 MB Used:398 MB (19%)SVED TOOLS table size: 100 MB Used:6 MB (6%)SVED UNDOTBS1 table size: 580 MB Used:95 MB (16%)SVED USERS table size: 25 MB Used:0 MB (0%)SVED USERS_SZVDDEV table size: 2000 MB Used:14 MB (0%)SVED USERS_SZVSVED table size: 2000 MB Used:4 MB (0%)SVED USERS_TTM table size: 500 MB Used:237 MB (47%)SVED XDB table size: 200 MB Used:37 MB (18%). |CWMLITE=9MB DRSYS=9MB EXAMPLE=148MB INDX=0MB ODM=9MB SYSTEM=398MB TOOLS=6MB UNDOTBS1=95MB USERS=0MB USERS_SZVDDEV=14MB USERS_SZVSVED=4MB USERS_TTM=237MB XDB=37MB
[root@SZVSXMON libexec]#

插件代碼如下：

注意修改自己的數(shù)據(jù)庫DB的帳號
my $dbuser = 'checkdb';
my $dbpass = 'checkdb$password;

[root@SZVSXMON libexec]# cat ./check_oracle_space.pl
#!/usr/bin/perl
######################### check_oracle_table_space ##############
# Program check_oracle_table_space
# Version : 0.2
# Date :  Sep 10 2008
# Author  : liwei
# TODO : Password can't bring into the parameters
# website:http://www.centreon.com.cn
# mail:　liwei@centreon.com.cn
#################################################################

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 = undef;
my $sid  = undef;
my $port = undef;
my $dbuser = 'checkdb';
my $dbpass = 'checkdb$password;
my $tablespace = undef;
my $alertpct = undef;
my $critpct =  undef;

my $dbname = undef;
my $tbname = undef;
my $total  = undef;
my $used  = undef;
my $pct_used  = undef;
my $pct_used_max = 0;
my $total = undef;
my $rc = undef;
my $exit_code = undef;
my $output = undef;
my $perfout = undef;
my $outputok = undef;
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);

[ 本帖最后由 cnweili 于 2008-12-29 16:10 編輯 ]

solaris小兵

無所不能的nagios��！很好很強(qiáng)大��！

marinercn

有那么麻煩嗎？使用OP或者AP manage很簡單的、

jascar

希望能夠更詳細(xì)點(diǎn)就好了！