7 Replies Latest reply: Mar 25, 2013 9:27 AM by 978204 RSS

    Notifications not being sent

    978225
      Hello,

      I am trying to find out why the Scheduler is not sending out notifications for a job I am running.
      I set up a small job that calls a programm which references a stored procedure. The procedure executes a manual refresh of a materialized view.

      I did set up the SMTP parameters EMAIL_SENDER and EMAIL_SERVER, restarted the instance afterwards and verified that the parameters are set by logging into the Enterprise Manager web console and looking at the page with the email server parameters.

      I did verify that the job did actually start and complete successfully by checking the logs in ALL_SCHEDULER_JOB_RUN_DETAILS

      I did verify that I actually do have the notifications activated by checking ALL_SCHEDULER_NOTIFICATIONS
      There are notifications for all events (JOB_STARTED, JOB_COMPLETED, JOB_SUCCEEDED, JOB_FAILED, etc.) for my job

      I also verfied that the materialized view has actually been refreshed by checking in ALL_MVIEW_REFRESH_TIMES

      I also verified that the email server I am using is sending out emails by sending a test email from the shell using the same sender and recipient addresses as I am using in the notification settings.


      What could the reason be for the scheduler not sending out emails?

      Where would I find any kind of log or error messages for this?

      Thank you in advance...
        • 1. Re: Notifications not being sent
          978204
          Hi 975222 -

          Here is an earlier thread that might help.

          Re: DBMS_SCHEDULER
          • 2. Re: Notifications not being sent
            978225
            Actually, it turned out that I needed to create and configure a network ACL.
            I did not know about that part.

            I created a method send_mail according to
            [http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php]

            Executing that method reported the error message that led me to the cause of the problem.

            The ACL part is described towards the end of [http://matthiashoys.wordpress.com/2012/04/27/ora-06502-ora-24247-calling-utl_mail-from-oracle-11gr2/]
            • 3. Re: Notifications not being sent
              978225
              Ooops - jumped the gun.

              Emails are still not being sent.

              I am able to send emails using the send_mail procedure I created.

              Now I have this test setup:

              Job MAILTEST is calling the stored procedures SEND_MAIL with the same parameters (Sender, recipient, smtp-server) as the scheduler in order to send out a test message.
              That email does* get delivered. However, the job notification is still not* being sent, although all events are enabled:
              JOB_SUCCEEDED
              JOB_FAILED
              JOB_BROKEN
              JOB_COMPLETED
              JOB_STOPPED
              JOB_SCH_LIM_REACHED
              JOB_DISABLED
              JOB_CHAIN_STALLED
              JOB_OVER_MAX_DUR
              JOB_STARTED

              The job status in ALL_SCHEDULER_JOB_RUN_DETAILS shows "SUCCEEDED".

              Any idea what is wrong?
              Where can I check logs regarding this?

              Thank you!

              Edited by: 975222 on 21.03.2013 16:55

              Edited by: 975222 on 21.03.2013 16:56
              • 4. Re: Notifications not being sent
                978225
                I think now I got it.
                In addition to the desired events being set for the notification, they also need to raised by the job.
                Too bad it does not say that anywhere in the documentation...
                • 5. Re: Notifications not being sent
                  978204
                  I do not understand your response.
                  You should have got a notification for JOB_SUCCEEDED and JOB_STARTED regardless of whether the procedure called by the job was raising errors or not.

                  Can you please expand your answer....
                  • 6. Re: Notifications not being sent
                    978225
                    The job does not have to raise errors, but it seems like in order to send JOB_SUCCEEDED and JOB_STARTED notifications, those two events need to be raised.

                    I executed the below command in order to enable raise events for all events:
                    sys.dbms_scheduler.set_attribute( name => '"DN_DWH_CATALOG"."MAILTEST"', attribute => 'raise_events', value => '511');
                    and afterwards I did receive the two notifications just like you said.

                    Then I called this command:
                    sys.dbms_scheduler.set_attribute( name => '"DN_DWH_CATALOG"."MAILTEST"', attribute => 'raise_events', value => '0');
                    to disabled raise_events for all events and restarted my job => no more notifications.

                    Up to now, I thought just enabling notifications for a certain event type for the job would send out emails independent on the raise_event settings of the job.

                    Hope this is clearer now...

                    Thank you...
                    • 7. Re: Notifications not being sent
                      978204
                      Thank you! Great explaination