2 Replies Latest reply: Apr 11, 2012 7:22 AM by pl_sequel RSS

    max_failures never increments and job keeps retrying passed max_failures

    pl_sequel
      Hi all,

      This one has me stumped.

      Running : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, Oracle Label Security, OLAP, Data Mining,
      Oracle Database Vault and Real Application Testing options

      I found the following Metalink bug that explains my exact issue. the only difference is that bug was filed under 10g. We're running 11gR1... and the status was updated to "32 - Not a Bug. To Filer" ?

      Bug 7309810: DBMS_SCHEDULER JOB WITH EXCEPTION HANDLING FAILS MULTIPLE TIMES

      so not sure what the issue is.

      Basically, I have a job which calls a stored program (stored_procedure)... with attribute max_failures=3. However, the run_details never shows the failure count increments (stays at 0).. and the job keeps retrying past the max_failures attempt.

      Any ideas? Is this a documented bug or am I missing something?

      thanks

      Stephane
        • 1. Re: max_failures never increments and job keeps retrying passed max_failures
          RnR
          Hi,

          This is documented behaviour. It looks like you have "RESTARTABLE" set for the job. This flag always causes the job to retry after 1 second, 10 seconds etc up to 6 retries. The number or pattern of retries is not configurable.

          Only when all of the 6 retries have failed or when the next retry would be after the next scheduled job run will the job be marked as failed and the failure count and run_count will be updated.

          The max_failures attribute specifies after what number of failures the job will be disabled and marked as broken.

          It sounds like what you are looking for is a max_retries attribute which does not exist since it is not currently supported.

          If you do want to limit the number of retries, you will have to add application-level code to do this. One way to do this is to set restartable to false and add retry logic to your code.

          See here for a description of "restartable" and of "max_failures"

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHCIDII

          Hope this helps,
          Ravi.
          • 2. Re: max_failures never increments and job keeps retrying passed max_failures
            pl_sequel
            Thanks for that. Yes it helps... I found the documentation though up until know a bit misleading I guess. Coming from dbms_job, the scheduler offers a lot more, and when I saw the max_failures attribute, figured it would allow us to control when the job stops executing. I did read over the documentation a few times, but for some reason, missed the details on the restartable attribute and retry intervals. I was also curious about how that was configured.

            Thanks again.