oracle job失效解决一例
最近一个业务库上的JOB老是没法定时执行，也没有任何错误，今天就抽时间看了一下，应该是JOB失效了
手动执行也可以，用dbms_job.run(:job)也是可以正常执行。
metalink看了一下[ID 313102.1]，下面把具体内容贴一下吧，其实网上也都有的
Check the most 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';
If _system_trig_enabled=false, then
alter system set "_system_trig_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:
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/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) 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 proper, 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
Stop and restart CJQ process(es)
alter system set job_queue_processes=0 ;
--
alter system set job_queue_processes=4 ;
Ref: Bug 2649244 (fixed by: 9015, 9203, 10201)
^-- Done but did not help
11) DBMS_IJOB(Non-documented):
Either restart the database or try the following:
exec dbms_ijob.set_enabled(true);
Ref: Bug 3505718 (Closed, Not a Bug)
^-- Done but did not help
12) Check view DBA_SCHEDULER_GLOBAL_ATTRIBUTE for CURRENT_OPEN_WINDOW:
SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';
If a window