ORACLE JOB不能按預期執行的常見原因

最近在一個測試環境中建立物化檢視遠端同步環境，但是遠端檢視一直未按照預期的時間進行重新整理。以至於一開始讓我懷疑ONDEMAND與STARTWITHNEXTBY衝突。但再三確認，發現是我的測試環境中ora_cjq0程式不存在，唉！開始怎麼不往這方面去想呢，修改一下JOB_QUEUE_PROCESSES引數，一切恢復正常,突然發現ORACLE JOB方面的問題還不少，貼一篇METALINK文章，歷數以前碰到的JOB失效的原因，無非以下幾種：

 

JobsNotExecuting Automatically文件ID:註釋:313102.1

Symptoms

Jobs arenolonger executing automatically.

Ifforced(execdbms_job.run(<enter here jobnumber>);),theseexecutefine.

Cause

Tryingthemost common reasons why jobs don't execute automatically and as scheduled:

 

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;

^-- Checked!

 

 

2) JOB_QUEUE_PROCESSES=0

Make sure that job_queue_processes is > 0

show parameter job_queue_processes

^-- Checked!

 

 

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’;

^-- Checked!

 

 

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.

^-- Checked! The job is not broken.

 

 

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.

^-- Checked! The job is committed after submission.

 

 

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

^-- Checked! The server in this case has been up 126 days only

 

 

7) DBA_JOBS_RUNNING

Check dba_jobs_running to see if the job is still running:

select * from dba_jobs_running;

^-- Checked! The job is not 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.

 

 

10) Toggle value for JOB_QUEUE_PROCESSES

Set job_queue_processes=0, wait some time and then and set it back to the original value:

alter system set job_queue_processes=0 ;

--

alter system set job_queue_processes=4 ;

Ref: Bug 2649244 (fixed by: 9015,9203,A000)

^-- Done but did not help

 

11) DBMS_IJOB(Non-documented):

Last ditch effort.

Either restart the database or try the following:

exec dbms_ijob.set_enabled(true);

Ref: Bug 3505718 (Closed, Not a Bug)

@Note 90989.1 (Using DBMS_IJOB) INTERNAL NOTE

 

 

Done but did not help

 

These are the most common causes for this behavior.

 

Solution

The solution ended up to be the server (machine) uptime.

Even though it was up for only 126 days, after the server was rebooted all jobs were able to execute automatically.

 

 To implement the solution, please execute the following steps:

 

1. Shutdown all applications, including databases.

2. Shutdown the server (machine)

3. Restart all applications, including databases.

4. Check that jobs are executing automatically.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/119501/viewspace-611011/，如需轉載，請註明出處，否則將追究法律責任。