create 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;/
發現同樣的，不執行。
但是通過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 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仍然不執行，而且在再次修改的時候，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.嘗試重起資料庫
這個必須在晚上進行
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正常啟動，但是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.08bash-2.03$ dateFri Nov 19 22:00:14 CST 2004
[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.shnum is 32 bits longnum = 0xffffffffnum + 1 = 0x0[oracle@jumper oracle]$
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.
