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.
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply