JobsNotExecuting Automatically
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=<job_number>;
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(<job_no>);), 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;
if the job is running,check the next two view v$access and v$locked_object to
find out what resources which the job is using are locking by other process.
^-- 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=<job_number>;
^-- 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=<job_number>;
^-- 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 ;
--<Wait for some time>
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.
原文出自:http://space.itpub.net/7813229/viewspace-610829
分享到:
相关推荐
从oracle库中导出自定义的job脚本。
Oracle Job定时任务
Oracle 性能问题一般解决思路.ppt
oracle job 使用详解: 每天1点执行的oracle JOB样例
oracleJob创建job的脚本。 sql语句
oracle job实例 测试通过
简单的oracle job脚本,如有需求,清参考网上其他资源
oracle JOB常见的执行时间, 在初学者定义JOB时,对于执行时间往往不知道如何设置. 该文档列举了常见的定义方式.可以由此进行扩展.
oracle job
Oracle JOB 定时任务 定时执行存储过程
oraclejob例子
关于oracle 数据库中job任务如何创建、查询和修改、如何启用job和停止job,可以帮助新手了解job的使用,包括任务运行时间的设置,具体的代码很实用,希望对你有帮助
自己查阅资料后总结的一点Job的使用方法和技巧
创建oracle job ,初学,遇到的问题日志记录
oracle job 的使用总结,通过dbms 创建job,从而执行存储过程
oracle的job定时,henyouyongde ziliao
这是本人在实践中总结的关于oracle中job的一些总结。希望能帮助大家在理论上和实际中解决一些问题。
写好的ORACLE创建JOB的脚本,可直接替换本次执行时间,脚本中的循环执行时间为每天晚上9点,想换的话网上都有
总结如何使oracle job有效停止 不建议使用job