oracle job没有运行

今天遇到一个很奇怪的问题，job到点了不运行。

首先会想到job_queue_processes是否设置正确了，数据库显示正确。

SQL> show parameter job_queue_processes

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

job_queue_processes                  integer                           20

排除这个错误，是否job的状态为broken，查询了下也是不是。

程序的时间是否写错呢？

declare
job_name varchar2(100):='pacs_sync_user_info_job';
begin
sys.dbms_job.submit(job_name,
what => 'pacsdata.pacs_sync_user_info.sync_user_info;',
next_date => to_date('05-11-2013 14:20:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE+1)+5/24');
commit;
end;
/

也没有任何问题。

程序是否还在继续跑没有结束呢？

检查了下也不是。

每一个job都无法定时执行，但是单独执行没有任何问题。

这是为啥呢？

主机上alert日志没有明显的报错。但是发现没有ora_j0**的进程。但是这个进程是怎么启动呢？

Metlink上查询下执行ora_j0**进程是有ora_cjq0进程管理的。The CJQ0 process is the one which processes the dba_jobs/dbms_scheduler_jobs.说明cjq0进程是管理job的进程，而j0**进程是job运行的进程。如果cjq0进程没有启动那么job是无法运行的。

所以如果job不运行还需要检查cjq0是否正常运行。

 

总结下：job为啥没有运行

1，  检查程序时间是否正确

2，  检查job是否还在运行

3，  检查参数job_queue_processes进程是否设置

4，  检查cjq0进程是否运行正常

 

下面是metlink上解决cjq0进程没有启动的方式：

Symptoms

Jobs (dba_jobs/dbms_scheduler_jobs) are not running at specified timestamp.

Checking makes it clear that CJQ0 process is not running.

 

 

Changes

The problem might have been introduced in pre-migrate/upgrade steps which have disabled scheduler.

 

Cause

The scheduler was disabled which implies that no CJQ0 process was started as part of database startup. The CJQ0 process is the one which processes the dba_jobs/dbms_scheduler_jobs.

 

Solution

1) Check if the scheduler is indeed disabled:

 

SQL>

connect / as sysdba

set pagesize 9999

select * from dba_objects where object_name='SCHEDULER_DISABLED';

 

-- In case a row as following is returned it implies that scheduler is disabled:

 

OWNER

------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

SUBOBJECT_NAME                 OBJECT_ID  DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- -------------------

CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S NAMESPACE

--------- --------- ------------------- ------- - - - ----------

EDITION_NAME

------------------------------

SYS

SCHEDULER_DISABLED

                                    71649                UNDEFINED

03-FEB-10 03-FEB-10 2010-02-03:10:29:55 VALID   N N N 51

2) Enable the scheduler:

SQL>

connect / as sysdba

set pagesize 9999

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');Check if scheduler has been enabled:

 

SQL> select * from dba_objects where object_name='SCHEDULER_DISABLED';

 

no rows selectedAlso check the process on OS level, e.g.:

 

$> ps -ef|grep $ORACLE_SID|grep -i cjq0

oracle 14116 1 0 10:30 ? 00:00:00 ora_cjq0_v111