email notification using Oracle Job

User_UEQ6Q

Hi,

I am trying to setup a email notification job using Oracle 11gR2. Per online documnetation I have done the following setup, but the email notification job is failing. If you guys have any ideas, please assist.

Step 1:

BEGIN

  DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'oracle.kotaise.com:25');

  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', '[email protected]');

END;

/

Step2:

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'email_notification_job',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN NULL; END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=minutely; bysecond=0',   

    enabled         => TRUE);

END;

/

Step 3:

BEGIN

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (

  job_name   =>  'email_notification_job',

  recipients =>  '[email protected]',

  sender     =>  '[email protected]',

  subject    =>  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',

  body       =>   '%event_type% occurred at %event_timestamp%. %error_message%',

  events     =>  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');

END;

/

Step 4:

I have created the following  job that executes a package which executes a package "test_package.pdata". This package is not created yet, becuase I wanted to see whether the email notification is working or not. As expected the "TEST_JOB" is failed becuase of missing package. But the email notification job "'email_notification_job'", which runs every minute, failed to send an email notification.

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'test_job'

      ,start_date      => SYSTIMESTAMP

      ,repeat_interval => 'freq=minutely; bysecond=0'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'PLSQL_BLOCK'

      ,job_action      => 'BEGIN test_package.pdata; END;'

      ,comments        => NULL

      ,enabled         => TRUE

    );

end;

/

I have queried the DBA_SCHEDULER_NOTIFICATIONS table, and I see that the email notification job did not started.

JOB_NAME                                      RECIPIENT                     EVENT

EMAIL_NOTIFICATION_JOB      [email protected]      JOB_FAILED

EMAIL_NOTIFICATION_JOB      [email protected]      JOB_BROKEN

EMAIL_NOTIFICATION_JOB      [email protected]      JOB_SCH_LIM_REACHED

EMAIL_NOTIFICATION_JOB      [email protected]      JOB_DISABLED

Thanks

GregV

Ok, now I understand your misunderstanding

The ADD_JOB_EMAIL_NOTIFICATION procedure pertains to the job you specify in the job_name parameter. In your case then, email notifications will be sent against your email_notification_job, not to test_job. You need to use the ADD_JOB_EMAIL_NOTIFICATION for every job whose outcome you're interested in. So if you're interested in TEST_JOB run's outcome, then you need to specify this job for the ADD_JOB_EMAIL_NOTIFICATION procedure.

GregV

Hi,

The DBA_SCHEDULER_NOTIFICATIONS show the notification settings, not the notifications that were sent. To see that, run the follwoing query:

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;

Does it show anything for your job?

User_UEQ6Q

Hi GregV,

Thanks for the reply. I have queried the table "AQ$SCHEDULER$_EVENT_QTAB" and saw nothing in the table. is there a way to test whether the SMTP server host is working or not?

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;

GregV

Ok so no mail was sent. Can you run the job manually and see if the given query returns a row:

BEGIN

DBMS_SCHEDULER.RUN_JOB('email_notification_job');

END;

/

User_UEQ6Q

I ran the job manually, but did not see any email notification sent, The query from the view AQ$SCHEDULER$_EVENT_QTAB returned no data.

GregV

I'm just realising that the events you've chosen don't include JOB_SUCEEDED or JOB_COMPLETED. Since your job does nothing, it finishes with the SUCEEDED status, hence no mail received. Try to add this event and you should get the mails.

User_UEQ6Q

Thanks Greg for the quick response. The reason for using this email notification job is to check for any failed/stopped/disabled oracle jobs in the system, and communicate this by sending an email. That's the reason I have included only failed/stopped/disabled events in the notification email job. In my case the TEST_JOB is failing (status=FAILED)  becuase of the missing package (that this job needs to execute). And I think the email notification job should send an email looking at the FAILED status of a job (TEST_JOB) . Please let me know if my thinking is incorrect.

Thanks

GregV

Ok, now I understand your misunderstanding

The ADD_JOB_EMAIL_NOTIFICATION procedure pertains to the job you specify in the job_name parameter. In your case then, email notifications will be sent against your email_notification_job, not to test_job. You need to use the ADD_JOB_EMAIL_NOTIFICATION for every job whose outcome you're interested in. So if you're interested in TEST_JOB run's outcome, then you need to specify this job for the ADD_JOB_EMAIL_NOTIFICATION procedure.

User_UEQ6Q

Awesome. it works now. Thanks for your help in resolving this issue.