DB스케쥴러 작업 e-mail로 알려주기

Wednesday, February 27, 2013

Configure a DB Scheduler job to send e-mail notifications

Most of the time you want to configure a job to send e-mail notifications about its state. The different states that a job can raise an event and thus send an email are :


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

job_all_events --> all the above.

First of all we have to provide the DB Scheduler with the SMTP mail server to use and a default email sender to be used:

begin

  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server'); --> Using SMTP default port (25) 
--  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server:777'); --> Using SMTP 777 port 

  dbms_scheduler.set_scheduler_attribute('email_sender','myemail@myserver.com');
  commit;
end;
/

This must be configured once. 

Then assuming that a job named MYJOB has been created run this as the job owner.

begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB',
 recipients => 'myrecipient1@myemail.com,myrecipient2@myemail.com',
  subject => 'Scheduler Job Notification : %job_owner%.%job_name%-%event_type%'
  , body => '%event_type% occurred at %event_timestamp%. %error_message%',
  events => 'job_all_events' );
  commit;

end;
/

The previous command will send an email for all job events to the recipients specified using the default sender. use the following to specify sender and specific job states:

begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB', recipients
  => 'myrecipient1@myemail.com, myrecipient1@myemail.com', sender =>
  'db_job_notification@myemail.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, JOB_SCH_LIM_REACHED');
  commit;

end;
/

By using the following query you have information for which jobs email notifications have been setup.

select job_name,
  recipient,
  event
from user_scheduler_notifications;

REFERENCES

Oracle® Database Administrator's Guide 11g Release 2 (11.2)



http://agstamy.blogspot.kr/2013/02/configure-db-scheduler-job-to-send-e.html