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?
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 firstname.lastname@example.org JOB_SUCCEEDED Test body. 2
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?
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.
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)
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','email@example.com''); 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 => 'firstname.lastname@example.org', subject => 'test Message', body => 'Job error!', events => 'job_failed,job_disabled,job_broken,job_succeeeded'); end; /
Can you run the following queries and check the results:
select * from dba_subscr_registrations;
from sys.AQ$SCHEDULER$_EVENT_QTAB t;
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
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.
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..
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.
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 126.96.36.199.
Maybe your WL user has some settings in AQ (Advanced Queuing) and that's why it goes in the AQ$SCHEDULER$_EVENT_QTAB_E.
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?
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?