Oracle診斷案例-Job任務(wù)停止執(zhí)行Last Updated: Saturday, 2004-11-20 12:47 Eygle ; 昨天接到研發(fā)人員報(bào)告，數(shù)據(jù)庫(kù)定時(shí)任務(wù)未正常執(zhí)行，導(dǎo)致某些操作失敗。 開(kāi)始介入處理該事故.系統(tǒng)環(huán)境:SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4 Oracle9i Enterprise Edition Release 9.2.0.3.0 - ProdUCtion1.首先介入檢查數(shù)據(jù)庫(kù)任務(wù)$ sqlplus '/ as sysdba'SQL*Plus: Release 9.2.0.3.0 - Production on Wed Nov 17 20:23:53 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.3.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - ProductionSQL> 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 FAILURESINTERVAL---------- --------- ---------------- --------- ---------------- - ---------- ----------------------------31 16-NOV-04 01:00:02 17-NOV-04 01:00:00 N0trunc(sysdate+1)+1/2427 16-NOV-04 00:00:04 17-NOV-04 00:00:00 N0TRUNC(SYSDATE) + 135 16-NOV-04 01:00:02 17-NOV-04 01:00:00 N0trunc(sysdate+1)+1/2429 16-NOV-04 00:00:04 17-NOV-04 00:00:00 N0TRUNC(SYSDATE) + 130 01-NOV-04 06:00:01 01-DEC-04 06:00:00 N0trunc(add_months(sysdate,1),'MM')+6/2465 16-NOV-04 04:00:03 17-NOV-04 04:00:00 N0trunc(sysdate+1)+4/2446 16-NOV-04 02:14:27 17-NOV-04 02:14:27 N0sysdate+166 16-NOV-04 03:00:02 17-NOV-04 18:14:49 N0trunc(sysdate+1)+3/248 rows selected. 發(fā)現(xiàn)JOB任務(wù)是都沒(méi)有正常執(zhí)行，最早一個(gè)應(yīng)該在17-NOV-04 01:00:00執(zhí)行。但是沒(méi)有執(zhí)行。2.建立測(cè)試JOBcreate or replace PROCEDURE pining ISBEGIN 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;/發(fā)現(xiàn)同樣的，不執(zhí)行。但是通過(guò)dbms_job.run(<job>)執(zhí)行沒(méi)有任何問(wèn)題。3.進(jìn)行恢復(fù)嘗試懷疑是CJQ0進(jìn)程失效，首先設(shè)置JOB_QUEUE_PROCESSES為0，Oracle會(huì)殺掉CJQ0及相應(yīng)job進(jìn)程SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; 等2~3分鐘，重新設(shè)置SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 5; 此時(shí)PMON會(huì)重起CJQ0進(jìn)程在警報(bào)日志中可以看到以下信息:Thu Nov 18 11:59:50 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 12:01:30 2004ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY;Thu Nov 18 12:01:30 2004Restarting dead background process CJQ0CJQ0 started with pid=8但是Job仍然不執(zhí)行，而且在再次修改的時(shí)候，CJQ0直接死掉了。 Thu Nov 18 13:52:05 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 14:09:30 2004ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY;Thu Nov 18 14:10:27 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 14:10:42 2004ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY;Thu Nov 18 14:31:07 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 14:40:14 2004ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY;Thu Nov 18 14:40:28 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 14:40:33 2004ALTER SYSTEM SET job_queue_processes=1 SCOPE=MEMORY;Thu Nov 18 14:40:40 2004ALTER SYSTEM SET job_queue_processes=10 SCOPE=MEMORY;Thu Nov 18 15:00:42 2004ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;Thu Nov 18 15:01:36 2004ALTER SYSTEM SET job_queue_processes=15 SCOPE=MEMORY;4.嘗試重起數(shù)據(jù)庫(kù)這個(gè)必須在晚上進(jìn)行PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6RECO started with pid=7CJQ0 started with pid=8QMN0 started with pid=9....CJQ0正常啟動(dòng)，但是Job仍然不執(zhí)行。5.沒(méi)辦法了...繼續(xù)研究...居然發(fā)現(xiàn)Oralce有這樣一個(gè)bug1. 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. 好么，原來(lái)是計(jì)時(shí)器溢出了，一檢查我的主機(jī):bash-2.03$ uptime 10:00pm up 500 day(s), 14:57, 1 user, load average: 1.31, 1.09, 1.08bash-2.03$ dateFri Nov 19 22:00:14 CST 2004剛好到事發(fā)時(shí)是497天多一點(diǎn).ft.6.安排重起主機(jī)系統(tǒng)..這個(gè)問(wèn)題夠郁悶的，NND，誰(shuí)曾想Oracle這都成...Oracle最后聲稱(chēng):fix made it into 9.2.0.6 patchset在Solaris上的9206尚未發(fā)布...暈.好了，就當(dāng)是個(gè)經(jīng)歷吧，假如有問(wèn)題非常不可思議的話，那么大膽懷疑Oracle吧，是Bug，可能就是Bug。7.FAQ一些朋友在Pub上問(wèn)的問(wèn)題Q:對(duì)于不同平臺(tái)，是否存在同樣的問(wèn)題?A:對(duì)于不同平臺(tái)，存在同樣的問(wèn)題因?yàn)镺racle使用了標(biāo)準(zhǔn)C函數(shù)gethrtime參考:http://www.eygle.com/unix/Man.Page.Of.gethrtime.htm使用了該函數(shù)的代碼都會(huì)存在問(wèn)題.在Metalink Note:3427424.8 文檔中，Oracle定義的平臺(tái)影響為:Generic (all / most platforms affected)Q.計(jì)數(shù)器溢出，看了看job 中基本都是1天左右執(zhí)行一次，假如設(shè)置 3 天執(zhí)行一次的 job , 是否出問(wèn)題的uptime 應(yīng)該是 497*3 之后呢 ？ A:不會(huì)Oracle內(nèi)部通過(guò)計(jì)時(shí)器來(lái)增進(jìn)相對(duì)時(shí)間.由于Oracle內(nèi)部hrtime_t使用了32位計(jì)數(shù)那么最大值也就是0xffffffff0xffffffff = 4294967295slgcs()是10億分之一秒，溢出在42949672950000000這個(gè)點(diǎn)上.注重，這里0xffffffff，達(dá)到這個(gè)值時(shí)，本來(lái)是無(wú)符號(hào)整型，現(xiàn)在變成了-1，那么這個(gè)值遞增時(shí)，+1 = 0了。 時(shí)間就此停住了。我寫(xiě)了一小段代碼來(lái)驗(yàn)證這個(gè)內(nèi)容，參考：[oracle@jumper oracle]$ cat unsign.c#include int main(void){unsigned int num = 0xffffffff;printf('num is %d bits longn', sizeof(num) * 8);printf('num = 0x%xn', num);printf('num + 1 = 0x%xn', num + 1);return 0;}[oracle@jumper oracle]$ gcc -o unsign.sh unsign.c[oracle@jumper oracle]$ ./unsign.shnum is 32 bits longnum = 0xffffffffnum + 1 = 0x0[oracle@jumper oracle]$ 本文作者:eygle,Oracle技術(shù)關(guān)注者,來(lái)自中國(guó)最大的Oracle技術(shù)論壇itpub.www.eygle.com是作者的個(gè)人站點(diǎn).你可通過(guò)Guoqiang.Gai@gmail.com來(lái)聯(lián)系作者.歡迎技術(shù)探討交流以及鏈接交換.原文出處:http://www.eygle.com/case/Job.Can.Not.Execute.Auto.htm