服务器 频道

Oracle诊断案例:Job任务停止执行

进行恢复尝试

怀疑是CJQ0进程失效,首先设置JOB_QUEUE_PROCESSES为0,Oracle会杀掉CJQ0及相应job进程

以下为引用的内容:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

   等2~3分钟,重新设置 以下为引用的内容:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 5;

   此时PMON会重起CJQ0进程 以下为引用的内容:

Thu Nov 18 11:59:50 2004 

ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 
Thu Nov 18 12:01:30 2004 
ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY; 
Thu Nov 18 12:01:30 2004 
Restarting dead background process CJQ0 
CJQ0 started with pid=8 
但是Job仍然不执行,而且在再次修改的时候,CJQ0直接死掉了。 
Thu Nov 18 13:52:05 2004 
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 
Thu Nov 18 14:09:30 2004 
ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY; 
Thu Nov 18 14:10:27 2004 
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 
Thu Nov 18 14:10:42 2004 
ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY; 
Thu Nov 18 14:31:07 2004 
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 
Thu Nov 18 14:40:14 2004 
ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY; 
Thu Nov 18 14:40:28 2004 
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 
Thu Nov 18 14:40:33 2004 
ALTER SYSTEM SET job_queue_processes=1 SCOPE=MEMORY; 
Thu Nov 18 14:40:40 2004 
ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY; 
Thu Nov 18 15:00:42 2004 
ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; 

Thu Nov 18 15:01:36 2004 
ALTER SYSTEM SET job_queue_processes=15 SCOPE=MEMORY;

尝试重起数据库,这个必须在晚上进行:

以下为引用的内容:

PMON started with pid=2 
DBW0 started with pid=3 
LGWR started with pid=4 
CKPT started with pid=5 
SMON started with pid=6 
RECO started with pid=7 
CJQ0 started with pid=8 
QMN0 started with pid=9 
....

CJQ0正常启动,但是Job仍然不执行。

没办法了...

继续研究...居然发现Oralce有这样一个bug 以下为引用的内容:

1. Clear description of the problem encountered: 
slgcsf() / slgcs() on Solaris will stop incrementing after 
497 days 2 hrs 28 mins (approx) machine uptime. 

2. Pertinent configuration information 
No special configuration other than long machine uptime. . 
3. Indication of the frequency and predictability of the problem 
100% but only after 497 days. 
4. Sequence of events leading to the problem 
If the gethrtime() OS call returns a value > 42949672950000000 
nanoseconds then slgcs() stays at 0xffffffff. This can 
cause some problems in parts of the code which rely on 
slgcs() to keep moving. 
eg: In kkjssrh() does "now = slgcs(&se)" and compares that 
to a previous timestamp. After 497 days uptime slgcs() 
keeps returning 0xffffffff so "now - kkjlsrt" will 
always return 0. . 
5. Technical impact on the customer. Include persistent after effects. 
In this case DBMS JOBS stopped running after 497 days uptime. 
Other symptoms could occur in various places in the code.

好么,原来是计时器溢出了,一检查我的主机:

以下为引用的内容:

bash-2.03$ uptime 
10:00pm up 500 day(s), 14:57, 1 user, load average: 1.31, 1.09, 1.08 
bash-2.03$ date 
Fri Nov 19 22:00:14 CST 2004

刚好到事发时是497天多一点.ft.

安排重起主机系统..

这个问题够郁闷的,NND,谁曾想Oracle这都成...

Oracle最后声称:

fix made it into 9.2.0.6 patchset

在Solaris上的9206尚未发布...晕.

好了,就当是个经历吧,如果有问题非常不可思议的话,那么大胆怀疑Oracle吧,是Bug,可能就是Bug。

重起以后问题解决,状态如下:

以下为引用的内容:

$ sqlplus "/ as sysdba" 
SQL*Plus: Release 9.2.0.3.0 - Production on Fri Nov 26 09:21:21 2004 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
Connected to: 
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production 
With the Partitioning, OLAP and Oracle Data Mining options 
JServer Release 9.2.0.3.0 - Production 
SQL> select job,last_date,last_sec,next_date,next_sec from user_jobs; 

JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC 
---------- --------- ---------------- --------- ---------------- 
70 26-NOV-04 09:21:04 26-NOV-04 09:26:00 
SQL> / 
JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC 
---------- --------- ---------------- --------- ---------------- 
70 26-NOV-04 09:26:01 26-NOV-04 09:31:00 
SQL> 
SQL> select * from v$timer; 
HSECS 
---------- 
3388153 
SQL> select * from v$timer; 
HSECS 
---------- 
3388319 
SQL> 
FAQ
0
相关文章