I am novice to perl. Can someone guide me on the below query. We have an existing perl script which connects to database to check the disk group status which i wanted to retieve directly from ASM rather than database.
This is because, a cluster has more than 4 databases running and a check on each database would report same diskgroup infiormation.
Hence had modified the exitsting script as shown below ...
\#!/usr/bin/perl
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
\# \#
\# ASM Diskgroup space usage \#
\# \#
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
use lib '.';
\#use lib "/opt/nagios/libexec";
use nagios\_dbcheck_lib;
\#use strict;
use warnings;
use Getopt::Long;
use DBI;
use DBD::Oracle qw\(:ora\_session_modes\);
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
\# Global Variables \#
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
\# Nagios Thresholds
my %G_SETTINGS = \(
asmdgspace => \{ warning => 15, critical => 10 \}, \# Free extends.
\);
my %G_PARAMS; \# Script parameters
my $nagios_message = " "; \# Body of nagios email
my $debug = 0; \# troubleshooting only, must be 0 in production!
my $warning_thresh = 0;
my $critical_thresh = 0;
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
\# Parameter Checks \#
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
GetOptions\(
"sid=s" => \\$G_PARAMS\{sid\}, \# can be a service, too. Must be defined in tnsnames.ora
\);
\# sanity check
foreach my $key \(keys %G_PARAMS\) \{
if \( !defined \($G_PARAMS\{$key\}\) \) \{
print "UNKNOWN - mandatory parameters not passed to script\\n";
help\(\);
exit 3
\}
\}
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
\# Nagios Check Code \#
\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#-\#
print "ASM Diskgroup space usage check\\n" if $debug;
my $query = "
SELECT \(CASE
WHEN \(total_mb/1024\) >= 2000 AND \(free_mb/1024\) < 150 THEN 'CRITICAL'
WHEN \(total_mb/1024\) < 2000 AND \(free\_mb/total_mb*100\) < $G_SETTINGS\{asmdgspace\}\{critical\} THEN 'CRITICAL'
WHEN \(free\_mb/total_mb*100\) between $G_SETTINGS\{asmdgspace\}\{critical\} and $G_SETTINGS\{asmdgspace\}\{warning\} THEN 'WARNING'
ELSE 'N/A'
END\) Alert,
NAME,
TOTAL_MB,
FREE_MB,
ROUND\(free\_mb/total_mb*100, 2\) pct_free,
STATE,
OFFLINE_DISKS from v\\$ASM\_DISKGROUP_STAT
where \(free\_mb/total_mb*100\) <= $G_SETTINGS\{asmdgspace\}\{warning\}";
my $sth = runAsmCheck\($G_PARAMS\{sid\}, $query\);
while \( my $r = $sth->fetchrow\_hashref\('NAME_lc'\)\) \{
$nagios_message .= "ASM Diskgroup filling: $r->\{name\} Total\(MB\): $r->\{total_mb\} Free\(MB\): $r->\{free_mb\} PCT_Free: \($r->\{pct_free\} %\) \\n";
$critical_thresh = 1 if \($r->\{alert\} eq "CRITICAL" \);
$warning_thresh = 1 if \($r->\{alert\} eq "WARNING" \);
\}
$sth->finish;
dbh_dc\(\);
cleanup\_and_exit\("CRITICAL"," Priority 2: ASM Diskgroup Filling | " . $nagios_message\) if $critical_thresh;
cleanup\_and_exit\("WARNING"," Priority 3: ASM Diskgroup Filling | " . $nagios_message\) if $warning_thresh;
cleanup\_and_exit\("OK","No problem detected |"\);
Note: runAsmCheck is a subroutine within the script nagios_dbcheck_lib.pm and that reads as ...
sub runAsmCheck {
my ($sid, $query) = @_;
# connect to the database
eval {
$dbh = DBI->connect(
"dbi:Oracle:sid=$sid",
"nagiosmon", "password", { ora_session_mode => 2 }, { RaiseError => 1, AutoCommit => 0 });
};
if ($@) {
print "ERROR";
my $dbi_errorstr;
if (DBI::errstr) {
$dbi_errorstr = DBI::errstr;
}else{
$dbi_errorstr = "";
}
cleanup_and_exit("CRITICAL", "Failed to connect to database $sid | " . $dbi_errorstr);
}
my $sth = $dbh->prepare($query);
$sth->execute();
return $sth;
}
It simply reports failed to connect ...
$ perl check_asm_diskgroup_space_usage.pl -s whqa
ERRORCRITICAL - Failed to connect to database whqa
Thanks