Unable to stop running scheduler job

Few days ago we noticed one of our jobs in the database was not executing regularly. Job was defined through dbms_scheduler, and should be running each day. However it did not. Looking on the Enterprise Manager we could see that the job was still running from it’s last run (few days ago). That would explain why it did not start subsequently. Now the question is why was it still stated as running.

Closer examination showed that the state of the job really is running, but there is no session associated with the job and that if we try to stop the execution of a job manually, we get an ORA-27478: job is running.

SELECT STATE
  FROM DBA_SCHEDULER_JOBS
 WHERE JOB_NAME = 'MY_JOB'
   AND OWNER = 'JOB_OWNER';

STATE           JOB_NAME                       OWNER
--------------- -------------------- ---------------
RUNNING         MY_JOB               JOB_OWNER

A search on metalink however revealed a note 422134.1 stating a possible scheduler problem that may occur on Oracle versions 10.1.0.0 – 10.2.0.3. Solution: drop the job and recreate it later. 🙁

So we tried that, but cjq0 stopped us from doing that with lock it had on the job making it impossible to stop our not-running job. Another metalink note (330725.1) mentions exactly this situation and adds another step to our solution, killing the cjq0 process. Here is the exact procedure:

  • Kill the cqj0 from the OS.
  • As the lock is removed, you can drop the scheduler job. If you want, you can make a backup copy using DBMS_SCHEDULER.COPY_JOB.
  • Restart the cqj0 if it didn’t start automatically,
  • Share the joy
    •  
    •  
    •  
    •  
    •  
    •  
    •  

    Leave a Comment

    This site uses Akismet to reduce spam. Learn how your comment data is processed.