AWS Database Blog

Physical migration of Oracle databases to Amazon RDS Custom using RMAN duplication

This blog post was reviewed and updated May 2022.

Organizations are moving their self-managed Oracle databases to AWS managed database services to modernize their applications and gain access to cloud services. With Amazon RDS Custom, you now have the privileges needed to perform a physical migration of your Oracle database into Amazon Relational Database Service (Amazon RDS). In this post, we describe the key factors you should consider for a migration and then dive into an example of performing a physical migration of a self-managed Oracle database to Amazon RDS Custom using RMAN duplication.

Amazon RDS Custom for Oracle is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database environment. It automates database administration tasks and operations while making it possible for you as a database administrator to access and customize your database environment and operating system.

Amazon RDS Custom provides the flexibility to bring your own media and perform custom patching and high availability configurations. It provides a managed experience by providing automatic provisioning, monitoring, backup and restore, scaling, and automation.

Key factors to consider for a migration

Database migration to AWS depends on many factors, such as the following:

  • The amount of downtime the application can afford
  • The size of the source database
  • Network connectivity (public internet, VPN, AWS Direct Connect) and bandwidth between the source and target databases
  • A requirement for a fallback plan
  • The source and target Oracle database version and DB instance OS types
  • Available replication tools like AWS Database Migration Service (AWS DMS), Oracle GoldenGate, or any third-party replication tool

Based on these factors, you can choose offline or online migration using physical migration, logical migration, or a combination of both physical and logical migration approaches. In the next sections, we walk through the steps to migrate an Oracle database to Amazon RDS Custom using the RMAN duplicate tool.

Solution overview

In this post, we focus on the steps to migrate an Oracle database on Amazon Elastic Compute Cloud (Amazon EC2) to Amazon RDS Custom using RMAN duplication. In the second post of this series, we demonstrate how to migrate a self-managed Oracle database to Amazon RDS Custom using Oracle Data Guard.

RMAN supports both backup-based duplication and active database duplication. Active database duplication doesn’t require a backup of the source database. It duplicates the live source database to the destination host by copying the database files over the network to the auxiliary (clone) instance. The RMAN duplicate database function can copy the required files as image copies or backup sets. In this post, we focus on RMAN active database duplication.

We perform the following steps on the source DB instance side:

  1. Create a password file.
  2. Create a parameter file from the source database for the target database.
  3. Copy the password file and parameter file to the target database.
  4. Check to make sure the source database is running in archive log mode.
  5. Configure the tnsnames.ora file on the source DB server to be able to communicate with the target.

We perform the following steps on the RDS Custom DB instance side:

  1. Edit the parameter file copied from the source database host.
  2. Configure the tnsnames.ora file to be able to connect to the source DB instance.
  3. Configure the environment for the RDS Custom DB instance.
  4. Pause Amazon RDS Custom automation.
  5. Drop the empty ORCL database on Amazon RDS Custom.
  6. Open the ORCL database on Amazon RDS Custom in NOMOUNT state using the modified parameter file.
  7. Perform RMAN active duplication.
  8. Verify the status of the RDS Custom DB instance.
  9. Create RDSADMIN user and grant privileges.
  10. Resume Amazon RDS Custom automation.

Prerequisites

This post assumes you have the following setup:

  • The source database is an Oracle database running on Amazon EC2 with Oracle Linux OS.
  • The source database name is ORCL.
  • The target database is an RDS Custom for Oracle DB instance.
  • The solution is applicable when the source DB instance is on premises as long as you have appropriate network connectivity between the on-premises network and Amazon Virtual Private Cloud (Amazon VPC).

Complete the following prerequisite steps before starting your migration:

  1. Create an RDS Custom for Oracle DB instance with a DB name called ORCL (the same as the source database name). For instructions, see Working with Amazon RDS Custom. Create this starter database with a custom name that you want for the target database.
    1. Create an Amazon Simple Storage Service (Amazon S3) bucket (In this post, use your bucket name where we use <YOUR BUCKET>). You use this as a landing zone to copy the parameter file and the password file from the source database server to the RDS Custom database server. If you have a pre-existing bucket, you can use that or you can also transfer files directly via SSH from the source to the RDS Custom database server if the ports are open for SSH between the two.
  2. Install and setup the AWS Command Line Interface (AWS CLI) on the source database host. For instructions, see Getting started with the AWS CLI.
  3. Make sure you have network connectivity between the source and RDS Custom database servers and DB port 1521 is open for communication between the two.

Source database instance steps

In this section, we walk through the steps to perform on the source DB instance.

Create a password file

  1. Log in to the source database host as the OS user who owns the Oracle database install.
  2. Set the correct environment variables of the source database. 
    -bash $ echo $ORACLE_HOME
	/u01/app/oracle/
	-bash $  echo $ORACLE_SID
	ORCL
  3. Create a password file using orapwd. 
    bash> $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwORCL
  4. Validate by listing the file. 
    ls -r $ORACLE_HOME/dbs/orapwORCL

Create a parameter file on the source database for the target database

  1. Login to the database as sysdba and create pfile. 
    $ sqlplus / as sysdba 
	SQL> create pfile='/tmp/initORCL.ora' from spfile;
  2. Validate the pfile creation by listing the file: 
    ls -r /tmp/initORCL.ora

Copy the password file and parameter file to the target database

Copy the files to the RDS Custom database server either via an S3 bucket or SSH, based on your VPC setup. The following steps show how to copy the files to via Amazon S3.

  1. Copy the files to Amazon S3: 
    $ aws s3 cp /tmp/initORCL.ora  s3://<YOUR BUCKET>
$ aws s3 cp $ORACLE_HOME/dbs/orapwORCL s3://<YOUR BUCKET>
  2. Validate the files by listing the bucket and the objects within the bucket: 
    -bash-4.2$ aws s3 ls <YOUR BUCKET>
2021-10-26 20:36:21          initORCL.ora
2021-10-26 20:36:21          orapwORCL

If you have any issues connecting to the S3 bucket, verify permissions on the bucket and connectivity to Amazon S3. For any issues with the AWS CLI, refer to Troubleshooting AWS CLI errors.

  1. Copy the files from Amazon S3 to the RDS Custom instance: 
    $ aws s3 cp s3://<YOUR BUCKET>/initORCL.ora $ORACLE_HOME/dbs/
$ aws s3 cp s3://<YOUR BUCKET>/orapwORCL /rdsdbdata/config/

    You can delete the files from the S3 bucket and the bucket itself if you no longer need it.

  2. Validate the files on the RDS Custom instance: 
    -bash-4.2$ ls -r $ORACLE_HOME/dbs/initORCL.ora
/rdsdbbin/bin initORCL.ora

-bash-4.2$ ls -r /rdsdbdata/config/orapwORCL
/rdsdbdata/config/orapwORCL
  3. Create a symbolic link: 
    $ ln -sf /rdsdbdata/config/orapw /rdsdbbin/oracle/dbs/orapwORCL

Make sure the source database is running in archive log mode

Check that your source database is in archive log mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

If your source database isn’t in archive log mode, refer to Changing the Database Archiving Mode to convert it to archive log mode.

Configure tnsnames.ora on the source DB server

To configure the tnsnames.ora file on the source DB server to be able to communicate with the target, complete the following steps:

  1. Go to $ORACLE_HOME/network/admin directory: 
    cd $ORACLE_HOME/network/admin
  2. Append the following to the tnsnames.ora file: 
    DB_SOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <source_instance_IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
DB_TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <RDS_Custom_instance_IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
  3. Verify the network connectivity by performing a tnsping check on the source DB instance. A successful configuration returns OK. 
    $ tnsping DB_TARGET
OK

RDS Custom database instance steps

In this section, we use the RMAN duplicate method to migrate the source database to the RDS Custom DB instance. Before running RMAN, we drop the ORCL starter database on the RDS Custom DB instance and bring up the empty database in NOMOUNT mode.

Edit the parameter file copied from the source database host

Open the initORCL.ora file copied from the source database server and edit it to include db_file_name_convert and log_file_name_convert variables. You can change the db_name if you’re required to have a different DB name. In this example, we use the same DB name same as the source. You can also change your memory parameter settings accordingly based on your environment. Refer to the below example:

	ORCL.__data_transfer_cache_size=0
	ORCL.__db_cache_size=6039797760
	ORCL.__inmemory_ext_roarea=0
	ORCL.__inmemory_ext_rwarea=0
	ORCL.__java_pool_size=0
	ORCL.__large_pool_size=33554432
	ORCL.__oracle_base='/rdsdbbin'#ORACLE_BASE set from environment
	ORCL.__pga_aggregate_target=4966055936
	ORCL.__sga_target=7449083904
	ORCL.__shared_io_pool_size=134217728
	ORCL.__shared_pool_size=1207959552
	ORCL.__streams_pool_size=0
	ORCL.__unified_pga_pool_size=0
	*.archive_lag_target=300
	*.audit_file_dest='/rdsdbdata/admin/ORCL/adump'
	*.compatible='19.0.0'
	*.control_files='/rdsdbdata/db/ORCL/controlfile/control-01.ctl'
	*.db_block_checking='MEDIUM'
	*.db_create_file_dest='/rdsdbdata/db'
	*.db_name='ORCL'
	*.db_recovery_file_dest_size=1073741824
	*.db_unique_name='ORCL'
	*.dbfips_140=FALSE
	*.diagnostic_dest='/rdsdbdata/log'
	*.filesystemio_options='setall'
	*.heat_map='OFF'
	*.job_queue_processes=50
	*.local_listener='(address=(protocol=tcp)(host=)(port=8200))'
	*.log_archive_dest_1='location="/rdsdbdata/db/ORCL/arch/redolog", valid_for=(ALL_LOGFILES,ALL_ROLES)'
	*.log_archive_format='-%s-%t-%r.arc'
	*.max_string_size='STANDARD'
	*.memory_max_target=12385852416
	*.memory_target=12385852416
	*.open_cursors=300
	*.pga_aggregate_target=0
	*.processes=1673
	*.recyclebin='OFF'
	*.sga_target=0
	*.undo_tablespace='UNDO_T1'
	*.use_large_pages='FALSE'
	*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/',' /rdsdbdata/db/ORCL/datafile/'
	*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/',' /rdsdbdata/db/ORCL/onlinelog/'

Configure tnsnames.ora to be able to connect to the source database instance

To configure tnsnames.ora so it can connect to the source DB instance, complete the following steps:

  1. Go to $ORACLE_HOME/network/admin directory: 
    $ cd /rdsdbbin/oracle/network/admin/

-bash-4.2$ ls -ltr
total 4
-rw-r--r-- 1 rdsdb database 1536 Feb 14  2018 shrept.lst
lrwxrwxrwx 1 rdsdb database   30 Oct 26 22:11 listener.ora -> /rdsdbdata/config/listener.ora
lrwxrwxrwx 1 rdsdb database   28 Oct 26 22:11 sqlnet.ora -> /rdsdbdata/config/sqlnet.ora
lrwxrwxrwx 1 rdsdb database   30 Oct 26 22:11 tnsnames.ora -> /rdsdbdata/config/tnsnames.oracd $ORACLE_HOME/network/admin
  2. Edit the tnsnames.ora file to append the following entries: 
    DB_SOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <source_instance_IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
DB_TARGET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <RDS_Custom_instance_IP>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)
  3. Verify the networking connection and TNS entries by running the following commands on the RDS Custom DB instance. A successful configuration returns OK. 
    $ tnsping DB_SOURCE
OK

Set the environment for the RDS Custom ORCL database instance

Make sure you have set your environment to ORCL in the RDS Custom database by verifying $ORACLE_SID, $ORACLE_HOME, $PATH.

Pause Amazon RDS Custom automation

You need to pause the automation mode on your RDS Custom instance before proceeding with the next steps to make sure the automation doesn’t interfere with the RMAN activity.

Pause the automation using the following AWS CLI command:

aws rds modify-db-instance --db-instance-identifier custom-ORCL --automation-mode all-paused  --resume-full-automation-mode-minute 120 --region us-east-2

Prepare RDS Custom monitoring and master user creation script

Prepare a SQL script for recreating RDSADMIN and RDS Custom master users, tablespaces and profile which can be run after recreating the database. Find the master username which needs to be recreated (Master username is under configuration tab of RDS console) If the master username is different from ADMIN, please replace the keyword ADMIN with correct username in the below line in the script.

define v_username = "'RDSADMIN','ADMIN'"

Copy below script as recreate_custom_resources_input.sql in a working directory and execute it. This will create a SQL script for recreating RDSADMIN and RDS Custom master user, tablespaces and profile which can be run at a later stage on the primary instance.

************************ COPY FROM NEXT LINE ******************
	set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off
	verify off trimspool on
	column ddl format a1000
	begin
	dbms_metadata.set_transform_param (dbms_metadata.session_transform,
	'SQLTERMINATOR', true);
	dbms_metadata.set_transform_param (dbms_metadata.session_transform,
	'PRETTY', true);
	end;
	/
	define v_username = "'RDSADMIN','ADMIN'"
	spool recreate_custom_resources_output.sql
	select dbms_metadata.get_ddl('DIRECTORY','OPATCH_INST_DIR') as ddl from dual;
	select dbms_metadata.get_ddl('DIRECTORY','OPATCH_LOG_DIR') as ddl from dual;
	select dbms_metadata.get_ddl('DIRECTORY','OPATCH_SCRIPT_DIR') as ddl from
	dual;
	select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
	from dba_users u
	where u.profile='RDSADMIN';
	select dbms_metadata.get_ddl('TABLESPACE','RDSADMIN' ) AS ddl from dual;
	select dbms_metadata.get_ddl('TABLESPACE','TEMP' ) AS ddl from dual;
	select dbms_metadata.get_ddl('TABLESPACE','USERS' ) AS ddl from dual;
	select dbms_metadata.get_ddl('USER', u.username) AS ddl
	from dba_users u
	where u.username in (&v_username)
	union all
	select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
	from dba_ts_quotas tq
	where tq.username in (&v_username)
	union all
	select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
	from dba_role_privs rp
	where rp.grantee in (&v_username)
	union all
	select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
	from dba_sys_privs sp
	where sp.grantee in (&v_username)
	union all
	select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
	from dba_tab_privs tp
	where tp.grantee in (&v_username)
	union all
	select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
	from dba_role_privs rp
	where rp.grantee in (&v_username)
	andrp.default_role = 'YES'
	/
	set linesize 80 pagesize 14 feedback on trimspool on verify on
	spool off
*******************COPY UNTIL PREVIOUS LINE ******************

Drop the ORCL empty database on RDS Custom

Make sure you’re connected to the empty database on the RDS Custom instance before running the DROP DATABASE command:

$ sqlplus / as sysdba 
SQL> SHUTDOWN IMMEDIATE 
SQL> STARTUP MOUNT EXCLUSIVE RESTRICT 
SQL> DROP DATABASE;

Open the ORCL database on Amazon RDS Custom in NOMOUNT state

Open the ORCL database on Amazon RDS Custom in NOMOUNT state using the modified parameter file:

SQL> startup nomount pfile='/rdsdbbin/oracle/dbs/initORCL.ora';
ORACLE instance started.

Run RMAN active duplication

Run the RMAN duplicate command as follows by entering the SYS password:

-bash $ rman target sys/<password>@DB-SOURCE auxiliary sys/<password>
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Oct 25 21:36:08 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=4089929259)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate database to 'ORCL' from active database NOFILENAMECHECK;

Starting Duplicate Db at 25-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
current log archived
…
…
…
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 25-OCT-21

Verify the status of the RDS Custom database instance

Verify the DB instance status with the following sql command:

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL     READ WRITE

Create the RDSADMIN and Master user and grant privileges

RDSADMIN is a monitoring and orchestrator database user in the RDS Custom DB instance. Because the starter database was dropped and the target database was restored from the source using RMAN, we must recreate this user after the restore to make sure RDS Custom monitoring works as expected. We also need a separate profile and tablespace created for the RDSADMIN user.

  1. Login to the database and alter the default profile as below: 
    SQL>set echo on feedback on serverout on
SQL>@?/rdbms/admin/utlpwdmg.sql
SQL>ALTER PROFILE DEFAULT
LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
  2. Execute script recreate_custom_resources_output.sql which was created as an output file on the RDS Custom instance before dropping the starter database. 
    SQL>@recreate_custom_resources_output.sql
  3. Set SYS, SYSTEM, and DBSNMP user profiles to RDSADMIN: 
    SQL>set echo on feedback on serverout on
SQL>alter user SYS profile RDSADMIN;
SQL>alter user SYSTEM profile RDSADMIN;
SQL>alter user DBSNMP profile RDSADMIN;

SQL> @?/rdbms/admin/userlock.sql
SQL> @?/rdbms/admin/utlrp

Resume Amazon RDS Custom automation

After all the activities are complete, we can resume the automation with the following AWS CLI command:

aws rds  modify-db-instance --db-instance-identifier custom-ORCL --automation-mode full --region us-east-2

You have now successfully migrated your Oracle database on Amazon EC2 to Amazon RDS Custom using RMAN duplicate.

Conclusion

In this post, we reviewed the key factors to consider for a database migration and went over how to perform a physical migration of a self-managed Oracle database to Amazon RDS Custom for Oracle using the native RMAN duplicate tool. In our next post, we walk through the steps to perform a physical migration of an Oracle database to Amazon RDS Custom using the Data Guard standby approach. Share your thoughts in the comments.

About the Authors

Yamuna Palasamudram is a Senior Database Specialist Solutions Architect with Amazon Web Services. She works with AWS RDS team, focusing on commercial database engines like Oracle. She enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.

Nitin Saxena is a Senior Database Engineer in RDS DBS Managed Commercial Engines with Amazon Web Services. He focuses on services like RDS Oracle and RDS Custom for Oracle . He enjoys designing and developing new features on RDS Oracle and RDS Custom to solve customer problems.