How do I send an email from my Amazon RDS for Oracle DB instance?

Short description

To send an email from an RDS for Oracle DB instance, use the UTL_MAIL or UTL_SMTP packages. To use UTL_MAIL with RDS for Oracle, add the UTL_MAIL option in the non-default option group that's attached to the instance. For more information about how to configure UTL_MAIL, see Oracle UTL_MAIL.

To use ULT_SMTP with RDS for Oracle, configure an SMTP server on an on-premises machine, or use Amazon Simple Email Service (Amazon SES). Confirm that the connectivity from the RDS for Oracle DB instance to the SMTP server is configured correctly.

The following resolution explains how to use Amazon SES to send emails through the UTL_SMTP package.

Prerequisites

Confirm that your Amazon SES endpoint is accessible from your RDS DB instance. If your DB instance runs in a private subnet, then create a virtual private cloud (VPC) endpoint to Amazon SES.

Note: For DB instances that run in a private subnet, you can also use a NAT gateway to communicate with the Amazon SES endpoint.

Configure your DB instance to send emails

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

1. Use Amazon SES to set up the SMTP mail server.
2. Create a VPC endpoint to Amazon SES.
3. Create an Amazon Elastic Compute Cloud (Amazon EC2) Linux instance. Then, use the appropriate certificate to configure the Oracle client and wallet.
4. Upload the wallet to an Amazon Simple Storage Service (Amazon S3) bucket.
5. Use Amazon S3 integration to download the wallet from the Amazon S3 bucket to the Amazon RDS server.
6. For non-primary users, grant the required permissions to the users, and then create the required network access control lists (network ACLs).
7. Use your Amazon SES credentials to send the email.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you use the most recent AWS CLI version.

Set up the SMTP mail server

For instructions on how to use Amazon SES to set up an SMTP mail server, see How do I set up and connect to SMTP using Amazon SES?

Create VPC with Amazon SES

For instructions on how to use Amazon SES to create a VPC, see Setting up VPC endpoints with Amazon SES.

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

Complete the following steps:

1. Create an Amazon EC2 Linux instance.

2. Install an Oracle client.
   Note: It's a best practice to use a client that has the same version as your DB instance. In this resolution, Oracle version 19c is used. To download this client, see Oracle Database 19c (19.3) on the Oracle website. This version comes with the orapki utility.

3. Open the AWS CLI.

4. From the EC2 instance, allow the connection on the database port in the Amazon RDS security group. If the DB instance and EC2 instance use the same VPC, then use their private IP addresses to allow the connection.

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

Complete the following steps:

1. Run the following command to upload the wallet to an Amazon S3 bucket:
   Note: The S3 bucket must be in the same AWS Region as the DB instance.

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

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

   aws s3 ls testbucket

Use Amazon S3 integration to download the wallet to the Amazon RDS server

Complete the following steps:

1. Open the Amazon RDS console, and then create an option group.
2. Add the S3_INTEGRATION option to the option group.
3. Create a DB instance with the option group.
4. Create an AWS Identity and Access Management (IAM) policy and role. For more information, see Configuring IAM permissions for RDS for Oracle integration with Amazon S3.
5. Run the following commands to download the wallet to Amazon 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

For non-primary RDS for Oracle users: Grant the required permissions to the user, and create the required network ACLs

Run the following command to grant the required permissions to the non-primary 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 network 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

To send the email, run the following procedure.

Note: Replace the following values with your values:

• 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 use an on-premises SMTP server or Amazon EC2 as the SMTP server, then replace the Amazon SES information with your on-premises or EC2 server details.

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;  
/

Troubleshoot errors

ORA-29279: If your SMTP username 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 a non-primary user runs the email package, then you might get the following error:

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

Identify the object that doesn't have access, and then grant the required permissions. For example, if certain permissions are missing for SYS-owned objects, such as DBA_directories for expample-username, then run following command:

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

ORA-24247: If you didn't assign the network ACL to the target host, then you get the following error. You also get this error when the user doesn't have the required permissions to access the target host:

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

To resolve this issue, run the following procedure to create a network ACL and assign the network ACL to the host:

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;  
/

ORA-29278: If you don't correctly configure the security groups, firewall or network ACL, then you get the following error:

"ORA-29278: SMTP transient error: 421 Service not available"

To resolve this issue, make sure that you correctly set up the network configuration. You can also review the VPC flow logs for the following information:

• Analyze source and destination IP addresses: From the VPC flow logs, verify that data that's transmitted from the source and destination IP addresses receive responses.
• Inspect port and protocol: Confirm that the correct port and protocol are used and that there are no unusual discrepancies.
• Security group and network ACLs: Check security group and network ACL configurations to confirm that they allow traffic on the necessary port.
• Subnet routing: Validate that the routing tables in the relevant subnets are correctly configured to route traffic to the database server.
• Latency and packet Loss: Look for latency or packet loss. Latency and packet loss might indicate network issues.

For more information, see Logging IP traffic using VPC Flow Logs and Troubleshooting ORA-29278 and ORA-29279 when using UTL_SMTP (Doc ID 2287232.1) on the Oracle website.

ORA-29279: If you didn't create an identity on Amazon SES, then you might get the following error:

"ORA-29279: SMTP permanent error: 554 Message rejected: Email address is not verified. The following identities failed the check in region <REGION>:'example-sender-email'"

To resolve this issue, configure an identity at the domain level, or create an email address identity. For more information, see Creating and verifying identities in Amazon SES.

Test connectivity from Amazon RDS to your Amazon SES endpoint

Run the following procedure to test the connection between Amazon RDS and the Amazon SES endpoint:

CREATE OR REPLACE FUNCTION fn_check_network  
(p_remote_host in varchar2, -- host name  
 p_port_no in integer default 587  
)  
RETURN number IS  
   v_connection   utl_tcp.connection;  
BEGIN  
   v_connection := utl_tcp.open_connection(REMOTE_HOST=>p_remote_host, REMOTE_PORT=>p_port_no, IN_BUFFER_SIZE=>1024, OUT_BUFFER_SIZE=>1024,   TX_TIMEOUT=>5);  
   RETURN 1;  
EXCEPTION  
   WHEN others THEN  
      return sqlcode;  
END fn_check_network;  
/

SELECT fn_check_network('email-smtp.<region>.amazonaws.com', 587) FROM dual; 

If the procedure is successful, then the function returns 1. If the procedure fails, then the function returns ORA -29260.

Related information

Overview of the email delivery service on the Oracle website

UTL_SMTP on the Oracle website