Automate Amazon RDS backups using the Oracle RMAN utility and upload backup files to Amazon S3

Amazon Relational Database Service (Amazon RDS) for Oracle creates automated snapshots and allows creation of user-initiated manual snapshots in a Region. In addition, Amazon RDS for Oracle supports Oracle native backup tools like Oracle Recovery Manager (RMAN) and Oracle Data Pump.

Many organizations spend considerable time and resources to manage and maintain database backups to meet business and regulatory requirements for data protection and long-term backup retention. Based on your backup compliance policies, you can choose Oracle native tools to backup the RDS for Oracle database during the following scenarios:

• The backup retention time required is greater than maximum retention period (35 days) of RDS automated snapshots and
• The number of RDS manual snapshots exceeds the limits per Region (100 snapshots per Region) for long term retention we can increase the limit as this limit is modifiable as long as the snapshots are needed.

For example, organizations with backup compliance requirements to retain Oracle database backups for seven or ten years with requirement of Point in time recovery (PITR) options can also choose Oracle RMAN to backup RDS for Oracle database and store the backup files on Amazon S3, using S3 for durable, scalable, and cost-effective storage.

You can restore the RMAN database backups in the same or different Region on Amazon EC2 or on premises for disaster recovery purposes, or to demonstrate backup compliance to auditors and regulators to meet business and regulatory requirements.

In this post, we show you how to automate creation of Oracle RMAN backups on an RDS database and upload RMAN backup files to Amazon S3, providing a cost-effective solution to store database backups for longer periods of time. We use AWS Lambda and Amazon SNS to notify database administrators on the status (completion or failure) of the automation task.

Understanding RDS snapshots and Oracle RMAN backups

RDS creates a storage volume snapshot of your DB instance, backing up the entire DB instance. These snapshots are stored in Amazon S3. RDS snapshots include the following:

• RDS automated snapshots creates the first snapshot of a DB instance containing the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental for automated snapshot. The maximum backup retention period of automated snapshots on Amazon RDS is 35 days.
• RDS manual snapshots creates the snapshot of a DB instance containing the data for the full DB instance and cannot be used for point in time recovery (PITR). Manual snapshots are not deleted automatically; they must be explicitly deleted. Visit Quotas and constraints for Amazon RDS for the number of manual snapshots that can be created per Region (the limit does not apply to automated snapshots).

Automated backups and manual snapshots are stored in an S3 bucket that is owned and managed by the Amazon RDS service. Hence, you are not able to see them from your Amazon S3 console.

Oracle Recovery Manager (RMAN) is an Oracle native tool to backup and restore Oracle database at DB level. The RMAN BACKUP command generates a backup set, which is a logical object containing one or more backup pieces. Each backup piece is a physical file in a binary format. Amazon RDS for Oracle uses the rdsadmin.rdsadmin_rman_util package to perform RMAN backups of an Oracle database on the RDS host.

Solution overview

We present a solution to automate creation of RMAN backups on the RDS for Oracle database and upload RMAN backup files to Amazon S3 for longer retention. The following architecture diagram presents an overview of the solution in this post:

The process includes the following steps:

1. The Oracle DBMS job scheduler schedules the creation of Oracle RMAN backups using an Oracle stored procedure on the RDS for Oracle database.
2. The stored procedure copies the RMAN backup files generated on the RDS host to an S3 bucket using Amazon S3 integration.
3. After completion of RMAN backup, the Oracle stored procedure uploads a status file with the success or failure of the task to an S3 bucket.
4. Uploading the status file to the S3 bucket invokes an AWS Lambda function.
5. The Lambda function uses Amazon SNS to send an email notification to database administrators (customers or subscribers) of the success or failure of the task.

The following high level diagram depicts AWS Identity and Access Management (IAM) role and policies created to grant access to AWS resources.

The solution copies Oracle RMAN backups created on RDS host to Amazon S3. We use an Amazon S3 bucket accessible through the IAM role from an RDS instance. The Lambda function uses an IAM role that has an AWS Lambda execution role and IAM policies attached granting access to read file in S3 bucket and publish SNS notification to the subscriber.

Prerequisites

To follow along with this post, you should have the following prerequisites:

• Familiarity with the following AWS services:
  • AWS Command Line Interface (AWS CLI)
  • AWS Lambda
  • Amazon RDS for Oracle
  • Amazon SNS
  • Amazon S3
  • Amazon EC2
  • Amazon Virtual Private Cloud (Amazon VPC)
• An RDS for Oracle database (or a restore from a snapshot).
• A client host to connect to the database, such as an Amazon EC2 instance or bastion host in the same Amazon VPC, with an Oracle client (for example, SQL*Plus or SQLcl).
• A dedicated S3 bucket for the backups. For more information, see Creating a bucket. Use a dedicated bucket: if it contains unrelated objects, the stored procedure’s upload status check may not complete as expected.
• Storage for backup staging. RMAN backup pieces are written to a directory on the DB instance host (for example, DATA_PUMP_DIR) before they’re uploaded to Amazon S3. You can use a default directory or create a new directory. To avoid oversizing your primary volume, you can instead stage backups on an additional storage volume and remove it afterward.
  Additional storage volumes require an instance with at least 64 GiB of memory and a primary volume of 200 GiB or larger, and each additional volume has a 200 GiB minimum. For details, see Working with storage in RDS for Oracle.

To implement this solution, you must create the following resources:

• Amazon RDS for Oracle integration with Amazon S3 (an option group with the S3_INTEGRATION option and an IAM role attached to the DB instance), described in the next section
• An SNS topic and subscription
• An IAM role and policy for the Lambda function
• A Lambda function
• An S3 event trigger
• An Oracle PL/SQL stored procedure on the RDS for Oracle database
• An Oracle Scheduler (DBMS_SCHEDULER) job to run the procedure on a schedule

Configure Amazon RDS for Oracle integration with Amazon S3

Amazon RDS for Oracle supports integration with Amazon S3 to transfer data between your RDS for Oracle DB instances and Amazon S3. This integration provides a secure way to use your S3 bucket to copy RMAN backup files from Amazon RDS for Oracle and share them for compliance or retention requirements, which you can access from both Amazon RDS for Oracle and other database hosts. Amazon S3’s lifecycle rules help you save costs by automating object transition from one storage class to another.

Configuring the integration requires an option group with the S3_INTEGRATION option attached to your DB instance, and an IAM role attached to the instance that grants access to your S3 bucket. The following steps use the AWS CLI.

1. Create an option group, add the S3_INTEGRATION option, and associate the option group with your DB instance. Match --engine-name and --major-engine-version to your DB instance (for example, oracle-se2 or a different version):

   aws rds create-option-group \
   --option-group-name oracle-s3-integration \
   --engine-name oracle-ee \
   --major-engine-version 19 \
   --option-group-description "RDS Oracle S3 integration"
   
   aws rds add-option-to-option-group \
   --option-group-name oracle-s3-integration \
   --options OptionName=S3_INTEGRATION \
   --apply-immediately
   
   aws rds modify-db-instance \
   --db-instance-identifier <db-instance> \
   --option-group-name oracle-s3-integration \
   --apply-immediately

2. Create an IAM role that Amazon RDS can assume, and attach a policy granting access to your S3 bucket:

   aws iam create-role \
   --role-name rds-oracle-s3-integration \
   --assume-role-policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Principal": { "Service": "rds.amazonaws.com" },
         "Action": "sts:AssumeRole"
       }
     ]
   }'
   
   aws iam put-role-policy \
   --role-name rds-oracle-s3-integration \
   --policy-name s3-access \
   --policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [
           "s3:GetObject",
           "s3:PutObject",
           "s3:ListBucket",
           "s3:GetBucketLocation"
         ],
         "Resource": [
           "arn:aws:s3:::<your-bucket>",
           "arn:aws:s3:::<your-bucket>/*"
         ]
       }
     ]
   }'

3. Attach the role to your DB instance for the S3_INTEGRATION feature:

   aws rds add-role-to-db-instance \
   --db-instance-identifier <db-instance> \
   --feature-name S3_INTEGRATION \
   --role-arn arn:aws:iam::<account>:role/rds-oracle-s3-integration

4. Confirm the role is associated and its status is ACTIVE before running the procedure:

   aws rds describe-db-instances \
   --db-instance-identifier <db-instance> \
   --query 'DBInstances[0].AssociatedRoles'

Configure an SNS topic

We use Amazon SNS to notify database administrators of the status (completion or failure) of the RMAN task on the Amazon RDS for Oracle database, including the upload of RMAN backup files from the RDS host to Amazon S3.

1. Create a standard SNS topic:

   aws sns create-topic \
   --name RMAN_S3_BACKUP_NOTIFY \
   --region <region>

2. Using the topic ARN returned by the previous command, subscribe an email endpoint:

   aws sns subscribe \
   --topic-arn arn:aws:sns:<region>:<AWS account number>:RMAN_S3_BACKUP_NOTIFY \
   --protocol email \
   --notification-endpoint <email address> \
   --region <region>

Amazon SNS sends a confirmation email to the endpoint. Choose Confirm subscription in that email before testing; the endpoint won’t receive notifications until the subscription is confirmed.

Configure an IAM role and policies

In this step, we configure the IAM role and policies for the Lambda function that sends notifications. (This is separate from the S3 integration role you attached to the DB instance in the previous section.)

1. Create an IAM role using the AWS CLI (for this post, called RMAN-backup-automate-S3-role). The following trust policy allows Lambda to assume the role:

   aws iam create-role \
   --role-name RMAN-backup-automate-S3-role \
   --assume-role-policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Principal": { "Service": "lambda.amazonaws.com" },
         "Action": "sts:AssumeRole"
       }
     ]
   }'

2. Create an IAM policy (lambda-sns-policy) to allow the function to publish to the SNS topic (RMAN_S3_BACKUP_NOTIFY):

   aws iam create-policy \
   --policy-name lambda-sns-policy \
   --policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [ "sns:Publish" ],
         "Resource": "arn:aws:sns:<region>:<AWS account number>:RMAN_S3_BACKUP_NOTIFY"
       }
     ]
   }'

3. Create an IAM policy (RMAN-backup-automate1-S3) to allow the function to read the status file from your S3 bucket:

   aws iam create-policy \
   --policy-name RMAN-backup-automate1-S3 \
   --policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [ "s3:GetObject" ],
         "Resource": "arn:aws:s3:::<your-bucket>/Status/*"
       }
     ]
   }'

4. Attach the lambda-sns-policy policy to the role RMAN-backup-automate-S3-role:

   aws iam attach-role-policy \
   --policy-arn "arn:aws:iam::<AWS account number>:policy/lambda-sns-policy" \
   --role-name RMAN-backup-automate-S3-role

5. Attach the RMAN-backup-automate1-S3 policy to the role RMAN-backup-automate-S3-role:

   aws iam attach-role-policy \
   --policy-arn "arn:aws:iam::<AWS account number>:policy/RMAN-backup-automate1-S3" \
   --role-name RMAN-backup-automate-S3-role

6. Attach the AWS managed policy AWSLambdaBasicExecutionRole to the role. The Lambda execution role grants the function permission to write logs:

   aws iam attach-role-policy \
   --policy-arn "arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole" \
   --role-name RMAN-backup-automate-S3-role

7. Confirm the policies are attached to the role:

   aws iam list-attached-role-policies \
   --role-name RMAN-backup-automate-S3-role

Configure a Lambda function

We use a Lambda function to send email notifications using Amazon SNS about the success or failure of RMAN backups on Amazon RDS for Oracle and the upload of RMAN backup files to Amazon S3. The function reads the status file the stored procedure uploads to Status/job_status.txt and publishes the result to the SNS topic. To create the function, complete the following steps:

1. Save the following code as rman_notify.py:

   # Send an SNS notification based on the RMAN backup status file.
   #
   # The rman_s3 procedure writes its status to Status/job_status.txt in the backup
   # bucket, containing either "File loaded to s3 success" or "Rman Backup failed".
   #
   # Configure these environment variables on the Lambda function:
   #     BACKUP_BUCKET  - the same S3 bucket passed to rman_s3()
   #     STATUS_KEY     - status object key (default: Status/job_status.txt)
   #     SNS_TOPIC_ARN  - ARN of the SNS topic to notify
   
   import os
   import boto3
   
   s3 = boto3.client('s3')
   sns = boto3.client('sns')
   
   BACKUP_BUCKET = os.environ['BACKUP_BUCKET']
   STATUS_KEY = os.environ.get('STATUS_KEY', 'Status/job_status.txt')
   SNS_TOPIC_ARN = os.environ['SNS_TOPIC_ARN']
   
   
   def lambda_handler(event, context):
       obj = s3.get_object(Bucket=BACKUP_BUCKET, Key=STATUS_KEY)
       status = obj['Body'].read().decode('utf-8')
       print('file status:', status)
   
       if 'success' in status:
           sns.publish(
               TopicArn=SNS_TOPIC_ARN,
               Message='RMAN backup completed and uploaded to S3',
           )
       elif 'failed' in status:
           sns.publish(
               TopicArn=SNS_TOPIC_ARN,
               Message='RMAN backup failed to upload to S3',
           )

2. Package the file into a deployment .zip:

   zip function.zip rman_notify.py

3. Create the function with the AWS CLI, using the execution role from the previous section and setting the environment variables. We use Python 3.13; we recommend the latest available runtime:

   aws lambda create-function \
   --function-name RMAN-backup-automate-S3 \
   --runtime python3.13 \
   --handler rman_notify.lambda_handler \
   --role arn:aws:iam::<AWS account number>:role/RMAN-backup-automate-S3-role \
   --zip-file fileb://function.zip \
   --timeout 15 \
   --environment "Variables={BACKUP_BUCKET=<your-bucket>,STATUS_KEY=Status/job_status.txt,SNS_TOPIC_ARN=arn:aws:sns:<region>:<AWS account number>:RMAN_S3_BACKUP_NOTIFY}" \
   --region <region>

Configure an S3 bucket event trigger

In this section, you configure a trigger on your S3 bucket so that when the stored procedure uploads the status file, it invokes the Lambda function RMAN-backup-automate-S3. Complete the following steps:

1. Grant Amazon S3 permission to invoke the function:

   aws lambda add-permission \
   --function-name RMAN-backup-automate-S3 \
   --statement-id s3invoke \
   --action lambda:InvokeFunction \
   --principal s3.amazonaws.com \
   --source-arn arn:aws:s3:::<your-bucket> \
   --source-account <AWS account number> \
   --region <region>

2. Configure the bucket to invoke the function when the status file is created. The filter matches the Status/ prefix and the job_status.txt suffix, so only the status file triggers the function:

   aws s3api put-bucket-notification-configuration \
   --bucket <your-bucket> \
   --notification-configuration '{
     "LambdaFunctionConfigurations": [
       {
         "LambdaFunctionArn": "arn:aws:lambda:<region>:<AWS account number>:function:RMAN-backup-automate-S3",
         "Events": [ "s3:ObjectCreated:*" ],
         "Filter": {
           "Key": {
             "FilterRules": [
               { "Name": "prefix", "Value": "Status/" },
               { "Name": "suffix", "Value": "job_status.txt" }
             ]
           }
         }
       }
     ]
   }'

3. Confirm the notification configuration was applied:

   aws s3api get-bucket-notification-configuration \
   --bucket <your-bucket>

Create a stored procedure on the RDS for Oracle DB instance

We create a stored procedure on the RDS for Oracle database that uses RDS packages to set the configurations and create backups using the Oracle RMAN utility.

1. From your client host, connect to the RDS for Oracle DB instance as the master user (such as admin).
2. Create the directory used for the status file:

   SQL> exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'BKP_DIR_STS');

3. Retain archive logs long enough for the Oracle RMAN tool to use them. This example uses 48 hours (see Retaining archived redo logs):

   SQL> exec rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '48');

4. Create the stored procedure. It removes any leftover backup pieces, runs a full RMAN backup, uploads the backup pieces to Amazon S3, and writes a status file to Status/job_status.txt (which triggers the notification). Run the following in your SQL client:

   /* Grants needed for the admin user (object-level grants can be used instead). */
   grant create any procedure to admin;
   grant select any dictionary to admin;
   grant execute any procedure to admin;
   
   /*
     Procedure: rman_s3
     Purpose  : Run a full RMAN backup on an Amazon RDS for Oracle instance,
                upload the backup pieces to Amazon S3, then write a job-status
                file to <bucket>/Status/job_status.txt for downstream alerting.
   
     One-time setup:
       exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'BKP_DIR_STS');
       exec rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '48');
   
     Execute (pass the bucket name in single quotes):
       exec rman_s3('<bucket_name>');
   
     Note: Use a DEDICATED S3 bucket for backups. If unrelated files are present,
           the upload status-polling loop may not terminate as expected.
   
     If compilation fails on Oracle SE, remove the trailing '/' on the last line
     and recompile.
   */
   CREATE OR REPLACE PROCEDURE rman_s3(rman_bucket IN VARCHAR2) AS
     n_rec_cnt            PLS_INTEGER := 0;
     p_backup_cur_status  VARCHAR2(30);
     v_task_id            VARCHAR2(30);
     sql_stmt_1           VARCHAR2(400);
     sql_stmt_2           VARCHAR2(400);
     sql_stmt_3           VARCHAR2(400);
     v_par                NUMBER(1);
     v_cnt_num            NUMBER(4) := 0;
     v_task_status        VARCHAR2(30);
     v_bkp_files_tot      NUMBER;
     status_bucket        VARCHAR2(200) := rman_bucket;
     status_path          VARCHAR2(100) := 'Status/';
     ltype                UTL_FILE.FILE_TYPE;
     ldir                 VARCHAR2(100) := 'BKP_DIR_STS';
     lfile                VARCHAR2(100) := 'job_status.txt';
   BEGIN
     dbms_output.put_line(status_bucket);
   
     FOR rec IN (SELECT filename
                 FROM   TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
                 WHERE  type = 'file'
                 AND    filename LIKE 'BACKUP%')
     LOOP
       UTL_FILE.FREMOVE('DATA_PUMP_DIR', rec.filename);
     END LOOP;
   
     /* Detect Oracle edition (Enterprise -> parallel 4, otherwise 1). */
     SELECT decode(substr(banner, 21, 4), 'Ente', 4, 1)
     INTO   v_par
     FROM   v$version
     WHERE  banner LIKE 'Oracle%';
   
     /* Run the full RMAN backup. */
     rdsadmin.rdsadmin_rman_util.backup_database_full(
       p_owner               => 'SYS',
       p_directory_name      => 'DATA_PUMP_DIR',
       p_parallel            => v_par,
       p_section_size_mb     => 100,
       p_rman_to_dbms_output => FALSE);
   
     /* Get the most recent backup job status. */
     SELECT status INTO p_backup_cur_status FROM
       (SELECT status
          FROM v$rman_backup_job_details
         WHERE start_time >= sysdate - 1
         ORDER BY end_time)
      WHERE ROWNUM = 1;
   
     /* Proceed only if the backup completed. */
     IF p_backup_cur_status IN ('COMPLETED') THEN
   
       SELECT COUNT(1) INTO n_rec_cnt
         FROM v$rman_backup_job_details
        WHERE start_time >= sysdate - 1;
   
       /* Total number of RMAN backup pieces created in the dump directory. */
       SELECT COUNT(1) INTO v_bkp_files_tot
         FROM table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
        WHERE filename like 'BACKUP%';
   
       IF n_rec_cnt = 0 THEN
         RAISE_APPLICATION_ERROR(-20003, 'RMAN procedure, Err Point : FAILED');
   
       ELSIF (n_rec_cnt != 0 AND p_backup_cur_status = 'COMPLETED') THEN
   
         /* Upload the backup pieces to S3. */
         sql_stmt_1:='SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name'||'=>'''||rman_bucket||''',p_prefix =>'||''''''||',p_s3_prefix =>'||''''''||',p_directory_name=>'||'''DATA_PUMP_DIR'''||') AS TASK_ID FROM DUAL';
         dbms_output.put_line(sql_stmt_1);
         EXECUTE IMMEDIATE sql_stmt_1 INTO v_task_id;
         dbms_output.put_line(v_task_id);
   
         /* Poll the task log in BDUMP until all pieces are uploaded. */
         sql_stmt_2 := 'SELECT count(1)  FROM table(rdsadmin.rds_file_util.read_text_file('||'''BDUMP'''||','||'''dbtask-'||v_task_id||'.'||'log'''||'))';
         dbms_output.put_line(v_bkp_files_tot);
         WHILE ( v_cnt_num < (((v_bkp_files_tot)*2 +1 ) )) LOOP
           dbms_lock.sleep(1);
           dbms_output.put_line(v_cnt_num);
           EXECUTE IMMEDIATE sql_stmt_2 INTO v_cnt_num;
         END LOOP;
   
         /* Write the success status file.
            (To send email instead, replace with UTL_MAIL.SEND.) */
         ltype := utl_file.fopen(ldir, lfile, 'w');
         utl_file.putf(ltype, 'File loaded to s3 success');
         utl_file.fclose(ltype);
   
         /* Upload the status file to <bucket>/Status/job_status.txt. */
         sql_stmt_3 := 'SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name'||'=>'''||status_bucket||''',p_prefix =>'||'''job_status.txt'''||',p_s3_prefix =>'||''''||status_path||''''||',p_directory_name=>'||'''BKP_DIR_STS'''||') AS TASK_ID FROM DUAL';
         EXECUTE IMMEDIATE sql_stmt_3 INTO v_task_status;
   
       END IF;
     END IF;
   
   EXCEPTION
     WHEN OTHERS THEN
       /* Write the failure status file. */
       UTL_FILE.FREMOVE('BKP_DIR_STS', lfile);
       ltype := utl_file.fopen(ldir, lfile, 'w');
       utl_file.putf(ltype, 'Rman Backup failed');
       utl_file.fclose(ltype);
   
       /* Upload the failure status file (same corrected prefix). */
       sql_stmt_3 := 'SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name'||'=>'''||status_bucket||''',p_prefix =>'||'''job_status.txt'''||',p_s3_prefix =>'||''''||status_path||''''||',p_directory_name=>'||'''BKP_DIR_STS'''||') AS TASK_ID FROM DUAL';
       EXECUTE IMMEDIATE sql_stmt_3 INTO v_task_status;
   
       RAISE_APPLICATION_ERROR(-20003, ' procedure, Err Point : FAILED' || SQLERRM);
   END;
   /

Schedule the backup

The stored procedure runs on demand, but you’ll typically want backups to run automatically. Create an Oracle Scheduler (DBMS_SCHEDULER) job that calls the procedure on a schedule. Adjust repeat_interval and the bucket argument to match your backup policy; you can customize it to your liking (for example, a different time of day or frequency):

/* Optional but recommended: schedule the procedure to run automatically with
   DBMS_SCHEDULER (supported on Amazon RDS for Oracle). Customize repeat_interval to suit your backup 
   policy and replace amzn-s3-demo-bucket with your bucket name. */
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'RMAN_S3_DAILY_BACKUP',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN rman_s3(''amzn-s3-demo-bucket''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',   -- e.g. every day at 02:00; customize as needed
    enabled         => TRUE);
END;
/

/* Verify the job and its next run */
SELECT job_name, enabled, state, next_run_date
FROM   user_scheduler_jobs
WHERE  job_name = 'RMAN_S3_DAILY_BACKUP';

/* Run on demand:  EXEC DBMS_SCHEDULER.RUN_JOB('RMAN_S3_DAILY_BACKUP'); */
/* Remove the job: EXEC DBMS_SCHEDULER.DROP_JOB('RMAN_S3_DAILY_BACKUP'); */

Test the solution

Now you can test the stored procedure on your RDS for Oracle instance to create backups using the RMAN utility. RMAN backup files are copied from the RDS host to an S3 bucket using the Amazon S3 integration.

1. Connect to the RDS for Oracle DB instance as the admin user and run the stored procedure. In this post, amzn-s3-demo-bucket is the example S3 bucket:

   SQL> exec rman_s3('amzn-s3-demo-bucket');

2. Optionally, list the RMAN backup pieces created on the DB instance. The pieces are written to DATA_PUMP_DIR:

   SQL> SELECT * FROM TABLE (rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) ORDER BY MTIME;

3. Confirm the files were uploaded to Amazon S3. List the bucket to see the backup pieces at the root and the status file under Status/:

   aws s3 ls s3://amzn-s3-demo-bucket/ --recursive

   You should see the RMAN backup pieces (names beginning with BACKUP) and the status file Status/job_status.txt, similar to the following (sizes and timestamps will vary):

   2026-08-18 09:14:02   1156562944 BACKUP_1_20260818
   2026-08-18 09:14:05    268435456 BACKUP_2_20260818
   2026-08-18 09:14:21           25 Status/job_status.txt

4. Optionally, check the status file contents:

   aws s3 cp s3://amzn-s3-demo-bucket/Status/job_status.txt -

   On a successful run, the file contains File loaded to s3 success.

When the status file is written to amzn-s3-demo-bucket/Status/, the S3 event notification triggers the Lambda function, which uses Amazon SNS to email the subscriber the success or failure of the task. Check your inbox for the notification.

You can move these RMAN backups from Amazon S3 to Amazon S3 Glacier for long-term storage that complements your automated backup strategy. For more information, see Transitioning objects using Amazon S3 Lifecycle.

Clean up

On its next run, the stored procedure removes leftover RMAN backup pieces from the DB instance directory (DATA_PUMP_DIR); it does not delete objects from Amazon S3. Manage S3 retention with S3 Lifecycle rules, or delete objects manually. To remove the resources created in this post, delete the following:

• The Oracle Scheduler job and the stored procedure on the RDS for Oracle DB instance:

  SQL> EXEC DBMS_SCHEDULER.DROP_JOB('RMAN_S3_DAILY_BACKUP');
  SQL> DROP PROCEDURE rman_s3;

• The Lambda function and its execution role and policies — RMAN-backup-automate-S3, RMAN-backup-automate-S3-role, lambda-sns-policy, and RMAN-backup-automate1-S3 (To delete a Lambda function)
• The S3 event notification on the bucket
• The SNS topic and associated subscription (Deleting an Amazon SNS subscription and topic)
• The Amazon S3 integration you configured for the DB instance — the IAM role (rds-oracle-s3-integration) and the option group (oracle-s3-integration). Detach the role and reset the instance to its previous option group before deleting the option group.
• The S3 bucket and its contents (Deleting a bucket)
• The RDS for Oracle database instance (Deleting a DB instance)

Conclusion

In this post, we showed you how to automate RMAN backups on Amazon RDS for Oracle to Amazon S3 and send email notifications on the completion or failure of the backups, which reduces operational overhead. With the approach described in this post and using Amazon S3 lifecycle policies, you can achieve a cost-effective and scalable solution with unlimited storage maintaining database backups for higher retention periods.

Try out the solution for your real-world use cases, and leave your feedback, thoughts, and ideas in the comments.

About the Authors

Zeeshan (Zee) Mirza is a Database Consultant with the Professional Services team at AWS. He works with customers in their journey to the cloud with a focus on complex database migration programs. In his spare time, Zee enjoys traveling to new places with his wife and riding his bicycle whenever weather permits.

Harish Lingegowda is a Database Consultant with the Professional Services team at AWS. He works as a database migration specialist to help Amazon customers migrate their on-premises database environment to AWS Cloud database solutions.

Nethravathi Muddarajaiah is a Senior Database Specialist Solutions Architect at AWS. She works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Vijaya Kumar Mallela is a Database Consultant with the Professional Services team at AWS. He works as a database migration specialist to help Amazon customers migrate their on-premises database environment to AWS Cloud database solutions.