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 two pictures below 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 works fine, but we could not receive email notifications from DBMS_SCHEDULER  jobs.
Solutions:

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;
  • 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.
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;
/

 

Advertisements
This entry was posted in dbms_scheduler and tagged , . Bookmark the permalink.

8 Responses to How to send email notifications for dbms_scheduler jobs

  1. Rick Thomas says:

    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

    • jameshuangsj says:

      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. Rick Thomas says:

    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

    • jameshuangsj says:

      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. Rick Thomas says:

    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

  4. Rick Thomas says:

    James,
    Looks like I had a bug. Check out this link:

    http://www.dba-oracle.com/t_dbms_job_email_failure.htm

    It’s my question and resolution on his forum that he’s referring to.
    Thank you for your help,
    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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s