1 2 Previous Next 18 Replies Latest reply: Apr 1, 2014 10:59 AM by GregV RSS

    Schedular Job Email Notification

    CycleGeek

      I have used the directions from the following link to set up email notifications.  When I execute the job I don't receive any email?  I have setup the email in OEM under Setup => Notification Methods and tested it successfully.  I've also setup my email address in OEM under preferences  and tested successfully as well.  What am I missing?

       

      I've determined that it's privileges, as I've set the exact same thing up under a sys admin user and it works fine.  What privileges do I need?

       

      Thanks,

      Mark

        • 1. Re: Schedular Job Email Notification
          GregV

          Hi Mark,

           

          You don't need to setup the email in OEM, seeting up the mail server and using the ADD_JOB_EMAIL_NOTIFICATION procedure is enough.

          Have you setup the mail server using the SET_SCHEDULER_ATTRIBUTE procedure? You can check that the server is configured by querying the DBA_SCHEDULER_GLOBAL_ATTRIBUTE.

           

          Are the notifications setup? Can you post the entries of the USER_SCHEDULER_NOTIFICATIONS view for your job?

           

          Thanks

          • 2. Re: Schedular Job Email Notification
            CycleGeek

            I have set the server up and I can see it when I query all_scheduler_global_attribute.

             

            Like I said, this works if I setup and run the exact same job and notification under a user with DBA privs?

             

            JOB_NAME       RECIPIENT           EVENT             BODY          EVENT_FLAG

            MISS_P_PROC    mrprice@xxx.com     JOB_SUCCEEDED     Test body.    2

            • 3. Re: Schedular Job Email Notification
              GregV

              Well, to use the ADD_JOB_EMAIL_NOTIFICATION you just need to be the owner of the job or have the CREATE ANY JOB privilege.

              Is the user for whom you are setting up the notification the owner of the job? If yes then it shouldn't be a privilege issue.

               

              The question seems silly, but you have set up notification for the JOB_SUCCEEDED event, so are you sure the job succeeds when executed from this user? Did you check the USER_SCHEDULER_JOB_RUN_DETAILS?

              • 4. Re: Schedular Job Email Notification
                CycleGeek

                I created the job and the notification logged in as that user, so he should be the owner.

                 

                Actually I'm capturing JOB_SUCCEEDED, JOB_FAILED, JOB_BROKEN, and JOB_DISABLED.  Yes I've verified the job has succeeded.

                • 5. Re: Schedular Job Email Notification
                  GregV

                  So the same thing works for a DBA user but not for a non-DBA user, weird. Check the alert.log file for any error.

                  I've seen that sometimes the email ends up in the spam folder.

                   

                  If no error then if possible post all the steps you did (from job creation to notification setup) with the code (you can hide personal information)

                  • 6. Re: Schedular Job Email Notification
                    CycleGeek

                    Nothing in event log.  I already checked that.

                     

                    begin
                    dbms_scheduler.set_scheduler_attribute('email_server','127.0.0.1:25');
                    dbms_scheduler.set_scheduler_attribute('email_sender','oracle@127.0.0.1'');
                    end;
                    /
                    
                    begin
                    dbms_scheduler.create_job (
                    job_name => 'MISS_P_PROC',
                    job_type => 'PLSQL_BLOCK';
                    job_action => 'begin p_pkg.p_proc; end;',
                    start_date => systimestamp at time zone 'America/New_York',
                    auto_drop => FALSE,
                    enabled => TRUE);
                    end;
                    /
                    
                    begin
                    dbms_scheduler.add_job_email_notification (
                    job_name => 'MISS_P_PROC',
                    recipients => 'mrprice@xxxx.com',
                    subject => 'test Message',
                    body => 'Job error!',
                    events => 'job_failed,job_disabled,job_broken,job_succeeeded');
                    end;
                    /
                    
                    • 7. Re: Schedular Job Email Notification
                      GregV

                      Can you run the following queries and check the results:

                       

                      select * from dba_subscr_registrations;

                       

                      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;

                      • 8. Re: Schedular Job Email Notification
                        CycleGeek

                        I entry in the dba_subscr_registrations;

                         

                        SUBSCRIPTION_NAME is "SYS".SCHEDULER$_EVENT_QUEUE":SCHED$_AGT$99;

                         

                        Do you need other fields?

                         

                        40 entries in the sys.AQ$SCHEDULER$_EVENT_QTAB and I am not able to cut and paste due to work restrictions, but here are a few of the lines.

                         

                        AQ$SCHEDULER$_EVENT_QTAB_E  EXPIRED        27-MAR-14 16.57.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.57.06

                        AQ$SCHEDULER$_EVENT_QTAB_E  EXPIRED        27-MAR-14 16.52.57  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.52.57

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.50.09  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.50.09

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.48.00  (null)  (null)  SYS  JOB_STARTED    WL  MISS_P_PROC  27-MAR-14 04.48.00

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.35.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.35.06

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.33.00  (null)  (null)  SYS  JOB_STARTED    WL  MISS_P_PROC  27-MAR-14 04.33.00

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.27.06  SYS     (null)  SYS  JOB_SUCCEEDED  WL  MISS_P_PROC  27-MAR-14 04.35.27

                        AQ$SCHEDULER$_EVENT_QTAB_E  UNDELIVERABLE  27-MAR-14 16.23.44  SYS     (null)  SYS  JOB_FAILED     WL  MISS_P_PROC  27-MAR-14 04.23.44

                        .

                        .

                        .

                        • 9. Re: Schedular Job Email Notification
                          GregV

                          Can you try to run the job now and check the sys.AQ$SCHEDULER$_EVENT_QTAB table again.

                          It should have an entry for your job with value for MSG_STATE as READY and then PROCESSED once the mail has been sent.

                          • 10. Re: Schedular Job Email Notification
                            CycleGeek

                            The job is running every 15 minutes.  Still no emails.

                             

                            As a test I created the job under myself (superuser) and executed.  I got this line in the AQ$SCHEDULER$_EVENT_QTAB.

                             

                            SCHEDULER$_EVENT_QUEUE  PROCESSED  28-MAR-14 07.24.51  SYS  (null)  (null)  JOB_FAILED   MRPRICE  MISS_P_PROC  28-MAR-14 04.24.51


                            And I received an email that the job_succeeded..

                            • 11. Re: Schedular Job Email Notification
                              CycleGeek

                              As another test I created the same job and notification under another schema that has no superuser privs, and the email was SENT?!

                               

                              For some reason when I create the job under the WL user, the QUEUE in the AQ$SCHEDULER$_EVENT_QTAB table is AQ$SCHEDULER$_EVENT_QTAB_E.


                              When I create it under the mrprice user or the acc user the QUEUE in the AQ$SCHEDULER$_EVENT_QTAB table is SCHEDULER$_EVENT_QUEUE.



                              • 12. Re: Schedular Job Email Notification
                                GregV

                                Yes, same for me. Queue is SCHEDULER$_EVENT_QUEUE when I setup notifications. It seems there's an issue with AQ and PL/SQL notifications. I have an opened SR about it, and still no news. My version is 11.2.0.3.

                                Maybe your WL user has some settings in AQ (Advanced Queuing) and that's why it goes in the AQ$SCHEDULER$_EVENT_QTAB_E.

                                • 13. Re: Schedular Job Email Notification
                                  CycleGeek

                                  How would I check for WL user settings in AQ?

                                   

                                  It appears that the AQ$SCHEDULER$_EVENT_QTAB_E.is the exception queue.  Whenever I run this as the WL user the msg_state is EXPIRED, so maybe that's why it ends up in that queue?

                                  • 14. Re: Schedular Job Email Notification
                                    CycleGeek

                                    The only difference I can think of is that the usesr where the notifications worked (mrprice and acc) did not have any jobs that they owned previously.  The wl user did, and those jobs were imported via expdp originally.  Maybe something came over during the data pump import?

                                    1 2 Previous Next