Source

Portfolio / scripts / oracle / dbnotify.pl

## NAME: oramon.pl 
## AUTHOR: Adam M Dutko
## PURPOSE: Monitor Oracle Single Instances
## REQUIRES: Working tnsping.
## LICENSE: GPL v3 or Later Revisions
##
##
## USAGE: perl dbnotify.pl
##
##        Change options in NOTIFICATION section
##        and the DATABASE section.  @DATABASES
##        is an array of the 'SIDs' you defined
##        in your tnsnames.ora.
##
##        If you want to test mailing you can
##        use the $TEST_EMAIL and if you want to
##        see output from each of your tnsping
##        attempts you can use $TEST_DB;
##
## PREREQS: It is expected you have the tnsping
##          utility in your path and that you 
##          have a tnsnames.ora file.
##          
##          On Windows you need a version of 
##          ActivePerl installed.
##  
##          You need to install MIME::Lite using
##          CPAN on nix and ppm on Windows.


use Mime::Lite;

###########################
## CONFIGURATION OPTIONS ##
###########################
###########
## DEBUG ##
###########
my $TEST_EMAIL = 0;  ## 1 --> DEBUG
my $TEST_DB = 0;     ## 1 --> DEBUG
##################
## NOTIFICATION ##
##################
my $FROM_DOMAIN = "YOURDOMAIN";
my $TO_DOMAIN = "YOURDOMAIN";
my $FROM = "oracle-no-reply@" . $FROM_DOMAIN;
my $TO = "adutko@" . $TO_DOMAIN;
my $SUBJECT_0 = "ALERT: Oracle Database ";
my $SUBJECT_1 = " down!";
my $SUBJECT_2 = "DEBUG: Oracle Database ";
my $MESSAGE = "DEFAULT";
my $MAILSRV = "smtp2.lakelandcc.edu";
##############
## DATABASE ##
##############
my $HOSTENV = "windows"; ## windows || nix
my $TNSPING = "tnsping";
my $ORACLE_ROOT = "";
my @DATABASES = ("dwork","tpprd");


######################################
## DO NOT EDIT BELOW THIS NEXT LINE ##
######################################

############################
## SIMPLE SETTINGS CHECKS ##
############################
my $DEF = "CHANGEME";
my $INVALID = 0;
if ($FROM_DOMAIN eq $DEF) {
    print "Please change the \$FROM_DOMAIN.\n";
    $INVALID = 1;
} elsif ($TO_DOMAIN eq $DEF) {
    print "Please change the \$TO_DOMAIN.\n";
    $INVALID = 1;
} elsif ($FROM eq $DEF) {
    print "Please change \$FROM.\n";
    $INVALID = 1;
} elsif ($TO eq $DEF) {
    print "Please change \$TO.\n";
    $INVALID = 1;
} elsif ($MAILSRV eq $DEF) {
    print "Please change \$MAILSRV.\n";
    $INVALID = 1;
}

if ($INVALID) {
    print "Please take action and try again.\n";
    exit(-1);
}


###########
## DEBUG ##
###########
if ($TEST_EMAIL) {
    my $subj = "TEST::: " . $SUBJECT_0 . $SUBJECT_1;
    send_msg($FROM,$TO,$subj,$MESSAGE,'1',$MAILSRV);
    exit(0);
}



#################
## SUBROUTINES ##
#################
sub send_msg {

    my $from = shift | $FROM;
    my $to = shift | $TO;
    my $subject = shift | $SUBJECT;
    my $message = shift | $MESSAGE;
    my $debug = shift | 0;
    my $mailsrv = shift | $MAILSRV;
    my $msg = MIME::Lite->new(
        From    => $from,
        To      => $to,
        Subject => $subject,
        Type    => 'multipart/mixed'
    );

    $msg->attach(
        Type    => 'TEXT',
        Data    => $message
    );

    $msg->send('smtp',$mailsrv,Debug=>$DEBUG);
}

sub tnsping {

    my $databaseToCheck = shift;
    my $status = "";
    my $tnspingOutput = "";
    if ($databaseToCheck eq "") {
        print "No databases to check.\n";
        return;
    }
    
    $tnspingOutput = `$TNSPING $databaseToCheck`;
    ## VERY WEAK WAY TO CHECK
    if ($tnspingOutput =~ /OK/) {
        if ($TEST_DB) {
            $status = $tnspingOutput;
        } else {
            $status = "OK";
        }
    } else {
        $status = $tnspingOutput;
    }
    return $status;
}


###########################
## MAIN APPLICATION CODE ##
###########################
if ($HOSTENV eq "windows") {
    system("set ORACLE_ROOT=" . $ORACLE_ROOT);
}
if ($HOSTENV eq "nix") {
    system("export ORACLE_ROOT=" . $ORACLE_ROOT);
}
my $statusMessage = "";
my $subj = "";
foreach $database (@DATABASES) {
    $statusMessage = "";
    $subj = "";
    $statusMessage = tnsping($database);
    if ($statusMessage ne "OK") {
        if ($TEST_DB) {
            $subj = $SUBJECT_2 . $database;
        } else {
            $subj = $SUBJECT_0 . $database . $SUBJECT_1;
        }
        send_msg($FROM,$TO,$subj,$statusMessage,'0',$MAILSRV); 
    }
}