Restore an Amazon RDS for Oracle instance to a self-managed instance

Fully managed database services often bring lots of benefits to customers for running their database workload in the cloud, such as easy administration and high scalability, availability, and durability. Amazon Relational Database Service (Amazon RDS) for Oracle is a fully managed commercial database that makes it easy to set up, operate, and scale Oracle database deployments in the cloud. You may need to restore a physical copy of an RDS for Oracle instance to a self-managed instance for various reasons, such as to reproduce an issue that is dependent on the physical layout of the database, produce a physical copy of the database for use by vendors and partners, or to comply with various compliance requirements.

In this post, we share step-by-step instructions to help you back up and restore a physical copy of an RDS for Oracle instance to a self-managed instance using Oracle Recovery Manager (RMAN), a native backup and recovery tool from Oracle. We also use AWS services such as Amazon Simple Storage Service (Amazon S3), an object storage service. Amazon RDS for Oracle automatically creates snapshots of underlying storage volumes along with frequent backups of archive logs to Amazon S3 to help meet your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) requirements.

However, it’s not possible to restore these snapshot-based backups to a platform outside the AWS Cloud or to a self-managed instance running on an Amazon Elastic Compute Cloud (Amazon EC2) instance. Although you can copy data from an RDS for Oracle instance to a self-managed instance using logical backup or replication tools, it can be cumbersome, especially for large databases. Amazon RDS for Oracle integrates various functionalities of RMAN using a custom package called rdsadmin.rdsadmin_rman_util, which helps you take a physical backup of your RDS for Oracle instance to restore to a self-managed instance.

Solution Overview

To restore a physical copy of an RDS for Oracle instance, we take a RMAN backup of the instance using the custom package rdsadmin.rdsadmin_rman_util, transfer backup files to the target platform with an S3 bucket as the staging area using Amazon S3 integration, and restore the database in the target platform using RMAN.

The following diagram illustrates the solution workflow:

The process of using rdsadmin.rdsadmin_rman_util to back up your RDS for Oracle instance using RMAN consumes resources on the instance. You can offload the backup process to a copy of the RDS for Oracle instance created from a snapshot backup. Using a temporary instance that is a copy of the RDS for Oracle instance for the backup process can also avoid extending RDS instance storage for staging RMAN backup pieces. For more information about restoring an instance from an Amazon RDS snapshot, refer to Restoring from a DB snapshot.

Prerequisites for backing up an RDS for Oracle instance

To back up an RDS for Oracle instance, you need the following prerequisites.

Availability of space in the RDS for Oracle instance

RMAN backup pieces created by this process are stored in underlying Amazon Elastic Block Store (Amazon EBS) volumes of the RDS for Oracle instance using a DIRECTORY object. Therefore, the RDS for Oracle instance should have sufficient free space to hold the backup pieces. You can monitor the current free space in an RDS for Oracle instance using the Amazon CloudWatch metric FreeStorageSpace. We recommending having free space greater than the current size of the database, though RMAN backs up only formatted blocks and supports compression.

Log in to the database using any client tool like SQL*Plus as a user that has access to dba_data_files and run the following query to view the current size of all database files:

SQL>select sum(bytes/1024/1024/1024) GB from dba_data_files;

Archive log mode on the RDS for Oracle instance

To take a backup of a database using the rdsadmin.rdsadmin_rman_util package, the RDS for Oracle instance must be in ARCHIVELOG mode. To do so, set the backup retention period to a non-zero value. Amazon RDS for Oracle automatically disables ARCHIVELOG mode when backup retention is set to 0 days. For more details on setting the backup retention period, refer to Backup retention period.

Archive logs are required to make database files consistent while restoring and to do point-in-time recovery on the target platform. Therefore it’s imperative that archive logs are retained in the RDS for Oracle instance at least for the duration of the backup. You can modify the archive log retention period using the function rdsadmin.rdsadmin_util.set_configuration.

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’re using RDS for Oracle with transparent data encryption (TDE) enabled, you receive the following error when you try to access the encrypted columns in the restored database:

ORA-28362: master key not found

The error occurs when TDE wallet is used to encrypt data and the wallet isn’t included in the backup for security reasons. You can use Oracle native export and import for this specific use case.

Back up an RDS for Oracle instance using RMAN

You can take an RMAN backup of an RDS for Oracle instance using the rdsadmin.rdsadmin_rman_util package without needing to have shell access to the underlying EC2 instance of Amazon RDS. This section discusses the steps to take an online backup of the RDS for Oracle instance using RMAN.

Create a directory object and back up database files

The following commands take a compressed backup of the entire database with four channels. This can consume significant CPU and I/O resources. For more information about the parameters of the rdsadmin.rdsadmin_rman_util package, refer to Performing common RMAN tasks for Oracle DB instances. You can choose appropriate parameters that best suit your environment.

Check the sequence number to start recovery from 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 perform 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 end recovery:

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

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;

Back up the required archive logs

In this step, we back up the required archive logs from the RDS for Oracle instance.

You can back up all available archive logs or selective archive logs between begin_seq and end_seq. See the following code:

SQL>BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_all(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN_BACKUP',
        p_label               => 'ARCH-',
        p_rman_to_dbms_output => TRUE);
END;
/
OR
SQL>BEGIN
    rdsadmin.rdsadmin_rman_util.backup_archivelog_sequence(
        p_owner               => 'SYS', 
        p_directory_name      => 'RMAN_BACKUP',
        p_from_sequence       => <begin_seq>,
        p_to_sequence         => <end_seq>,
        p_parallel            => 4,  
        p_compress            => TRUE,
        p_label               => 'ARCH-',
        p_rman_to_dbms_output => TRUE);
 END;
/

List the backup pieces in the directory with the following code:

SQL>select filename, filesize, mtime
    from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP'))
   where type = 'file'
   order by filename;

Back up the current control file

The previous steps automatically take a control file backup along with the database and archive log backup. Optionally, you can take a separate backup for the control file.

Identify the control file backup files using the following query:

SQL>select filename, filesize, mtime
    from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP'))
   where type = 'file'
   and filename like '%-c-%'
   order by filename;

Back up the current control file and server parameter file:

SQL>BEGIN
rdsadmin.rdsadmin_rman_util.backup_current_controlfile(
        p_owner               => 'SYS',
        p_directory_name      => 'RMAN_BACKUP',
        p_label               => 'CONTROLFILE-',
        p_rman_to_dbms_output => TRUE);
END;
/

List the backup pieces in the directory:

SQL>select filename, filesize, mtime
    from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP'))
   where type = 'file'
   order by filename;

Transfer backup files to an S3 bucket

With Amazon S3 integration, you can transfer files between an RDS for Oracle instance and an S3 bucket. For instructions to set up Amazon S3 integration between your RDS for Oracle instance and the S3 bucket created for copying backup files, refer to Amazon S3 integration. You can also automate RMAN backup on RDS for Oracle instance and upload backup files to Amazon S3. Refer to Automate Amazon RDS backups using the Oracle RMAN utility for more details on this.

Transfer files from the RDS for Oracle instance directory to the S3 bucket. Provide the name of your bucket in the following code. You can use p_prefix to upload specific files.

SQL>SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
      p_bucket_name    =>  'mys3bucket', 
      p_prefix         =>  '',
      p_s3_prefix      =>  '',  
      p_directory_name =>  'RMAN_BACKUP') 
   AS TASK_ID FROM DUAL;

Verify the progress of the task with the following code. Replace the task ID with the output from the preceding command:
```
SQL>SELECT text FROM table(rdsadmin.rds_file_util.read_text_file
('BDUMP','dbtask-<task-id>.log'));
```

Prepare the target platform for the restore

You must choose the Linux-x86-64 bit platform for hosting the self-managed database because the package rdsadmin.rdsadmin_rman_util doesn’t support TO PLATFORM or FOR TRANSPORT clauses for platform conversion.

You can restore the database using an Oracle database software version higher than the version used by the RDS for Oracle instance and complete the upgrade process after the recovery. However, we recommend using the same version of binaries for the restore with the same or higher Patch Set Update (PSU) or Release Update (RU) level.

Restore the backup to a self-managed instance

The steps for restoring the backup to a self-managed instance on Amazon EC2 or on premises are similar. The first step is to make the backup files available to the compute instance that hosts the self-managed database. You can either copy the files from the S3 bucket manually to the target host or mount it directly using solutions like Amazon FSx for Luster, AWS Storage Gateway, or s3fs-fuse.

Prepare the environment for the restore and start the instance

To prepare the environment and start the instance, complete the following steps:

Make the backup files available to the target instance.

In the following example, the backup files are copied to the /opt/oracle/backup directory in the target server.

Prepare a dummy parameter file for your target instance with the instance name of your choice, the same db_name parameter as the db_name of the RDS for Oracle instance, and the location of data files and control files.

You can find the db_name of the RDS for Oracle instance on the Amazon RDS console on the Configuration tab, as shown in the following screenshot.

The following code is the contents of a dummy parameter file with required parameters:

initORCL.ora
db_name='ORCL'
db_create_file_dest='/opt/oracle/datafiles'
control_files='/opt/oracle/datafiles/ORCL/controlfile/control01.ctl'

Start the target instance in NOMOUNT state

export ORACLE_SID=ORCL
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
sqlplus "/ as sysdba"
SQL>startup nomount

Restore the control file from the control file backup piece and mount the database

Refer to the backup section to locate the backup piece name containing the control file backup and use it in the following restore command.

$rman target /

RMAN>restore controlfile from '/opt/oracle/backup/CONTROLFILE--backup-20210723-0l04lla4_21_1_1';

RMAN>alter database mount;

You should see control files restored to the location mentioned in your parameter file.

Catalog the new location of the backup files to the control file

$rman target /

RMAN>catalog start with '/opt/oracle/backup';

Restore the database until the last archive log sequenced included in the backup

Find the sequence number of the last archive log that was backed up. Verify end_seq from the backup section.

$ rman target /

 RMAN>run{
set until sequence <end_seq>;
set newname for database to new;
restore database;
switch datafile all;
recover database;
}

These commands restore the data files from the backup to the location indicated by the db_create_file_dest parameter. Then they update the new location of the data files in the control file and recover the database to the archive log sequence number you input.

Back up more archive logs and roll forward the database

Optionally, if you want to roll forward the database, you can back up the newly generated archive logs and apply them on the restored instance. Refer to the earlier steps to back up more archive logs, transfer the backup pieces to the target instance using Amazon S3 integration, catalog new backup pieces to the control file, and apply them to the self-managed instance, as shown in the following code.

Log in to RMAN.
Catalog the new archive log backup pieces to the control file:
```
$rman target /
RMAN>catalog start with '/opt/oracle/backup';
```
Find the sequence of the last archive log that was backed up. Verify end_seq from the backup section.
```
RMAN>run{
set until sequence <new_end_seq>;
recover database;
}
```

Open the database with the resetlogs option

When you’re ready to open the database, you can issue the open resetlogs command. If the target Oracle binaries are of a different PSU or RU level than the source RDS for Oracle instance patch set, the open resetlogs command stops with an error. In this case, mount the instance, complete instance recovery, open with the upgrade option, and complete the upgrade process by running the necessary upgrade scripts. For details of the scripts to downgrade or upgrade the database based on the version of the target binaries, refer to About Dbupgrade Scripts and catupgrd.sql in Earlier Releases of Oracle Database.

You can check the version of Oracle binaries by invoking SQL*Plus, as in the following code:

$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 25 04:10:36 2021
Version 19.11.0.0.0

Alternatively, you can check the version from the output of the Opatch lsinventory command:

$ORACLE_HOME/OPatch/opatch lsinventory |grep "Patch description"
Patch description:  "RDBMS - DSTV36 UPDATE - TZDATA2020E"
Patch description:  "Database Release Update : 19.11.0.0.210420 (32545013)"
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"

You can refer to the README of the Release Update (patch number 32545013 in preceding example) for the details of the script to run the database to upgrade the dictionary.

$sqlplus "/ as sysdba"
SQL>alter database open resetlogs;

If the instance stopped due to version mismatch, you receive bootstrap failure messages in alert.log, as shown in the following code:

ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER (ospid: 29710): terminating the instance due to error 704
Instance terminated by USER, pid = 29710
ORA-1092 signalled during: ALTER DATABASE OPEN..

If this occurs, use the following code to complete post-upgrade or post-downgrade steps and open the database:

sqlplus "/ as sysdba"
SQL>startup mount
SQL>recover database
SQL>shutdown immediate
SQL>startup upgrade
run appropriate upgrade/downgrade script(eg:datapatch utility)

Post-recovery steps

Because you have sysdba access on the restored self-managed database, you can customize it according to your needs and standards. We recommend performing the following actions on the self-managed instance after you open it with the resetlogs option.

Make sure Oracle binaries and database are at the same PSU or RU level

The following query on the RDS for Oracle instance or restored database can show the PSU or RU details at the dictionary level:

select PATCH_ID,PATCH_TYPE,ACTION,STATUS,ACTION_TIME,DESCRIPTION from sys.dba_registry_sqlpatch;

To check the PSU or RU level of ORACLE_HOME, run the opatch lsinventory command:

$ORACLE_HOME/OPatch/opatch lsinventory

If ORACLE_HOME is lagging on the PSU or RU level, you need to apply the same PSU or RU as used by the RDS for Oracle instance to the ORACLE_HOME of the self-managed instance.

If the restored database is behind the PSU or RU level of ORACLE_HOME, you can update the dictionary by running the necessary SQLs for applying the PSU to the database. Refer the README of the PSU or RU for more details.

Make sure ORACLE_HOME has the required time zone files

If required time zone files are missing in the ORACLE_HOME used by the restored database, certain actions performed on the instance can throw the error ORA-01804: failure to initialize timezone information.

To avoid this, you need to ensure that ORACLE_HOME has the required time zone files used by the restored database.

To check the time zone version used by the RDS for Oracle instance, log in to the instance as the Amazon RDS primary user and run the following query:

SQL>select NAME, VALUE$ from SYS.PROPS$ where NAME like ('DST_%_TT_VERSION');
SQL>select * from v$timezone_file;

If the required time zone files are missing in the $ORACLE_HOME/oracore/zoneinfo directory after applying the required PSU or RU patches to make dictionary and ORACLE_HOME be at the same level, you may need to apply DST-related patches into the ORACLE_HOME. Refer to My Oracle Support for more details.

The following support note lists patch details for DST version 36:

Applying the DSTv36 update for the Oracle Database (Doc ID 2767770.1)

Drop the RDSADMIN user

Most of the customization required to provide a managed service for an RDS for Oracle instance is implemented as the RDSADMIN user. You can safely drop this user in a self-managed environment.

SQL> drop user rdsadmin cascade;

Clean up RMAN backup files in RDS for Oracle instance storage

When the process is complete, you can remove the RMAN backup files from RDS for Oracle instance storage using the utlfile.fremove procedure.

Log in to the instance as the Amazon RDS primary user using a client tool like SQL*Plus and list the contents of the directory:

SQL>select filename, filesize, mtime from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP')) where type = 'file' order by filename;

Remove RMAN backup files from the RMAN_BACKUP directory:

SQL>exec UTL_FILE.FREMOVE('RMAN_BACKUP','<backup piece name>');

You can use the space freed up by removing backup files for future database growth. If the backup was taken from a copy of your original RDS for Oracle instance created from a snapshot, you may consider deleting that temporary instance.

Conclusion

You might need to restore a physical copy of an RDS for Oracle instance to meet various requirements, such as migration and replication. This may not be an easy process, like in the case of a self-managed instance, due to restrictions in accessing physical database files in the case of an RDS for Oracle instance. However, Amazon RDS for Oracle supports RMAN backups using the customized package rdsadmin.rdsadmin_rman_util.

In this post, we discussed the step-by-step instructions to restore an RDS for Oracle instance into a self-managed instance using the rdsadmin.rdsadmin_rman_util package, RMAN, and Amazon S3 integration. With this procedure, you can restore a physical copy of your RDS for Oracle instance into Amazon EC2 or an on-premises environment.

Refer to Restore Amazon RDS for Oracle to a self-managed infrastructure using RMAN duplication for information about using the RMAN DUPLICATE command to restore a backup of an RDS for Oracle instance to a self-managed instance and Automate Amazon RDS backups using the Oracle RMAN utility for how to automate RMAN backup on RDS for Oracle instance and upload backup files to Amazon S3.

If you have any comments or questions, leave them in the comment section.

About the Authors

Jobin Joseph is a Senior Database Specialist Solution Architect based in Dubai. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS. He is an Oracle Certified Master with 20 years of experience with Oracle databases.

AWS Database Blog