Recently, we had a requirement to implement email notifications from services running in OCI. The customer requested email notifications from the DBCS and Operating systems (RHEL 7.9). In this article, I will explain how we achieved it.
OCI Email Delivery Service
OCI’s Email Delivery Service is a cloud-based, reliable, scalable email-sending service provided by Oracle Cloud Infrastructure (OCI). It is designed to allow applications and users to send large volumes of transactional emails to customers or recipients over the Internet. The service is primarily used for sending notifications, alerts, or transactional emails (such as order confirmations, password resets, or promotional messages). It integrates with various services, such as Oracle databases, and other Oracle cloud services, allowing seamless and secure email sending.
To send an email from an Oracle Database System (DB System) on OCI using the UTL_SMTP package, you need to set up and configure your database to use OCI’s Email Delivery Service.
Below are the steps to achieve this, including enabling SSL for secure communication.
A user must be assigned to a group with permission to manage approved senders
Allow group <group name> to use approved senders in compartment <compartment name>
SMTP credentials are necessary to send emails through Email Delivery. Each user is limited to a maximum of two SMTP credentials.
You must set up an approved sender for all “From:” addresses sending mail via Oracle Cloud Infrastructure or mail will be rejected. An approved sender is associated with a compartment and only exists in the region where the approved sender was configured.
Click on the Configuration and note the SMTP Sending Information
If you're sending emails over a secure connection (SSL/TLS), you need to configure an Oracle Wallet that stores the trusted certificates for secure communication.
Root Certificate - DigiCert Global Root G2
Intermediate Certificate - DigiCert Global G2 TLS RSA SHA256 2020 CA1
orapki wallet create -wallet /home/oracle/wallet -auto_login
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle /DigiCertGlobalRootCA.crt"
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle/DigiCertSHA2SecureServerCA.crt"
Verify the Wallet
orapki wallet display -wallet /home/oracle/wallet
You may verify the certs using "echo |openssl s_client -starttls smtp -crlf -showcerts -connect <SMTP Server for e.g smtp.email.ca-toronto-1.oci.oraclecloud.com >:587"
Below is an example PL/SQL block to send an email using UTL_SMTP. It includes authentication and uses SSL/TLS.
|
SET SERVEROUTPUT ON;
DECLARE l_mailhost VARCHAR2(64) := 'smtp.email.us-ashburn-1.oci.oraclecloud.com'; -- Example SMTP server l_port NUMBER := 587; l_username VARCHAR2(500) := 'ocid1.user.oc1..aaaaaaaabbbbbbbbbbbbbbbb@ocid1.tenancy.oc1..aaaaaaaazzibbbbbbbbb.ct.com'; -- SMTP username l_password VARCHAR2(64) := 'xxxxxxxxxxx'; -- SMTP password l_from VARCHAR2(64) := 'aaaaaaaaa@abc.ca'; -- Approved sender email address l_to VARCHAR2(64) := 'bbbbbbbbb@abc.ca'; -- Recipient email address l_subject VARCHAR2(256) := 'Test Email from DBCS system'; l_message VARCHAR2(512) := 'This is a test email sent using UTL_SMTP.'; l_wallet_loc VARCHAR2(64) := 'file:/home/oracle/dbc/commonstore/wallets/ssl'; l_wallet_pwd VARCHAR2(64) := 'xxxxxxxxxxxxx'; l_mail_conn UTL_SMTP.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(l_mail_conn, name || ': ' || header || UTL_TCP.CRLF); END;
BEGIN -- Open a connection to the SMTP server l_mail_conn := utl_smtp.open_connection(host => l_mailhost, port => l_port, wallet_path => l_wallet_loc, wallet_password => l_wallet_pwd, secure_connection_before_smtp => FALSE);
-- Start TLS (if required by the SMTP server) UTL_SMTP.starttls(l_mail_conn);
-- Perform the SMTP handshake UTL_SMTP.ehlo(l_mail_conn, l_mailhost);
-- Authenticate UTL_SMTP.AUTH(c => l_mail_conn, username => l_username, password => l_password, schemes => 'PLAIN'); -- OCI Email delivery only support AUTH_PLAIN and STARTTLS (with TLS 1.2) to authenticate. Do not use AUTH_LOGIN, and utilizing it will result in failure.
-- Specify the sender and recipient UTL_SMTP.mail(l_mail_conn, l_from); UTL_SMTP.rcpt(l_mail_conn, l_to);
-- Write the email content (header + body) UTL_SMTP.open_data(l_mail_conn); send_header('From', '"Testing" <aaaaaaaaa@abc.ca>'); -- -- Approved sender email address send_header('Subject', l_subject);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.CRLF); -- Blank line separating headers from body UTL_SMTP.write_data(l_mail_conn, l_message); UTL_SMTP.close_data(l_mail_conn);
-- Close the SMTP connection UTL_SMTP.quit(l_mail_conn);
DBMS_OUTPUT.put_line('Email sent successfully.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error: ' || SQLERRM); END; / |
sudo yum install mailx -y
Edit the mailx configuration file /etc/mail.rc to set up the SMTP server and credentials for OCI Email Delivery
|
set nss-config-dir=/etc/pki/nssdb/ set smtp-use-starttls set smtp-auth=plain set smtp=smtp.email.ca-toronto-1.oci.oraclecloud.com:587 set from=approvedsender@example.ca set smtp-auth-user=ocid1.user.oc1..aaao@ocid1.tenancy.oc1.bbb tx.com set smtp-auth-password=<password> |
From email address is the one that we registered as an approved sender.
The smtp-auth-user and smtp-auth-password are the credentials we saved in step 2.
|
echo "This is a test email sent via OCI Email Delivery" | mailx -s "Test Email from OCI" recipient@example.com |
Possible errors
If you get an error(s) while running the above PLSQL code, below are the possible causes.
ORA-29024: Certificate validation failure: This error occurs if the SSL certificate isn't trusted by the Oracle Wallet. Ensure the proper CA certificates are imported.
ORA-24263: The certificate of the remote server does not match the target address: This happens if the SSL certificate name doesn’t match the server’s hostname.
Summary
By following these steps, you'll be able to send emails securely from your OCI DB system using the UTL_SMTP package, with SSL enabled.