注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

让一切都逝去吧

排骨炖泥菜/猪肝炒苹果/鱼籽狗肉汤/狗头薏米汤

 
 
 

日志

 
 

Oracle job 不执行  

2010-04-26 14:44:26|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

      Oracle里的 job 执行出问题了,plsql developer中看上次执行时间和下次执行时间都正常,也未标明为执行失败,也不是broken状态。但job 执行结果与预期不一致,就与job未执行的情况一样,而在外部手动执行job里的内容,又是成功的。情况是这样的,其中有一些job是需要从另一个数据库复制数据的。搜索oracle job无法执行,发现问题还是蛮多的:

      1) Instance in RESTRICTED SESSIONS mode?
  Check if the instance is in restricted sessions mode:
  select instance_name,logins from v$instance;
  If logins=RESTRICTED, then:
  alter system disable restricted session;

  2) JOB_QUEUE_PROCESSES=0
  Make sure that job_queue_processes is > 0
  show parameter job_queue_processes

  3) _SYSTEM_TRIG_ENABLED=FALSE
  Check if _system_enabled_trigger=false
  col parameter format a25
  col value format a15
  select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b
  Where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

  4) Is the job BROKEN?
  select job,broken from dba_jobs where job=;
  If broken, then check the alert log and trace files to diagnose the issue.

  5) Is the job COMMITted?
  Make sure a commit is issued after submitting the job:
  DECLARE X NUMBER;
  BEGIN
  SYS.DBMS_JOB.SUBMIT
  (
  job => X
  ,what => 'dbms_utility.analyze_schema
  (''SCOTT'',''COMPUTE'',NULL,NULL,NULL);'
  ,next_date => to_date('08/06/200509:35:00','dd/mm/yyyy hh24:mi:ss')
  ,no_parse => FALSE
  );
  COMMIT;
  END;
  /
  If the job executes fine if forced (i.e., exec dbms_jobs.run();), then likely a commit
  is missing.

  6) UPTIME > 497 days
  Check if the server (machine) has been up for more than 497 days:
  For SUN , use 'uptime' OS command.
  If uptime>497 and the jobs do not execute automatically, then you are hitting bug 3427424
  (Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

  7) DBA_JOBS_RUNNING
  Check dba_jobs_running to see if the job is still running:
  select * from dba_jobs_running;

  8) LAST_DATE and NEXT_DATE
  Check if the last_date and next_date for the job are proper:
  select Job,Next_date,Last_date from dba_jobs where job=;
  ^-- NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

  9) NEXT_DATE and INTERVAL
  Check if the Next_date is changing properly as per the interval set in dba_jobs:
  select Job,Interval,Next_date,Last_date from dba_jobs where job=;
  ^-- This is not possible since the job never gets executed automatically.

      测试一下,发现不是上述问题。另外有个情况,当前数据库复制数据的源数据库是这样的情况,有2个用户,用户下面都有相同的数据源表,但表结构略有不同。于是猜测,是否是数据库不知道具体去哪个表里取数据,而不在job里在外部环境执行时,会去找与当前用户名相同的用户下的表,正好有这样的情况,job则好像是在dba下执行的。查看数据库连接(database link),发现使用的数据库连接没有设置用户名和密码,添加进去后,问题竟然解决了。

      我猜测是执行job时,找了未对应的表来复制数据,在执行插入时,由于表结构不一致,出错了,而job执行的内容中,有当执行失败进行回滚的处理。但这个job出问题后,竟然导致其他不需要database link的job也无法执行,这个原因还未弄清楚。

      下面有个解决同样问题的例子,来自:http://hi.baidu.com/yuncsoft/blog/item/a65b9cadc11e6a0e4a36d6cc.html,其中有部分内容还没有试,留着备用:

我们用的生产环境Job是可以正常执行的,但是在测试环境是不可以执行的

在网上查了一下原因如下

SQL> show parameter job

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     0

job_queue_processes 这个参数设置成了0,可以通过语句

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10

来解决。另外,看了一下Eygle的文章也有帮助,下面是链接过来的文章:转自:http://www.eygle.com/archives/2004/11/job_can_not_execute_auto.html

昨天接到研发人员报告,数据库定时任务未正常执行,导致某些操作失败。开始介入处理该事故.

系统环境:

SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

1.首先进入检查数据库任务

 $ 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执行。但是没有执行。

2.建立测试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>)执行没有任何问题。

3.进行恢复尝试
怀疑是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;

4.尝试重起数据库
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仍然不执行。

5.没办法了...

继续研究...居然发现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.

6.安排重起主机系统..

这个问题够郁闷的,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

7.FAQ

一些朋友在Pub上问的问题

Q:对于不同平台,是否存在同样的问题?

A:对于不同平台,存在同样的问题

因为Oracle使用了标准C函数gethrtime

参考:

http://www.eygle.com/unix/Man.Page.Of.gethrtime.htm

使用了该函数的代码都会存在问题.

在Metalink Note:3427424.8 文档中,Oracle定义的平台影响为:Generic (all / most platforms affected)

Q.计数器溢出,看了看job 中基本都是1天左右执行一次,如果设置 3 天执行一次的 job , 是否出问题的uptime 应该是 497*3 之后呢 ?

A:不会

Oracle内部通过计时器来增进相对时间.由于Oracle内部hrtime_t使用了32位计数,那么最大值也就是0xffffffff

0xffffffff = 4294967295

slgcs()是10亿分之一秒,溢出在42949672950000000这个点上.注意,这里0xffffffff,达到这个值时,本来是无符号整型,现在变成了-1,那么这个值递增时,+1 = 0了。时间就此停住了。
      我写了一小段代码来验证这个内容,参考:
[oracle@jumper oracle]$ cat unsign.c
#include
int main(void){
unsigned int num = 0xffffffff;

printf("num is %d bits long\n", sizeof(num) * 8);
printf("num = 0x%x\n", num);
printf("num + 1 = 0x%x\n", num + 1);

return 0;
}

[oracle@jumper oracle]$ gcc -o unsign.sh unsign.c
[oracle@jumper oracle]$ ./unsign.sh
num is 32 bits long
num = 0xffffffff
num + 1 = 0x0

Q:内部时钟之一应该就是这个吧: v$timer 精确到1/100 秒的数据

没错!

注意前面说的:

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.

也就是说如果gethrtime() 操作系统调用返回值大于42949672950000000(单位10亿分之一秒),也就是说Oracle将得到一个cs值为4294967295的时间值,而4294967295值就是0xffffffff,所以当时v$timer的计时也就是:
SQL> select * from v$timer;

     HSECS
----------
4294967295

SQL> /                  

     HSECS
----------
4294967295

SQL> /

     HSECS
----------
4294967295
 

  评论这张
 
阅读(13796)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017