• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1279
  • Last Modified:

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

0
toooki
Asked:
toooki
  • 3
  • 3
  • 2
1 Solution
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now