Oracle Database

Oracle Scheduler fail to send email notifications

4 min read

Jul 21, 2017 12:00:00 AM

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. [code] exec dbms_scheduler.create_job(job_name => 'JOB1', job_type => 'PLSQL_BLOCK', job_action => 'begin null; end;'); PL/SQL procedure successfully completed. [/code] 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". [code] 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 [/code] 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). [code] exec DBMS_AQADM.ENABLE_DB_ACCESS(agent_name => 'SCHED$_AGT2$_101', db_username => 'SYSTEM'); PL/SQL procedure successfully completed. [/code] We can confirm the granted privileges via the DBA_AQ_AGENT_PRIVS dictionary view (Line 11): [code] 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. [/code] Let's now try to define job email notification for IARSOV.JOB1: [code] 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 [/code] 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. [code] 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 [/code] 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. [code] 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 [/code] 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. [code] --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> [/code] 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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Oracle Scheduler fail to send email notifications

Share this

Share this

More resources

Oracle Scheduler Job is Not Running as Expected After Bouncing Databases

OraQuiz #1: Quote a job name in lowercase to drop it with DBMS_SCHEDULER

Oracle: Limiting Query Runtime Without Killing the Session

Ready to unlock value from your data?