Oracle email notification stopped working

I used this code to set the Oracle (11gR2) email notification. It used to send out email when the 'MyJOB1' failed. Suddenly the notification stopped working (it does not send email when the job fails).

How could I troubleshoot?
I tested the O/P of :
SELECT *  FROM all_scheduler_global_attribute
 WHERE attribute_name IN ('EMAIL_SERVER', 'EMAIL_SENDER')

on the server and what I get is correct. The o/p I get above is the same that I get on another server on which the email notification is working (continued working).

Is there any way I could troubleshoot...
Thanks
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name   => 'MyJOB1',
    recipients => 'myEmail@yahoo.com',
    sender     => 'noreply@yahoo.com',
    events     => 'job_failed',
    subject    => 'Job Failed: %job_owner%.%job_name%'||' - '||SYS_CONTEXT('USERENV','SERVER_HOST'),
    body       => 'Job Failed: %job_owner%.%job_name% Event: %event_type% Date: %event_timestamp% Error code: %error_code% Error message: %error_message%'
  );
 
  COMMIT;
END;
/

Open in new window

toookiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
One of the possible reasons could be the scheduler itself may not be running.
so when the job has failed it may have failed via the scheduler.
check whether the scheduler is running or not...

0
sdstuberCommented:
was the database recently upgraded to 11 from 10g?

if so, you may be running into an acl issue blocking network access to the smtp server

http://www.experts-exchange.com/A_8429.html
0
toookiAuthor Commented:
Thank you.

The job was created via dbms_scheduler itself.. The scheduled job runs so I assume the dbms_scheduler is running .... but the email is not sent when the job fails (it fails sometime but for a reason not related to scheduler or so).

The database has been in 11gR2 itself (not moved fro 10g). but the account ACL permission could change. But I verified the attcahed code. When the ACL permission is an issue, the attached code used to fail before. But it does not fail so I guess SMTP permission setup may not be an issue...
not sure what else could be an issue...

BEGIN
dbms_scheduler.create_job('MyJOB1',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN
      myProc;
      COMMIT;
END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('01-JAN-2011 10.00.00.000000000 AM CST6CDT','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=HOURLY;INTERVAL=6'
, end_date=>NULL,
job_class=>'DEFAULT_JOB_CLASS', enabled=>FALSE, auto_drop=>FALSE,comments=>
NULL);
dbms_scheduler.enable('"MyJOB1 details"');
COMMIT;
END;
/
DECLARE
l_mailhost VARCHAR2(64) := 'abcd.myserver.com';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
END;

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Wasim Akram ShaikCommented:
I would like to suggest you to change the existing code to capture the error log , whether the program itself is throwing error i guess. catch the error using exception block so as to check whether a smtp connection is invoked or not...!!!!
 
DECLARE
l_mailhost VARCHAR2(64) := 'abcd.myserver.com';
l_mail_conn UTL_SMTP.connection;
var_error_msg varchar2(1000);
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
EXCEPTION WHEN OTHERS THEN
var_error_msg := substr(sqlerrm,1,999);
dbms_output.put_line('Error'||sqlerrm);
---Alternatively you log the error on a table
insert into some_tab values(sqlerrm||to_char(sysdate,'DD-MON-YYYY hh24:mi:ss'));
END;

Open in new window


0
sdstuberCommented:
your dbms_scheduler call has

enabled=>FALSE


so, your job is disabled.  You're probably not getting email because the job isn't failing, it's just not running.
0
toookiAuthor Commented:
sdstuber, the job is enabled...And it runs the way it should...
No the job failed 2-3 times (because of FK violation or field size issues). It showed in the job log but email was not sent...
0
Wasim Akram ShaikCommented:
toooki, agreed that job has run, but sdstuber had suggested  that probable reason for mail not getting sent is due to the setting the enabled flag as FALSE ie., in your scheduler the parameters passed are

job_class=>'DEFAULT_JOB_CLASS', enabled=>FALSE, auto_drop=>FALSE,comments=>
NULL

change this setting to

job_class=>'DEFAULT_JOB_CLASS', enabled=>TRUE, auto_drop=>FALSE,comments=>
NULL

and try again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
toookiAuthor Commented:
Thanks a lot. I will change the settings. It happens to be a production server...I will change the setting and will test run a job to get it to fail..
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.