Restoring an Amazon RDS for Oracle Instance to a Self-Managed Instance

More Info

Fully managed database services provide numerous advantages for customers running their database workloads in the cloud, including simplified management, scalability, availability, and durability. Amazon Relational Database Service (Amazon RDS) for Oracle offers a fully managed commercial database, facilitating the setup, operation, and scaling of Oracle database deployments in the cloud.

There may be multiple scenarios requiring the restoration of a physical copy of an RDS for Oracle instance to a self-managed environment. Examples include reproducing an issue tied to the physical layout of the database, creating a physical copy for vendors and partners, or fulfilling specific compliance obligations.

In this article, we outline step-by-step instructions for backing up and restoring a physical copy of an RDS for Oracle instance to a self-managed platform using Oracle Recovery Manager (RMAN), Oracle’s native backup and recovery tool. We also leverage AWS services such as Amazon Simple Storage Service (Amazon S3), an object storage solution. Amazon RDS for Oracle regularly creates snapshots of its underlying storage volumes and frequently backs up archive logs to Amazon S3, assisting in meeting your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) needs.

However, it’s important to note that restoring these snapshot-based backups to a non-AWS platform or a self-managed instance running on Amazon Elastic Compute Cloud (Amazon EC2) is not feasible. While you can utilize logical backup or replication tools to transfer data from an RDS for Oracle instance to a self-managed environment, this method can be cumbersome, particularly with large databases. Amazon RDS for Oracle integrates various RMAN functionalities through a custom package called rdsadmin.rdsadmin_rman_util, which enables taking a physical backup of your RDS for Oracle instance for restoration to a self-managed setup.

Solution Overview

To restore a physical copy of an RDS for Oracle instance, we will take an RMAN backup of the instance using the rdsadmin.rdsadmin_rman_util package, transfer the backup files to the target platform via an S3 bucket as a staging area, and then restore the database on the target platform using RMAN.

The diagram below illustrates the workflow of the solution.

Utilizing rdsadmin.rdsadmin_rman_util for backing up your RDS for Oracle instance consumes resources on the instance. To mitigate this, you can transfer the backup process to a duplicate RDS for Oracle instance created from a snapshot backup. Using a temporary instance, which is a copy of the RDS for Oracle instance for the backup process, can also help avoid extending RDS instance storage for staging RMAN backup pieces. For additional information on restoring an instance from an Amazon RDS snapshot, refer to this link, which offers an excellent resource.

Prerequisites for Backing Up an RDS for Oracle Instance

To back up an RDS for Oracle instance, the following prerequisites must be met:

1. Sufficient Storage Space: RMAN backup pieces are stored in the underlying Amazon Elastic Block Store (Amazon EBS) volumes of the RDS for Oracle instance using a DIRECTORY object. Therefore, the RDS for Oracle instance must have adequate free space to accommodate the backup pieces. Monitoring current free space can be done using the Amazon CloudWatch metric FreeStorageSpace. It is advisable to have free space greater than the current size of the database, even though RMAN only backs up formatted blocks and supports compression.

Log in to the database using any client tool like SQL*Plus as a user with access to dba_data_files and run the following query:

SQL> SELECT SUM(bytes / 1024 / 1024 / 1024) AS GB FROM dba_data_files;

2. Archive Log Mode: To back up a database using the rdsadmin.rdsadmin_rman_util package, the RDS for Oracle instance must be in ARCHIVELOG mode. Set a non-zero value for the backup retention period. Note that Amazon RDS for Oracle automatically disables ARCHIVELOG mode if the backup retention is set to 0 days. Archive logs are essential for maintaining database file consistency during restoration and for performing point-in-time recovery on the target platform. Therefore, it is crucial to retain archive logs in the RDS for Oracle instance for at least the duration of the backup. Adjust the archive log retention period using the rdsadmin.rdsadmin_util.set_configuration function.

The following code sets the archive log retention for the RDS for Oracle instance to 24 hours:

SQL>
BEGIN
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '24');
END;
/
COMMIT;

As of this writing, RMAN backups are not supported on read replicas and multitenant container databases.

If you are utilizing RDS for Oracle with transparent data encryption (TDE) enabled, you may encounter the following error when attempting to access encrypted columns in the restored database:

ORA-28362: master key not found

This error arises when TDE wallet is used for data encryption, and the wallet is not included in the backup for security reasons. For this specific case, consider using Oracle native export and import.

Backing Up an RDS for Oracle Instance Using RMAN

You can perform an RMAN backup of an RDS for Oracle instance using the rdsadmin.rdsadmin_rman_util package without needing shell access to the underlying EC2 instance of Amazon RDS. The following section outlines the steps for taking an online backup of the RDS for Oracle instance using RMAN.

1. Create a Directory Object and Back Up Database Files: The following commands will create a compressed backup of the entire database with four channels. This process can consume considerable CPU and I/O resources. For more details about the parameters of the rdsadmin.rdsadmin_rman_util package, refer to this post.

– Log in to the RDS for Oracle instance using any client tool like SQL*Plus as a privileged user.

– Check the sequence number to determine the recovery start point and then switch the log file:

SQL> SELECT MAX(sequence#) AS begin_seq FROM v$log WHERE archived='YES';
SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;

– Create a directory object and execute a full backup of the database:

SQL> EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'RMAN_BACKUP');
SQL> SET SERVEROUTPUT ON;
BEGIN
    rdsadmin.rdsadmin_rman_util.backup_database_full(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN_BACKUP',
        p_parallel            => 4, 
        p_compress            => TRUE,
        p_label               => 'DB-',
        p_rman_to_dbms_output => TRUE);
END;
/

– Switch the log file a few times and check the minimum sequence number to determine the end recovery point:

SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;
SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;
SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;
SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;

SQL> SELECT MAX(sequence#) AS end_seq FROM v$log WHERE archived='YES';

– Finally, list the backup pieces created in the directory:

SQL> SELECT filename, filesize, mtime
FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP'))
WHERE type = 'file'
ORDER BY filename;

Remember to consult this authority for deeper insights into this topic.