AWS Database Blog

Restore Amazon RDS for Oracle to a self-managed infrastructure using RMAN duplication

Amazon Relational Database Service (Amazon RDS) for Oracle is a fully managed commercial database that makes it easy to set up, operate, scale a relational database in the cloud. It takes care of installation, storage provisioning, management, OS and database patching, backup and restore automatically. This helps you focus your efforts on where you can make the biggest difference in your business by offloading the undifferentiated heavy lifting of database infrastructure management. As discussed in Restore an Amazon RDS for Oracle instance to a self-managed instance, there are a variety of reasons to restore a copy of your RDS for Oracle instance to a self-managed environment to test out new application code developments or changes. This provides you full control of the Oracle binaries, OS-level access, and elevated database level privileges.

In this post, we show how to take a backup of an RDS for Oracle database instance and restore it on a self-managed environment like Amazon Elastic Compute Cloud (Amazon EC2) using the Oracle Recovery Manager (RMAN) database duplicate method. This is a specific restoration technique, not to be confused with duplication in the generic sense of the word. This method doesn’t require a connection to a target or a recovery catalog. The procedure restores the database, performs a database rename, and starts it on a self-managed server in OPEN mode automatically.

Solution overview

In this method, we take an RMAN backup of the RDS for Oracle instance by using the custom package rdsadmin.rdsadmin rman util as discussed in Step by Step Guide to restore an Amazon RDS for Oracle instance to a self-managed Instance. Then we transfer it to target instance with an Amazon Simple Storage Service (Amazon S3) bucket using Amazon S3 integration. Finally, we restore the backup pieces to the target using the RMAN duplicate method. The following diagram illustrates our solution architecture.

On the Amazon RDS side, you perform the following high-level steps:

  1. Configure the RDS for Oracle instance with Amazon S3 integration using an AWS Identity and Access Management (IAM) role and option group.
  2. Create a database directory to keep the RMAN backups.
  3. Validate the database files (an optional but recommended step).
  4. Enable archive log retention to 24 hours.
  5. Take the RMAN backup of Amazon RDS for Oracle using rdsadmin.rdsadmin_rman_util.
  6. Transfer the backup pieces to your S3 bucket.
  7. Delete the files from the RDS instance using Fremove to free up the storage space.

In the self-managed environment, you perform the following steps:

  1. Download the RMAN backup pieces from the S3 bucket to your local server.
  2. Prepare the environment to initiate the restore.
  3. Start the auxiliary instance using the modified parameter file.
  4. Restore the database using the RMAN duplicate method.
  5. Perform post-recovery steps.

If you’re using Amazon 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. As a result, AWS can’t include these items in backup for security reasons. You can use Oracle native export and import for this specific use case.

Prerequisites

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

Amazon RDS steps

In this section, we walk you through the steps needed on the Amazon RDS side. In a nutshell, we will configure the RDS for Oracle instance, create a database directory inside it, validate the database files, enable archive log retention and finally take the RMAN backup of the instance. This will prepare the RDS for Oracle instance for the next steps.

Configure the RDS for Oracle instance

Create an RDS for Oracle instance with Amazon S3 integration using an IAM role and option group. The IAM role provides the permissions required to transfer files. For instructions, see the user guide.

Create a database directory

Create a database directory in the RDS for Oracle instance with the following code:

- Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

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

SQL> select DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME='RMAN_BACKUP';
DIRECTORY_PATH
--------------------------------------------------------------
/rdsdbdata/userdirs/01

Amazon RDS stores the RMAN backup in this directory.

Validate the database files

It’s a best practice to check for the integrity of the database structure, data block, and redo logs. In this step, we validate database files using rdsadmin_rman_util.validate_database.

We check the data files for any physical or logical corruption using the backup validate command. This command doesn’t create a backup of any data file. Instead, it checks for the validity of the data files and updates the v$database_block_corruption view if it finds any corruption.

Complete the following steps:

  1. Check the database structure integrity: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

set serveroutput on;
BEGIN rdsadmin.rdsadmin_rman_util.validate_database(
p_validation_type => 'PHYSICAL+LOGICAL',
p_parallel => 4,
p_section_size_mb => 10,
p_rman_to_dbms_output => FALSE);
END;
/
  2. Run the following query to check the log file of the previous operation: 
    SQL> SELECT filename FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like '%validate%';
  3. Run the following query to open the log file and check for any errors: 
    SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','rds-rman-validate-DATABASE-XXXX.txt'));
  4. Validate the control file: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

set serveroutput on;
exec rdsadmin.rdsadmin_rman_util.validate_current_controlfile;
  5. Run the following query to check for the logs: 
    SQL> SELECT filename FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like 'rds-rman-validate-CURRENT-CONTROLFILE%';
  6. Run the following query to open the log file and check for any errors. Replace the file name obtained from the previous query and add it in this query. 
    SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'File-name-from above-query'));
  7. Validate spfile with the following code: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

exec rdsadmin.rdsadmin_rman_util.validate_spfile;
  8. Run the following query to check for the logs: 
    SQL> SELECT filename FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like 'rds-rman-validate-SPFILE%';
  9. Run the following query to open the log file and check for any errors. Replace the file name obtained from the previous query and add it in this query. 
    SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'File-name-from above-query'));
  10. Cross-check the archive logs by running the following command: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

BEGIN rdsadmin.rdsadmin_rman_util.crosscheck_archivelog(
p_delete_expired => TRUE,
p_rman_to_dbms_output => FALSE);
END;
/

Enable archive log retention

For this post, we enable the archive log mode on the RDS for Oracle instance and increase the archive log retention period to 24 hours. Because the archive logs are required for a consistent point-in-time restoration, it’s important that the archive logs are retained for at least the duration of the backup. The archive logs are retained in the underlying Amazon Elastic Block Store (Amazon EBS) volumes.

Configure the archive log with the following code:

- Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 
begin
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '24');
end;
/
commit;

Take the RMAN backup of Amazon RDS for Oracle

Take an RMAN backup for the entire RDS for Oracle database, along with archive log and control file. The RMAN backup pieces are stored in the underlying EBS volume of the RDS for Oracle instance. It’s imperative that your RDS for Oracle instance has sufficient free space to accommodate the RMAN backup pieces. You can check the free storage space of your RDS for Oracle instance by referring to the Amazon CloudWatch metric FreeStorageSpace. You can remove the backup pieces from the EBS volumes by using the fremove command.

  1. Run the following command to take the RMAN backup of the entire database with two channels: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

BEGIN
rdsadmin.rdsadmin_rman_util.backup_database_full(
p_owner => ‘SYS’,
p_directory_name => ‘RMAN_BACKUP’,
p_parallel => 2,
p_section_size_mb => 100,
p_include_archive_logs =>TRUE,
p_rman_to_dbms_output => FALSE);
END;
/

You can change any parameters depending on your environment. For more details of all parameters of the rdsadmin.rdsadmin_rman_util package, refer to Performing a full database backup.

  1. To get the log file name, use the following query: 
    SELECT filename FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like 'rds-rman-backup%';
  2. Run the following query to open the log file and check for any errors. Replace the file name obtained from the previous query and add it in the next query. 
    SELECT text FROM 
table(rdsadmin.rds_file_util.read_text_file('BDUMP','File-name-from above-query'));
  3. Run the following query to check the RMAN backup status: 
    - Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS 

col STATUS format a9
col hrs format 999.99 
select SESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs 
from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Transfer the backup pieces 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.

Upload the backup pieces to an S3 bucket by running the following command. We use the custom bucket name as input to the parameter p_bucket_name and specify the database directory name in the parameter p_directory_name.

- Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

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

The command returns a task ID. Use the following code to check the log file, making sure to insert the task ID. Confirm that the files are uploaded successfully.

SQL> SELECT text FROM table (rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-<Task ID>.log'));

Delete the files from the RDS for Oracle instance

After you upload the backup pieces, they’re ready to be downloaded to a self-managed instance like Amazon EC2. As a best practice, delete the files from the RDS for Oracle instance using the following command. This frees up space from the EBS volume. You can observe the increase via CloudWatch metrics.

- Login to the RDS for Oracle instance with a user with appropriate privileges using tools like SQLPLUS – 

-begin
for i in (select filename from (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('RMAN_BACKUP')) order by mtime) where filename like 'BACKUP%')
loop
UTL_FILE.FREMOVE ('RMAN_BACKUP', i.filename );
end loop;
end;
/

Self-managed environment steps

In this section, we walk through the steps to complete in your self-managed environment.

You must choose the Linux-x86-64 bit platform for hosting a self-managed database because Amazon RDS for Oracle runs on this platform. It’s always recommended to install the same version of binaries for the restore with the same or higher Patch Set Update (PSU) level in the self-managed target instance.

We use the AWS Command Line Interface (AWS CLI) to complete this process. Make sure that the AWS CLI is installed and configured on the self-managed target server and the S3 bucket is accessible from it.

Download the RMAN backup files and upload them to your local server

Navigate to the directory where you want to store the RMAN backup pieces for the RDS for Oracle instance. Use the following command to download the RMAN backup pieces that are stored in that location:

[ec2-user@ip-xxxx backup]$ aws s3 cp s3://<<YOUR_BUCKET_NAME>> . --recursive

In our case, we use a bucket named aws2p900rmanbackup:

[ec2-user@ip-xxxx backup]$ aws s3 cp s3://aws2p900rmanbackup . --recursive

Prepare the environment for restore

Prepare a dummy parameter file for the target database instance by providing a new instance name in the DB_NAME parameter, the control file location, and the most important parameters for RMAN duplication like db_file_name_convert and log_file_name_convert.

-Open initp900.ora and set the following parameters
db_name='p900'
db_file_name_convert = ('/rdsdbdata/db/ORCL_A/datafile/','/oracle/oradata/p900/')
log_file_name_convert=('/rdsdbdata/db/ORCL_A/onlinelog/','/oracle/oradata/p900/redo/')
control_files='/oracle/oradata/p900/control01.ctl'

Note the following parameter formats:

  • DB_FILE_NAME_CONVERT – (‘Source Location’,’Target Location‘)
  • LOG_FILE_NAME_CONVERT – (‘Source Location‘,’Target Location‘)
  • Default source data file location – /rdsdbdata/db/<DB Name>_A/datafile/
  • Default source redo file location – /rdsdbdata/db/<DB Name> _A/onlinelog/

You can obtain the database name on the Amazon RDS console on the Configuration tab. The target location is the respective mount point and directories created in the target server for hosting the data files and redo log files.

Start the auxiliary instance

To start the auxiliary instance using the dummy parameter file, enter the following code:

- Login to sqlplus as sysdba and start the instance in nomount state using the dummy parameter file -

ORACLE_HOME=/oracle/orabin; export ORACLE_HOME
ORACLE_SID=p900; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH

sqlplus / as sysdba

startup nomount pfile='initp900.ora';

SQL> startup nomount pfile='initp900.ora';

ORACLE instance started.

Total System Global Area
    9965666304 bytes

Fixed Size           12169848 bytes

Variable Size        1879051656 bytes

Database Buffers    8053063680 bytes

Redo Buffers  21381120 bytes

SQL> exit

Restore the database using the RMAN duplicate method

The following command changes the database name and restores the data files from the backup to the location indicated by DB_FILE_NAME_CONVERT, updates the new location of the data files in the control file, and recovers the database. In the following example, the location where the RMAN backup was downloaded from in the Amazon S3 environment is ‘/oracle/backup/’:

rman auxiliary /

RMAN> DUPLICATE DATABASE TO 'p900' BACKUP LOCATION '/oracle/backup/';

The following commands perform the following high-level steps while restoring the database:

  1. Restore the control file.
  2. Rename the database file names.
  3. Restore the data files in the location as specified in db_file_name_convert.
  4. Recover the instance.
  5. Start up the database in read/write mode.
- Login to sqlplus as sysdba --

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


SQL> select name from v$database;

NAME
---------
P900

You can check the progress of the RMAN restore using the following queries:

Query 1:
TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
FROM gv$process p, gv$session s WHERE p.addr = s.paddr AND client_info LIKE 'rman%';


Query 2:

alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, sysdate + TIME_REMAINING/3600/24 end_at from v$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';

Perform post-recovery steps

The self-managed Oracle instance has sysdba access; you can customize it according to your organizational policies and standards. After you restore the database, perform the following steps on the self-managed instance:

  1. Make sure the Oracle binaries and database are at the same Patch Release Update (PSU) or Release Update (RU) level as Amazon RDS for Oracle: 
    --Login to the RDS for Oracle instance with a user with appropriate privileges and run the below query--

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

--Login to the self-managed instance and run the below query--

$ORACLE_HOME/OPatch/opatch lsinventory

If ORACLE_HOME is lagging on the PSU or RU level, apply the same PSU or RU used by the RDS for Oracle instance to ORACLE_HOME of the self-managed instance. Refer the README of the given PSU or RU for more details.

  1. Make sure the ORACLE_HOME used by the self-managed instance has all the required time zone files; otherwise it can throw the error ORA-01804: failure to initialize timezone information.

You can upgrade the DST manually on your self-managed server to solve this issue by applying DST-related patches to ORACLE_HOME.

  1. Drop the RDSADMIN user: 
    --Login to self-managed Oracle instance as sysdba --

SQL>  drop user rdsadmin cascade;

The customizations implemented in Amazon RDS for Oracle are achieved by the RDSADMIN user, so you can safely drop this user in a self-managed environment.

Conclusion

In this post, we provided step-by-step guidance on restoring Amazon RDS for Oracle to a self-managed environment using the RMAN duplicate method, which doesn’t require a recovery catalog or connection to the target. You can use the Amazon RDS rdsadmin.rdsadmin_rman_util package, RMAN, and Amazon S3 integration to restore a physical copy of Amazon RDS for Oracle in a self-managed environment like Amazon EC2 or any other self-managed servers.

We welcome your feedback. If you have questions or suggestions, leave them in the comment section.

About the Authors

Arnab Saha is a Database Administrator with the Support Engineering team at Amazon Web Services. He specializes in Amazon RDS, Amazon Aurora and Amazon Elastic Block Store. He provides guidance and technical assistance to customers thus enabling them to build scalable, highly available and secure solutions in AWS Cloud.

Radhika Chakravarty is a Database Solutions Architect with Amazon Web Services. She works with customers and partners by providing technical assistance to design and implement cloud migration projects, helping them to migrate and modernize their existing databases to AWS Cloud.