How to send email notifications for dbms_scheduler jobs

SITUATION

Jobs can be scheduled  from either OEM ( Oracle Enterprise Manager 11g Grid and 12c Cloud ) or database DBMS_SCHEDULER. The following two pictures  show the boxes ticked will trigger notifications when the event happens.

Environment:

                 OEM 12c :       12.1.0.4
                 Database:       11.2.0.4

(picture 1 ) — OEM JOB

OEM Job Email Notification
( picture 2 )– DBMS_SCHEDULER JOB

DBMS_SCHEDULER Job Notification

Email notification for OEM jobs works fine, but we could not receive email notifications from DBMS_SCHEDULER  jobs.

WORKAROUND

Email notification needs to be configured explicitly for database DBMS_SCHEDULER jobs to send email notifications.

  • Ask system administrator to check or configure SMTP server availability from database server.
  • Check and make sure ACL is configured for the database user who runs the DBMS_SCHEDULER job as per “Configure ACL”
  • Set up the SMTP server and the email address
Begin
dbms_scheduler.set_scheduler_attribute('email_server','smtp.mydomain.com:25');
dbms_scheduler.set_scheduler_attribute('email_sender','job_runner@mydomain.com');
End;
variable v_value VARCHAR2(1000);
BEGIN
    DBMS_SCHEDULER.get_scheduler_attribute (
      attribute => '&1',
      value => :v_value);
END;
/
Enter value for 1: email_server
old 3: attribute => '&1',
new 3: attribute => 'email_server',

PL/SQL procedure successfully completed.

SQL> print v_value

V_VALUE
---------------------------------------------------
smtp.mydomain.com:25

SQL> /
Enter value for 1: email_sender
old 3: attribute => '&1',
new 3: attribute => 'email_sender',

PL/SQL procedure successfully completed.

SQL> print v_value

V_VALUE
---------------------------------------------
job_runner@mydomain.com
  • Configure the job with notification as the job owner to run :
 BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_JOB',
 recipients => 'oracle_dba@mydomain.com',
 sender => 'do_not_reply@mydomain.com',
 subject=>'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, OB_SCH_LIM_REACHED');
END;
/

PL/SQL procedure successfully completed.
A notification can be generated for the following events:

 

  • job_all_events
  • job_broken
  • job_chain_stalled
  • job_completed
  • job_disabled
  • job_failed
  • job_over_max_dur
  • job_run_completed
  • job_sch_lim_reached
  • job_started
  • job_stopped
  • job_succeeded
SQL> SELECT JOB_NAME, RECIPIENT, EVENT FROM USER_SCHEDULER_NOTIFICATIONS;
JOB_NAMER   RECIPIENT                   EVENT
----------  --------------------------- -----------------------------
TEST_JOB    oracle_dba@mydomain.com     JOB_FAILED
TEST_JOB    oracle_dba@mydomain.com     JOB_BROKEN
TEST_JOB    oracle_dba@mydomain.com     JOB_SCH_LIM_REACHED
TEST_JOB    oracle_dba@mydomain.com     JOB_DISABLED
 
PLEASE NOTE:  don’t tick the box of email notification for database dbms_scheduler  jobs ( ( picture 2 )– DBMS_SCHEDULER JOB ) , otherwise it will not work again. Hopefully this will be fixed in future. It is not working for OEM 12.1.0.4.
 
  • To remove notifications:
BEGIN
 DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
   job_name => 'TEST_JOB',
 recipients => 'oracle_dba@mydomain.com',
     events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, OB_SCH_LIM_REACHED');
END;
/

8 thoughts on “How to send email notifications for dbms_scheduler jobs”

  1. James,
    Thank you for your blog. Could you explain what you mean by “it will not work again” in the following:
    “PLEASE NOTE: don’t tick the box of email notification for database dbms_scheduler jobs ( ( picture 2 )– DBMS_SCHEDULER JOB ) , otherwise it will not work again. Hopefully this will be fixed in future. It is not working for OEM 12.1.0.4.’

    Do you mean the job won’t run again or email notifications won’t be sent?

    I’m asking because our email DBMS Jobs don’t send notifications on one of our prod databases, but they run when we make a clone of it. SMTP is sending emails from the prod server and so is OEM.
    Thanks again,
    Rick

    Like

    1. Hello Rick,
      Thanks for reading my blog.
      It has been long time. As far as i remember, after everything is configured for DBMS_SCHEDULER JOB, and email notification is working fine. Then if you click the the box of email notification for database dbms_scheduler jobs ( ( picture 2 )– DBMS_SCHEDULER JOB, and save it. The email notifications won’t be received for some reason, it is an OEM bug. not sure it is fixed or not. Certainly the job will run normally. So better configure DBMS jobs email notifications manually, instead of using OEM by clicking the boxes.

      Thanks,
      James

      Like

  2. James,
    Thank you for the quick response!
    I was hoping the bug you mention above was my problem, but I just created a fresh job, being careful to set up the email notifications outside of OEM and still am not receiving notifications. I’ve tested mail from the server outside of Oracle, and it works fine. OEM jobs themselves also send notifications without any issues. It’s just the DBMS jobs that have a problem.

    I have a clone of the database that sends email notifications without a problem as well. Here’s one clue from the clone that might help: msg_state for AQ$SCHEDULER$_EVENT_QTAB on the clone show’s PROCESSED, while it shows READY on the production database that does not send email notifications. Do you have any ideas?

    Thank you very much for your time,
    Rick

    Like

    1. Hi Rick,

      It is a strange problem. since it works on clone database, so there is no ACL problem. Also there is no SMTP problem. Sorry about, I am really not sure why.

      job_queue_processes is not zero ?

      Thanks,
      james

      Like

  3. James,
    job_queue_processes is 100. It may not be high enough, but I assume that too low a value wouldn’t keep notifications from being sent at all.

    We upgraded the clone that sends email notifications to 12.1.0.2. We did this before anyone reported the notification problem. Now that I think of it, the email notifications didn’t start working until after the upgrade.

    Does that give you any clues to the problem?

    Thanks again,
    Rick

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.