Enable and Configure DB Scheduler Job notification

 

Here I explain how to enable and configure email notification for DB Scheduler Jobs.

First –

DB account requires you to Enable Scheduler with email Server.

What is Required: your company’s email server.

Once you have this information, executing following PL/SQL block enables Scheduler job to send emails. (notice it just enables , doesn’t send emails yet)

BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
    (attribute => 'email_server'
     ,value     => 'smtp.company.com');
END;
/

Remember Pre-Requisite: In order to execute above code, this DB account requires grants on ‘manage scheduer’

Grant manage scheduler to DB_ACCT

 

Second –

To send email after particular job is successfully completed or failed, this notification should be enabled for that specific job using following code.

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  '<JOB_NAME>',
  recipients =>  'testing@company.com',
  events     =>  'job_succeeded, JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR');
END;
/

Replace: ‘JOB_NAME’ that you want to send email notification for

Email: replace email where you want send the email to

Events: This can be specified if notifying on specific events such only notifying on success, or failure etc..

 

Sample Email:

here is the sample email that you receive .

From: MAILER-DAEMON
Sent: Tuesday, February 07, 2017 11:49 AM
To: <you>
Subject: Oracle Scheduler Job Notification – <JOB_NAME> JOB_SUCCEEDED

Message: 

Job: <JOB_NAME>

Event: JOB_SUCCEEDED

Date: 07-FEB-17 11.00.13.163348 AM -05:00 Log id: 6312345 Job class: DEFAULT_JOB_CLASS Run count: 4 Failure count: 2 Retry count: 0 Error code: 0 Error message:

 

 

 

Read Full Post »