This discussion is archived
3 Replies Latest reply: Sep 13, 2012 4:13 PM by FrancoisvanZyl RSS

DBMS_SCHEDULER

FrancoisvanZyl Newbie
Currently Being Moderated
Hi

I have a problem in that my dbms_scheduler jobs do not send out email notifications.

The email server (smtp_out) has been set correctly -- I can ping the email server from the database server at command line level.

The user_scheduler_notifications table contains the right records with the correct events including JOB_SUCCEEDED.

My jobs run on schedule and succeeds OK.

I get no email.

What pre-requisites are there at database level (packages, parameters, privileges or some such) that I also need to set for the notifications to be emailed ?

I am breaking my head here reading everything I can get my hands on but I know I am missing something somewhere.

Also, is there some table or view somewhere that keeps a log of email sent or, in my case, not sent??

Please help.

Thanks

Francois

Edited by: Francois van Zyl on Sep 13, 2012 2:20 PM
  • 1. Re: DBMS_SCHEDULER
    BobLilly Expert
    Currently Being Moderated
    You can test your SMTP connection from the database server using telnet as follows (replace items in angle-brackets as appropriate):
    telnet <smtp_host> <smtp_port>
    214 HELP 
    EHLO <db_host>
    250-...................Hello .................., pleased to meet you
    250-HELP
    250-SIZE
    250-8BITMIME
    250 PIPELINING
    MAIL FROM:<email_sender>
    250 .................. Sender OK
    RCPT TO:<recipient>
    250 ................... Recipient OK
    DATA
    354 Enter message, end with "." on a line by itself
    Subject: Email testing
    Testing
    .
    250 Message accepted for delivery
    QUIT
    221 ............................ SMTP Service closing transmission channel
    Connection closed.
    Its possible that your SMTP server only allows connections from authorized hosts. Check with your email server administrator.

    Regards,
    Bob
  • 2. Re: DBMS_SCHEDULER
    sb92075 Guru
    Currently Being Moderated
    http://www.orafaq.com/forum/t/183996/136107/
  • 3. Re: DBMS_SCHEDULER
    FrancoisvanZyl Newbie
    Currently Being Moderated
    Thank you for the suggestion -- you are on the right path.

    After researching all over the place I ended up creating a sendmail procedure (which I should have done right off the bat) and got the Oracle error message. I tested this proc on multiple servers and it worked fine every time except for this production box.

    It seems the file /etc/mail/sendmail.mc has a wrong entry in it: dnl define('SMART_HOST','smtp.your.provider')dnl that needs to be corrected.

    Our Linux admin is addressing it.

    Again, Thanks

    Edited by: Francois van Zyl on Sep 13, 2012 4:13 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points