Permalink
Switch branches/tags
Nothing to show
Find file
Fetching contributors…
Cannot retrieve contributors at this time
6330 lines (5957 sloc) 218 KB
#! /usr/bin/perl -w
my %ERRORS=( OK => 0, WARNING => 1, CRITICAL => 2, UNKNOWN => 3 );
my %ERRORCODES=( 0 => 'OK', 1 => 'WARNING', 2 => 'CRITICAL', 3 => 'UNKNOWN' );
package DBD::Oracle::Server::Instance::SGA::DataBuffer;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
sum_physical_reads => undef,
sum_physical_reads_direct => undef,
sum_physical_reads_direct_lob => undef,
sum_session_logical_reads => undef,
hitratio => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga::databuffer::hitratio/) {
($self->{sum_physical_reads}, $self->{sum_physical_reads_direct},
$self->{sum_physical_reads_direct_lob},
$self->{sum_session_logical_reads}) =
$self->{handle}->fetchrow_array(q{
SELECT SUM(DECODE(name, 'physical reads', value, 0)),
SUM(DECODE(name, 'physical reads direct', value, 0)),
SUM(DECODE(name, 'physical reads direct (lob)', value, 0)),
SUM(DECODE(name, 'session logical reads', value, 0))
FROM sys.v_$sysstat
});
if (! defined $self->{sum_physical_reads}) {
$self->add_nagios_critical("unable to get sga buffer cache");
} else {
$self->valdiff(\%params, qw(sum_physical_reads sum_physical_reads_direct
sum_physical_reads_direct_lob sum_session_logical_reads));
$self->{hitratio} = $self->{delta_sum_session_logical_reads} ?
100 - 100 * ((
$self->{delta_sum_physical_reads} -
$self->{delta_sum_physical_reads_direct_lob} -
$self->{delta_sum_physical_reads_direct}) /
$self->{delta_sum_session_logical_reads}) : 0;
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::sga::databuffer::hitratio/) {
$self->add_nagios(
$self->check_thresholds($self->{hitratio}, "98:", "95:"),
sprintf "SGA data buffer hit ratio %.2f%%", $self->{hitratio});
$self->add_perfdata(sprintf "sga_data_buffer_hit_ratio=%.2f%%;%s;%s",
$self->{hitratio},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::SGA::SharedPool::DictionaryCache;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA::SharedPool);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
sum_gethits => undef,
sum_gets => undef,
hitratio => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~
/server::instance::sga::sharedpool::dictionarycache::hitratio/) {
($self->{sum_gets}, $self->{sum_gethits}) =
$self->{handle}->fetchrow_array(q{
SELECT SUM(gets), SUM(gets-getmisses) FROM v$rowcache
});
if (! defined $self->{sum_gets}) {
$self->add_nagios_critical("unable to get sga dc");
} else {
$self->valdiff(\%params, qw(sum_gets sum_gethits));
$self->{hitratio} = $self->{delta_sum_gets} ?
(100 * $self->{delta_sum_gethits} / $self->{delta_sum_gets}) : 0;
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~
/server::instance::sga::sharedpool::dictionarycache::hitratio/) {
$self->add_nagios(
$self->check_thresholds($self->{hitratio}, "95:", "90:"),
sprintf "SGA dictionary cache hit ratio %.2f%%", $self->{hitratio});
$self->add_perfdata(sprintf "sga_dictionary_cache_hit_ratio=%.2f%%;%s;%s",
$self->{hitratio}, $self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::SGA::SharedPool::LibraryCache;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA::SharedPool);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
sum_gets => undef,
sum_gethits => undef,
sum_pins => undef,
sum_pinhits => undef,
get_hitratio => undef,
pin_hitratio => undef,
reloads => undef,
invalidations => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~
/server::instance::sga::sharedpool::librarycache::(reloads|.*hitratio)/) {
# http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/sqlviews.htm
# Look for the following when querying this view:
#
# High RELOADS or INVALIDATIONS
# Low GETHITRATIO or GETPINRATIO
#
# High number of RELOADS could be due to the following:
#
# Objects being invalidated (large number of INVALIDATIONS)
# Objects getting swapped out of memory
#
# Low GETHITRATIO could indicate that objects are getting swapped out of memory.
#
# Low PINHITRATIO could indicate the following:
#
# Session not executing the same cursor multiple times (even though it might be shared across different sessions)
# Session not finding the cursor shared
#
# The next step is to query V$DB_OBJECT_CACHE/V$SQLAREA to see if problems are limited to certain objects or spread across different objects. If invalidations are high, then it might be worth investigating which of the (invalidated object's) underlying objects are being changed.
#
($self->{sum_gethits}, $self->{sum_gets}, $self->{sum_pinhits},
$self->{sum_pins}, $self->{reloads}, $self->{invalidations}) =
$self->{handle}->fetchrow_array(q{
SELECT SUM(gethits), SUM(gets), SUM(pinhits), SUM(pins),
SUM(reloads), SUM(invalidations)
FROM v$librarycache
});
if (! defined $self->{sum_gets} || ! defined $self->{sum_pinhits}) {
$self->add_nagios_critical("unable to get sga lc");
} else {
$self->valdiff(\%params, qw(sum_gets sum_gethits sum_pins sum_pinhits reloads invalidations));
$self->{get_hitratio} = $self->{delta_sum_gets} ?
(100 * $self->{delta_sum_gethits} / $self->{delta_sum_gets}) : 0;
$self->{pin_hitratio} = $self->{delta_sum_pins} ?
(100 * $self->{delta_sum_pinhits} / $self->{delta_sum_pins}) : 0;
$self->{reload_rate} = $self->{delta_reloads} / $self->{delta_timestamp};
$self->{invalidation_rate} = $self->{delta_invalidations} / $self->{delta_timestamp};
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~
/server::instance::sga::sharedpool::librarycache::(get)*hitratio/) {
$self->add_nagios(
$self->check_thresholds($self->{get_hitratio}, "98:", "95:"),
sprintf "SGA library cache (get) hit ratio %.2f%%", $self->{get_hitratio});
$self->add_perfdata(sprintf "sga_library_cache_hit_ratio=%.2f%%;%s;%s",
$self->{get_hitratio}, $self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::sharedpool::librarycache::pinhitratio/) {
$self->add_nagios(
$self->check_thresholds($self->{pin_hitratio}, "98:", "95:"),
sprintf "SGA library cache (pin) hit ratio %.2f%%", $self->{get_hitratio});
$self->add_perfdata(sprintf "sga_library_cache_hit_ratio=%.2f%%;%s;%s",
$self->{get_hitratio}, $self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::sharedpool::librarycache::reloads/) {
$self->add_nagios(
$self->check_thresholds($self->{pin_hitratio}, "10", "100"),
sprintf "SGA library cache reloads %.2f/sec", $self->{reload_rate});
$self->add_perfdata(sprintf "sga_library_cache_reloads_per_sec=%.2f;%s;%s",
$self->{reload_rate}, $self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "sga_library_cache_invalidations_per_sec=%.2f",
$self->{invalidation_rate});
}
}
}
package DBD::Oracle::Server::Instance::SGA::SharedPool;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA);
sub new {
my $class = shift;
my %params = @_;
my $self = {
free => undef,
reloads => undef,
pins => undef,
handle => $params{handle},
library_cache => undef,
dictionary_cache => undef,
parse_soft => undef,
parse_hard => undef,
parse_failures => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga::sharedpool::librarycache/) {
$self->{library_cache} =
DBD::Oracle::Server::Instance::SGA::SharedPool::LibraryCache->new(
%params);
} elsif ($params{mode} =~ /server::instance::sga::sharedpool::dictionarycache/) {
$self->{dictionary_cache} =
DBD::Oracle::Server::Instance::SGA::SharedPool::DictionaryCache->new(
%params);
} elsif ($params{mode} eq "server::instance::sga::sharedpool::free") {
$self->init_shared_pool_free(%params);
} elsif ($params{mode} eq "server::instance::sga::sharedpool::reloads") {
$self->init_shared_pool_reloads(%params);
} elsif ($params{mode} eq "server::instance::sga::sharedpool::softparse") {
$self->init_shared_pool_parser(%params);
}
}
sub init_shared_pool_reloads {
my $self = shift;
my %params = @_;
($self->{reloads}, $self->{pins}) = $self->{handle}->fetchrow_array(q{
SELECT SUM(reloads), SUM(pins)
FROM v$librarycache
WHERE namespace IN ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')
});
if (! defined $self->{reloads}) {
$self->add_nagios_critical("unable to get sga reloads");
} else {
$self->valdiff(\%params, qw(reloads pins));
$self->{reload_ratio} = $self->{delta_pins} ?
100 * $self->{delta_reloads} / $self->{delta_pins} : 100;
}
}
sub init_shared_pool_free {
my $self = shift;
my %params = @_;
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("9.x")) {
$self->{free_percent} = $self->{handle}->fetchrow_array(q{
SELECT ROUND(a.bytes / b.sm * 100,2) FROM
(SELECT bytes FROM v$sgastat
WHERE name='free memory' AND pool='shared pool') a,
(SELECT SUM(bytes) sm FROM v$sgastat
WHERE pool = 'shared pool' AND bytes <=
(SELECT bytes FROM v$sgastat
WHERE name='free memory' AND pool='shared pool')) b
});
} else {
# i don't know if the above code works for 8.x, so i leave the old one here
$self->{free_percent} = $self->{handle}->fetchrow_array(q{
SELECT ROUND((SUM(DECODE(name, 'free memory', bytes, 0)) /
SUM(bytes)) * 100,2) FROM v$sgastat where pool = 'shared pool'
});
}
if (! defined $self->{free_percent}) {
$self->add_nagios_critical("unable to get sga free");
return undef;
}
}
sub init_shared_pool_parser {
my $self = shift;
my %params = @_;
($self->{parse_total}, $self->{parse_hard}, $self->{parse_failures}) =
$self->{handle}->fetchrow_array(q{
SELECT
(SELECT value FROM v$sysstat WHERE name = 'parse count (total)'),
(SELECT value FROM v$sysstat WHERE name = 'parse count (hard)'),
(SELECT value FROM v$sysstat WHERE name = 'parse count (failures)')
FROM DUAL
});
if (! defined $self->{parse_total}) {
$self->add_nagios_critical("unable to get parser");
} else {
$self->valdiff(\%params, qw(parse_total parse_hard parse_failures));
$self->{parse_soft_ratio} = $self->{delta_parse_total} ?
100 * ($self->{delta_parse_total} - $self->{delta_parse_hard}) /
$self->{delta_parse_total} : 100;
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::sga::sharedpool::librarycache/) {
$self->{library_cache}->nagios(%params);
$self->merge_nagios($self->{library_cache});
} elsif ($params{mode} =~ /server::instance::sga::sharedpool::dictionarycache/) {
$self->{dictionary_cache}->nagios(%params);
$self->merge_nagios($self->{dictionary_cache});
} elsif ($params{mode} eq "server::instance::sga::sharedpool::free") {
$self->add_nagios(
$self->check_thresholds($self->{free_percent}, "10:", "5:"),
sprintf "SGA shared pool free %.2f%%", $self->{free_percent});
$self->add_perfdata(sprintf "sga_shared_pool_free=%.2f%%;%s;%s",
$self->{free_percent}, $self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} eq "server::instance::sga::sharedpool::reloads") {
$self->add_nagios(
$self->check_thresholds($self->{reload_ratio}, "1", "10"),
sprintf "SGA shared pool reload ratio %.2f%%", $self->{reload_ratio});
$self->add_perfdata(sprintf "sga_shared_pool_reload_ratio=%.2f%%;%s;%s",
$self->{reload_ratio}, $self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} eq "server::instance::sga::sharedpool::softparse") {
$self->add_nagios(
$self->check_thresholds( $self->{parse_soft_ratio}, "98:", "90:"),
sprintf "Soft parse ratio %.2f%%", $self->{parse_soft_ratio});
$self->add_perfdata(sprintf "soft_parse_ratio=%.2f%%;%s;%s",
$self->{parse_soft_ratio},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::SGA::RollbackSegments;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA);
# only create one object with new which stands for all rollback segments
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
gets => undef,
waits => undef,
wraps => undef,
extends => undef,
undo_header_waits => undef,
undo_block_waits => undef,
rollback_segment_hit_ratio => undef,
rollback_segment_header_contention => undef,
rollback_segment_block_contention => undef,
rollback_segment_extents => undef,
rollback_segment_wraps => undef,
rollback_segment_wraps_persec => undef,
rollback_segment_extends => undef,
rollback_segment_extends_persec => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga::rollbacksegments::wraps/) {
$self->{wraps} = $self->{handle}->fetchrow_array(q{
SELECT SUM(wraps) FROM v$rollstat
});
if (! defined $self->{wraps}) {
$self->add_nagios_critical("unable to get rollback segments stats");
} else {
$self->valdiff(\%params, qw(wraps));
$self->{rollback_segment_wraps} = $self->{delta_wraps};
$self->{rollback_segment_wraps_persec} = $self->{delta_wraps} /
$self->{delta_timestamp};
}
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::extends/) {
$self->{extends} = $self->{handle}->fetchrow_array(q{
SELECT SUM(extends) FROM v$rollstat
});
if (! defined $self->{extends}) {
$self->add_nagios_critical("unable to get rollback segments stats");
} else {
$self->valdiff(\%params, qw(extends));
$self->{rollback_segment_extends} = $self->{delta_extends};
$self->{rollback_segment_extends_persec} = $self->{delta_extends} /
$self->{delta_timestamp};
}
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::headercontention/) {
($self->{undo_header_waits}, $self->{waits}) = $self->{handle}->fetchrow_array(q{
SELECT (
SELECT SUM(count)
FROM v$waitstat
WHERE class = 'undo header' OR class = 'system undo header'
) undo, (
SELECT SUM(count)
FROM v$waitstat
) complete
FROM DUAL
});
if (! defined $self->{undo_header_waits}) {
$self->add_nagios_critical("unable to get rollback segments wait stats");
} else {
$self->valdiff(\%params, qw(undo_header_waits waits));
$self->{rollback_segment_header_contention} =
$self->{delta_waits} ? 100 * $self->{delta_undo_header_waits} / $self->{delta_waits} : 0;
}
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::blockcontention/) {
($self->{undo_block_waits}, $self->{waits}) = $self->{handle}->fetchrow_array(q{
SELECT (
SELECT SUM(count)
FROM v$waitstat
WHERE class = 'undo block' OR class = 'system undo block'
) undo, (
SELECT SUM(count)
FROM v$waitstat
) complete
FROM DUAL
});
if (! defined $self->{undo_block_waits}) {
$self->add_nagios_critical("unable to get rollback segments wait stats");
} else {
$self->valdiff(\%params, qw(undo_block_waits waits));
$self->{rollback_segment_block_contention} =
$self->{delta_waits} ? 100 * $self->{delta_undo_block_waits} / $self->{delta_waits} : 0;
}
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::hitratio/) {
($self->{waits}, $self->{gets}) = $self->{handle}->fetchrow_array(q{
SELECT SUM(waits), SUM(gets) FROM v$rollstat
});
if (! defined $self->{gets}) {
$self->add_nagios_critical("unable to get rollback segments wait stats");
} else {
$self->valdiff(\%params, qw(waits gets));
$self->{rollback_segment_hit_ratio} = $self->{delta_gets} ?
100 - 100 * $self->{delta_waits} / $self->{delta_gets} : 100;
}
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::avgactivesize/) {
if ($params{selectname}) {
$self->{rollback_segment_optimization_size} = $self->{handle}->fetchrow_array(q{
SELECT AVG(s.optsize / 1048576) optmization_size
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn AND n.name != 'SYSTEM' AND n.name = ?
}, $params{selectname}) || 0;
$self->{rollback_segment_average_active} = $self->{handle}->fetchrow_array(q{
SELECT AVG(s.aveactive / 1048576) average_active
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn AND n.name != 'SYSTEM' AND n.name = ?
}, $params{selectname}) || 0;
} else {
$self->{rollback_segment_optimization_size} = $self->{handle}->fetchrow_array(q{
SELECT AVG(s.optsize / 1048576) optmization_size
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn AND n.name != 'SYSTEM'
}) || 0;
$self->{rollback_segment_average_active} = $self->{handle}->fetchrow_array(q{
SELECT AVG(s.aveactive / 1048576) average_active
FROM v$rollstat s, v$rollname n
WHERE s.usn = n.usn AND n.name != 'SYSTEM'
}) || 0;
}
} else {
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::sga::rollbacksegments::wraps/) {
if ($params{absolute}) {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_wraps}, "1", "100"),
sprintf "Rollback segment wraps %d times",
$self->{rollback_segment_wraps});
} else {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_wraps_persec}, "1", "100"),
sprintf "Rollback segment wraps %.2f/sec",
$self->{rollback_segment_wraps_persec});
}
$self->add_perfdata(
sprintf "rollback_segment_wraps=%d;%s;%s",
$self->{rollback_segment_wraps},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(
sprintf "rollback_segment_wraps_rate=%.2f;%s;%s",
$self->{rollback_segment_wraps_persec},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::extends/) {
if ($params{absolute}) {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_extends}, "1", "100"),
sprintf "Rollback segment extends %d times",
$self->{rollback_segment_extends});
} else {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_extends_persec}, "1", "100"),
sprintf "Rollback segment extends %.2f/sec",
$self->{rollback_segment_extends_persec});
}
$self->add_perfdata(
sprintf "rollback_segment_extends=%d;%s;%s",
$self->{rollback_segment_extends},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(
sprintf "rollback_segment_extends_rate=%.2f;%s;%s",
$self->{rollback_segment_extends_persec},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::headercontention/) {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_header_contention}, "1", "2"),
sprintf "Rollback segment header contention is %.2f%%",
$self->{rollback_segment_header_contention});
$self->add_perfdata(
sprintf "rollback_segment_header_contention=%.2f%%;%s;%s",
$self->{rollback_segment_header_contention},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::blockcontention/) {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_block_contention}, "1", "2"),
sprintf "Rollback segment block contention is %.2f%%",
$self->{rollback_segment_block_contention});
$self->add_perfdata(
sprintf "rollback_segment_block_contention=%.2f%%;%s;%s",
$self->{rollback_segment_block_contention},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::hitratio/) {
$self->add_nagios(
$self->check_thresholds(
$self->{rollback_segment_hit_ratio}, "99:", "98:"),
sprintf "Rollback segment hit ratio is %.2f%%",
$self->{rollback_segment_hit_ratio});
$self->add_perfdata(
sprintf "rollback_segment_hit_ratio=%.2f%%;%s;%s",
$self->{rollback_segment_hit_ratio},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::rollbacksegments::avgactivesize/) {
$self->add_nagios_ok(sprintf "Rollback segment average size %.2f MB",
$self->{rollback_segment_average_active});
$self->add_perfdata(
sprintf "rollback_segment_avgsize=%.2f rollback_segment_optsize=%.2f",
$self->{rollback_segment_average_active},
$self->{rollback_segment_optimization_size});
}
}
}
package DBD::Oracle::Server::Instance::SGA::RedoLogBuffer;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
last_switch_interval => undef,
redo_buffer_allocation_retries => undef,
redo_entries => undef,
retry_ratio => undef,
redo_size => undef,
redo_size_per_sec => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga::redologbuffer::switchinterval/) {
if ($self->instance_rac()) {
eval {
# alles was jemals geswitcht hat, letzter switch, zweitletzter switch
# jetzt - letzter switch = mindestlaenge des naechsten intervals
# wenn das lang genug ist, dann war das letzte, kurze intervall
# wohl nur ein ausreisser oder manueller switch
# derzeit laufendes intervall, letztes intervall, vorletztes intervall
($self->{next_switch_interval}, $self->{last_switch_interval}, $self->{nextto_last_switch_interval}) =
$self->{handle}->fetchrow_array(q {
WITH temptab AS
(
SELECT sequence#, first_time FROM sys.v_$log WHERE status = 'CURRENT'
AND thread# = ?
UNION ALL
SELECT sequence#, first_time FROM sys.v_$log_history
WHERE thread# = ?
ORDER BY first_time DESC
)
SELECT
(sysdate - a.first_time) * 1440 * 60 thisinterval,
(a.first_time - b.first_time) * 1440 * 60 lastinterval,
(b.first_time - c.first_time) * 1440 * 60 nexttolastinterval
FROM
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 1
) WHERE irow = 1
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) a,
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 2
) WHERE irow = 2
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) b,
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 3
) WHERE irow = 3
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) c
}, $self->instance_thread(), $self->instance_thread());
};
} else {
eval {
# alles was jemals geswitcht hat, letzter switch, zweitletzter switch
# jetzt - letzter switch = mindestlaenge des naechsten intervals
# wenn das lang genug ist, dann war das letzte, kurze intervall
# wohl nur ein ausreisser oder manueller switch
# derzeit laufendes intervall, letztes intervall, vorletztes intervall
($self->{next_switch_interval}, $self->{last_switch_interval}, $self->{nextto_last_switch_interval}) =
$self->{handle}->fetchrow_array(q {
WITH temptab AS
(
SELECT sequence#, first_time FROM sys.v_$log WHERE status = 'CURRENT'
UNION ALL
SELECT sequence#, first_time FROM sys.v_$log_history ORDER BY first_time DESC
)
SELECT
(sysdate - a.first_time) * 1440 * 60 thisinterval,
(a.first_time - b.first_time) * 1440 * 60 lastinterval,
(b.first_time - c.first_time) * 1440 * 60 nexttolastinterval
FROM
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 1
) WHERE irow = 1
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) a,
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 2
) WHERE irow = 2
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) b,
(
SELECT NVL(
(
SELECT first_time FROM (
SELECT first_time, rownum AS irow FROM temptab WHERE ROWNUM <= 3
) WHERE irow = 3
) , to_date('20090624','YYYYMMDD')) as first_time FROM dual
) c
});
};
}
if (! defined $self->{last_switch_interval}) {
$self->add_nagios_critical(
sprintf "unable to get last switch interval");
}
} elsif ($params{mode} =~ /server::instance::sga::redologbuffer::retryratio/) {
($self->{redo_buffer_allocation_retries}, $self->{redo_entries}) =
$self->{handle}->fetchrow_array(q{
SELECT a.value, b.value
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'redo buffer allocation retries'
AND b.name = 'redo entries'
});
if (! defined $self->{redo_buffer_allocation_retries}) {
$self->add_nagios_critical("unable to get retry ratio");
} else {
$self->valdiff(\%params, qw(redo_buffer_allocation_retries redo_entries));
$self->{retry_ratio} = $self->{delta_redo_entries} ?
100 * $self->{delta_redo_buffer_allocation_retries} / $self->{delta_redo_entries} : 0;
}
} elsif ($params{mode} =~ /server::instance::sga::redologbuffer::iotraffic/) {
$self->{redo_size} = $self->{handle}->fetchrow_array(q{
SELECT value FROM v$sysstat WHERE name = 'redo size'
});
if (! defined $self->{redo_size}) {
$self->add_nagios_critical("unable to get redo size");
} else {
$self->valdiff(\%params, qw(redo_size));
$self->{redo_size_per_sec} =
$self->{delta_redo_size} / $self->{delta_timestamp};
# Megabytes / sec
$self->{redo_size_per_sec} = $self->{redo_size_per_sec} / 1048576;
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~
/server::instance::sga::redologbuffer::switchinterval/) {
my $nextlevel = $self->check_thresholds($self->{next_switch_interval}, "600:", "60:");
my $nexttolastlevel = $self->check_thresholds($self->{nextto_last_switch_interval}, "600:", "60:");
my $lastlevel = $self->check_thresholds($self->{last_switch_interval}, "600:", "60:");
if ($lastlevel) {
# nachschauen, ob sich die situation schon entspannt hat
if ($nextlevel == 2) {
# das riecht nach aerger. kann zwar auch daran liegen, weil der check unmittelbar nach dem kurzen switch
# ausgefuehrt wird, aber dann bleibts beim soft-hard und beim retry schauts schon besser aus.
if ($self->{next_switch_interval} < 0) {
# jetzt geht gar nichts mehr
$self->add_nagios(
2,
"Found a redo log with a timestamp in the future!!");
$self->{next_switch_interval} = 0;
} else {
$self->add_nagios(
# 10: minutes, 1: minute = 600:, 60:
$nextlevel,
sprintf "Last redo log file switch interval was %d minutes%s. Next interval presumably >%d minutes",
$self->{last_switch_interval} / 60,
$self->instance_rac() ? sprintf " (thread %d)", $self->instance_thread() : "",
$self->{next_switch_interval} / 60);
}
} elsif ($nextlevel == 1) {
# das kommt daher, weil retry_interval < warningthreshold
if ($nexttolastlevel) {
# aber vorher war auch schon was faul. da braut sich vieleicht was zusammen.
# die warnung ist sicher berechtigt.
$self->add_nagios(
$nextlevel,
sprintf "Last redo log file switch interval was %d minutes%s. Next interval presumably >%d minutes. Second incident in a row.",
$self->{last_switch_interval} / 60,
$self->instance_rac() ? sprintf " (thread %d)", $self->instance_thread() : "",
$self->{next_switch_interval} / 60);
} else {
# hier bin ich grosszuegig. vorletztes intervall war ok, letztes intervall war nicht ok.
# ich rechne mir also chancen aus, dass $nextlevel nur auf warning ist, weil der retry zu schnell
# nach dem letzten switch stattfindet. sollte sich entspannen und wenns wirklich ein problem gibt
# dann kommt sowieso wieder ein switch. also erstmal ok.
$self->add_nagios(
0,
sprintf "Last redo log file switch interval was %d minutes%s. Next interval presumably >%d minutes. Probably a single incident.",
$self->{last_switch_interval} / 60,
$self->instance_rac() ? sprintf " (thread %d)", $self->instance_thread() : "",
$self->{next_switch_interval} / 60);
}
} else {
# war wohl ein einzelfall. also gehen wir davon aus, dass das warninglevel nur wegen des retrys
# unterschritten wurde und der naechste switch wieder lange genug sein wird
$self->add_nagios(
$nextlevel, # sollte 0 sein
sprintf "Last redo log file switch interval was %d minutes%s. Next interval presumably >%d minutes",
$self->{last_switch_interval} / 60,
$self->instance_rac() ? sprintf " (thread %d)", $self->instance_thread() : "",
$self->{next_switch_interval} / 60);
}
} else {
$self->add_nagios(
$lastlevel,
sprintf "Last redo log file switch interval was %d minutes%s. Next interval presumably >%d minutes",
$self->{last_switch_interval} / 60,
$self->instance_rac() ? sprintf " (thread %d)", $self->instance_thread() : "",
$self->{next_switch_interval} / 60);
}
$self->add_perfdata(sprintf "redo_log_file_switch_interval=%ds;%s;%s",
$self->{last_switch_interval},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::redologbuffer::retryratio/) {
$self->add_nagios(
$self->check_thresholds($self->{retry_ratio}, "1", "10"),
sprintf "Redo log retry ratio is %.6f%%",$self->{retry_ratio});
$self->add_perfdata(sprintf "redo_log_retry_ratio=%.6f%%;%s;%s",
$self->{retry_ratio},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::redologbuffer::iotraffic/) {
$self->add_nagios(
$self->check_thresholds($self->{redo_size_per_sec}, "100", "200"),
sprintf "Redo log io is %.6f MB/sec", $self->{redo_size_per_sec});
$self->add_perfdata(sprintf "redo_log_io_per_sec=%.6f;%s;%s",
$self->{redo_size_per_sec},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::SGA::Latch;
our @ISA = qw(DBD::Oracle::Server::Instance::SGA);
{
my @latches = ();
my $initerrors = undef;
sub add_latch {
push(@latches, shift);
}
sub return_latches {
my %params = @_;
if ($params{mode} =~ /server::instance::sga::latch::contention/) {
return reverse
sort { $a->{contention} <=> $b->{contention} } @latches;
} else {
return reverse
sort { $a->{name} cmp $b->{name} } @latches;
}
}
sub init_latches {
my %params = @_;
my $num_latches = 0;
if (($params{mode} =~ /server::instance::sga::latch::contention/) ||
($params{mode} =~ /server::instance::sga::latch::waiting/) ||
($params{mode} =~ /server::instance::sga::latch::hitratio/) ||
($params{mode} =~ /server::instance::sga::latch::listlatches/)) {
my $sumsleeps = $params{handle}->fetchrow_array(q{
SELECT SUM(sleeps) FROM v$latch
});
my @latchresult = $params{handle}->fetchall_array(q{
SELECT latch#, name, gets, sleeps, misses, wait_time
FROM v$latch
});
foreach (@latchresult) {
my ($number, $name, $gets, $sleeps, $misses, $wait_time) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if ($params{selectname} && (
($params{selectname} !~ /^\d+$/ && (lc $params{selectname} ne lc $name)) ||
($params{selectname} =~ /^\d+$/ && ($params{selectname} != $number))));
}
my %thisparams = %params;
$thisparams{number} = $number;
$thisparams{name} = $name;
$thisparams{gets} = $gets;
$thisparams{misses} = $misses;
$thisparams{sleeps} = $sleeps;
$thisparams{wait_time} = $wait_time;
$thisparams{sumsleeps} = $sumsleeps;
my $latch = DBD::Oracle::Server::Instance::SGA::Latch->new(
%thisparams);
add_latch($latch);
$num_latches++;
}
if (! $num_latches) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
number => $params{number},
name => $params{name},
gets => $params{gets},
misses => $params{misses},
sleeps => $params{sleeps},
wait_time => $params{wait_time},
sumsleeps => $params{sumsleeps},
hitratio => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~
/server::instance::sga::latch::hitratio/) {
if (! defined $self->{gets}) {
$self->add_nagios_critical(
sprintf "unable to get sga latches %s", $self->{name});
} else {
$params{differenciator} = lc $self->{name}.$self->{number};
$self->valdiff(\%params, qw(gets misses));
$self->{hitratio} = $self->{delta_gets} ?
100 * ($self->{delta_gets} - $self->{delta_misses}) / $self->{delta_gets} : 100;
}
} elsif (($params{mode} =~ /server::instance::sga::latch::contention/) ||
($params{mode} =~ /server::instance::sga::latch::waiting/)) {
if (! defined $self->{gets}) {
$self->add_nagios_critical(
sprintf "unable to get sga latches %s", $self->{name});
} else {
$params{differenciator} = lc $self->{name}.$self->{number};
$self->valdiff(\%params, qw(gets sleeps misses wait_time sumsleeps));
# latch contention
$self->{contention} = $self->{delta_gets} ?
100 * $self->{delta_misses} / $self->{delta_gets} : 0;
# latch percent of sleep during the elapsed time
$self->{sleep_share} = $self->{delta_wait_time} ?
((100 * $self->{wait_time}) / 1000) / $self->{delta_timestamp} : 0;
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~
/server::instance::sga::latch::hitratio/) {
$self->add_nagios(
$self->check_thresholds($self->{hitratio}, "98:", "95:"),
sprintf "SGA latches hit ratio %.2f%%", $self->{hitratio});
$self->add_perfdata(sprintf "sga_latches_hit_ratio=%.2f%%;%s;%s",
$self->{hitratio}, $self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::instance::sga::latch::contention/) {
$self->add_nagios(
$self->check_thresholds($self->{contention}, "1", "2"),
sprintf "SGA latch %s (#%d) contention %.2f%%",
$self->{name}, $self->{number}, $self->{contention});
$self->add_perfdata(sprintf "'latch_%d_contention'=%.2f%%;%s;%s",
$self->{number}, $self->{contention}, $self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "'latch_%d_gets'=%u",
$self->{number}, $self->{delta_gets});
} elsif ($params{mode} =~
/server::instance::sga::latch::waiting/) {
$self->add_nagios(
$self->check_thresholds($self->{sleep_share}, "0.1", "1"),
sprintf "SGA latch %s (#%d) sleeping %.6f%% of the time",
$self->{name}, $self->{number}, $self->{sleep_share});
$self->add_perfdata(sprintf "'latch_%d_sleep_share'=%.6f%%;%s;%s;0;100",
$self->{number}, $self->{sleep_share}, $self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::SGA;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
data_buffer => undef,
shared_pool => undef,
latches => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga::databuffer/) {
$self->{data_buffer} = DBD::Oracle::Server::Instance::SGA::DataBuffer->new(
%params);
} elsif ($params{mode} =~ /server::instance::sga::sharedpool/) {
$self->{shared_pool} = DBD::Oracle::Server::Instance::SGA::SharedPool->new(
%params);
} elsif ($params{mode} =~ /server::instance::sga::latch/) {
DBD::Oracle::Server::Instance::SGA::Latch::init_latches(%params);
if (my @latches =
DBD::Oracle::Server::Instance::SGA::Latch::return_latches(%params)) {
$self->{latches} = \@latches;
} else {
$self->add_nagios_critical("unable to aquire latch info");
}
} elsif ($params{mode} =~ /server::instance::sga::redolog/) {
$self->{redo_log_buffer} =
DBD::Oracle::Server::Instance::SGA::RedoLogBuffer->new(%params);
} elsif ($params{mode} =~ /server::instance::sga::rollbacksegments/) {
$self->{rollback_segments} =
DBD::Oracle::Server::Instance::SGA::RollbackSegments->new(%params);
}
}
sub nagios {
my $self = shift;
my %params = @_;
if ($params{mode} =~ /server::instance::sga::databuffer/) {
$self->{data_buffer}->nagios(%params);
$self->merge_nagios($self->{data_buffer});
} elsif ($params{mode} =~ /server::instance::sga::sharedpool/) {
$self->{shared_pool}->nagios(%params);
$self->merge_nagios($self->{shared_pool});
} elsif ($params{mode} =~ /server::instance::sga::latch::hitratio/) {
if (! $self->{nagios_level}) {
my $hitratio = 0;
foreach (@{$self->{latches}}) {
$hitratio = $hitratio + $_->{hitratio};
}
$hitratio = $hitratio / scalar(@{$self->{latches}});
$self->add_nagios(
$self->check_thresholds($hitratio, "98:", "95:"),
sprintf "SGA latches hit ratio %.2f%%", $hitratio);
$self->add_perfdata(sprintf "sga_latches_hit_ratio=%.2f%%;%s;%s",
$hitratio, $self->{warningrange}, $self->{criticalrange});
}
} elsif ($params{mode} =~ /server::instance::sga::latch::listlatches/) {
foreach (sort { $a->{number} <=> $b->{number} } @{$self->{latches}}) {
printf "%03d %s\n", $_->{number}, $_->{name};
}
$self->add_nagios_ok("have fun");
} elsif ($params{mode} =~ /server::instance::sga::latch/) {
foreach (@{$self->{latches}}) {
$_->nagios(%params);
$self->merge_nagios($_);
}
} elsif ($params{mode} =~ /server::instance::sga::redologbuffer/) {
$self->{redo_log_buffer}->nagios(%params);
$self->merge_nagios($self->{redo_log_buffer});
} elsif ($params{mode} =~ /server::instance::sga::rollbacksegments/) {
$self->{rollback_segments}->nagios(%params);
$self->merge_nagios($self->{rollback_segments});
}
}
package DBD::Oracle::Server::Instance::PGA;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
internals => undef,
pgas => [],
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::pga/) {
$self->{internals} =
DBD::Oracle::Server::Instance::PGA::Internals->new(%params);
}
}
sub nagios {
my $self = shift;
my %params = @_;
if ($params{mode} =~ /server::instance::pga/) {
$self->{internals}->nagios(%params);
$self->merge_nagios($self->{internals});
}
}
package DBD::Oracle::Server::Instance::PGA::Internals;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance::PGA);
my $internals; # singleton, nur ein einziges mal instantiierbar
sub new {
my $class = shift;
my %params = @_;
unless ($internals) {
$internals = {
handle => $params{handle},
in_memory_sorts => undef,
in_disk_sorts => undef,
in_memory_sort_ratio => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless($internals, $class);
$internals->init(%params);
}
return($internals);
}
sub init {
my $self = shift;
my %params = @_;
$self->debug("enter init");
$self->init_nagios();
if ($params{mode} =~ /server::instance::pga::inmemorysortratio/) {
($self->{in_memory_sorts}, $self->{in_disk_sorts}) =
$self->{handle}->fetchrow_array(q{
SELECT mem.value, dsk.value
FROM v$sysstat mem, v$sysstat dsk
WHERE mem.name='sorts (memory)' AND dsk.name='sorts (disk)'
});
if (! defined $self->{in_memory_sorts}) {
$self->add_nagios_critical("unable to get pga ratio");
} else {
$self->valdiff(\%params, qw(in_memory_sorts in_disk_sorts));
$self->{in_memory_sort_ratio} =
($self->{delta_in_memory_sorts} + $self->{delta_in_disk_sorts}) == 0 ? 100 :
100 * $self->{delta_in_memory_sorts} /
($self->{delta_in_memory_sorts} + $self->{delta_in_disk_sorts});
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::pga::inmemorysortratio/) {
$self->add_nagios(
$self->check_thresholds($self->{in_memory_sort_ratio}, "99:", "90:"),
sprintf "PGA in-memory sort ratio %.2f%%",
$self->{in_memory_sort_ratio});
$self->add_perfdata(sprintf "pga_in_memory_sort_ratio=%.2f%%;%s;%s;0;100",
$self->{in_memory_sort_ratio},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::Event;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance);
{
my @events = ();
my $initerrors = undef;
sub add_event {
push(@events, shift);
}
sub return_events {
my %params = @_;
if ($params{mode} =~ /server::instance::event::waits/) {
return reverse
sort { $a->{waits_per_sec} <=> $b->{waits_per_sec} } @events;
} elsif ($params{mode} =~ /server::instance::event::waiting/) {
return reverse
sort { $a->{percent_waited} <=> $b->{percent_waited} } @events;
} else {
return reverse
sort { $a->{name} cmp $b->{name} } @events;
}
}
sub init_events {
my %params = @_;
my $num_events = 0;
my %longnames = ();
if (($params{mode} =~ /server::instance::event::wait/) || #waits, waiting
($params{mode} =~ /server::instance::event::listevents/)) {
my $sql;
my @idlewaits = ();
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("10.x")) {
@idlewaits = map { $_->[0] } $params{handle}->fetchall_array(q{
SELECT name FROM v$event_name WHERE wait_class = 'Idle'
});
} elsif (DBD::Oracle::Server::return_first_server()->version_is_minimum("9.x")) {
@idlewaits = (
'smon timer',
'pmon timer',
'rdbms ipc message',
'Null event',
'parallel query dequeue',
'pipe get',
'client message',
'SQL*Net message to client',
'SQL*Net message from client',
'SQL*Net more data from client',
'dispatcher timer',
'virtual circuit status',
'lock manager wait for remote message',
'PX Idle Wait',
'PX Deq: Execution Msg',
'PX Deq: Table Q Normal',
'wakeup time manager',
'slave wait',
'i/o slave wait',
'jobq slave wait',
'null event',
'gcs remote message',
'gcs for action',
'ges remote message',
'queue messages',
);
}
if ($params{mode} =~ /server::instance::event::listeventsbg/) {
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("10.x")) {
$sql = q{
SELECT e.event_id, e.event, 0, 0, 0, 0 FROM v$session_event e WHERE e.sid IN
(SELECT s.sid FROM v$session s WHERE s.type = 'BACKGROUND') GROUP BY e.event, e.event_id
};
} else {
$sql = q{
SELECT n.event#, e.event, 0, 0, 0, 0 FROM v$session_event e, v$event_name n
WHERE n.name = e.event AND e.sid IN
(SELECT s.sid FROM v$session s WHERE s.type = 'BACKGROUND') GROUP BY e.event, n.event#
};
}
} else {
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("10.x")) {
$sql = q{
SELECT e.event_id, e.name,
NVL(s.total_waits, 0), NVL(s.total_timeouts, 0), NVL(s.time_waited, 0),
NVL(s.time_waited_micro, 0), NVL(s.average_wait, 0)
FROM v$event_name e LEFT JOIN sys.v_$system_event s ON e.name = s.event
};
} else {
$sql = q{
SELECT e.event#, e.name,
NVL(s.total_waits, 0), NVL(s.total_timeouts, 0), NVL(s.time_waited, 0),
NVL(s.time_waited_micro, 0), NVL(s.average_wait, 0)
FROM v$event_name e LEFT JOIN sys.v_$system_event s ON e.name = s.event
};
}
}
my @eventresults = $params{handle}->fetchall_array($sql);
foreach (@eventresults) {
my ($event_no, $name, $total_waits, $total_timeouts,
$time_waited, $time_waited_micro, $average_wait) = @{$_};
$longnames{$name} = "";
}
abbreviate(\%longnames, 2);
foreach (@eventresults) {
my ($event_no, $name, $total_waits, $total_timeouts,
$time_waited, $time_waited_micro, $average_wait) = @{$_};
my $shortname = $longnames{$name}->{abbreviation};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if ($params{selectname} && (
(($params{selectname} !~ /^\d+$/) &&
(! grep /^$params{selectname}$/, map { $longnames{$_}->{abbreviation} }
keys %longnames) &&
(lc $params{selectname} ne lc $name)) ||
(($params{selectname} !~ /^\d+$/) &&
(grep /^$params{selectname}$/, map { $longnames{$_}->{abbreviation} }
keys %longnames) &&
(lc $params{selectname} ne lc $shortname)) ||
($params{selectname} =~ /^\d+$/ &&
($params{selectname} != $event_no))));
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{shortname} = $shortname;
$thisparams{event_id} = $event_no; # bei > 10.x unbedingt event_id aus db holen
$thisparams{total_waits} = $total_waits;
$thisparams{total_timeouts} = $total_timeouts;
$thisparams{time_waited} = $time_waited;
$thisparams{time_waited_micro} = $time_waited_micro;
$thisparams{average_wait} = $average_wait;
$thisparams{idle} = scalar(grep { lc $name =~ /$_/ } @idlewaits);
my $event = DBD::Oracle::Server::Instance::Event->new(
%thisparams);
add_event($event);
$num_events++;
}
if (! $num_events) {
$initerrors = 1;
return undef;
}
}
}
sub begindiff {
# liefere indices fuer das erste untersch. wort und innerhalb diesem das erste untersch. zeichen
my @names = @_;
my $len = 100;
my $first_diff_word = 0;
my $first_diff_pos = 0;
my $smallest_wordcnt = (sort { $a->{wordcnt} <=> $b->{wordcnt} } @names)[0]->{wordcnt};
foreach my $wordno (0..$smallest_wordcnt-1) {
my $wordequal = 1;
my $refword = @{$names[0]->{words}}[$wordno];
foreach (@names) {
if (@{$_->{words}}[$wordno] ne $refword) {
$wordequal = 0;
}
}
$first_diff_word = $wordno;
if (! $wordequal) {
last;
}
}
my $smallest_wordlen =
length(${(sort { length(${$a->{words}}[$first_diff_word]) <=> length(${$b->{words}}[$first_diff_word]) } @names)[0]->{words}}[$first_diff_word]);
foreach my $posno (0..$smallest_wordlen-1) {
my $posequal = 1;
my $refpos = substr(@{$names[0]->{words}}[$first_diff_word], $posno, 1);
foreach (@names) {
if (substr(@{$_->{words}}[$first_diff_word], $posno, 1) ne $refpos) {
$posequal = 0;
}
}
$first_diff_pos = $posno;
if (! $posequal) {
last;
}
}
return ($first_diff_word, $first_diff_pos);
}
sub abbreviate {
#
# => zeiger auf hash, dessen keys lange namen sind
# <= gleicher hash mit ausgefuellten eindeutigen values
#
my $names = shift;
my %done = ();
my $collisions = {};
foreach my $long (keys %{$names}) {
# erstmal das noetige werkzeug schmieden
# und kurzbezeichnungen aus jeweils zwei zeichen bilden
$names->{$long} = {};
$names->{$long}->{words} = [
map { lc }
map { my $x = $_; $x =~ s/[()\/\-]//g; $x }
map { /^\-$/ ? () : $_ }
split(/_|\s+/, $long) ];
$names->{$long}->{wordcnt} = scalar (@{$names->{$long}->{words}});
$names->{$long}->{shortwords} = [ map { substr $_, 0, 2 } @{$names->{$long}->{words}} ];
$names->{$long}->{abbreviation} = join("_", @{$names->{$long}->{shortwords}});
$names->{$long}->{unique} = 1;
}
individualize($names, -1, -1);
}
sub individualize {
my $names = shift;
my $delword = shift;
my $delpos = shift;
my %done = ();
my $collisions = {};
if ($delword >= 0 && $delpos >= 0) {
# delpos ist die position mit dem ersten unterschied. kann fuer den kuerzesten string
# schon nicht mehr existieren.
map {
if (length(${$names->{$_}->{words}}[$delword]) > 2) {
if (length(${$names->{$_}->{words}}[$delword]) == $delpos) {
${$names->{$_}->{shortwords}}[$delword] =
substr(${$names->{$_}->{words}}[$delword], 0, 2)
} else {
${$names->{$_}->{shortwords}}[$delword] =
substr(${$names->{$_}->{words}}[$delword], 0, 1).
substr(${$names->{$_}->{words}}[$delword], $delpos);
}
}
} keys %{$names};
}
map { $names->{$_}->{abbreviation} = join("_", @{$names->{$_}->{shortwords}}) } keys %{$names};
map { $done{$names->{$_}->{abbreviation}}++ } keys %{$names};
map { $names->{$_}->{unique} = $done{$names->{$_}->{abbreviation}} > 1 ? 0 : 1 } keys %{$names};
#
# hash mit abkuerzung als key und array(langnamen, ...) als value.
# diese sind nicht eindeutig und muessen noch geschickter abgekuerzt werden
#
foreach my $collision (map { $names->{$_}->{unique} ? () : $_ } keys %{$names}) {
if (! exists $collisions->{$names->{$collision}->{abbreviation}}) {
$collisions->{$names->{$collision}->{abbreviation}} = [];
}
push(@{$collisions->{$names->{$collision}->{abbreviation}}}, $collision);
}
#
# jeweils gruppen mit gemeinsamer, mehrdeutiger abkuerzung werden nochmals gerechnet
#
foreach my $collision (keys %{$collisions}) {
my $newnames = {};
# hilfestellung, wo es unterschiede gibt
my($wordnum, $posnum) = begindiff(map { $names->{$_} } @{$collisions->{$collision}});
map { $newnames->{$_} =
$names->{$_} } grep { $names->{$_}->{abbreviation} eq $collision } keys %{$names};
individualize($newnames, $wordnum, $posnum);
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
name => $params{name},
shortname => $params{shortname},
event_id => $params{event_id}, # > 10.x
total_waits => $params{total_waits},
total_timeouts => $params{total_timeouts},
time_waited => $params{time_waited}, # divide by 100
time_waited_micro => $params{time_waited_micro}, # divide by 1000000
average_wait => $params{average_wait},
idle => $params{idle} || 0,
waits_per_sec => undef,
percent_waited => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
#$self->{name} =~ s/^\s+//;
#$self->{name} =~ s/\s+$//;
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::event::wait/) {
if (! defined $self->{total_waits}) {
$self->add_nagios_critical("unable to get event info");
} else {
$params{differenciator} = lc $self->{name};
$self->valdiff(\%params, qw(total_waits total_timeouts time_waited
time_waited_micro average_wait));
$self->{waits_per_sec} =
$self->{delta_total_waits} / $self->{delta_timestamp};
$self->{percent_waited} =
100 * ($self->{delta_time_waited_micro} / 1000000 ) / $self->{delta_timestamp};
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::event::waits/) {
$self->add_nagios(
$self->check_thresholds($self->{waits_per_sec}, "10", "100"),
sprintf "%s : %.6f waits/sec", $self->{name}, $self->{waits_per_sec});
$self->add_perfdata(sprintf "'%s_waits_per_sec'=%.6f;%s;%s",
$self->{name},
$self->{waits_per_sec},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~ /server::instance::event::waiting/) {
$self->add_nagios(
$self->check_thresholds($self->{percent_waited}, "0.1", "0.5"),
sprintf "%s waits %.6f%% of the time", $self->{name}, $self->{percent_waited});
$self->add_perfdata(sprintf "'%s_percent_waited'=%.6f%%;%s;%s",
$self->{name},
$self->{percent_waited},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Instance::Enqueue;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance);
{
my @enqueues = ();
my $initerrors = undef;
sub add_enqueue {
push(@enqueues, shift);
}
sub return_enqueues {
return reverse
sort { $a->{name} cmp $b->{name} } @enqueues;
}
sub init_enqueues {
my %params = @_;
my $num_enqueues = 0;
if (($params{mode} =~ /server::instance::enqueue::contention/) ||
($params{mode} =~ /server::instance::enqueue::waiting/) ||
($params{mode} =~ /server::instance::enqueue::listenqueues/)) {
# ora11 PE FP TA DL SR TQ KT PW XR SS SJ SQ IT IA UL WP RR KM
# PD CF SW CT US TD TK JS FS CN DT TS TT JD SE MW AF TL
# PV AS TM TX FB JQ MD TO TH PR RO MR DP WF TB SH RS CU
# AE CI PG IS RT HW DR FU
# ora10 PE FP TA DL SR TQ KT PW XR SS SQ PF IT IA UL WP KM PD
# CF SW CT US TD AG JS DT TS TT CN JD SE MW AF TL PV AS
# TM FB TX JQ MD TO PR RO MR SK DP WF TB SH RS CU AW CI
# PG IS RT HW DR FU
# ora9 CF CI CU DL DP DR DT DX FB HW IA IS IT JD MD MR PE PF
# RO RT SQ SR SS SW TA TD TM TO TS TT TX UL US XR
my @enqueueresults = $params{handle}->fetchall_array(q{
SELECT inst_id, eq_type, total_req#, total_wait#,
succ_req#, failed_req#, cum_wait_time
FROM v$enqueue_stat
});
foreach (@enqueueresults) {
my ($inst_id, $name, $total_requests, $total_waits,
$succeeded_requests, $failed_requests, $cumul_wait_time) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{total_requests} = $total_requests;
$thisparams{total_waits} = $total_waits;
$thisparams{succeeded_requests} = $succeeded_requests;
$thisparams{failed_requests} = $failed_requests;
$thisparams{cumul_wait_time} = $cumul_wait_time;
my $enqueue = DBD::Oracle::Server::Instance::Enqueue->new(
%thisparams);
add_enqueue($enqueue);
$num_enqueues++;
}
if (! $num_enqueues) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
name => $params{name},
total_requests => $params{total_requests},
total_waits => $params{total_waits},
succeeded_requests => $params{succeeded_requests},
failed_requests => $params{failed_requests},
cumul_wait_time => $params{cumul_wait_time}, # ! milliseconds
contention => undef,
percent_waited => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
$self->{name} =~ s/^\s+//;
$self->{name} =~ s/\s+$//;
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if (($params{mode} =~ /server::instance::enqueue::contention/) ||
($params{mode} =~ /server::instance::enqueue::waiting/)) {
$params{differenciator} = lc $self->{name};
$self->valdiff(\%params, qw(total_requests total_waits succeeded_requests
failed_requests cumul_wait_time));
# enqueue contention
$self->{contention} = $self->{delta_total_requests} ?
100 * $self->{delta_total_waits} / $self->{delta_total_requests} : 0;
# enqueue waiting
$self->{percent_waited} = ($self->{delta_cumul_wait_time} /
($self->{delta_timestamp} * 1000)) * 100;
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::enqueue::contention/) {
$self->add_nagios(
$self->check_thresholds($self->{contention}, "1", "10"),
sprintf "enqueue %s %s: %.2f%% of the requests must wait ",
$self->{name}, $self->longname(), $self->{contention});
$self->add_perfdata(sprintf "'%s_contention'=%.2f%%;%s;%s '%s_requests'=%d '%s_waits'=%d",
$self->{name},
$self->{contention},
$self->{warningrange}, $self->{criticalrange},
$self->{name},
$self->{delta_total_requests},
$self->{name},
$self->{delta_total_waits});
} elsif ($params{mode} =~ /server::instance::enqueue::waiting/) {
$self->add_nagios(
# 1 ms wait in 5 minutes
$self->check_thresholds($self->{percent_waited}, "0.0003333", "0.003333"),
sprintf "enqueue %s %s: waiting %.4f%% of the time",
$self->{name}, $self->longname(), $self->{percent_waited});
$self->add_perfdata(sprintf "'%s_ms_waited'=%d '%s_pct_waited'=%.4f%%;%s;%s",
$self->{name},
$self->{delta_cumul_wait_time},
$self->{name},
$self->{percent_waited},
$self->{warningrange}, $self->{criticalrange});
}
}
}
sub longname {
my $self = shift;
my $abbrev = <<EOEO;
BL, Buffer Cache Management
BR, Backup/Restore
CF, Controlfile Transaction
CI, Cross-instance Call Invocation
CU, Bind Enqueue
DF, Datafile
DL, Direct Loader Index Creation
DM, Database Mount
DR, Distributed Recovery Process
DX, Distributed Transaction
FP, File Object
FS, File Set
HW, High-Water Lock
IN, Instance Number
IR, Instance Recovery
IS, Instance State
IV, Library Cache Invalidation
JI, Enqueue used during AJV snapshot refresh
JQ, Job Queue
KK, Redo Log "Kick"
KO, Multiple Object Checkpoint
L[A-P], Library Cache Lock
LS, Log Start or Switch
MM, Mount Definition
MR, Media Recovery
N[A-Z], Library Cache Pin
PE, ALTER SYSTEM SET PARAMETER = VALUE
PF, Password File
PI, Parallel Slaves
PR, Process Startup
PS, Parallel Slave Synchronization
Q[A-Z], Row Cache
RO, Object Reuse
RT, Redo Thread
RW, Row Wait
SC, System Commit Number
SM, SMON
SN, Sequence Number
SQ, Sequence Number Enqueue
SR, Synchronized Replication
SS, Sort Segment
ST, Space Management Transaction
SV, Sequence Number Value
TA, Transaction Recovery
TC, Thread Checkpoint
TE, Extend Table
TM, DML Enqueue
TO, Temporary Table Object Enqueue
TS, Temporary Segment (also TableSpace)
TT, Temporary Table
TX, Transaction
UL, User-defined Locks
UN, User Name
US, Undo Segment, Serialization
WL, Being Written Redo Log
XA, Instance Attribute Lock
XI, Instance Registration Lock
EOEO
my $descriptions = {};
foreach (split(/\n/, $abbrev)) {
my ($short, $descr) = split /,/;
if ($self->{name} =~ /^$short$/) {
$descr =~ s/^\s+//g;
return $descr;
}
}
return "";
}
package DBD::Oracle::Server::Instance::Sysstat;
use strict;
our @ISA = qw(DBD::Oracle::Server::Instance);
{
my @sysstats = ();
my $initerrors = undef;
sub add_sysstat {
push(@sysstats, shift);
}
sub return_sysstats {
return reverse
sort { $a->{name} cmp $b->{name} } @sysstats;
}
sub init_sysstats {
my %params = @_;
my $num_sysstats = 0;
my %longnames = ();
if (($params{mode} =~ /server::instance::sysstat::rate/) ||
($params{mode} =~ /server::instance::sysstat::listsysstats/)) {
my @sysstatresults = $params{handle}->fetchall_array(q{
SELECT statistic#, name, class, value FROM v$sysstat
});
foreach (@sysstatresults) {
my ($number, $name, $class, $value) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if ($params{selectname} && (
($params{selectname} !~ /^\d+$/ && (lc $params{selectname} ne lc $name)) ||
($params{selectname} =~ /^\d+$/ && ($params{selectname} != $number))));
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{number} = $number;
$thisparams{class} = $class;
$thisparams{value} = $value;
my $sysstat = DBD::Oracle::Server::Instance::Sysstat->new(
%thisparams);
add_sysstat($sysstat);
$num_sysstats++;
}
if (! $num_sysstats) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
name => $params{name},
number => $params{number},
class => $params{class},
value => $params{value},
rate => $params{rate},
count => $params{count},
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
#$self->{name} =~ s/^\s+//;
#$self->{name} =~ s/\s+$//;
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sysstat::rate/) {
$params{differenciator} = lc $self->{name};
$self->valdiff(\%params, qw(value));
$self->{rate} = $self->{delta_value} / $self->{delta_timestamp};
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::instance::sysstat::rate/) {
$self->add_nagios(
$self->check_thresholds($self->{rate}, "10", "100"),
sprintf "%.6f %s/sec", $self->{rate}, $self->{name});
$self->add_perfdata(sprintf "'%s_per_sec'=%.6f;%s;%s",
$self->{name},
$self->{rate},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "'%s'=%u",
$self->{name},
$self->{delta_value});
}
}
}
package DBD::Oracle::Server::Instance;
use strict;
our @ISA = qw(DBD::Oracle::Server);
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
sga => undef,
processes => {},
events => [],
enqueues => [],
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::instance::sga/) {
$self->{sga} = DBD::Oracle::Server::Instance::SGA->new(%params);
} elsif ($params{mode} =~ /server::instance::pga/) {
$self->{pga} = DBD::Oracle::Server::Instance::PGA->new(%params);
} elsif ($params{mode} =~ /server::instance::sysstat/) {
DBD::Oracle::Server::Instance::Sysstat::init_sysstats(%params);
if (my @sysstats =
DBD::Oracle::Server::Instance::Sysstat::return_sysstats(%params)) {
$self->{sysstats} = \@sysstats;
} else {
$self->add_nagios_critical("unable to aquire sysstats info");
}
} elsif ($params{mode} =~ /server::instance::event/) {
DBD::Oracle::Server::Instance::Event::init_events(%params);
if (my @events =
DBD::Oracle::Server::Instance::Event::return_events(%params)) {
$self->{events} = \@events;
} else {
$self->add_nagios_critical("unable to aquire event info");
}
} elsif ($params{mode} =~ /server::instance::enqueue/) {
DBD::Oracle::Server::Instance::Enqueue::init_enqueues(%params);
if (my @enqueues =
DBD::Oracle::Server::Instance::Enqueue::return_enqueues(%params)) {
$self->{enqueues} = \@enqueues;
} else {
$self->add_nagios_critical("unable to aquire enqueue info");
}
} elsif ($params{mode} =~ /server::instance::connectedusers/) {
$self->{connected_users} = $self->{handle}->fetchrow_array(q{
SELECT COUNT(*) FROM v$session WHERE type = 'USER'
});
} elsif ($params{mode} =~ /server::instance::rman::backup::problems/) {
$self->{rman_backup_problems} = $self->{handle}->fetchrow_array(q{
SELECT COUNT(*) FROM v$rman_status WHERE status != 'COMPLETED'
AND
status != 'RUNNING'
AND start_time > sysdate-3
});
} elsif ($params{mode} =~ /server::instance::sessionusage/) {
$self->{session_usage} = $self->{handle}->fetchrow_array(q{
SELECT current_utilization/limit_value*100
FROM v$resource_limit WHERE resource_name LIKE '%sessions%'
});
} elsif ($params{mode} =~ /server::instance::processusage/) {
$self->{process_usage} = $self->{handle}->fetchrow_array(q{
SELECT current_utilization/limit_value*100
FROM v$resource_limit WHERE resource_name LIKE '%processes%'
});
}
}
sub nagios {
my $self = shift;
my %params = @_;
if ($params{mode} =~ /server::instance::sga/) {
$self->{sga}->nagios(%params);
$self->merge_nagios($self->{sga});
} elsif ($params{mode} =~ /server::instance::pga/) {
$self->{pga}->nagios(%params);
$self->merge_nagios($self->{pga});
} elsif ($params{mode} =~ /server::instance::event::listevents/) {
foreach (sort { $a->{name} cmp $b->{name} } @{$self->{events}}) {
printf "%10u%s %s %s\n", $_->{event_id}, $_->{idle} ? '*' : '', $_->{shortname}, $_->{name};
}
$self->add_nagios_ok("have fun");
} elsif ($params{mode} =~ /server::instance::event/) {
foreach (@{$self->{events}}) {
$_->nagios(%params);
$self->merge_nagios($_);
}
if (! $self->{nagios_level} && ! $params{selectname}) {
$self->add_nagios_ok("no wait problems");
}
} elsif ($params{mode} =~ /server::instance::sysstat::listsysstat/) {
foreach (sort { $a->{name} cmp $b->{name} } @{$self->{sysstats}}) {
printf "%10d %s\n", $_->{number}, $_->{name};
}
$self->add_nagios_ok("have fun");
} elsif ($params{mode} =~ /server::instance::sysstat/) {
foreach (@{$self->{sysstats}}) {
$_->nagios(%params);
$self->merge_nagios($_);
}
if (! $self->{nagios_level} && ! $params{selectname}) {
$self->add_nagios_ok("no wait problems");
}
} elsif ($params{mode} =~ /server::instance::enqueue::listenqueues/) {
foreach (sort { $a->{name} cmp $b->{name} } @{$self->{enqueues}}) {
printf "%s\n", $_->{name};
}
$self->add_nagios_ok("have fun");
} elsif ($params{mode} =~ /server::instance::enqueue/) {
foreach (@{$self->{enqueues}}) {
$_->nagios(%params);
$self->merge_nagios($_);
}
if (! $self->{nagios_level} && ! $params{selectname}) {
$self->add_nagios_ok("no enqueue problem");
}
} elsif ($params{mode} =~ /server::instance::connectedusers/) {
$self->add_nagios(
$self->check_thresholds($self->{connected_users}, 50, 100),
sprintf "%d connected users",
$self->{connected_users});
$self->add_perfdata(sprintf "connected_users=%d;%d;%d",
$self->{connected_users},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~ /server::instance::rman::backup::problems/) {
$self->add_nagios(
$self->check_thresholds($self->{rman_backup_problems}, 1, 2),
sprintf "rman had %d problems during the last 3 days",
$self->{rman_backup_problems});
$self->add_perfdata(sprintf "rman_backup_problems=%d;%d;%d",
$self->{rman_backup_problems},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~ /server::instance::sessionusage/) {
$self->add_nagios(
$self->check_thresholds($self->{session_usage}, 80, 100),
sprintf "%.2f%% of session resources used",
$self->{session_usage});
$self->add_perfdata(sprintf "session_usage=%.2f%%;%d;%d",
$self->{session_usage},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~ /server::instance::processusage/) {
$self->add_nagios(
$self->check_thresholds($self->{process_usage}, 80, 100),
sprintf "%.2f%% of process resources used",
$self->{process_usage});
$self->add_perfdata(sprintf "process_usage=%.2f%%;%d;%d",
$self->{process_usage},
$self->{warningrange}, $self->{criticalrange});
}
}
package DBD::Oracle::Server::Database::FlashRecoveryArea;
use strict;
our @ISA = qw(DBD::Oracle::Server::Database);
{
my @flash_recovery_areas = ();
my $initerrors = undef;
sub add_flash_recovery_area {
push(@flash_recovery_areas, shift);
}
sub return_flash_recovery_areas {
return reverse
sort { $a->{name} cmp $b->{name} } @flash_recovery_areas;
}
sub init_flash_recovery_areas {
# as far as i understand it, there is only one flra.
# we use an array here anyway, because the tablespace code can be reused
my %params = @_;
my $num_flash_recovery_areas = 0;
if (($params{mode} =~ /server::database::flash_recovery_area::usage/) ||
($params{mode} =~ /server::database::flash_recovery_area::free/) ||
($params{mode} =~ /server::database::flash_recovery_area::listflash_recovery_areas/)) {
my @flash_recovery_arearesult = ();
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("10.x")) {
@flash_recovery_arearesult = $params{handle}->fetchall_array(q{
SELECT
name, space_limit, space_used, space_reclaimable, number_of_files
FROM
v$recovery_file_dest
});
} else {
# no flash before 10.x
}
foreach (@flash_recovery_arearesult) {
my ($name, $space_limit, $space_used, $space_reclaimable,
$number_of_files) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{space_limit} = $space_limit;
$thisparams{space_used} = $space_used;
$thisparams{space_reclaimable} = $space_reclaimable;
$thisparams{number_of_files} = lc $number_of_files;
my $flash_recovery_area = DBD::Oracle::Server::Database::FlashRecoveryArea->new(
%thisparams);
add_flash_recovery_area($flash_recovery_area);
$num_flash_recovery_areas++;
}
if (! $num_flash_recovery_areas) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
verbose => $params{verbose},
handle => $params{handle},
name => $params{name},
space_limit => $params{space_limit},
space_used => $params{space_used},
space_reclaimable => $params{space_reclaimable},
number_of_files => $params{number_of_files},
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::database::flash_recovery_area::(usage|free)/) {
$self->{percent_used} =
($self->{space_used} - $self->{space_reclaimable}) / $self->{space_limit} * 100;
$self->{percent_free} = 100 - $self->{percent_used};
$self->{bytes_used} = $self->{space_used} - $self->{space_reclaimable};
$self->{bytes_free} = $self->{space_limit} - $self->{space_used};
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::database::flash_recovery_area::usage/) {
$self->check_thresholds($self->{percent_used}, "90", "98");
$self->add_nagios(
$self->check_thresholds($self->{percent_used}, "90", "98"),
sprintf("flra (%s) usage is %.2f%%",
$self->{name}, $self->{percent_used}));
$self->add_perfdata(sprintf "\'flra_usage_pct\'=%.2f%%;%d;%d",
$self->{percent_used},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "\'flra_usage\'=%dMB;%d;%d;%d;%d",
($self->{space_used} - $self->{space_reclaimable}) / 1048576,
$self->{warningrange} * $self->{space_limit} / 100 / 1048576,
$self->{criticalrange} * $self->{space_limit} / 100 / 1048576,
0, $self->{space_limit} / 1048576);
} elsif ($params{mode} =~ /server::database::flash_recovery_area::free/) {
if (($self->{warningrange} && $self->{warningrange} !~ /^\d+:/) ||
($self->{criticalrange} && $self->{criticalrange} !~ /^\d+:/)) {
$self->add_nagios_unknown("you want an alert if free space is _above_ a threshold????");
return;
}
if (! $params{units}) {
$params{units} = "%";
}
$self->{warning_bytes} = 0;
$self->{critical_bytes} = 0;
if ($params{units} eq "%") {
$self->add_nagios(
$self->check_thresholds($self->{percent_free}, "5:", "2:"),
sprintf("flra %s has %.2f%% free space left",
$self->{name}, $self->{percent_free})
);
$self->{warningrange} =~ s/://g;
$self->{criticalrange} =~ s/://g;
$self->add_perfdata(sprintf "\'flra_free_pct\'=%.2f%%;%d:;%d:",
$self->{percent_free},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "\'flra_free\'=%dMB;%.2f:;%.2f:;0;%.2f",
$self->{bytes_free} / 1048576,
$self->{warningrange} * $self->{space_limit} / 100 / 1048576,
$self->{criticalrange} * $self->{space_limit} / 100 / 1048576,
$self->{space_limit} / 1048576);
} else {
my $factor = 1024 * 1024; # default MB
if ($params{units} eq "GB") {
$factor = 1024 * 1024 * 1024;
} elsif ($params{units} eq "MB") {
$factor = 1024 * 1024;
} elsif ($params{units} eq "KB") {
$factor = 1024;
}
$self->{warningrange} ||= "5:";
$self->{criticalrange} ||= "2:";
my $saved_warningrange = $self->{warningrange};
my $saved_criticalrange = $self->{criticalrange};
# : entfernen weil gerechnet werden muss
$self->{warningrange} =~ s/://g;
$self->{criticalrange} =~ s/://g;
$self->{warningrange} = $self->{warningrange} ?
$self->{warningrange} * $factor : 5 * $factor;
$self->{criticalrange} = $self->{criticalrange} ?
$self->{criticalrange} * $factor : 2 * $factor;
$self->{percent_warning} = 100 * $self->{warningrange} / $self->{space_limit};
$self->{percent_critical} = 100 * $self->{criticalrange} / $self->{space_limit};
$self->{warningrange} .= ':';
$self->{criticalrange} .= ':';
$self->add_nagios(
$self->check_thresholds($self->{bytes_free}, "5242880:", "1048576:"),
sprintf("flra (%s) has %.2f%s free space left", $self->{name},
$self->{bytes_free} / $factor, $params{units})
);
$self->{warningrange} = $saved_warningrange;
$self->{criticalrange} = $saved_criticalrange;
$self->{warningrange} =~ s/://g;
$self->{criticalrange} =~ s/://g;
$self->add_perfdata(sprintf "\'flra_free_pct\'=%.2f%%;%.2f:;%.2f:",
$self->{percent_free}, $self->{percent_warning},
$self->{percent_critical});
$self->add_perfdata(sprintf "\'flra_free\'=%.2f%s;%.2f:;%.2f:;0;%.2f",
$self->{bytes_free} / $factor, $params{units},
$self->{warningrange},
$self->{criticalrange},
$self->{space_limit} / $factor);
}
}
}
}
package DBD::Oracle::Server::Database::Tablespace::Datafile;
use strict;
use File::Basename;
our @ISA = qw(DBD::Oracle::Server::Database::Tablespace);
{
my @datafiles = ();
my $initerrors = undef;
sub add_datafile {
push(@datafiles, shift);
}
sub return_datafiles {
return reverse
sort { $a->{name} cmp $b->{name} } @datafiles;
}
sub clear_datafiles {
@datafiles = ();
}
sub init_datafiles {
my %params = @_;
my $num_datafiles = 0;
if (($params{mode} =~ /server::database::tablespace::datafile::iotraffic/) ||
($params{mode} =~ /server::database::tablespace::datafile::listdatafiles/)) {
# negative values can occur
# column datafile format a30
my @datafileresults = $params{handle}->fetchall_array(q{
SELECT
name datafile, phyrds reads, phywrts writes
FROM
v$datafile a, v$filestat b
WHERE
a.file# = b.file#
UNION
SELECT
name datafile, phyrds reads, phywrts writes
FROM
v$tempfile a, v$tempstat b
WHERE
a.file# = b.file#
});
if (DBD::Oracle::Server::return_first_server()->windows_server()) {
fileparse_set_fstype("MSWin32");
}
foreach (@datafileresults) {
my ($name, $phyrds, $phywrts) = @{$_};
if ($params{regexp}) {
next if $params{selectname} &&
(($name !~ /$params{selectname}/) &&
(basename($name) !~ /$params{selectname}/));
} else {
next if $params{selectname} &&
((lc $params{selectname} ne lc $name) &&
(lc $params{selectname} ne lc basename($name)));
}
my %thisparams = %params;
$thisparams{path} = $name;
$thisparams{name} = basename($name);
$thisparams{phyrds} = $phyrds;
$thisparams{phywrts} = $phywrts;
my $datafile =
DBD::Oracle::Server::Database::Tablespace::Datafile->new(
%thisparams);
add_datafile($datafile);
$num_datafiles++;
}
} elsif ($params{mode} =~ /server::database::tablespace::iobalance/) {
my $sql = q{
-- SELECT REGEXP_REPLACE(file_name,'^.*.\/.*.\/', '') file_name,
SELECT file_name,
SUM(phyrds), SUM(phywrts)
FROM dba_temp_files, v$filestat
WHERE tablespace_name = UPPER(?)
AND file_id=file# GROUP BY tablespace_name, file_name
UNION
-- SELECT REGEXP_REPLACE(file_name,'^.*.\/.*.\/', '') file_name,
SELECT file_name,
SUM(phyrds), SUM(phywrts)
FROM dba_data_files, v$filestat
WHERE tablespace_name = UPPER(?)
AND file_id=file# GROUP BY tablespace_name, file_name };
if (! DBD::Oracle::Server::return_first_server()->version_is_minimum("9.2.0.3")) {
# bug 2436600
$sql = q{
-- SELECT REGEXP_REPLACE(file_name,'^.*.\/.*.\/', '') file_name,
SELECT file_name,
SUM(phyrds), SUM(phywrts)
FROM dba_data_files, v$filestat
WHERE tablespace_name = UPPER(?)
AND file_id=file# GROUP BY tablespace_name, file_name };
}
my @datafileresults = $params{handle}->fetchall_array($sql, $params{tablespace}, $params{tablespace});
if (DBD::Oracle::Server::return_first_server()->windows_server()) {
fileparse_set_fstype("MSWin32");
}
foreach (@datafileresults) {
my ($name, $phyrds, $phywrts) = @{$_};
my %thisparams = %params;
$thisparams{path} = $name;
$thisparams{name} = basename($name);
$thisparams{phyrds} = $phyrds;
$thisparams{phywrts} = $phywrts;
my $datafile =
DBD::Oracle::Server::Database::Tablespace::Datafile->new(
%thisparams);
add_datafile($datafile);
$num_datafiles++;
}
if (! $num_datafiles) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
path => $params{path},
name => $params{name},
phyrds => $params{phyrds},
phywrts => $params{phywrts},
io_total => undef,
io_total_per_sec => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if ($params{mode} =~ /server::database::tablespace::iobalance/) {
if (! defined $self->{phyrds}) {
$self->add_nagios_critical(sprintf "unable to read datafile io %s", $@);
} else {
$params{differenciator} = $self->{path};
$self->valdiff(\%params, qw(phyrds phywrts));
$self->{io_total} = $self->{delta_phyrds} + $self->{delta_phywrts};
}
} elsif ($params{mode} =~ /server::database::tablespace::datafile::iotraffic/) {
if (! defined $self->{phyrds}) {
$self->add_nagios_critical(sprintf "unable to read datafile io %s", $@);
} else {
$params{differenciator} = $self->{path};
$self->valdiff(\%params, qw(phyrds phywrts));
$self->{io_total_per_sec} = ($self->{delta_phyrds} + $self->{delta_phywrts}) /
$self->{delta_timestamp};
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::database::tablespace::datafile::iotraffic/) {
$self->add_nagios(
$self->check_thresholds($self->{io_total_per_sec}, "1000", "5000"),
sprintf ("%s: %.2f IO Operations per Second",
$self->{name}, $self->{io_total_per_sec}));
$self->add_perfdata(sprintf "'dbf_%s_io_total_per_sec'=%.2f;%d;%d",
$self->{name}, $self->{io_total_per_sec},
$self->{warningrange}, $self->{criticalrange});
}
}
}
package DBD::Oracle::Server::Database::Tablespace::Segment;
use strict;
our @ISA = qw(DBD::Oracle::Server::Database::Tablespace);
{
my @segments = ();
my $initerrors = undef;
sub add_segment {
push(@segments, shift);
}
sub return_segments {
return reverse
sort { $a->{name} cmp $b->{name} } @segments;
}
sub clear_segments {
@segments = ();
}
sub init_segments {
my %params = @_;
my $num_segments = 0;
if (($params{mode} =~
/server::database::tablespace::segment::top10logicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10physicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10bufferbusywaits/) ||
($params{mode} =~
/server::database::tablespace::segment::top10rowlockwaits/)) {
my %thisparams = %params;
$thisparams{name} = "dummy_segment";
my $segment = DBD::Oracle::Server::Database::Tablespace::Segment->new(
%thisparams);
add_segment($segment);
$num_segments++;
} elsif ($params{mode} =~
/server::database::tablespace::segment::extendspace/) {
my @tablespaceresult = $params{handle}->fetchall_array(q{
SELECT /*+ RULE */
-- tablespace, segment, extent
-- aber dadurch, dass nur das letzte extent selektiert wird
-- werden praktisch nur tablespace und segmente ausgegeben
b.tablespace_name "Tablespace",
b.segment_type "Type",
SUBSTR(ext.owner||'.'||ext.segment_name,1,50) "Object Name",
DECODE(freespace.extent_management,
'DICTIONARY', DECODE(b.extents,
1, b.next_extent, ext.bytes * (1 + b.pct_increase / 100)),
'LOCAL', DECODE(freespace.allocation_type,
'UNIFORM', freespace.initial_extent,
'SYSTEM', ext.bytes)
) "Required Extent",
freespace.largest "MaxAvail"
FROM
-- dba_segments b,
-- dba_extents ext,
(
SELECT
owner, segment_type, segment_name, extents, pct_increase,
next_extent, tablespace_name
FROM
dba_segments
WHERE
tablespace_name = ?
) b,
(
SELECT
owner, segment_type, segment_name, extent_id, bytes,
tablespace_name
FROM
dba_extents
WHERE
tablespace_name = ?
) ext,
(
-- dictionary/local, uniform/system, initial, next
-- und der groesste freie extent pro tablespace
SELECT
b.tablespace_name,
b.extent_management,
b.allocation_type,
b.initial_extent,
b.next_extent,
max(a.bytes) largest
FROM
dba_free_space a,
dba_tablespaces b
WHERE
b.tablespace_name = a.tablespace_name
AND
b.status = 'ONLINE'
GROUP BY
b.tablespace_name,
b.extent_management,
b.allocation_type,
b.initial_extent,
b.next_extent
) freespace
WHERE
b.owner = ext.owner
AND
b.segment_type = ext.segment_type
AND
b.segment_name = ext.segment_name
AND
b.tablespace_name = ext.tablespace_name
AND
-- so landet nur das jeweils letzte extent im ergebnis
(b.extents - 1) = ext.extent_id
AND
b.tablespace_name = freespace.tablespace_name
AND
freespace.tablespace_name = ?
ORDER BY
b.tablespace_name,
b.segment_type,
b.segment_name
}, $params{tablespace}, $params{tablespace}, $params{tablespace});
foreach (@tablespaceresult) {
my ($tablespace_name, $segment_type, $object_name,
$required_for_next_extent, $largest_free) = @{$_};
my %thisparams = %params;
$thisparams{name} = $object_name;
$thisparams{segment_type} = $segment_type;
$thisparams{required_for_next_extent} = $required_for_next_extent;
$thisparams{largest_free} = $largest_free;
my $segment = DBD::Oracle::Server::Database::Tablespace::Segment->new(
%thisparams);
add_segment($segment);
$num_segments++;
}
}
if (! $num_segments) {
$initerrors = 1;
return undef;
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
handle => $params{handle},
name => $params{name},
segment_type => $params{segment_type},
required_for_next_extent => $params{required_for_next_extent},
largest_free => $params{largest_free},
num_users_among_top10logicalreads => undef,
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
if (($params{mode} =~
/server::database::tablespace::segment::top10logicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10physicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10bufferbusywaits/) ||
($params{mode} =~
/server::database::tablespace::segment::top10rowlockwaits/)) {
my $sql;
my $mode = (split(/::/, $params{mode}))[4];
## -- SELECT owner, object_name, object_type, value, statistic_name
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("10.x")) {
# this uses oracle analytic function rank() over (),
# needs oracle >= 10.x
# for more information see:
# http://kenntwas.de/2010/linux/monitoring/check_oracle_health-seg-top10-abfragen-verbessern/
$sql = q{
SELECT DO.owner,
DO.object_name,
DO.object_type,
SS.VALUE,
SS.statistic_name
FROM dba_objects DO,
(SELECT *
FROM (SELECT S.OBJ#,
s.VALUE,
s.statistic_name,
RANK () OVER (ORDER BY s.VALUE DESC) rk
FROM v$segstat s
WHERE s.statistic_name = ?
/* reduce data to significant values */
AND VALUE <> 0)
WHERE rk <= 10 /* top 10 */
) SS
WHERE DO.object_id = SS.obj#
};
} else {
my $sql = q{
SELECT COUNT(*)
FROM (select DO.owner, DO.object_name, DO.object_type, SS.value,
SS.statistic_name, row_number () over (order by value desc) RN
FROM dba_objects DO, v$segstat SS
WHERE DO.object_id = SS.obj#
AND statistic_name = ?)
WHERE RN <= 10
AND owner not in
('CTXSYS', 'DBSNMP', 'MDDATA', 'MDSYS', 'DMSYS', 'OLAPSYS',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA',
'SYS', 'SYSMAN', 'SYSTEM')
};
# this is a very heavy operation and de-selecting system users
# makes it even slower, so we fetch all data and do the filtering
# later in perl.
$sql = q{
select DO.owner, DO.object_name, DO.object_type, SS.value,
SS.statistic_name
FROM dba_objects DO, v$segstat SS
WHERE DO.object_id = SS.obj#
AND statistic_name = ?
};
}
my $statname = {
top10logicalreads => "logical reads",
top10physicalreads => "physical reads",
top10bufferbusywaits => "buffer busy waits",
top10rowlockwaits => "row lock waits",
}->{$mode};
#$self->{"num_users_among_".$mode} =
# $self->{handle}->fetchrow_array($sql, $statname);
# faster version
# fetch everything
my @sortedsessions = reverse sort { $a->[3] <=> $b->[3] } $self->{handle}->fetchall_array($sql, $statname);
if (scalar(@sortedsessions) > 10) {
@sortedsessions = (@sortedsessions)[0..9];
}
my @usersessions = map { $_->[0] !~ /^(CTXSYS|DBSNMP|MDDATA|MDSYS|DMSYS|OLAPSYS|ORDPLUGINS|ORDSYS|OUTLN|SI_INFORMTN_SCHEMA|SYS|SYSMAN|SYSTEM)$/ ? $_ : () } @sortedsessions;
$self->{"num_users_among_".$mode} = scalar(@usersessions);
if (scalar(@sortedsessions) == 0) {
#if (! defined $self->{"num_users_among_".$mode}) {
$self->add_nagios_critical(sprintf "unable to read top10: %s", $@);
}
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if (($params{mode} =~
/server::database::tablespace::segment::top10logicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10physicalreads/) ||
($params{mode} =~
/server::database::tablespace::segment::top10bufferbusywaits/) ||
($params{mode} =~
/server::database::tablespace::segment::top10rowlockwaits/)) {
my $mode = (split(/::/, $params{mode}))[4];
my $statname = {
top10logicalreads => "logical reads",
top10physicalreads => "physical reads",
top10bufferbusywaits => "buffer busy waits",
top10rowlockwaits => "row lock waits",
}->{$mode};
$self->add_nagios(
$self->check_thresholds(
$self->{"num_users_among_".$mode}, "1", "9"),
sprintf "%d user processes among the top10 %s",
$self->{"num_users_among_".$mode}, $statname);
$statname =~ s/\s/_/g;
$self->add_perfdata(sprintf "users_among_top10_%s=%d;%d;%d",
$statname, $self->{"num_users_among_".$mode},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~
/server::database::tablespace::segment::extendspace/) {
if ($self->{required_for_next_extent} > $self->{largest_free}) {
$self->add_nagios_critical(
sprintf "segment %s cannot extend", $self->{name});
}
}
}
}
package DBD::Oracle::Server::Database::Tablespace;
use strict;
our @ISA = qw(DBD::Oracle::Server::Database);
{
my @tablespaces = ();
my $initerrors = undef;
sub add_tablespace {
push(@tablespaces, shift);
}
sub return_tablespaces {
return reverse
sort { $a->{name} cmp $b->{name} } @tablespaces;
}
sub init_tablespaces {
my %params = @_;
my $num_tablespaces = 0;
if (($params{mode} =~ /server::database::tablespace::usage/) ||
($params{mode} =~ /server::database::tablespace::free/) ||
($params{mode} =~ /server::database::tablespace::remainingfreetime/) ||
($params{mode} =~ /server::database::tablespace::listtablespaces/)) {
my @tablespaceresult = ();
if (DBD::Oracle::Server::return_first_server()->version_is_minimum("9.x")) {
@tablespaceresult = $params{handle}->fetchall_array(q{
SELECT
a.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
a.bytes bytes,
a.maxbytes bytes_max,
c.bytes_free + NVL(d.bytes_expired,0) bytes_free
FROM
(
-- belegter und maximal verfuegbarer platz pro datafile
-- nach tablespacenamen zusammengefasst
-- => bytes
-- => maxbytes
SELECT
a.tablespace_name,
SUM(a.bytes) bytes,
SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes)) maxbytes
FROM
dba_data_files a
GROUP BY
tablespace_name
) a,
sys.dba_tablespaces b,
(
-- freier platz pro tablespace
-- => bytes_free
SELECT
a.tablespace_name,
SUM(a.bytes) bytes_free
FROM
dba_free_space a
GROUP BY
tablespace_name
) c,
(
-- freier platz durch expired extents
-- speziell fuer undo tablespaces
-- => bytes_expired
SELECT
a.tablespace_name,
SUM(a.bytes) bytes_expired
FROM
dba_undo_extents a
WHERE
status = 'EXPIRED'
GROUP BY
tablespace_name
) d
WHERE
a.tablespace_name = c.tablespace_name (+)
AND a.tablespace_name = b.tablespace_name
AND a.tablespace_name = d.tablespace_name (+)
UNION ALL
SELECT
d.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
sum(a.bytes_free + a.bytes_used) bytes, -- allocated
SUM(DECODE(d.autoextensible, 'YES', d.maxbytes, 'NO', d.bytes)) bytes_max,
SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, 0)) bytes_free
FROM
sys.v_$TEMP_SPACE_HEADER a,
sys.dba_tablespaces b,
sys.v_$Temp_extent_pool c,
dba_temp_files d
WHERE
c.file_id(+) = a.file_id
and c.tablespace_name(+) = a.tablespace_name
and d.file_id = a.file_id
and d.tablespace_name = a.tablespace_name
and b.tablespace_name = a.tablespace_name
GROUP BY
b.status,
b.contents,
b.extent_management,
d.tablespace_name
ORDER BY
1
});
} elsif (DBD::Oracle::Server::return_first_server()->version_is_minimum("8.x")) {
@tablespaceresult = $params{handle}->fetchall_array(q{
SELECT
a.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
a.bytes bytes,
a.maxbytes bytes_max,
c.bytes_free bytes_free
FROM
(
-- belegter und maximal verfuegbarer platz pro datafile
-- nach tablespacenamen zusammengefasst
-- => bytes
-- => maxbytes
SELECT
a.tablespace_name,
SUM(a.bytes) bytes,
SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes)) maxbytes
FROM
dba_data_files a
GROUP BY
tablespace_name
) a,
sys.dba_tablespaces b,
(
-- freier platz pro tablespace
-- => bytes_free
SELECT
a.tablespace_name,
SUM(a.bytes) bytes_free
FROM
dba_free_space a
GROUP BY
tablespace_name
) c
WHERE
a.tablespace_name = c.tablespace_name (+)
AND a.tablespace_name = b.tablespace_name
UNION ALL
SELECT
a.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
b.extent_management "Extent Mgmt",
sum(a.bytes_free + a.bytes_used) bytes, -- allocated
d.maxbytes bytes_max,
SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, 0)) bytes_free
FROM
sys.v_$TEMP_SPACE_HEADER a,
sys.dba_tablespaces b,
sys.v_$Temp_extent_pool c,
dba_temp_files d
WHERE
c.file_id(+) = a.file_id
and c.tablespace_name(+) = a.tablespace_name
and d.file_id = a.file_id
and d.tablespace_name = a.tablespace_name
and b.tablespace_name = a.tablespace_name
GROUP BY
a.tablespace_name,
b.status,
b.contents,
b.extent_management,
d.maxbytes
ORDER BY
1
});
} else {
@tablespaceresult = $params{handle}->fetchall_array(q{
SELECT
a.tablespace_name "Tablespace",
b.status "Status",
b.contents "Type",
'DICTIONARY' "Extent Mgmt",
a.bytes bytes,
a.maxbytes bytes_max,
c.bytes_free bytes_free
FROM
(
-- belegter und maximal verfuegbarer platz pro datafile
-- nach tablespacenamen zusammengefasst
-- => bytes
-- => maxbytes
SELECT
a.tablespace_name,
SUM(a.bytes) bytes,
SUM(a.bytes) maxbytes
FROM
dba_data_files a
GROUP BY
tablespace_name
) a,
sys.dba_tablespaces b,
(
-- freier platz pro tablespace
-- => bytes_free
SELECT
a.tablespace_name,
SUM(a.bytes) bytes_free
FROM
dba_free_space a
GROUP BY
tablespace_name
) c
WHERE
a.tablespace_name = c.tablespace_name (+)
AND a.tablespace_name = b.tablespace_name
});
}
foreach (@tablespaceresult) {
my ($name, $status, $type, $extentmgmt, $bytes, $bytes_max, $bytes_free) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
# host_filesys_pctAvailable
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{bytes} = $bytes;
$thisparams{bytes_max} = $bytes_max;
$thisparams{bytes_free} = $bytes_free;
$thisparams{status} = lc $status;
$thisparams{type} = lc $type;
$thisparams{extent_management} = lc $extentmgmt;
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
$num_tablespaces++;
}
if (! $num_tablespaces) {
$initerrors = 1;
return undef;
}
} elsif ($params{mode} =~ /server::database::tablespace::fragmentation/) {
my @tablespaceresult = $params{handle}->fetchall_array(q{
SELECT
tablespace_name,
COUNT(*) free_chunks,
DECODE(
ROUND((max(bytes) / 1024000),2),
NULL,0,
ROUND((MAX(bytes) / 1024000),2)) largest_chunk,
NVL(ROUND(SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks)) )),2),
0) fragmentation_index
FROM
sys.dba_free_space
GROUP BY
tablespace_name
ORDER BY
2 DESC, 1
});
foreach (@tablespaceresult) {
my ($name, $free_chunks, $largest_chunk, $fragmentation_index) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{fsfi} = $fragmentation_index;
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
$num_tablespaces++;
}
if (! $num_tablespaces) {
$initerrors = 1;
return undef;
}
} elsif ($params{mode} =~ /server::database::tablespace::segment::top10/) {
my %thisparams = %params;
$thisparams{name} = "dummy_segment";
$thisparams{segments} = [];
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
} elsif ($params{mode} =~
/server::database::tablespace::segment::extendspace/) {
my @tablespaceresult = $params{handle}->fetchall_array(q{
SELECT
tablespace_name, extent_management, allocation_type
FROM
dba_tablespaces
});
foreach (@tablespaceresult) {
my ($name, $extent_management, $allocation_type) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
my %thisparams = %params;
$thisparams{name} = $name;
$thisparams{extent_management} = $extent_management;
$thisparams{allocation_type} = $allocation_type;
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
$num_tablespaces++;
}
if (! $num_tablespaces) {
$initerrors = 1;
return undef;
}
} elsif ($params{mode} =~ /server::database::tablespace::datafile/) {
my %thisparams = %params;
$thisparams{name} = "dummy_for_datafiles";
$thisparams{datafiles} = [];
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
} elsif ($params{mode} =~ /server::database::tablespace::iobalance/) {
my @tablespaceresult = $params{handle}->fetchall_array(q{
SELECT tablespace_name FROM dba_tablespaces
});
foreach (@tablespaceresult) {
my ($name) = @{$_};
if ($params{regexp}) {
next if $params{selectname} && $name !~ /$params{selectname}/;
} else {
next if $params{selectname} && lc $params{selectname} ne lc $name;
}
my %thisparams = %params;
$thisparams{name} = $name;
my $tablespace = DBD::Oracle::Server::Database::Tablespace->new(
%thisparams);
add_tablespace($tablespace);
$num_tablespaces++;
}
if (! $num_tablespaces) {
$initerrors = 1;
return undef;
}
}
}
}
sub new {
my $class = shift;
my %params = @_;
my $self = {
verbose => $params{verbose},
handle => $params{handle},
name => $params{name},
bytes => $params{bytes},
bytes_max => $params{bytes_max},
bytes_free => $params{bytes_free} || 0,
extent_management => $params{extent_management},
type => $params{type},
status => $params{status},
fsfi => $params{fsfi},
segments => [],
datafiles => [],
io_total => 0,
usage_history => [],
allocation_type => $params{allocation_type},
largest_free_extent => $params{largest_free_extent},
warningrange => $params{warningrange},
criticalrange => $params{criticalrange},
};
bless $self, $class;
$self->init(%params);
return $self;
}
sub init {
my $self = shift;
my %params = @_;
$self->init_nagios();
$self->set_local_db_thresholds(%params);
if ($params{mode} =~ /server::database::tablespace::(usage|free)/) {
if (! defined $self->{bytes_max}) {
$self->{bytes} = 0;
$self->{bytes_max} = 0;
$self->{bytes_free} = 0;
$self->{percent_used} = 0;
$self->{real_bytes_max} = $self->{bytes};
$self->{real_bytes_free} = $self->{bytes_free};
$self->{percent_as_bar} = '____________________';
} else {
# (total - free) / total * 100 = % used
# (used + free - free) / ( used + free)
if ($self->{bytes_max} == 0) {
$self->{percent_used} =
($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100; $self->{real_bytes_max} = $self->{bytes}; $self->{real_bytes_free} = $self->{bytes_free};
} elsif ($self->{bytes_max} > $self->{bytes}) {
$self->{percent_used} =
($self->{bytes} - $self->{bytes_free}) / $self->{bytes_max} * 100;
$self->{real_bytes_max} = $self->{bytes_max};
$self->{real_bytes_free} = $self->{bytes_free} + ($self->{bytes_max} - $self->{bytes});
} else {
# alter tablespace USERS add datafile 'users02.dbf'
# size 5M autoextend on next 200K maxsize 6M;
# bytes = 5M, maxbytes = 6M
# ..... data arriving...until ORA-01652: unable to extend temp segment
# bytes = 6M, maxbytes = 6M
# alter database datafile 5 resize 8M;
# bytes = 8M, maxbytes = 6M
$self->{percent_used} =
($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100;
$self->{real_bytes_max} = $self->{bytes};
$self->{real_bytes_free} = $self->{bytes_free};
}
}
$self->{percent_free} = 100 - $self->{percent_used};
my $tlen = 20;
my $len = int((($params{mode} =~ /server::database::tablespace::usage/) ?
$self->{percent_used} : $self->{percent_free} / 100 * $tlen) + 0.5);
$self->{percent_as_bar} = '=' x $len . '_' x ($tlen - $len);
} elsif ($params{mode} =~ /server::database::tablespace::fragmentation/) {
} elsif ($params{mode} =~ /server::database::tablespace::segment::top10/) {
DBD::Oracle::Server::Database::Tablespace::Segment::init_segments(%params);
if (my @segments =
DBD::Oracle::Server::Database::Tablespace::Segment::return_segments()) {
$self->{segments} = \@segments;
} else {
$self->add_nagios_critical("unable to aquire segment info");
}
} elsif ($params{mode} =~ /server::database::tablespace::datafile/) {
DBD::Oracle::Server::Database::Tablespace::Datafile::init_datafiles(%params);
if (my @datafiles =
DBD::Oracle::Server::Database::Tablespace::Datafile::return_datafiles()) {
$self->{datafiles} = \@datafiles;
} else {
$self->add_nagios_critical("unable to aquire datafile info");
}
} elsif ($params{mode} =~ /server::database::tablespace::iobalance/) {
$params{tablespace} = $self->{name};
DBD::Oracle::Server::Database::Tablespace::Datafile::init_datafiles(%params);
if (my @datafiles =
DBD::Oracle::Server::Database::Tablespace::Datafile::return_datafiles()) {
$self->{datafiles} = \@datafiles;
map { $self->{io_total} += $_->{io_total} } @datafiles;
DBD::Oracle::Server::Database::Tablespace::Datafile::clear_datafiles();
} else {
$self->add_nagios_critical("unable to aquire datafile info");
}
} elsif ($params{mode} =~ /server::database::tablespace::segment::extendspace/) {
$params{tablespace} = $self->{name};
DBD::Oracle::Server::Database::Tablespace::Segment::init_segments(%params);
my @segments =
DBD::Oracle::Server::Database::Tablespace::Segment::return_segments();
$self->{segments} = \@segments;
DBD::Oracle::Server::Database::Tablespace::Segment::clear_segments();
} elsif ($params{mode} =~ /server::database::tablespace::remainingfreetime/) {
# load historical data
# calculate slope, intercept (go back periods * interval)
# calculate remaining time
$self->{percent_used} = $self->{bytes_max} == 0 ?
($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100 :
($self->{bytes} - $self->{bytes_free}) / $self->{bytes_max} * 100;
$self->{usage_history} = $self->load_state( %params ) || [];
my $now = time;
my $lookback = ($params{lookback} || 30) * 24 * 3600;
#$lookback = 91 * 24 * 3600;
if (scalar(@{$self->{usage_history}})) {
$self->trace(sprintf "loaded %d data sets from %s - %s",
scalar(@{$self->{usage_history}}),
scalar localtime((@{$self->{usage_history}})[0]->[0]),
scalar localtime($now));
# only data sets with valid usage. only newer than 91 days
$self->{usage_history} =
[ grep { defined $_->[1] && ($now - $_->[0]) < $lookback } @{$self->{usage_history}} ];
$self->trace(sprintf "trimmed to %d data sets from %s - %s",
scalar(@{$self->{usage_history}}),
scalar localtime((@{$self->{usage_history}})[0]->[0]),
scalar localtime($now));
} else {
$self->trace(sprintf "no historical data found");
}
push(@{$self->{usage_history}}, [ time, $self->{percent_used} ]);
$params{save} = $self->{usage_history};
$self->save_state(%params);
}
}
sub nagios {
my $self = shift;
my %params = @_;
if (! $self->{nagios_level}) {
if ($params{mode} =~ /server::database::tablespace::usage/) {
if (! $self->{bytes_max}) {
$self->check_thresholds($self->{percent_used}, "90", "98");
if ($self->{status} eq 'offline') {
$self->add_nagios_warning(
sprintf("tbs %s is offline", $self->{name})
);
} else {
$self->add_nagios_critical(
sprintf("tbs %s has has a problem, maybe needs recovery?", $self->{name})
);
}
} else {
$self->add_nagios(
# 'tbs_system_usage_pct'=99.01%;90;98 percent used, warn, crit
# 'tbs_system_usage'=693MB;630;686;0;700 used, warn, crit, 0, max=total
$self->check_thresholds($self->{percent_used}, "90", "98"),
$params{eyecandy} ?
sprintf("[%s] %s", $self->{percent_as_bar}, $self->{name}) :
sprintf("tbs %s usage is %.2f%%",
$self->{name}, $self->{percent_used})
);
}
$self->add_perfdata(sprintf "\'tbs_%s_usage_pct\'=%.2f%%;%d;%d",
lc $self->{name},
$self->{percent_used},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "\'tbs_%s_usage\'=%dMB;%d;%d;%d;%d",
lc $self->{name},
($self->{bytes} - $self->{bytes_free}) / 1048576,
$self->{warningrange} * $self->{bytes_max} / 100 / 1048576,
$self->{criticalrange} * $self->{bytes_max} / 100 / 1048576,
0, $self->{bytes_max} / 1048576);
$self->add_perfdata(sprintf "\'tbs_%s_alloc\'=%dMB;;;0;%d",
lc $self->{name},
$self->{bytes} / 1048576,
$self->{bytes_max} / 1048576);
} elsif ($params{mode} =~ /server::database::tablespace::fragmentation/) {
$self->add_nagios(
$self->check_thresholds($self->{fsfi}, "30:", "20:"),
sprintf "tbs %s fsfi is %.2f", $self->{name}, $self->{fsfi});
$self->add_perfdata(sprintf "\'tbs_%s_fsfi\'=%.2f;%s;%s;0;100",
lc $self->{name},
$self->{fsfi},
$self->{warningrange}, $self->{criticalrange});
} elsif ($params{mode} =~ /server::database::tablespace::free/) {
# ->percent_free
# ->real_bytes_max
#
# ausgabe
# perfdata tbs_<tbs>_free_pct
# perfdata tbs_<tbs>_free (real_bytes_max - bytes) + bytes_free (with units)
# perfdata tbs_<tbs>_alloc_free bytes_free (with units)
#
# umrechnen der thresholds
# ()/%
# MB
# GB
# KB
if (($self->{warningrange} && $self->{warningrange} !~ /^\d+:/) ||
($self->{criticalrange} && $self->{criticalrange} !~ /^\d+:/)) {
$self->add_nagios_unknown("you want an alert if free space is _above_ a threshold????");
return;
}
if (! $params{units}) {
$params{units} = "%";
}
$self->{warning_bytes} = 0;
$self->{critical_bytes} = 0;
if ($params{units} eq "%") {
if (! $self->{bytes_max}) {
$self->check_thresholds($self->{percent_used}, "5:", "2:");
if ($self->{status} eq 'offline') {
$self->add_nagios_warning(
sprintf("tbs %s is offline", $self->{name})
);
} else {
$self->add_nagios_critical(
sprintf("tbs %s has has a problem, maybe needs recovery?", $self->{name})
);
}
} else {
$self->add_nagios(
$self->check_thresholds($self->{percent_free}, "5:", "2:"),
sprintf("tbs %s has %.2f%% free space left",
$self->{name}, $self->{percent_free})
);
}
$self->{warningrange} =~ s/://g;
$self->{criticalrange} =~ s/://g;
$self->add_perfdata(sprintf "\'tbs_%s_free_pct\'=%.2f%%;%d:;%d:",
lc $self->{name},
$self->{percent_free},
$self->{warningrange}, $self->{criticalrange});
$self->add_perfdata(sprintf "\'tbs_%s_free\'=%dMB;%.2f:;%.2f:;0;%.2f",
lc $self->{name},
$self->{real_bytes_free} / 1048576,
$self->{warningrange} * $self->{bytes_max} / 100 / 1048576,
$self->{criticalrange} * $self->{bytes_max} / 100 / 1048576,
$self->{real_bytes_max} / 1048576);
} else {
my $factor = 1024 * 1024; # default MB
if ($params{units} eq "GB") {
$factor = 1024 * 1024 * 1024;
} elsif ($params{units} eq "MB") {
$factor = 1024 * 1024;
} elsif ($params{units} eq "KB") {
$factor = 1024;
}
$self->{warningrange} ||= "5:";
$self->{criticalrange} ||= "2:";
my $saved_warningrange = $self->{warningrange};
my $saved_criticalrange = $self->{criticalrange};
# : entfernen weil gerechnet werden muss
$self->{warningrange} =~ s/://g;
$self->{criticalrange} =~ s/://g;
$self->{warningrange} = $self->{warningrange} ?
$self->{warningrange} * $factor : 5 * $factor;
$self->{criticalrange} = $self->{criticalrange} ?
$self->{criticalrange} * $factor : 2 * $factor;
if (! $self->{bytes_max}) {
$self->{percent_warning} = 0;
$self->{percent_critical} = 0;
$self->{warningrange} .= ':';
$self->{criticalrange} .= ':';
$self->check_thresholds($self->{real_bytes_free}, "5242880:", "1048576:");
if ($self->{status} eq 'offline') {
$self->add_nagios_warning(
sprintf("tbs %s is offline", $self->{name})
);
} else {
$self->add_nagios_critical(
sprintf("tbs %s has a problem, maybe needs recovery?", $self->{name})
);
}
} else {
$self->{percent_warning} = 100 * $self->{warningrange} / $self->{real_bytes_max};
$self->{percent_critical} = 100 * $self->{criticalrange} / $self->{real_bytes_max};
$self->{warningrange} .= ':';
$self->{criticalrange} .= ':';
$self->add_nagios(
$self->check_thresholds($self->{real_bytes_free}, "5242880:", "1048576:"),
sprintf("tbs %s has %.2f%s free space left", $self->{name},
$self->{real_bytes_free} / $factor, $params{units})
);
}
$self->{warningrange} = $saved_warningrange;
$self->{criticalrange} = $saved_criticalrange;
$self->{warningrange} =~ s/://g;
$self->{criticalrange}