Oracle Scheduler Fail To Send Email Notifications

Posted in: Oracle, Technical Track

In this blog post I would like to share an interesting issue we encountered couple of months ago related to scheduler job email notifications. As some of you may know, starting with Oracle 11.2 you can subscribe to receive notification emails from a scheduler job. You can define an email to be sent on various job events (job_started, job_completed, job_failed etc.). The job email notification is defined with DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION procedure.

I am assuming you already have a configured and working SMTP server. If not, that can be done with DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (attributes: email_server and email_sender).

The issue we encountered was on database version 12.1. After configuring the scheduler jobs and email notification lists, emails were not sent out.

This blog post should give you some guidance on how you can troubleshoot and properly define email job notifications.

The problem:

In our case, we used one “system” account to manage the job email notification subscription. Some of you might use the same approach, having a single and separate account used to manage job email notifications for all other accounts.

Let’s assume we have a job called JOB1 defined in schema IARSOV.

exec dbms_scheduler.create_job(job_name => 'JOB1', job_type => 'PLSQL_BLOCK', job_action => 'begin null; end;');

PL/SQL procedure successfully completed.

If we now try to add the a job notification email for IARSOV.JOB1 with the SYSTEM user we should receive an error: “ORA-24093: AQ agent SCHED$_AGT2$_xx not granted privileges of database user”.

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

BEGIN dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com'); END;

*
ERROR at line 1:
ORA-24093: AQ agent SCHED$_AGT2$_101 not granted privileges of database user
SYSTEM
ORA-06512: at "SYS.DBMS_ISCHED", line 7847
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4063
ORA-06512: at line 1

As a workaround we can grant the necessary privileges with the DBMS_AQADM.ENABLE_DB_ACCESS package used for managing Oracle Database Advanced Queuing (AQ).

exec DBMS_AQADM.ENABLE_DB_ACCESS(agent_name  => 'SCHED$_AGT2$_101', db_username => 'SYSTEM');

PL/SQL procedure successfully completed.

We can confirm the granted privileges via the DBA_AQ_AGENT_PRIVS dictionary view (Line 11):

set lines 120
col agent_name for a40
col db_username for a40

select * from dba_aq_agent_privs;

AGENT_NAME                     DB_USERNAME                    HTTP SMTP
------------------------------ ------------------------------ ---- ----
DB12C_3938_ORCL11G             DBSNMP                         NO   NO
SCHED$_AGT2$_101               IARSOV                         NO   NO
SCHED$_AGT2$_101               SYSTEM                         NO   NO
SCHEDULER$_EVENT_AGENT         SYS                            NO   NO
SCHEDULER$_REMDB_AGENT         SYS                            NO   NO
SERVER_ALERT                   SYS                            NO   NO
HAE_SUB                                                       NO   NO

7 rows selected.

Let’s now try to define job email notification for IARSOV.JOB1:

exec dbms_scheduler.add_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

set pages 200
set lines 200
col owner for a40
col job_name for a40
col recipient for a20
select owner, job_name, recipient, event from dba_scheduler_notifications where job_name = 'JOB1';

OWNER                          JOB_NAME                       RECIPIENT            EVENT
------------------------------ ------------------------------ -------------------- -------------------
IARSOV                         JOB1                           arsov@pythian.com    JOB_FAILED
IARSOV                         JOB1                           arsov@pythian.com    JOB_BROKEN
IARSOV                         JOB1                           arsov@pythian.com    JOB_SCH_LIM_REACHED
IARSOV                         JOB1                           arsov@pythian.com    JOB_CHAIN_STALLED
IARSOV                         JOB1                           arsov@pythian.com    JOB_OVER_MAX_DUR

The notification has been successfully defined, however, upon testing the events email was not sent. In our case, the events were ending up in the exception queue AQ$_SCHEDULER$_EVENT_QTAB_E and there was not much information we could derive from the AQ$ scheduler related tables.

Troubleshooting:

The DBA_SUBSCR_REGISTRATIONS view contains mapping definitions for each schema showing which event_queue:consumer_group it is subscribed to. If we check the subscription definition for IARSOV user we can see the event_queue:consumer_group is linked to USER# 5 which is the SYSTEM user. In this case IARSOV’s AQ agent SCHED$_AGT2$_101 is linked to wrong user.

What we’re interested in is the association between SUBSCRIPTION_NAME and USER# columns.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                                                     USER# STATUS
-------------------------------------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"                                         5 DB REG


SQL> select username, user_id from dba_users where user_id = 5;

USERNAME                          USER_ID
------------------------------ ----------
SYSTEM                                  5

In this case the emails won’t be sent out because the subscription registration is not properly initialized (linked) with the correct user (schema). In order for the notifications to work we need the proper link between the agent and the agent’s owner. In this case “SYS”.”SCHEDULER$_EVENT_QUEUE”:”SCHED$_AGT2$_101″ and the IARSOV schema should be properly linked – notice that the user’s ID is also part of the agent name.

What we now need to do is to drop all job email notifications (in this case only one) for IARSOV jobs. When dropping the last job email notification the subscription registration will be removed from DBA_SUBSCR_REGISTRATIONS.
However, note that you have to drop the job email notifications as the user to which the subscription registration is defined, in this case the SYSTEM user.

Hint: If you don’t know the password for the schema you need to connect to, you can can use the Proxy Authenticated Connection feature as documented in the blog article The power of the Oracle Database “proxy authenticated” connections.

SQL> show user;

USER is "SYSTEM"

SQL>
SQL> exec dbms_scheduler.remove_job_email_notification('IARSOV.JOB1','arsov@pythian.com');

PL/SQL procedure successfully completed.

SQL> select subscription_name, user#, status from dba_subscr_registrations;

no rows selected

Once we clear the subscription, we can properly initialize the link by adding the first job notification with the job schema’s owner. This will properly initialize the event_queue:consumer_group with the correct user. After that we can add multiple job notifications from other users as long as we have appropriate privileges granted.

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG

SQL>

--as IARSOV user.

SQL> show user;
USER is "IARSOV"
SQL>
SQL> exec DBMS_SCHEDULER.add_job_email_notification (job_name => 'IARSOV.JOB1', recipients => 'arsov@pyhian.com');

PL/SQL procedure successfully completed.

SQL>
SQL>

--as SYSTEM user.

SQL> show user;
USER is "SYSTEM"
SQL>
SQL> select subscription_name, user#, status from dba_subscr_registrations;

SUBSCRIPTION_NAME                                       USER# STATUS
-------------------------------------------------- ---------- --------
"SYS"."SCHEDULER$_EVENT_QUEUE":"ILM_AGENT"                  0 DB REG
"SYS"."SCHEDULER$_EVENT_QUEUE":"SCHED$_AGT2$_101"         101 DB REG

SQL>
SQL>
SQL> select username from dba_users where user_id = 101;

USERNAME
----------------
IARSOV

SQL>

Conclusion:

If you decide to use scheduler job email notifications, and also prefer that the notifications management is done by a single user (such as “system”) I would advise you to create a “dummy” job notification (with the job owner’s schema) as soon as you create the first scheduler job. This will link the event_queue:consumer_group to the proper user. Afterwards, once you define the rest of the scheduler job notifications (under the common “system” user), you can clean-up that initial “dummy” job notification.

This behavior (bug) is fixed in 12.2 so that the notifications always go through the user’s AQ agent which defines the notifications.

email

Interested in working with Ivica? Schedule a tech call.

About the Author

Senior Database Consultant

Ivica is an Oracle Certified Master 12c and 11g, and a recognized member of the Oracle ACE Program as an Oracle ACE Associate. He is a blogger and active contributor to the Oracle community and presents at many technology conferences. Known for his deep Oracle expertise and ability to troubleshoot quickly and efficiently, Ivica has the skills to solve problems quickly regardless of size and complexity. He is passionate about database performance and stability.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *