Re: Scheduler question
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-lReceived on Tue Dec 17 2019 - 11:39:45 CET