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 Simple Storage Service (Amazon S3), leveraging S3 for durable, scalable, and cost-effective storage.

You can restore the RMAN database backups in the same or different Region on Amazon Elastic Compute Cloud (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 Amazon Simple Notification Service (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 do 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

This post shows how you can 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:

The Oracle DBMS job scheduler schedules the creation of Oracle RMAN backups using an Oracle stored procedure on the RDS for Oracle database.
The Oracle stored procedure on the RDS database copies the RMAN backup files generated on the RDS host to an S3 bucket using Amazon S3 integration.
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.
Uploading the status file to the S3 bucket invokes an AWS Lambda function.
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 architecture of this 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 status file uploaded to Amazon S3 invokes Lambda function to send email notification of the success or failure of the RMAN task. 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 Virtual Private Cloud (Amazon VPC)
An S3 bucket with a globally unique name. In this post, our S3 bucket name is backup-rman-s3. For more information, see Creating a bucket.
RMAN backups consume storage space on the RDS DB instance host. You can use default directories, such as DATA_PUMP_DIR, or create a new directory. The backup files will be placed in the specified directory if a new directory is created.
An RDS for Oracle database or database restore from a snapshot.

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

An SNS topic and subscription
An IAM role and policies
A Lambda function
An S3 event trigger
An Oracle PL/SQL stored procedure on RDS for Oracle database

Configure Amazon RDS for Oracle integration with Amazon S3

Amazon RDS for Oracle supports integration with Amazon S3 to easily, efficiently, and securely 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.

To configure Amazon S3 integration with Amazon RDS for Oracle, check out Integrating Amazon RDS for Oracle with Amazon S3 using S3_integration.

Configure an SNS topic

We use Amazon SNS to notify the database administrators of the status (completion or failure) of completion of RMAN task on Amazon RDS for Oracle database (including upload of RMAN backup files from RDS host to S3). Upon receiving the SNS notification, database administrators can take corrective action, such as improving operational efficiency.

To configure an SNS topic, complete the following steps:

On the Amazon SNS console, choose Topics in the navigation pane.
Choose Create topic.
For Type¸ select Standard.
For Name¸ enter RMAN_S3_BACKUP_NOTIFY.
Choose Create topic.
On the new topic’s Details page, copy the topic ARN (for example, arn:aws:sns:us-east-1:<AWS account number>:RMAN_S3_BACKUP_NOTIFY).
On the Subscriptions page, choose Create subscription.
For Topic ARN, enter the topic ARN you copied earlier.
For Protocol, choose Email.
For Endpoint, enter the email address of the user subscribing to the notification.
Choose Create subscription.

You must confirm the subscription before the email address can start to receive messages.

Check your email inbox and choose Confirm subscription in the email from Amazon SNS.

A new window in your web browser opens and displays a subscription confirmation with your subscription ID.

Configure an IAM role and policies

In this step, we configure an IAM role and necessary policies for granting access to AWS resources.

Create an IAM role using the AWS CLI (for this post, called RMAN-backup-automate-S3-role).

The following trust policy allows Lambda and Amazon SNS to assume the role to send email notifications using the SNS topic via the Lambda function:

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

Create an IAM policy (for this post, lambda-sns-policy) to allow Lambda to perform the sns publish action on 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:us-east-1:<AWS account number>:RMAN_S3_BACKUP_NOTIFY "
            }
        ]
    }'

Create an IAM policy (RMAN-backup-automate1-S3) to allow the function to upload objects and read objects to your S3 bucket (rds-backup-rman-s3):

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

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/RMAN-backup-automate1-S3" \
--role-name RMAN-backup-automate-S3-role

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/lambda-sns-policy" \
   --role-name RMAN-backup-automate-S3-role

Attach the AWS managed policy AWSLambdaBasicExecutionRole to role RMAN-backup-automate-S3-role. The Lambda execution role grants the lambda function permission to access AWS services and resources.
```
aws iam attach-role-policy \
   --policy-arn "arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole" \
   --role-name RMAN-backup-automate-S3-role 
```

Now we can verify the creation of the role and polices via the IAM console.

On the IAM console, choose Roles in the navigation pane.
Choose the RMAN-backup-automate-S3-role role you created in the previous steps.
On the Permissions tab, confirm the policies are attached.

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. To create the function, complete the following steps:

On the Lambda console, choose Functions in the navigation pane.
Choose Create function.
For Function name, enter RMAN-backup-automate-S3.
For Runtime, choose the latest version of Python.

In this post, we use Python v3.9. We recommend using the latest available version of Python.

For Execution role, select Use an existing role.
For Existing role, choose the role RMAN-backup-automate-S3-role.
Choose Create function.

You can download the Python code for the Lambda function from the GitHub repo.

In the function code, update <topic ARN> to the ARN of the SNS topic you copied earlier (arn:aws:sns:us-east-1:<AWS account number>:RMAN_S3_BACKUP_NOTIFY).

While viewing your function on the Lambda console, you can review the function code on the Code tab, under Code source. The function reads the jobstatus.txt status file uploaded to the S3 folder (backup-rman-s3/Status/) and sends an email notification using Amazon SNS on its success or failure to subscribers.

Configure an S3 bucket event trigger

In this section, you configure a trigger on your S3 bucket. This way, when a file is uploaded to your S3 bucket, it triggers the Lambda function RMAN-Backup-Automate-S3. Complete the following steps:

On the Amazon S3 console, choose your source bucket (for this post, backup-rman-s3/).
Choose the Properties tab.
Under Event notifications, choose Create event notification.
For Event name, enter a name (S3_RMAN_EVENT).
For Prefix, enter a prefix (Status/).
For Suffix, enter a suffix (jobstatus.txt).
In the Event types section, select the Put check box.
For Destination, select Lambda function.
For Specify Lambda function, select Choose from your Lambda functions.
For Lambda function, choose the function you created (RMAN-Backup-Automate-S3).
Choose Save changes.

Navigate to your Lambda function. You should see Amazon S3 added as a trigger to invoke the function.

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.

Connect to the RDS for Oracle DB instance as the master user (such as admin).

Create the directory and stored procedure with the following commands:

/* following sql command creates directory by name 'BKP_DIR_STS’ on RDS Oracle DB instance */

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

Make sure the archive logs are retained on the RDS database server as long as the Oracle RMAN tool requires them. In our example we have used 48 hours as retention of archive logs.

Run the following command to ensure that archive logs are retained on your RDS source for 48 hours:

/* following sql command increases the archive log retention on RDS Oracle DB instance to 48 hours */

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

Create a stored procedure on the RDS for Oracle database instance.

You can download the PL/SQL code to create the stored procedure from the GitHub repo.

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.

Connect to the RDS for Oracle database instance as the admin user and run the stored procedure using following SQL command. (In this post, backup-rman-s3 is the example S3 bucket created to copy RMAN backups from the RDS directory to Amazon S3.)
```
SQL>  exec rman_s3 (backup-rman-s3);
```
To list the RMAN backup files in a DB instance directory on Amazon RDS for Oracle, use the following SQL command. (In this post, BKP_DIR_STS is the example directory created on Amazon RDS.)
```
SQL> SELECT * FROM TABLE (rdsadmin.rds_file_util.listdir('BKP_DIR_STS')) ORDER BY MTIME;
```

When the RMAN backups on the RDS for Oracle database are complete, the Oracle stored procedure initiates the upload of the status file (job_status.txt)to the S3 folder backup-rman-s3/Status/. Upload of the status file to the S3 folder triggers the Lambda function to send an email notification using Amazon SNS to the subscriber regarding the success or failure of the task.

Check your email notification stating the status (completion or failure) of the task.

You can move these RMAN backups from Amazon S3 to Amazon S3 Glacier for long-term storage that complements your automated backup strategy. Visit Transitioning to the S3 Glacier and S3 Glacier Deep Archive storage classes (object archival).

Clean up

The Oracle stored procedure cleans up backup files on Amazon S3 and the directory on the RDS for Oracle DB instance in the next run. For a complete cleanup, make sure the following resources are deleted:

Lambda function and associated roles (To delete a Lambda function)
SNS topic and associated subscription (Deleting an Amazon SNS subscription and topic)
Oracle stored procedure in the RDS for Oracle DB instance
S3 bucket and its contents (Deleting a bucket)
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 feedbacks, 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.

AWS Database Blog