Re: Scheduler question

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Tue, 17 Dec 2019 10:39:45 +0000
Message-ID: <CALe4Hp=tNfxRvRpxDtnifoDZOpXyM3zQWAH7TjHQooxj=JXZ1Q_at_mail.gmail.com>



I was able to get an email notification using the following code. The code below also stops the job when it exceeds the MAX_DURATION attribute.

drop table job_watchdog_log;
create table job_watchdog_log(
  audsid int,
  log_date date default sysdate);

create or replace procedure stop_job(
  msg sys.scheduler$_event_info)
is
begin
  insert into job_watchdog_log(audsid) values (sys_context('userenv', 'sessionid'));
  commit;

  dbms_scheduler.stop_job(
    job_name=>
dbms_assert.enquote_name(dbms_assert.schema_name(msg.object_owner), false) ||

                 '.' ||

dbms_assert.enquote_name(msg.object_name)); end;
/
exec dbms_scheduler.drop_job('SCJ_WATCHDOG')
exec dbms_scheduler.drop_program('SCP_WATCHDOG')
exec dbms_scheduler.remove_event_queue_subscriber('job_watchdog')
exec dbms_scheduler.add_event_queue_subscriber('job_watchdog')

exec dbms_scheduler.create_program( -
  program_name=> 'SCP_WATCHDOG', -

  program_action=> 'STOP_JOB', -
  program_type=> 'STORED_PROCEDURE', -
  number_of_arguments=> 1, -
  enabled=> false)

exec dbms_scheduler.define_metadata_argument( -
'SCP_WATCHDOG', 'EVENT_MESSAGE', 1)
exec dbms_scheduler.enable('SCP_WATCHDOG')

exec dbms_scheduler.create_job( -
  job_name=> 'SCJ_WATCHDOG', -
  program_name => 'SCP_WATCHDOG', -
  queue_spec=> 'sys.scheduler$_event_queue,job_watchdog', -   event_condition=> q'{tab.user_data.object_name='SCJ_TEST' and tab.user_data.event_type = 'JOB_OVER_MAX_DUR'}', -   enabled=> true)

exec dbms_scheduler.stop_job('SCJ_TEST') exec dbms_scheduler.drop_job('SCJ_TEST') drop table job_log;

create table job_log(
  audsid int,
  log_date date default sysdate);

create or replace procedure job_proc
is
begin
  insert into job_log(audsid) values (sys_context('userenv', 'sessionid'));   commit;
  dbms_session.sleep(120);
end;
/

exec dbms_scheduler.create_job( -

  job_name=> 'SCJ_TEST', -
  job_type=> 'STORED_PROCEDURE', -
  job_action=> 'job_proc', -

  enabled=> false, -
  auto_drop=> false)

exec dbms_scheduler.set_attribute( -
'SCJ_TEST', -
'MAX_RUN_DURATION', -

  interval '60' second)

exec dbms_scheduler.add_job_email_notification ( -   job_name => 'SCJ_TEST', -
  recipients => 'your_email_account_at_your_domain')

exec dbms_scheduler.enable('SCJ_TEST')

select jl.audsid, jl.log_date, s.logon_time, p.spid   from job_log jl,

       v$session s,
       v$process p

 where jl.audsid = s.audsid(+)
   and s.paddr = p.addr(+);

The following query can be used to check the scheduler event queue (notice that the DELAY_TIMESTAMP is set to 10:25:43 whereas ENQ_TIME is set to 10:24:43; when the job starts it immediately enqueues this message that becomes available only after a minute, which is my MAX_RUN_DURATION setting):

SQL> select msg_id, delay_timestamp, enq_time, qt.user_data.event_type event_type, qt.user_data.object_name object_name, qt.user_data.event_status   2 from sys.aq$scheduler$_event_qtab qt   3 where consumer_name = 'JOB_WATCHDOG'   4 order by enq_time desc fetch first 10 rows only;

MSG_ID                           DELAY_TIMESTAMP                ENQ_TIME
         EVENT_TYPE           OBJECT_NAME
 USER_DATA.EVENT_STATUS
-------------------------------- ------------------------------
------------------- -------------------- ------------------------------
----------------------
99E447E1988C752FE053DB1F13ACCBE9 17-DEC-19 10.25.43.334832 AM   17.12.2019
10:24:43 JOB_OVER_MAX_DUR     SCJ_TEST
       0

The default job email notification which I am getting looks like this:

Job: MY_SCHEMA.SCJ_TEST
Event: JOB_OVER_MAX_DUR
Date: 17-DEC-19 02.24.43.331672 AM PST8PDT Log id: 18823702
Job class: DEFAULT_JOB_CLASS
Run count: 0
Failure count: 0
Retry count: 0
Error code: 0
Error message:

Since you are getting some emails, your scheduler notification settings seem to be okay. To get any notifications from my database, I first setup the email server (I had a postfix server running on the database server that listens on port 25):
exec dbms_scheduler.set_scheduler_attribute('email_server', 'localhost:25')

I ran the code above in the Oracle database 19.5.0.0.191015.

On Mon, 16 Dec 2019 at 17:31, Wenping Bo <wbo1_at_swarthmore.edu> wrote:

> Has anybody configured email notifications successfully in DBMS_SCHEDULER?
> I have one job that got stuck for no apparent reason, I configured an email
> notification when the job run exceeds 30 mins (the job itself usually
> finishes run in less than 5 mins), but I haven't got a notification even if
> it exceeds a day. When I find out, I have to force stop the job, then the
> scheduler kicks off the next job on time (the job runs every ten minutes on
> schedule). I did get a notification for the stopped job, but that is too
> late. It will be nice if the scheduler can detect the hanging job, to put
> an end to it is even better.
>
> Your insights are greatly appreciated.
>
> Wenping
>
> On Sat, Dec 14, 2019 at 4:45 PM Jack Applewhite <
> jack.applewhite_at_austinisd.org> wrote:
>
>> Or, results of SD's Wizard...   FREQ=MONTHLY;BYDAY=1SUN,2SUN,3SUN
>> This method would, possibly, lend itself more easily to, say, 2nd, 3rd,
>> and 4th...... FREQ=MONTHLY;BYDAY=2SUN,3SUN,4SUN
>> --
>> Jack C. Applewhite - Database Administrator
>> Austin I.S.D. - MIS Department
>> 512.414.9250 (wk)
>>
>> I cannot help but notice that there is no problem between us that cannot
>> be solved by your departure.  -- Mark Twain
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
>> behalf of Mladen Gogala <gogala.mladen_at_gmail.com>
>> *Sent:* Saturday, December 14, 2019 12:21
>> *To:* Ilmar Kerm <ilmar.kerm_at_gmail.com>
>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>> *Subject:* Re: Scheduler question
>>
>>
>> Thanks Ilmar!
>> On 12/14/19 1:20 PM, Ilmar Kerm wrote:
>>
>> Byday=Sun;bymonthday=1-21
>>
>> On Sat, 14 Dec 2019 at 19:58, Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>> I have to schedule a job that will run first 3 sundays in the month and
>> am not at all sure how to do it. For now, my only solution is to have a
>> table with all Sundays until  01/01/2100 and exit if the Sunday is the
>> 4th Sunday in the month. Is there a smarter way of doing that through
>> the DBMS_SCHEDULER?
>>
>> Regards
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>> --
>> Ilmar Kerm
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>>
>> Confidentiality Notice: This email message, including all attachments, is
>> for the sole use of the intended recipient(s) and may contain confidential
>> student and/or employee information. Unauthorized use of disclosure is
>> prohibited under the federal Family Educational Rights & Privacy Act (20
>> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
>> 21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
>> may not use, disclose, copy or disseminate this information. Please call
>> the sender immediately or reply by email and destroy all copies of the
>> original message, including attachments.
>>
>
>
> --
> Systems Analyst
> Information Technology Services
> Swarthmore College
> 500 College Ave
> Swarthmore, PA 19081
> (610) 328-7825
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 17 2019 - 11:39:45 CET

Original text of this message