Wednesday, March 2, 2011

Perl script for Querying Oracle DB

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.

Oracle SID
Port Number
Oracle username & Password


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`;

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);


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;

  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";
                        if(($bkpday eq "$yesterday") && ($bkpstatus eq 'COMPLETED'))
                          Log("RMAN DBA activity completed",0,$outputDestination);
                        elsif (($bkpday eq "$yesterday") && ($bkpstatus eq 'FAILED'))
                          Log("RMAN DBA activity failed",0,$outputDestination);
                        Log("RMAN DBA activity is still in progress",0,$outputDestination);


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";
    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;

No comments:

Post a Comment

Popular Posts

About Me

My photo
I have started this blog to share my work experience and spread some smart solutions on Linux to Internet community. I'm hoping more people will get benefited from this blog. Brief about me: I have 14+ years experience working as System Admin and currently work with VMware.