Sample Perl program for cleaning database on a routine basis

Here is a samlpe Perl Program that gives you an outline on how to deal with cleaning DB on a routine basis (ofcourse scheudled via cron), log the activity as well as maintain the required historic data in the Database. It executes the sql stored in a CTL file by issuing Oracle SQL Plus command and uses all close knit linux commands.

#!/usr/bin/perl
umask 0000;

#######################################################
# #
#######################################################

$BASE_DIR = “/yourFolder”;
$SQL_DIR = “$BASE_DIR/sql”;
$ORACLE_HOME=”/usr/oracle/bin”;
$SQL_PLUS=”$ORACLE_HOME/sqlplus “;
$ORACLE_USER=”youruser”;
$ORACLE_PASS=”yourpass”;
$SQL = “$SQL_DIR/dbClean.sql”;
$LOGGER=”/usr/bin/logger -i -p local0.info -t CleanDBLogger”;
$LockFile = “$BASE_DIR/dbClean.pid”;

sub MyLog {
my $msg=sprintf(“%s”,@_);
my $COMMAND=”” ;
chomp($msg);
if ($daemon==0) {
print “$msg\n”;
} else {
$COMMAND=”$LOGGER $msg”;
system($COMMAND);
}
}

sub MyExit {
MyLog(@_);
exit 0;
}

sub GotSignal {
my $signame = shift;
if (length($signame)>0) {
MyLog “end process with signal SIG$signame”;
} else {
MyLog “end Process”;
}
}

sub MyLock($) {
local($LockFile)=(@_);
local($pid);
open(PRG,”+<$LockFile”) or open(PRG,”>$LockFile”);
flock(PRG,LOCK_EX);
$_=;
$pid=””;
$pid=$1 if /^([0-9]*)/;
if (length($pid) > 0) {
MyExit(“Exit since process $pid is already running\n”) if (kill(0,$pid)==1);
}
seek(PRG,0,0);
printf(PRG “%d\n”,$$);
flock(PRG,LOCK_UN);
close(PRG);
}

if (-t STDIN) {
$daemon=0;
} else {
$daemon=1;
}

$SIG{INT} = \&GotSignal;
$SIG{HUP} = \&GotSignal;
$SIG{QUIT} = \&GotSignal;
$SIG{ALRM} = \&GotSignal;

MyLock(“$LockFile”);

$cleanDatabase = “$SQL_PLUS $ORACLE_USER/$ORACLE_PASS < $SQL > /dev/null 2>&1”;
if (system($cleanDatabase) !=0){
MyLog(“Cannot Clean Database”);
}
else{
MyLog(“Completed DB clean”);
}
#END