Tuesday, March 3, 2015

Configure email server to send job notifcations- Oracle

Sample for adding scheduler e-mail notification

    Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25:

SQL> connect / as sysdba
Connected.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');

PL/SQL procedure successfully completed.

        where:

             host is the host name or IP address of the SMTP server.
            port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.

        If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.

Optional Setup (Default email sender )

    Using the same procedure we have set the email_sender attribute to set the default email address for the email notifications:

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');

PL/SQL procedure successfully completed.

Confirm the email server has been set

Using the get_scheduler_attribute procedure we can check the current values of both email_server and email_sender attributes.

declare
 v_att VARCHAR2(64);
 v_att2 varchar2(64);
 BEGIN
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
 dbms_output.put_line('server: ' || v_att);
 dbms_output.put_line('sender: ' || v_att2);
 END;
 /


 Create Sample Job

    Connecting to the test user to create a sample job and created the test_mail job, that will start after 10 seconds from now.

SQL> BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'TEST_MAIL',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 for i in 1..200 loop
 for j in 1..200 loop
 null;
 end loop;
 end loop;
 end;
 ',
 start_date => systimestamp + interval '10' second ,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 enabled => True,
comments => '');
 END;
 /

Add Job Notifications to send emails :


    Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used. Also we used job_all_events that will send mail notification for all events:

SQL> BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_MAIL',
 recipients => 'arvind@organization.net',
 sender => 'DBMS_NOTIFICATION@organization.net',
 subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'job_all_events');
 END;
 /

PL/SQL procedure successfully completed.


    To review the scheduler email notifications setup, we will use the newly introduced views:
    USER | ALL | DBA_SCHEDULER_NOTIFICATIONS

SQL> desc user_SCHEDULER_NOTIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

SQL> select EVENT from user_SCHEDULER_NOTIFICATIONS where job_name='TEST_MAIL';

EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR

10 rows selected


Finally, after all the testing is done we can now remove the (Just) email notification part for the job:

SQL> connect test_job/test_job
Connected.

SQL> exec DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('TEST_MAIL');

PL/SQL procedure successfully completed.

Drop scheduler job :

SQL> BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST_MAIL',
                                defer => false,
                                force => false);
END;
/



Note : this has been tested on Oracle 11g Databases

1 comment: