oracle job使用详解及job不运行的检查方法(二)
sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
JOB不运行的检查步骤：
ORACLE有一种定时调度机制，用dbms_job包来管理。
设置的JOB就是不运行，搞得的郁闷，
最好执行了这个才搞定 exec dbmsijob.setenabled(true);
下面提供一个checklist用于检查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;
^– Checked!
2) JOBQUEUEPROCESSES=0
Make sure that jobqueueprocesses is > 0
show parameter jobqueueprocesses
^– Checked!
3) SYSTEMTRIG_ENABLED=FALSE
Check if systemenabled_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=’systemtrig_enabled’;
If systemtrig_enabled=false, then
alter system set “systemtrig_enabled”=TRUE scope=both;
^– 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:
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => ‘dbmsutility.analyzeschema
(”SCOTT”,”COMPUTE”,NULL,NULL,NULL);’
,nextdate => todate(’08/06/2005 09: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 unpublished 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) DBAJOBSRUNNING
Check dbajobsrunning to see if the job is still running:
select * from dbajobsrunning;
^– Checked! The job is not running.
LASTDATE and NEXTDATE
Check if the lastdate and nextdate for the job are proper:
select Job,Nextdate,Lastdate from dba_jobs where job=;
^– NEXTDATE is porper, however LASTDATE is null since the job never executes automatically.
9) NEXT_DATE and INTERVAL
Check if the Nextdate is changing properly as per the interval set in dbajobs:
select Job,Interval,Nextdate,Lastdate from dba_jobs where job=;
^– This is not possible since the job never gets executed automatically.
10) Toggle value for JOBQUEUEPROCESSES
Stop and restart CJQ process(es)
alter system set jobqueueprocesses=0 ;
–
alter system set jobqueueprocesses=4 ;
Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)
^– Done but did not help
11) DBMS_IJOB(Non-documented):
Last ditch effort.
Either restart the database or try the following:
exec dbmsijob.setenabled(true);