Scheduled job completion verification

Oracle Database Tips by Donald Burleson

When scheduling jobs via dbms_scheduler or on a cron, all jobs should be configured to look for an affirmative completion message and complain if it is not found.

Many PL/SQL jobs will contain the affirmative message "successfully completed.".

For all of our scheduled jobs, we must grep for this and only send an e-mail when the message "successfully completed." IS NOT found. For example, a cron job may look like this:

00 07 * * * /home/get_stats.ksh > /tmp/cleanup.lst

The code inside this ksh file will access Oracle and you can spool and check, like this:

#!/bin/ksh

# First, we must set the environment . . . .

ORACLE_SID=$1

export ORACLE_SID

ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`

export ORACLE_HOME

MON=`echo ~oracle/bei`

export MON

# Enter SQL*Plus and spool output:
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!

spool $MON/run_job.lst

exec my_stored_procedure

spool off

exit

!

# Now we look for the "successfully completed."
# message from the PL/SQL procedure
# We can simply grep for it and send an alert
# if it's not in the output

rm -f /tmp/check.lst

grep -i "successfully completed." \
   ~oracle/bei/run_job.lst > /tmp/check.lst

var=`cat /tmp/check.lst|wc -l`

echo $var
if [[ $var -lt 1 ]];
then
echo "Job Execution Error"
cat /tmp/cleanup.lst|mailx \
     -s "Our job failed!"\
     lellison@mycompany.com

��

Scheduled job completion verification Oracle Database Tips by Donald Burleson

Scheduled job completion verification

Oracle Database Tips by Donald Burleson