ORACLE下一次奇怪的job停止问题分析及处理

问题描述：     1、job_queue_processes=10     2、alert日志没有任何报错     2、exec dbms_job.run(1819) 手工执行没有问题；     3、使用如下命令修改下次执行时间：     BEGIN     DBMS_JOB.CHANGE(1819,NULL,to_date('05-09-2011 13:30:00','mm-dd-yyyy hh24 i:ss'),'trunc(SYSDATE)+1+1/48');     COMMIT;     END;     /

等过了05-09-2011 13:30:00，查询dba_jobs发现this_date、last_date、next_date都没有变化。     4、查看后台进程     ps -ef | grep ora_     发现没有jcq0 进程和jnnn进程；     从v$processes里查看，也没有这两种进程；     5、修改job_queue_processes       alter system set job_queue_processes=0 scope=both;       alter system set job_queue_processes=60 scope=both;       再次查看后台进程发现JCQ0进程已经起来，但是没有Jnnn进程，且JOB的情况与之前的2、3步骤相同，即手工可以，自动不行            6、uptime       时间为900多天，这是与BUG：3427424不同的地方            解决办法：     修改job_queue_processes为合适的值（根据JOB的数量判断，要足够用）；     重启数据库；     –我重启之后就正常了，如果是3427424BUG的话则需要重启主机。

附3427424.8：

Bug 3427424 – Jobs may stop running after 497 days uptime [ID 3427424.8]

——————————————————————————–            修改时间 24-SEP-2008     类型 PATCH     状态 PUBLISHED  

Bug 3427424  Jobs may stop running after 497 days uptime      This note gives a brief overview of bug 3427424.      The content was last updated on: 06-SEP-2004      Click here for details of each of the sections below.

Affects:     Product (Component) Oracle Server (Rdbms)     Range of versions believed to be affected Versions < 10.1.0.2       Versions confirmed as being affected 9.2.0.4     9.2.0.5          Platforms affected Generic (all / most platforms affected)

Fixed:     This issue is fixed in 9.2.0.6 (Server Patch Set)     10.1.0.2 (Base Release)     

Symptoms: Related To:     (None Specified)      Job Queues     Miscellaneous     

Description     An internal time value may stop incrementing after 497 days of machine uptime.     This can cause DBMS JOBS to stop running after 497 days uptime.

Other symptoms could occur in various places in the code.

Workaround:       Shutdown and restart the machine.

(The exact period may differ between platforms. 497 is for Solaris.)     Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.