Decryption of retry rules after Oracle Database JOB failure

Source: Internet
Author: User

Since no relevant instructions are found in the official documents, the following tests are conducted here to find the retry time rule for oracle Database jobs that fail.

Database Version:

Test Description: A log table and procedure with certain errors are created for running a job. Here, you only need to record the next_date in the figure user_jobs for each job execution to infer the retry rule after the job fails to be executed.
To test the retry rules of jobs, I have done the following work:
Log table and sequence:

Create table job_exec_logs (id number, current_date date, next_date date, failures number, broken varchar2 (2); create sequence seq_job_exec_logs_id; Test procedure
create or replace procedure pro_my_test isbegin  insert into job_exec_logs selectseq_job_exec_logs_id.nextval , sysdate , next_date , failures , broken from user_jobs ;  commit ;  execute immediate 'select * from ddddsfs' ;end ;
Ddddsfs indicates that the Stored Procedure pro_my_test does not exist. That is to say, errors will occur when the Stored Procedure pro_my_test is run (but the log table can still be inserted normally)
Create job:
Var job number; begin sys. dbms_job.submit (job =>: job, what => 'Pro _ my_test; ', next_date => sysdate, interval => 'sysdate + 5'); commit; end; /In fact, there is nothing to say about the entire test process. Let the job run automatically, but wait for the next retry time of the job. Test result:
Dexter @ REPO> select trunc (next_date-lag (next_date, 1) over (order by 4) * 24*60) from (2 select * from job_exec_logs 3 union all 4 select 11111, sysdate, next_date, failures, broken from user_jobs 5 order by 4 nulls first); TRUNC (NEXT_DATE-LAG (NEXT_DATE, 1) OVER (order by 4) * 24*60) ------------------------------------------------------- 2 4 8 15 32 64 128 256 512 1024 1440 already selected 13 rows.The above results and other tests of the author can be judged as follows: 1. The retry time increases progressively. The first push is 2 minutes, 4 minutes, 8 minutes, 16 minutes. 2. When the time exceeds 1440 minutes, that is, 24 hours, the fixed retry time is 1 day. 3. I also learned from other experiments that after more than 16 retries, the job will be marked as broken and next_date will be 4000-1-1, that is, no job retry will be performed. 4. After the oracle database retry time reaches the set next execution time, the set job execution time prevails.For more information, see metalink.Broken Jobs and the Job Queue (Document ID 103349.1)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.