A job has been created and it has been running successfully on the 15th of every month. On average, this job runs about 48 hours. I would now like to add email notifications for events job_started and job_succeeded. The oracle version is 19.8. The job runs from the pluggable database. Reading the oracle documentation, it should be a simple process to set up email notifications, but this is not the case for me. I wonder if I am missing a privilege which should have been granted to MYUSER?
I have run the following as sysdba in the CDB$ROOT:
SQL> alter system set smtp_out_server='mysmtp.myorg.net:25' scope=both sid='*';
--Database instances were cycled after adding smtp_out_server
@/u01/oracle/product/19/db/rdbms/admin/utlmail.sql
@/u01/oracle/product/19/db/rdbms/admin/prvtmail.plb;
@$ORACLE_HOME/rdbms/admin/utlsmtp.sql
alter session set container=myplug;
GRANT EXECUTE ON utl_smtp TO myuser;
grant execute on UTL_MAIL to public;
--connected as MYUSER in myplug:
BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
(attribute => 'email_server' ,value => 'mysmtp.myorg.net:25');
END;
/
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'MY_KEY_MAINT',
recipients => 'myself@myorg.com',
events => 'job_started, job_succeeded, job_broken');
END;
/
--No emails recieved for job_started or job_succeeded, etc, but I have the following in the alert log:
Mon Mar 08 12:29:01 2021
Stopping EMNC
Mon Mar 08 12:31:00 2021
MOCK(3):ERROR: Emon failed to start.
Mon Mar 08 12:31:00 2021
Stopping EMNC
Mon Mar 08 12:33:00 2021
MOCK(3):ERROR: Emon failed to start.
I have also tested UTL_MAIL - It works when connected to CDB$ROOT as sysdba. I cannot get UTL_MAIL to work as the SYS or MYUSER users in MYPLUG.
Did I miss granting a privilege? Any suggestions for troubleshooting?
thank you!