APEX MAIL on OCI

PJ

Hi There

I have set up a database on OCI (NOT Autoomous) and configured the SMTP credentials for a user. I have then set the instance email settings in APEX. I note that an ACL has been created.

However when I try to test it there are no errors - but the mails sit in the APEX mail queue.

Are there any detailed logs that I can look at to figure what the H**l is going on?

It is immensly frustrating trying to do what should be straight forward - when under severe pressure as I am sure most of you know already ...

smtp.email.eu-frankf       587       587 NETWORK_ACL_CB64B6B1AEEF78B0E0530B6E130A2519      0000000080002779 SYS
urt-1.oci.oracleclou
d.com

Thx

Paul

PJ

With the help of Oracle support I have managed to resolve this issue and will add a few notes here to assist others.

In short, when using OCI you must use secure mail (TLS/SSL on port 587) which means you need to load certificates into the wallet and configure the wallet settings in APEX Instance settings. The details are available in this note:

Database Cloud Service (DBCS / DBaaS) on OCI: How to Set Up APEX_MAIL (Doc ID 2428364.1)

paul

InoL

 but the mails sit in the APEX mail queue.

Are they in the queue without an error?

Is the mail job running? What does this return:

select state, next_run_date
from dba_scheduler_jobs
where job_name = 'ORACLE_APEX_MAIL_QUEUE'

PJ

Hi Thx,

see below ....

SQL> select state, next_run_date
from dba_scheduler_jobs
where job_name = 'ORACLE_APEX_MAIL_QUEUE' 2   3
 4 /

STATE
---------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SCHEDULED
09-SEP-21 07.05.00.289926 PM +00:00

SQL> select to_char(sysdate,'DD-Mon-YY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYH
---------------------------
09-Sep-21 19:05:38

SQL> select state, next_run_date
from dba_scheduler_jobs
where job_name = 'ORACLE_APEX_MAIL_QUEUE' 2   3 ;

STATE
---------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SCHEDULED
09-SEP-21 07.10.00.521167 PM +00:00


SQL> set pages 100
SQL> select * from apex_mail_log;

no rows selected

SQL> select count(*) from apex_mail_queue;

 COUNT(*)
----------
       15

InoL

Do you see any errors?

select mail_send_count, mail_send_error, last_updated_on
from apex_mail_queue

PJ

no but I can check - just takes time to get over there ....

SQL> select mail_send_count, mail_send_error, last_updated_on
from apex_mail_queue 2 ;

MAIL_SEND_COUNT
---------------
MAIL_SEND_ERROR
--------------------------------------------------------------------------------
LAST_UPDA
---------
             0

03-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

03-SEP-21

             0

07-SEP-21

             0

07-SEP-21

             0

07-SEP-21

             0

09-SEP-21

             0

09-SEP-21

             0

07-SEP-21


15 rows selected.

SQL>

InoL

Hmm, the background job is running, but it is not picking up any mails.

Just a thought: do you have multiple APEX schema's from older versions? Maybe the job from one version is running, and you are looking at the queue table from another version.

Another thought: is APEX installed in a PDB? Are you looking in the same PDB for the job and queue?

PJ

SQL> sho con_name

CON_NAME
------------------------------
TESTDB_PDB1
SQL> select username from all_users where username like 'APEX%';

USERNAME
--------------------------------------------------------------------------------
APEX_210100
APEX_LISTENER
APEX_PUBLIC_USER
APEX_REST_PUBLIC_USER

SQL>

PJ

SQL> select count(*) from dba_jobs;

 COUNT(*)
----------
        0

SQL>

PJ

With the help of Oracle support I have managed to resolve this issue and will add a few notes here to assist others.

In short, when using OCI you must use secure mail (TLS/SSL on port 587) which means you need to load certificates into the wallet and configure the wallet settings in APEX Instance settings. The details are available in this note:

Database Cloud Service (DBCS / DBaaS) on OCI: How to Set Up APEX_MAIL (Doc ID 2428364.1)

paul