Forum Stats

  • 3,770,730 Users
  • 2,253,161 Discussions
  • 7,875,566 Comments

Discussions

Schedular Job Email Notification

CycleGeek
CycleGeek Member Posts: 91
edited Apr 1, 2014 11:59AM in Scheduler

I have used the directions from the following link to set up email notifications.  When I execute the job I don't receive any email?  I have setup the email in OEM under Setup => Notification Methods and tested it successfully.  I've also setup my email address in OEM under preferences  and tested successfully as well.  What am I missing?

I've determined that it's privileges, as I've set the exact same thing up under a sys admin user and it works fine.  What privileges do I need?

Thanks,

Mark

«1

Answers

  • GregV
    GregV Member Posts: 3,069 Gold Crown

    Hi Mark,

    You don't need to setup the email in OEM, seeting up the mail server and using the ADD_JOB_EMAIL_NOTIFICATION procedure is enough.

    Have you setup the mail server using the SET_SCHEDULER_ATTRIBUTE procedure? You can check that the server is configured by querying the DBA_SCHEDULER_GLOBAL_ATTRIBUTE.

    Are the notifications setup? Can you post the entries of the USER_SCHEDULER_NOTIFICATIONS view for your job?

    Thanks

  • CycleGeek
    CycleGeek Member Posts: 91

    I have set the server up and I can see it when I query all_scheduler_global_attribute.

    Like I said, this works if I setup and run the exact same job and notification under a user with DBA privs?

    JOB_NAME       RECIPIENT           EVENT             BODY          EVENT_FLAG

    MISS_P_PROC    [email protected]     JOB_SUCCEEDED     Test body.    2

  • GregV
    GregV Member Posts: 3,069 Gold Crown

    Well, to use the ADD_JOB_EMAIL_NOTIFICATION you just need to be the owner of the job or have the CREATE ANY JOB privilege.

    Is the user for whom you are setting up the notification the owner of the job? If yes then it shouldn't be a privilege issue.

    The question seems silly, but you have set up notification for the JOB_SUCCEEDED event, so are you sure the job succeeds when executed from this user? Did you check the USER_SCHEDULER_JOB_RUN_DETAILS?

  • CycleGeek
    CycleGeek Member Posts: 91

    I created the job and the notification logged in as that user, so he should be the owner.

    Actually I'm capturing JOB_SUCCEEDED, JOB_FAILED, JOB_BROKEN, and JOB_DISABLED.  Yes I've verified the job has succeeded.

  • GregV
    GregV Member Posts: 3,069 Gold Crown

    So the same thing works for a DBA user but not for a non-DBA user, weird. Check the alert.log file for any error.

    I've seen that sometimes the email ends up in the spam folder.

    If no error then if possible post all the steps you did (from job creation to notification setup) with the code (you can hide personal information)

  • CycleGeek
    CycleGeek Member Posts: 91

    Nothing in event log.  I already checked that.

    begin
    dbms_scheduler.set_scheduler_attribute('email_server','127.0.0.1:25');
    dbms_scheduler.set_scheduler_attribute('email_sender','[email protected]'');
    end;
    /
    
    begin
    dbms_scheduler.create_job (
    job_name => 'MISS_P_PROC',
    job_type => 'PLSQL_BLOCK';
    job_action => 'begin p_pkg.p_proc; end;',
    start_date => systimestamp at time zone 'America/New_York',
    auto_drop => FALSE,
    enabled => TRUE);
    end;
    /
    
    begin
    dbms_scheduler.add_job_email_notification (
    job_name => 'MISS_P_PROC',
    recipients => '[email protected]',
    subject => 'test Message',
    body => 'Job error!',
    events => 'job_failed,job_disabled,job_broken,job_succeeeded');
    end;
    /
    
  • GregV
    GregV Member Posts: 3,069 Gold Crown

    Can you run the following queries and check the results:

    select * from dba_subscr_registrations;
    

    select queue,
           msg_state,
           enq_time,
           enq_user_id,
           deq_time,
           deq_user_id,
           t.user_data.event_type,
           t.user_data.object_owner,
           t.user_data.object_name,
           t.user_data.event_timestamp
      from sys.AQ$SCHEDULER$_EVENT_QTAB t;
    
  • CycleGeek
    CycleGeek Member Posts: 91

    I entry in the dba_subscr_registrations;

    SUBSCRIPTION_NAME is "SYS".SCHEDULER$_EVENT_QUEUE":SCHED$_AGT$99;

    Do you need other fields?

    40 entries in the sys.AQ$SCHEDULER$_EVENT_QTAB and I am not able to cut and paste due to work restrictions, but here are a few of the lines.

    AQ$SCHEDULER$_EVENT_QTAB_E  EXPIRED        27-MAR-14 16.57.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.57.06

    AQ$SCHEDULER$_EVENT_QTAB_E  EXPIRED        27-MAR-14 16.52.57  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.52.57

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.50.09  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.50.09

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.48.00  (null)  (null)  SYS  JOB_STARTED    WL  MISS_P_PROC  27-MAR-14 04.48.00

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.35.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.35.06

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.33.00  (null)  (null)  SYS  JOB_STARTED    WL  MISS_P_PROC  27-MAR-14 04.33.00

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.27.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.35.27

    AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.23.44  SYS     (null)  SYS  JOB_FAILED     WL  MISS_P_PROC  27-MAR-14 04.23.44

    .

    .

    .

  • GregV
    GregV Member Posts: 3,069 Gold Crown

    Can you try to run the job now and check the sys.AQ$SCHEDULER$_EVENT_QTAB table again.

    It should have an entry for your job with value for MSG_STATE as READY and then PROCESSED once the mail has been sent.

  • CycleGeek
    CycleGeek Member Posts: 91
    edited Mar 28, 2014 7:29AM

    The job is running every 15 minutes.  Still no emails.

    As a test I created the job under myself (superuser) and executed.  I got this line in the AQ$SCHEDULER$_EVENT_QTAB.

    SCHEDULER$_EVENT_QUEUE  PROCESSED  28-MAR-14 07.24.51  SYS  (null)  (null)  JOB_FAILED   MRPRICE  MISS_P_PROC  28-MAR-14 04.24.51


    And I received an email that the job_succeeded..

This discussion has been closed.