8 Replies Latest reply: Aug 1, 2014 9:59 AM by 972590 RSS

    email notification using Oracle Job

    972590

      Hi,

       

      I am trying to setup a email notification job using Oracle 11gR2. Per online documnetation I have done the following setup, but the email notification job is failing. If you guys have any ideas, please assist.

       

       

      Step 1:

      BEGIN

        DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'oracle.kotaise.com:25');

        DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'no_reply@kotaise.com');

      END;

      /

       

      Step2:

      BEGIN

        DBMS_SCHEDULER.create_job (

          job_name        => 'email_notification_job',

          job_type        => 'PLSQL_BLOCK',

          job_action      => 'BEGIN NULL; END;',

          start_date      => SYSTIMESTAMP,

          repeat_interval => 'freq=minutely; bysecond=0',   

          enabled         => TRUE);

      END;

      /

      Step 3:

      BEGIN

      DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (

        job_name   =>  'email_notification_job',

        recipients =>  'chartere@kotaise.com',

        sender     =>  'no_reply@kotaise.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');

      END;

      /

      Step 4:

      I have created the following  job that executes a package which executes a package "test_package.pdata". This package is not created yet, becuase I wanted to see whether the email notification is working or not. As expected the "TEST_JOB" is failed becuase of missing package. But the email notification job "'email_notification_job'", which runs every minute, failed to send an email notification.

       

      BEGIN

        SYS.DBMS_SCHEDULER.CREATE_JOB

          (

             job_name        => 'test_job'

            ,start_date      => SYSTIMESTAMP

            ,repeat_interval => 'freq=minutely; bysecond=0'

            ,end_date        => NULL

            ,job_class       => 'DEFAULT_JOB_CLASS'

            ,job_type        => 'PLSQL_BLOCK'

            ,job_action      => 'BEGIN test_package.pdata; END;'

            ,comments        => NULL

            ,enabled         => TRUE

          );

      end;

      /

       

      I have queried the DBA_SCHEDULER_NOTIFICATIONS table, and I see that the email notification job did not started.

       

      JOB_NAME                                      RECIPIENT                     EVENT

      EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_FAILED

      EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_BROKEN

      EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_SCH_LIM_REACHED

      EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_DISABLED

       

      Thanks

        • 1. Re: email notification using Oracle Job
          GregV

          Hi,

           

          The DBA_SCHEDULER_NOTIFICATIONS show the notification settings, not the notifications that were sent. To see that, run the follwoing query:

           

          select queue,

                 msg_state,

                 enq_time,

                 enq_user_id,

                 deq_time,

                 deq_user_id,

                 t.user_data.event_type,

                 t.user_data.object_owner,

                 t.user_data.object_name,

                 t.user_data.event_timestamp

            from sys.AQ$SCHEDULER$_EVENT_QTAB t;

           

          Does it show anything for your job?

          • 2. Re: email notification using Oracle Job
            972590

            Hi GregV,

             

            Thanks for the reply. I have queried the table "AQ$SCHEDULER$_EVENT_QTAB" and saw nothing in the table. is there a way to test whether the SMTP server host is working or not?

             

            select queue,

                   msg_state,

                   enq_time,

                   enq_user_id,

                   deq_time,

                   deq_user_id,

                   t.user_data.event_type,

                   t.user_data.object_owner,

                   t.user_data.object_name,

                   t.user_data.event_timestamp

              from sys.AQ$SCHEDULER$_EVENT_QTAB t;

            • 3. Re: email notification using Oracle Job
              GregV

              Ok so no mail was sent. Can you run the job manually and see if the given query returns a row:

               

              BEGIN

              DBMS_SCHEDULER.RUN_JOB('email_notification_job');

              END;

              /



              • 4. Re: email notification using Oracle Job
                972590

                I ran the job manually, but did not see any email notification sent, The query from the view AQ$SCHEDULER$_EVENT_QTAB returned no data.

                • 5. Re: email notification using Oracle Job
                  GregV

                  I'm just realising that the events you've chosen don't include JOB_SUCEEDED or JOB_COMPLETED. Since your job does nothing, it finishes with the SUCEEDED status, hence no mail received. Try to add this event and you should get the mails.

                  • 6. Re: email notification using Oracle Job
                    972590

                    Thanks Greg for the quick response. The reason for using this email notification job is to check for any failed/stopped/disabled oracle jobs in the system, and communicate this by sending an email. That's the reason I have included only failed/stopped/disabled events in the notification email job. In my case the TEST_JOB is failing (status=FAILED)  becuase of the missing package (that this job needs to execute). And I think the email notification job should send an email looking at the FAILED status of a job (TEST_JOB) . Please let me know if my thinking is incorrect.

                     

                    Thanks

                    • 7. Re: email notification using Oracle Job
                      GregV

                      Ok, now I understand your misunderstanding

                      The ADD_JOB_EMAIL_NOTIFICATION procedure pertains to the job you specify in the job_name parameter. In your case then, email notifications will be sent against your email_notification_job, not to test_job. You need to use the ADD_JOB_EMAIL_NOTIFICATION for every job whose outcome you're interested in. So if you're interested in TEST_JOB run's outcome, then you need to specify this job for the ADD_JOB_EMAIL_NOTIFICATION procedure.

                      • 8. Re: email notification using Oracle Job
                        972590

                        Awesome. it works now. Thanks for your help in resolving this issue.