Scheduled Jobs Hanging  

Posted by Mawahid

Problem:

Jobs are not executing automatically on its scheduled intervals, next_date is not updating though it is run manually.

Resolution:

1. If it is not executing automatically means, it is not entering into queue at the first place.

2. Generally any job scheduled through dbms_job will run in background, means helper processes(ora_j00,....etc) runs it, so, its queue-coordinator's (CJQ bg-process) responsibilty to create helper process to execute scheduled jobs. Here in this case, check at OS level if helper processes are exists or not.

ps -ef | grep <sid> | ora_j

If this return nothing means, cjq0 not doing its responsibility.

3. Check cjq0 process and kill it.

SELECT A1.SID, A2.SPID FROM V$SESSION A1,V$PROCESS A2
WHERE A1.PADDR = A2.ADDR AND TYPE='BACKGROUND' AND A1.PROGRAM LIKE '%CJQ%';

4. Set job_queue_process=0, this would kill all the helper process (ora_j001, ora_j002 etc) then after couple of mins, set it to old value.

5. Once we set to a value > 0, oracle automatically creates these processes. Now it will start executing scheduled jobs according to their interval.

6. If you still see any problems, check in dba_jobs_running and see if there is any locks, if yes, try to kill or release them.

This entry was posted on Monday, October 18, 2010 at 9:23 PM . You can follow any responses to this entry through the comments feed .

0 comments

Post a Comment