How do I configure my Amazon RDS for Oracle instance to send emails?

Short description

To send an email from an Amazon RDS for Oracle instance, you can use UTL_MAIL or UTL_SMTP packages.

• To use UTL_MAIL with RDS for Oracle, you must add UTL_MAIL option in the non-default option group attached with the instance. For more information on configuring UTL_MAIL, see ORACLE UTL_MAIL.
• To use UTL_SMTP with RDS for Oracle, you must configure an SMTP server on an on-premises machine or an Amazon Elastic Compute Cloud (Amazon EC2) instance using Amazon Simple Email Service (Amazon SES). In this case, be sure that the connectivity from RDS for Oracle to the SMTP server is configured correctly.

This article focuses on configuring the DB instance to send emails through the UTL_SMTP package using Amazon SES.

As a prerequisite, be sure that the Amazon SES endpoint is accessible from the RDS instance. If your RDS instance runs in a private subnet, then you must add a NAT gateway in the route table of the subnet. This is required for the subnet to communicate with the Amazon SES endpoint. To check the route table of the subnet, open the Amazon VPC console, and choose Route Tables in the navigation pane.

To configure your DB instance to send emails, do the following:

1. Set up the SMTP mail server. In this article, Amazon SES is used for setting up the SMTP mail server.
2. Create an Amazon EC2 instance. Then, configure the Oracle client and wallet using the appropriate certificate.
3. Upload the wallet to an Amazon Simple Storage Service (Amazon S3) bucket.
4. Download the wallet from the Amazon S3 bucket to the RDS server using S3 integration.
5. Grant the required privileges to the user (if the user is a non-master user), and create the required access control lists (ACLs).
6. Send the email using the Amazon SES credentials and the procedure provided in this article.

Resolution

Set up the SMTP mail server using Amazon SES

For instructions, see How do I set up and connect to SMTP using Amazon SES?

Create an Amazon EC2 instance and configure the Oracle client and wallet

1.    Create an Amazon EC2 Linux instance.

2.    Install the Oracle Client that preferably has the same version as that of the Amazon RDS instance. In this article, Oracle version 19c is used. You can download the Oracle 19c client, see Oracle Database 19c (19.3). This version also comes with the orapki utility.

3.    Install AWS Command Line Interface (AWS CLI).

4.    Allow connection on the database port in the RDS security group from the EC2 instance. If both instances use the same VPC, then allow the connection via their private IP addresses.

5.    Connect to the EC2 instance.

6.    Run the following command to download the AmazonRootCA1 certificate.

wget https://www.amazontrust.com/repository/AmazonRootCA1.pem

7.    Run the following commands to create the wallet:

orapki wallet create -wallet . -auto_login_only
orapki wallet add -wallet . -trusted_cert -cert AmazonRootCA1.pem -auto_login_only

Upload the wallet to Amazon S3

1.    Run the following command to upload the wallet to an Amazon S3 bucket:

Note: Be sure that the S3 bucket is in same Region as the RDS instance for S3 integration to work.

aws s3 cp cwallet.sso s3://testbucket/

2.    Run the following command to verify if the file is uploaded successfully:

aws s3 ls testbucket

Download the wallet to the RDS server using S3 integration

1.    Create an option group using the Amazon RDS console.

2.    Add the S3_INTEGRATION option in the option group that you created. This is required to download the wallet file from Amazon S3 to the RDS instance.

3.    Create an RDS for Oracle instance with the option group that you created.

4.    Prepare for the S3 integration by creating an AWS Identity and Access Management (IAM) policy and role. For more information, see Prerequisites for Amazon RDS for Oracle integration with Amazon S3.

5.    Run the following commands to download the wallet into RDS from the S3 bucket:

SQL> exec rdsadmin.rdsadmin_util.create_directory('S3_WALLET');

PL/SQL procedure successfully completed.

SQL> SELECT OWNER,DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='S3_WALLET';

OWNER             DIRECTORY_NAME            DIRECTORY_PATH
-------------------- ------------------------------ ----------------------------------------------------------------------
SYS             S3_WALLET                /rdsdbdata/userdirs/01

SQL> SELECT
rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'testbucket',
p_directory_name => 'S3_WALLET',
P_S3_PREFIX => 'cwallet.sso')  AS TASK_ID FROM DUAL;

TASK_ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1625291989577-52

SQL> SELECT filename FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('S3_WALLET'));

FILENAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01/
cwallet.sso

Grant the required privileges to the user and create the required ACLs

Note: You need this step if you're using the non-master user for RDS for Oracle.

Run the following command to grant the required privileges to the non-master user:

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'DBA_DIRECTORIES',
        p_grantee   => 'example-username',
        p_privilege => 'SELECT');
end;
/

Run the following commands to create the required ACLs:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'ses_1.xml',
description => 'AWS SES ACL 1',
principal => 'TEST',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'ses_1.xml',
host => 'example-host');
COMMIT;
END;
/

Send the email

Run the following procedure to send the email:

Note: Be sure to replace the following values in the procedure:

• example-server with the name of your SMTP mail server
• example-sender-email with the sender email address
• example-receiver-email with the receiver email address
• example-SMTP-username with your user name
• example-SMTP-password with your password

If you're using on-premises or Amazon EC2 as the SMTP server, then be sure to use the information related to the on-premises or EC2 server instead of Amazon SES.

declare
l_smtp_server varchar2(1024) := 'example-server';
l_smtp_port number := 587;
l_wallet_dir varchar2(128) := 'S3_WALLET';
l_from varchar2(128) := 'example-sender-email';
l_to varchar2(128)  := 'example-receiver-email';
l_user varchar2(128) := 'example-SMTP-username';
l_password varchar2(128) := 'example-SMTP-password';
l_subject varchar2(128) := 'Test mail from RDS Oracle';
l_wallet_path varchar2(4000);
l_conn utl_smtp.connection;
l_reply utl_smtp.reply;
l_replies utl_smtp.replies;
begin
select 'file:/' || directory_path into l_wallet_path from dba_directories where directory_name=l_wallet_dir;
--open a connection
l_reply := utl_smtp.open_connection(
host => l_smtp_server,
port => l_smtp_port,
c => l_conn,
wallet_path => l_wallet_path,
secure_connection_before_smtp => false);
dbms_output.put_line('opened connection, received reply ' || l_reply.code || '/' || l_reply.text);
--get supported configs from server
l_replies := utl_smtp.ehlo(l_conn, 'localhost');
for r in 1..l_replies.count loop
dbms_output.put_line('ehlo (server config) : ' || l_replies(r).code || '/' || l_replies(r).text);
end loop;
--STARTTLS
l_reply := utl_smtp.starttls(l_conn);
dbms_output.put_line('starttls, received reply ' || l_reply.code || '/' || l_reply.text);
--
l_replies := utl_smtp.ehlo(l_conn, 'localhost');
for r in 1..l_replies.count loop
dbms_output.put_line('ehlo (server config) : ' || l_replies(r).code || '/' || l_replies(r).text);
end loop;
utl_smtp.auth(l_conn, l_user, l_password, utl_smtp.all_schemes);
utl_smtp.mail(l_conn, l_from);
utl_smtp.rcpt(l_conn, l_to);
utl_smtp.open_data (l_conn);
utl_smtp.write_data(l_conn, 'Date: ' || to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'From: ' || l_from || utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'To: ' || l_to || utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'Subject: ' || l_subject || utl_tcp.crlf);
utl_smtp.write_data(l_conn, '' || utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'Test message.' || utl_tcp.crlf);
utl_smtp.close_data(l_conn);

l_reply := utl_smtp.quit(l_conn);
exception
when others then
utl_smtp.quit(l_conn);
raise;
end;
/

Troubleshooting errors

ORA-29279: If your SMTP user name or password is inaccurate, then you might get the following error

ORA-29279: SMTP permanent error: 535 Authentication Credentials Invalid

To resolve this issue, verify that your SMTP credentials are accurate.

ORA-00942: If the email package is run by a non-master user, then you might get the following error:

PL/SQL: ORA-00942: table or view does not exist

To resolve this issue, grant the required permissions to the user by running the following procedure:

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'DBA_DIRECTORIES',
        p_grantee   => 'example-username',
        p_privilege => 'SELECT');
end;
/

ORA-24247: If either an ACL isn't assigned to the target host or the user doesn't have the required privileges to access the target host, then you might get the following error:

ORA-24247: network access denied by access control list (ACL)

To resolve this issue, create an ACL and assign the ACL to the host by running the following procedure:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'ses_1.xml',
description => 'AWS SES ACL 1',
principal => 'TEST',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'ses_1.xml',
host => 'example-host');
COMMIT;
END;
/

---

Related information

Oracle documentation for Overview of the email delivery service