3

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration.

Unfortunately, I couldn't get the example to work for event JOB_OVER_MAX_DUR in Oracle 10gR2 on one particular box.

What I've tried is:

-- create log table
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
  (message IN sys.scheduler$_event_info) as
begin
--
  insert into job_output values (systimestamp,'sniper job started for '
   ||message.event_type||' from '
   ||'"'||message.object_owner||'"."'||message.object_name ||'"');
  commit;
end;
/

-- create a sniper program
begin
  dbms_scheduler.create_program (
      program_name => 'sniper_prog',
      program_action=> 'sniper_proc',
      program_type => 'stored_procedure',
      number_of_arguments => 1,
      enabled => FALSE) ;
--
  dbms_scheduler.define_metadata_argument ('sniper_prog','event_message',1);
  dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to log any job that has
-- raised an event
begin
  dbms_scheduler.create_job('sniper_job',
    program_name=>'sniper_prog',
    event_condition =>
      'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'' OR tab.user_data.event_type = ''JOB_SUCCEEDED''',
    queue_spec =>'sys.scheduler$_event_queue,myagent',
    enabled=>true);
end;
/

-- create job to test the sniper job
begin
  dbms_scheduler.create_job
    ( 'first_job', job_action =>
        'insert into job_output values(systimestamp, ''first job begins'');
         commit; dbms_lock.sleep(120);
         insert into job_output values(systimestamp, ''first job ends'');',
      job_type => 'plsql_block',
      enabled => false ) ;
  -- set max runtime
  dbms_scheduler.set_attribute
    ( 'first_job' , 'max_run_duration' , interval '60' second);
  -- set all events to be raised (for debugging)
  dbms_scheduler.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => dbms_scheduler.job_all_events);
  -- start the job
  dbms_scheduler.enable('first_job');  
end;
/

For some reason the event JOB_OVER_MAX_DUR is either not raised or the sniper_job doesn't receive it. Only JOB_SUCCEEDED is received.

My log table looks like:

SELECT *
  FROM job_output
ORDER BY 1 DESC;


25.07.2012 10:39:11,475879 +02:00   sniper job started for JOB_SUCCEEDED from "SCOTT"."FIRST_JOB"
25.07.2012 10:39:10,155557 +02:00   first job ends
25.07.2012 10:37:10,142660 +02:00   first job begins

So no event JOB_OVER_MAX_DUR was received.

Any ideas what I am doing wrong or which init parameters I have to check/set or how to debug these events?

Edit: I'm interested why this isn't working in 10g. This example is working fine in 11g but not in 10g and I don't see any reason why.

Edit 2: The example is working fine at a different 10g box, but on the intended one it's not. I've checked all the %scheduler% dictionary views, but none of them gives details on which events have been raised. Any ideas/hints how to debug scheduler events? In this case I have DBA rights and OS server level access to debug.

1
  • updated my answer with more info
    – tbone
    Jul 25, 2012 at 18:06

2 Answers 2

3

When adding multiple attributes, try:

DBMS_SCHEDULER.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.some_attribute);

The valid values for event types (11gr2) is here. And according to docs, job_over_max_dur is enabled already for 11gr2.

Now, for 10g or 11gr1, I don't see job_over_max_dur in the list of event types raised by the scheduler. But you mentioned in earlier post that you will very soon migrate to 11gr2, so I assume you have a test box setup already that you can test this with 11gr2. If you really want to go down the rabbit hole with AQ, you can do more custom events, but it would probably be much easier to wait a bit until you're on 11gr2.

Edit

To add more info, first some basic scheduler stuff you can view to see whats running (or scheduled, or stalled, or whatever state):

select * from dba_scheduler_jobs
where owner = 'SOME_OWNER'
and job_name = 'SOME_JOB';

You can see if max_run_duration column is set and also the "raise_events" column will show a list of event types that are to be raised by each job. You should see "JOB_OVER_MAX_DUR" type here.

If you want to test if your db environment is handling the JOB_OVER_MAX_DUR properly, then you can setup a quick test:

BEGIN
    -- setup simple scheduler job
   --DBMS_SCHEDULER.DROP_JOB('EVENT_RAISING_JOB',false);
   DBMS_SCHEDULER.create_job (
      job_name        => 'EVENT_RAISING_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN
                            dbms_lock.sleep(70);
                          END;',
      start_date      => SYSTIMESTAMP,
      end_date        => SYSTIMESTAMP + 2, -- 2 day
      repeat_interval => 'freq=daily; byhour=1',
      enabled         => TRUE);

    DBMS_SCHEDULER.set_attribute(name=>'event_raising_job', attribute=>'max_run_duration', value=>interval '60' second);

   DBMS_SCHEDULER.set_attribute(name => 'event_raising_job', attribute => 'raise_events',value => DBMS_SCHEDULER.job_all_events );

END;

-- setup email notification
exec dbms_scheduler.add_job_email_notification( job_name=>'EVENT_RAISING_JOB',recipients=>'[email protected]', events=>'job_all_events');

-- launch job
exec dbms_scheduler.run_job('EVENT_RAISING_JOB',false);

You should get 3 emails that this job: STARTED, SUCCEEDED, and JOB_OVER_MAX_DUR

9
  • 10gR2 gives ORA-27465: invalid value 514 for attribute RAISE_EVENTS when adding DBMS_SCHEDULER.JOB_OVER_MAX_DUR, but according to the docs it's already enabled and so no need to explicitly set it. Anyway this does not work in 10g and I have no ideas why. Yes, it works in 11g, but I'm curious and want to find out what's missing.
    – GWu
    Jul 25, 2012 at 13:52
  • According to the 11gr2 docs, its already enabled. As I said, I don't see it in the list of attributes that raise events for versions below 11gr2.
    – tbone
    Jul 25, 2012 at 13:54
  • It's listed in the 10gR2 docs: docs.oracle.com/cd/B19306_01/appdev.102/b14258/… at max_run_duration
    – GWu
    Jul 25, 2012 at 13:55
  • No, "job_over_max_dur" is NOT an event type raised by the scheduler 10gr2: docs.oracle.com/cd/B19306_01/appdev.102/b14258/…
    – tbone
    Jul 25, 2012 at 13:58
  • err, well, then the docs are contradicting. My link states for max_run_duration: If this attribute is set to a non-zero and non-NULL value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR.
    – GWu
    Jul 25, 2012 at 14:04
0

The JOB_OVER_MAX_DUR event isn't listed in docs until 11.2, even though MAX_RUN_DURATION is available in versions earlier than that.

Compare 11.2 and 11.1.

Additionally:

  • DBMS_SCHEDULER.JOB_ALL_EVENTS on 11.2 does not include JOB_OVER_MAX_DUR (despite the name).
  • Setting the RAISE_EVENTS attribute will neither set nor remove the JOB_OVER_MAX_DUR event.

The only way it would seem that flag can be set is by setting MAX_RUN_DURATION to a non-null value. Conversely, the only way to remove it is to set MAX_RUN_DURATION to null.

As for why the event is never even raised in 10.2, I suspect that the event may have only been initially implemented for e-mail notifications, and not other event listeners. It appears this is related to a bug I found on Oracle Support (bug #8792892); it implies that a similar problem with using the event in job chains was not fixed until 11.2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.