Oracle诊断案例：Job任务住手实施

根源：CSDN

　摘要:

　　本文经由历程一次Oracle Job任务异常案例诊断，阐明其缘故原由及措置奖励历程，从外部展现Oracle Job任务调剂及外部计机遇制。

　　效果及环境

　　接到研发职员讲演，数据库按时任务未正常实施，招致某些操纵失败。

　　发源劈脸介入措置奖励该变乱.

　　系统环境:

SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4 
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

　　措置奖励历程

　　首先介入反省数据库任务

 $ sqlplus "/ as sysdba" 
SQL*Plus: Release 9.2.0.3.0 - Production on Wed Nov 17 20:23:53 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,broken,failures from 
dba_jobs; 
JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B FAILURES 
INTERVAL 
---------- --------- ---------------- --------- ---------------- - ---------- 
---------------------------- 
31 16-NOV-04 01:00:02 17-NOV-04 01:00:00 N 0 
trunc(sysdate 1) 1/24 
27 16-NOV-04 00:00:04 17-NOV-04 00:00:00 N 0 
TRUNC(SYSDATE)   1 
35 16-NOV-04 01:00:02 17-NOV-04 01:00:00 N 0 
trunc(sysdate 1) 1/24 
29 16-NOV-04 00:00:04 17-NOV-04 00:00:00 N 0 
TRUNC(SYSDATE)   1 
30 01-NOV-04 06:00:01 01-DEC-04 06:00:00 N 0 
trunc(add_months(sysdate,1),’MM’) 6/24 
65 16-NOV-04 04:00:03 17-NOV-04 04:00:00 N 0 
trunc(sysdate 1) 4/24 
46 16-NOV-04 02:14:27 17-NOV-04 02:14:27 N 0 
sysdate 1 
66 16-NOV-04 03:00:02 17-NOV-04 18:14:49 N 0 
trunc(sysdate 1) 3/24 
8 rows selected.

　　发现JOB任务是都没有正常实施，最早一个应该在17-NOV-04 01:00:00实施。然则没有实施。

　　设立设置装备陈设测试JOB

create or replace PROCEDURE pining 

IS 
BEGIN 
NULL; 
END; 
/ 
variable jobno number; 
variable instno number; 
begin 
select instance_number into :instno from v $instance; 
dbms_job.submit(:jobno, ’pining;’, trunc(sysdate 1/288,’MI’), 
’trunc(SYSDATE 1/288,’’MI’’)’, TRUE, :instno); 
end; 
/

　　发现异样的，不实施。

　　然则经由历程dbms_job.run(<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

posted @ 2011-03-07 20:28 蓝色的天空III 阅读(181) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

蓝色的天空III

Oracle诊断案例：Job任务住手实施

公告