Perl script for Querying Oracle DB

Description: 

This script can be used as an template to query Oracle Database using the Sid, Port, Oracle DB username and Password. You can customize the SQL Query which I used in this script. This script basically checks the completion of RMAN backup activity done by Database Administrators.

Requirements:

Oracle SID

Port Number

Oracle username & Password

----------------------------------------------------------------------------------------------------------
#!/usr/bin/perl

use DBI;

my $oracle_sid = 'emcprod';
my $oracle_port = '1530';
my $oracle_user = 'oracleuser';
my $oracle_password = 'XXXXXXX';
my $bkptime;
my $bkpstatus;
my $yesterday = `date --date='1 day ago' +%d-%m-%Y`;
chomp($yesterday);

sub RmanDBStatusCheck($$);

# Create a temporary log file to log and track all the changes
$logFile = GetTime(1)."\_"."$userName"."\.log";
print "Logging all output to $logFile\n";

# Open the log file
open(LOGFILE,">$logFile") || die (print "Cannot create logfile $logFile \n");
Log ("Running verion $version of $program",0,LOGFILE);


RmanDBStatusCheck("oralsb40",LOGFILE);

sub RmanDBStatusCheck($$)
{
        my $hostname=$_[0];
        my $outputDestination=$_[1];
        my $dbh = DBI->connect("dbi:Oracle:host=$hostname;port=$oracle_port;sid=$oracle_sid", $oracle_user, $oracle_password)
                or die "Unable to initialize DB connection : " . DBI->errstr;

        Log("==================================================",0,$outputDestination);
  my $sql_query="select to_char(END_TIME , 'dd-mon-yyyy@hh24:mi:ss') "Date and Time",status from V$RMAN_BACKUP_JOB_DETAILS where start_time > (sysdate);";
               my $sth = $dbh->prepare("$sql_query") or die "Couldnot query Oracle Database" . $dbh->errstr;
               $sth->execute() or die "Couldnot execute query on Oracle Database" . $sth->errstr;
                        while ( my @column = $sth->fetchrow_array() ) {
                        $bkpday = $column[0];
                        $bkpstatus = $column[1];
                        print "Printing Arrage/DB Value=@column\n";
                        }
                        $sth->finish;
                        $dbh->disconnect;
                        if(($bkpday eq "$yesterday") && ($bkpstatus eq 'COMPLETED'))
                        {
                          Log("RMAN DBA activity completed",0,$outputDestination);
                          return(0);
                        }
                        elsif (($bkpday eq "$yesterday") && ($bkpstatus eq 'FAILED'))
                        {
                          Log("RMAN DBA activity failed",0,$outputDestination);
                          return(1);
                        }
                        else
                        {
                        Log("RMAN DBA activity is still in progress",0,$outputDestination);
                        return(2);
                        }

}

sub Log($$$)
{
    my $text = $_[0];
    my $exitStatus = $_[1];
    my $outputDestination = $_[2];
    my $time = GetTime(0);
    if ($exitStatus == 1)
    {
        my $time = GetTime(0);
        print "[$time] - $text\n";
                print "[$time] - Exiting..!\n";
        exit;
    }
    print $outputDestination "[$time] - $text\n";
    print "[$time] - $text\n";
}

sub GetTime($)
{
    # Get the locatime
    my ($Second, $Minute, $Hour, $Day, $Month, $Year) = (localtime)[0,1,2,3,4,5];

    # Add padding as required
    $Year = $Year+1900;
    $Month = $Month+1;

    # Format into a nice string
    my $Time = "$Month-$Day-$Year $Hour:$Minute:$Second";

    # Spit it out
    if ($_[0] == 1)
    {
        $Time = "$Month-$Day-$Year\_$Hour\_$Minute\_$Second";
        return $Time;
    }
    return $Time;
}