This discussion is archived
4 Replies Latest reply: Apr 8, 2013 1:30 PM by 978225 RSS

Problems with JOB_OVER_MAX_DUR events

978225 Newbie
Currently Being Moderated
Hello,

could somebody please explain to me what the meaning of the event JOB_OVER_MAX_DUR is exactly, and exactly under what condition(s) it is triggered?

I thought it was being triggered when a currently running JOB exceeds its assigned maximum runtime, meaning the time from when it is started to when it completed (i.e. succeeds, fails or is stopped).
This does not seem to be the case though:

I have a job running a chain of programs that started at around 5 am. Here is the corresponding email notification:
     Job: DN_DWH_CATALOG.ETL_JOB
     Event: JOB_STARTED
     Date: 27.03.13 05:04:01,287227 EUROPE/BERLIN
     Log id:
     Job class: DEFAULT_JOB_CLASS
     Run count: 8
     Failure count: 1
     Retry count: 0
     Error code: 0
     Error message:

     
     
The job completed successfully at around noon (see below 2 notifications):
     Job: DN_DWH_CATALOG.ETL_JOB
     Event: JOB_SUCCEEDED
     Date: 27.03.13 12:03:05,085313 EUROPE/BERLIN
     Log id: 3615
     Job class: DEFAULT_JOB_CLASS
     Run count: 9
     Failure count: 1
     Retry count: 0
     Error code: 0
     Error message:

     
Then, for whatever reason, 13 hours later, I receive another notification with a JOB_OVER_MAX_DUR:
     Job: DN_DWH_CATALOG.ETL_JOB
     Event: JOB_OVER_MAX_DUR
     Date: 27.03.13 05:04:01,287227 EUROPE/BERLIN
     Log id:
     Job class: DEFAULT_JOB_CLASS
     Run count: 8
     Failure count: 1
     Retry count: 0
     Error code: 0
     Error message:

The timestamp is identical with the starting time, but the email's timestamp is 28.03.13 1:04am.
Why is this notification being sent, although the job has long before succeeded?
The next run is not until another 4 hours later, since the job is scheduled to run every 24 hours.
Just about 1 hour before the JOB_OVER_MAX_DUR was sent, I had checked the various scheduling status views (ALL_SCHEDULER_JOB_RUN_DETAILS, ALL_SCHEDULER_JOB_LOG, ALL_SCHEDULER_RUNNING_JOBS, ALL_SCHEDULER_CHAINS, etc) and all of them showed that the job had finished and there was no active job in process.

I am wondering if the notification always gets sent out after the max time period has ellapsed, no matter whether the run has already completed or not.
Like saying "I started that job earlier and I don't know if it is still running, but if it is, then it would be over its time limit right now" - that would not make much sense to me, though!

What also irritates me is the "Run count" - does it increment once a run is complete? That would explain why it is different between start and completion...

And one last thing I do not understand is why both START and SUCCEEDED notifications are being sent at the same time, when the job is completed (at 12:03).
Why not send the START notification when the job started (at 5:04) and the SUCCEEDED notification at the point of completion, about 7 hours later?

Thank you in advance...

Edited by: 975222 on 28.03.2013 13:32
  • 1. Re: Problems with JOB_OVER_MAX_DUR events
    Gamblesk Explorer
    Currently Being Moderated
    DB 11.2.0.2

    Did you find any further information on this?

    I ran into the same thing today and I am sure it is something that I am missing.

    Used SQL Developer to set up a simple stored procedure that inserts some rows into a table.
    I set up notifications to mail me on max duration, completion and failure along with a couple of others.

    The max duration is set to 1 hour.

    During my testing I ran the procedure multiple times but I did not receive the successful completion email I did receive the failure message when I caused it to error. Instead 1 hour later I am receiving a max duration email reached for each time I ran the job successfully. Even though the job log shows successful.
  • 2. Re: Problems with JOB_OVER_MAX_DUR events
    978225 Newbie
    Currently Being Moderated
    No, nothing...

    No reply here either, unfortunately...
  • 3. Re: Problems with JOB_OVER_MAX_DUR events
    Gamblesk Explorer
    Currently Being Moderated
    Might have a slight workaround at least to my issue it may help you as well.

    I walked through a couple of examples that did not use the GUI to do the setup of a stored procedure job and for those I correctly received the email notifications that I was not getting before (completed ).

    I compared those examples to what SQL Developer was doing and noticed that those examples did not set Max Run Duration and Schedule Limit.

    I setup my job again with those 2 not checked (properties tab) of new job and I am correctly getting the emails and I so far have not received a message about exceeding the duration.
  • 4. Re: Problems with JOB_OVER_MAX_DUR events
    978225 Newbie
    Currently Being Moderated
    Thank you for the follow-up.

    The thing is that for me, I do need the notification in case the job does actually exceed the max allowed time.
    I just don't need it if the job finishes prior without exceeding the limit.

    Maybe Oracle really does always raise that event when there is a limit specified, just because the job has been started, and then it is up to you to figure out whether the job is still running or whether it has already completed.
    I see no reason why this would make sense, but it's the only explanation I have...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points