How to send job notifications with dbms_scheduler via ssl encrypted smtp
1
vote
0
answers
261
views
We have Oracle Enterprise 19 Databases installed on Linux and using job notification via smtp with **Oracle dbms_scheduler** for quite a long time. Defining jobs like this and having all the needed settings like SMTP server, ports etc defined in dbms_scheduler attributes:
dbms_scheduler.add_job_email_notification(
job_name => 'J1',
recipients => 'jobowner@example.com',
events => 'job_all_events');
That worked fine.
Now for obvious reasons our mail environment got encrypted and we need to use SSL to communicate with the SMTP server. A certificate is required and placed into a wallet. We did that using orapki and put the wallet in the filesystem location documented by Oracle $ORACLE_HOME/scheduler/wallet/
orapki wallet create -wallet /orasoft/oracle/product/19.0.0/world/scheduler/wallet/5 -pwd password -auto_login
orapki wallet add -wallet /orasoft/oracle/product/19.0.0/world/scheduler/wallet/5 -trusted_cert -cert ~/MailRootCA.cer
Inside the DB we changed dbms_scheduler settings accordingly:
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
attribute => 'EMAIL_SERVER_ENCRYPTION',
value => 'STARTTLS'); --thats what our mailserver expects
Added the correct ports to host ace's and also a wallet ace:
DBMS_NETWORK_ACL_ADMIN.append_WALLET_ACE(
wallet_path => 'file:/orasoft/oracle/product/19.0.0/world/scheduler/wallet/5/',
ace => xs$ace_type(
privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),
principal_name => 'SYS',
principal_type => xs_acl.ptype_db
)
But somehow it is not working anymore. A trace file is generated each time a mail should be sent with
ORA-29106: Cannot import PKCS #12 wallet.
Has anyone else encountered this issue? And how to get around it?
Thanks.
Asked by nightbird1972
(11 rep)
Dec 28, 2023, 01:23 PM
Last activity: Dec 29, 2023, 01:36 PM
Last activity: Dec 29, 2023, 01:36 PM