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

By mullaned2002

March 21, 2022

1224

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:

Create a password file.
Create a parameter file from the source database for the target database.
Copy the password file and parameter file to the target database.
Check to make sure the source database is running in archive log mode.
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:

Edit the parameter file copied from the source database host.
Configure the tnsnames.ora file to be able to connect to the source DB instance.
Configure the environment for the RDS Custom DB instance.
Pause Amazon RDS Custom automation.
Drop the empty ORCL database on Amazon RDS Custom.
Open the ORCL database on Amazon RDS Custom in NOMOUNT state using the modified parameter file.
Perform RMAN active duplication.
Verify the status of the RDS Custom DB instance.
Create RDSADMIN user and grant privileges.
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:

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.
Create an Amazon Simple Storage Service (Amazon S3) bucket called rds-rmanbackup. 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.
Install and setup the AWS Command Line Interface (AWS CLI) on the source database host. For instructions, see Getting started with the AWS CLI.
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

Log in to the source database host as the OS user who owns the Oracle database install.
Set the correct environment variables of the source database.

-bash $ echo $ORACLE_HOME
/u01/app/oracle/
-bash $ echo $ORACLE_SID
ORCL

Create a password file using orapwd.

bash> $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwORCL

Validate by listing the file.

ls -r $ORACLE_HOME/dbs/orapwORCL

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

Login to the database as sysdba and create pfile.

$ sqlplus / as sysdba
SQL> create pfile=’/tmp/initORCL.ora’ from spfile;

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.

Copy the files to Amazon S3:

$ aws s3 cp /tmp/initORCL.ora s3://rds-rmanbackup
$ aws s3 cp $ORACLE_HOME/dbs/orapwORCL s3://rds-rmanbackup

Validate the files by listing the bucket and the objects within the bucket:

-bash-4.2$ aws s3 ls rds-rmanbackup
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.

Copy the files from Amazon S3 to the RDS Custom instance:

$ aws s3 cp s3://rds-rmanbackup/initORCL.ora $ORACLE_HOME/dbs/
$ aws s3 cp s3://rds-rmanbackup/orapwORCL /rdsdbdata/config/

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

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:

Go to $ORACLE_HOME/network/admin directory:

cd $ORACLE_HOME/network/admin

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)
)
)

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:

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

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)
)
)

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

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

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 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.

Enter the following commands in sql prompt:

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;

Create the profile RDSADMIN:

SQL>set echo on feedback on serverout on
SQL>CREATE PROFILE RDSADMIN
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 86400/86400
PASSWORD_GRACE_TIME 604800/86400
;

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;

Create the RDSADMIN tablespace:

SQL>CREATE BIGFILE TABLESPACE rdsadmin DATAFILE SIZE 7M
AUTOEXTEND ON NEXT 1m
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

Create the user RDSADMIN:

SQL>CREATE USER rdsadmin IDENTIFIED BY ***
DEFAULT TABLESPACE rdsadmin
TEMPORARY TABLESPACE TEMP
profile rdsadmin ;

Grant privileges to RDSADMIN:

SQL>grant dba to rdsadmin;
SQL>grant inherit any privileges to rdsadmin;
SQL>grant alter system to rdsadmin;
SQL>grant alter database to rdsadmin;
SQL>grant administer database trigger to rdsadmin;
SQL>grant any object privilege to rdsadmin with admin option;
SQL>grant connect to rdsadmin with admin option;
SQL>grant select_catalog_role to rdsadmin with admin option;
SQL>grant select any table to rdsadmin;
SQL>grant resource to rdsadmin with admin option;
SQL>alter user rdsadmin account unlock identified by ***;

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

Create master user and grant privileges

Since the starter database was dropped and the target database was restored from the source using RMAN, we must recreate the master user. In this example the master username is called admin

SQL> create user admin identified by <password>;
SQL> grant dba to admin;

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 focus 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.