Guenadi N Jilevski's Oracle BLOG

Scheduling Oracle job through UNIX cron

Scheduling Oracle job through UNIX cron

This was a problem which I faced yesterday. I had a mview refresh which was lagging on one of the server. After checking master site, I found that there were many pending requests coming from the second materialized view site. Checking second site revealed that there were multiple sessions running in database which were waiting on ‘enq :JI Contention’ wait event.

These mviews were refreshed by job scheduled through a cron. Doing a grep for the refresh script gave following result

$ps –ef |grep scott | grep -v grep

oracle 11725 11714 0 13:00:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 19981 19970 0 Feb 7 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 24794 24781 0 06:30:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 10538 10527 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 13972 13935 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 2601 2592 0 19:00:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 17274 17246 0 Feb 6 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

oracle 8308 8294 0 13:30:00 ? 0:00 /usr/bin/sh /home/oracle/cron/Refresh_scott.sh

This was definitely wrong. I killed the database sessions and also killed the OS processes

$ ps –ef | grep Refresh_scott.sh | grep -v grep|awk ‘{print $2}’| xargs kill -9

This issue had occurred as another site was added for mview refresh and due to locking issues (while deleting records from Mlog$), job could not complete before next refresh schedule. While scheduling scripts through cron, care should be taken to put a check if script is already running or not. This is required as Cron will spawn the new job (as per job schedule) despite that whether earlier execution has completed or not. I used following logic to implement the same check

cnt=`ps –ef |grep Refresh_scott.sh |grep -v grep |wc -l`

if [ $cnt -eq 1 ]

then

echo “Running Fast Refresh now ” > $DIR/Check_refresh_scott.log

sqlplus /nolog < $DIR/Refresh_scott.log

conn scott/tiger

exec dbms_mview.refresh …

…

exit

EOF

else

echo “Fast refresh already running ” > $DIR/Check_refresh_scott.log

date >> $DIR/Check_refresh_scott.log

exit

fi

There could be other approach as well which can be used to implement the same. That is, another could be

filename=$DIR/Check_refresh_scott.log

if [ -e $filename ]

then

echo “Fast Refresh already running ” > refresh_runnning.log

exit

else

sqlplus /nolog < $DIR/Refresh_scott.log

conn scott/tiger

exec dbms_mview.refresh …

…

exit

EOF

rm $filename

fi

Another option could be to use DBMS_JOB and DBMS_SCHEDULER to schedule the database job.

June 6, 2008 - Posted by gjilevski | oracle