developers

    Forum Stats

  • 3,873,629 Users
  • 2,266,619 Discussions
  • 7,911,595 Comments

Discussions

Alert for dbms scheduler job

User_UHISO
User_UHISO Posts: 12 Employee
edited Sep 26, 2022 9:34AM in General Database Discussions

Hi,

I am facing an issue with a scheduler job I have created. I have created email notifications for some job events like job_completed, job_over_max_duration, etc,but the email alerts are not getting sent. How do I debug this? Did anyone else ever faced this issue? Where can I see if the job is raising these events ?

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,932 Red Diamond

    Did you setup mail server?

    BEGIN
        DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
                                               attribute => 'email_server',
                                               value     => 'your-mail-server-host-name:your-mail-server-port'
                                              );
    END;
    /
    

    SY.

«1

Answers

  • asahide
    asahide Expert Technical Engineer Member Posts: 1,496 Gold Trophy

    Hi,

    Do you want to send email using UTL_MAIL package? Or Others?

    Regards,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,932 Red Diamond

    Did you setup mail server?

    BEGIN
        DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
                                               attribute => 'email_server',
                                               value     => 'your-mail-server-host-name:your-mail-server-port'
                                              );
    END;
    /
    

    SY.

  • User_UHISO
    User_UHISO Posts: 12 Employee

    @asahide , I used SQL Developer to set up the emails, like the following: (cut out the sender and recipient but both of them have oracle email ids, like [email protected]:

    I have not set any email server attribute anywhere

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,932 Red Diamond

    @User_UHISO: I have not set any email server attribute anywhere

    So how would Oracle know where to send email? Oracle doesn't send email itself. It is mail server responsibility. You must tell Oracle mail server host/ip and port.

    SY.

  • User_UHISO
    User_UHISO Posts: 12 Employee

    hi @Solomon Yakobson , sorry I gave half details. This is already set at the database level, as we are receiving email alerts for a few other jobs. So using SQL developer, I have simply set up the notification as shown in the above screenshot.

  • User_UHISO
    User_UHISO Posts: 12 Employee

    @asahide , @Solomon Yakobson , the email server is set. So I am trying to figure out whether the job is even raising the events , like 'job_started', 'job_completed' etc. Do you know of a table where I can see if these events are raised? Couldn't find it in 'user_scheduler_job_run_Details'

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,932 Red Diamond

    DBA_SCHEDULER_NOTIFICATIONS will tell if job notifications are set. DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS will tell you each job run status.

    SY.

    User_UHISO
  • GregV
    GregV Member Posts: 3,106 Gold Crown

    As suggested by Solomon, start by checking from DBA_SCHEDULER_NOTIFICATIONS if notifications are properly set for the job and for the events you're interested in.

    Then, check DBA_SCHEDULER_JOB_RUN_DETAILS to see if the job has run and what the outcome status was.

    Has it ever worked? Does it work for some other jobs?

    User_UHISO
  • User_UHISO
    User_UHISO Posts: 12 Employee
    edited Sep 29, 2022 11:10AM

    @GregV @Solomon Yakobson , @asahide

    The main issue what I have here now is, I am receiving email notifications for job_started, job_succeeded, but not for job_over_max_duration. I have set the maximum run duration to 1 minute 30 seconds. Here is the sql excerpt for the job (I created it using SQL developer):

     SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

      ( name   => 'TEST_MAX_RUN_TIME'

       ,attribute => 'MAX_RUN_DURATION'

       ,value   => TO_DSINTERVAL('+000 00:01:30'));

      SYS.DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

      ( job_name     => 'TEST_MAX_RUN_TIME'

       ,recipients    => '...'

       ,sender      => '...'

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

       ,body       => 'Job: %job_owner%.%job_name%.%job_subname%

    Event: %event_type%

    Date: %event_timestamp%

    Log id: %log_id%

    Job class: %job_class_name%

    Run count: %run_count%

    Failure count: %failure_count%

    Retry count: %retry_count%

    Error code: %error_code

    %Error message: %error_message%'

       ,events      => 'JOB_OVER_MAX_DUR'

       ,filter_condition => NULL);

    Can you help me identify what might be the reason the notification is not coming for the event when job runs over the max_duration specified?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,932 Red Diamond

    Can you help me identify what might be the reason the notification is not coming for the event when job runs over the max_duration specified?

    Please post Oracle version.

    SY.

developers