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

    Notifications not being sent


      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
          Hi 975222 -

          Here is an earlier thread that might help.

          Re: DBMS_SCHEDULER
          • 2. Re: Notifications not being sent
            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

            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
              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:

              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
                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
                  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
                    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
                      Thank you! Great explaination