Physical migration of Oracle databases to Amazon RDS Custom using Data Guard

This blog post was reviewed and updated May 2022.

In the first post of this series, we showed you how to perform a physical migration of an Oracle database into Amazon RDS Custom using the RMAN duplicate tool. In this post, we walk through the steps to migrate Oracle databases into Amazon RDS Custom using Oracle Data Guard with minimum downtime.

Solution overview

We perform the following steps on the primary database instance (ORCL):

1. Check to make sure the primary database in running in archive log mode and FORCE_LOGGING is enabled.
2. Create the user RDS_DATAGUARD.
3. Create the user RDSADMIN.
4. Perform an RMAN online backup of the primary database, including data files, archive logs, and a control file for standby.
5. Create a parameter file.
6. Create a password file.
7. Copy the backup sets, parameters file, and password file to the S3 bucket.

We perform the following steps on the RDS Custom standby instance:

1. Copy all the files from the Amazon Simple Storage Service (Amazon S3) bucket rds-rmanbackup to the standby instance.
2. Pause automation on the standby DB instance.
3. Drop the ORCL empty database on Amazon RDS Custom.
4. Remove the transactionLogUploadTracker.json metadata file.
5. Edit the parameter file on the standby instance.
6. Create two subdirectories.
7. Create the server parameter file from the parameter file.

Finally, we perform the following steps to perform the migration:

1. Configure Oracle listeners on both instances.
2. Configure tnsnames.ora on both instances.
3. Perform restore and recovery on the standby instance by starting it in NOMOUNT mode.
4. Update the symbolic links on the standby instance.
5. Start the Oracle Data Guard broker on both instances.
6. Enable Oracle Data Guard on the primary instance, then add the standby instance to the configuration.
7. Configure fal_server and fal_client on the standby instance.
8. Update the local_listener parameter on both instances.
9. Configure the standby instance redo logs on both instances.
10. Recover the standby instance.
11. Perform the manual switchover.
12. Resume automation on the RDS Custom instance.

Prerequisites

This post assumes you have the following setup:

• The primary instance is on Amazon Elastic Compute Cloud (Amazon EC2) with Oracle Linux, and the primary and standby database software versions are same.
• The solution is applicable when the primary database instance is on premises as long as you have appropriate network connectivity between the on-premises network and Amazon Virtual Private Cloud (Amazon VPC).
• The primary database DB name is ORCL.

Complete the following prerequisite steps before starting your migration:

1. Create an RDS Custom for Oracle DB instance named ORCL (the same as the primary database name). For instructions, see Working with Amazon RDS Custom.
   • Once you have the RDS Custom for Oracle DB instance up & running, get user creation script for RDSADMIN user and master user. Use the below instructions to extract the definition of RDSADMIN and master user and we will use this script at a later stage to provision these users on the primary database instance.
   • 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'"

     Bash
   • 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 ******************

     SQL
2. Configure the AWS Command Line Interface (AWS CLI) on both instances. For instructions, see Getting started with the AWS CLI.
3. Make sure you have network connectivity between the primary and RDS Custom database servers and DB port 1521 is open for communication between the two.

Primary DB instance steps

In this section, we walk through the steps to perform on the primary DB instance (ORCL).

Confirm the primary database is running in archive log mode and FORCE_LOGGING is enabled

For instructions on enabling archive log mode on your database, refer to Changing the Database Archiving Mode. For instructions on enabling force logging, refer to Force Logging in Oracle.

Run the following sql queries to validate:

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

Create the user RDS_DATAGUARD on the primary instance

We create a dedicated user for Oracle Data Guard replication. In this example, the name of the user is RDS_DATAGUARD. We recommend using the same user name because Amazon RDS Custom uses RDS_DATAGUARD for replication.

SQL> CREATE USER RDS_DATAGUARD IDENTIFIED BY dg_12345$;

User created.

SQL> GRANT SYSOPER, SYSDG, ADMINISTER DATABASE TRIGGER TO RDS_DATAGUARD;

Grant succeeded.

SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER=RDS_DATAGUARD SCOPE=BOTH;

System altered.

Create RDSADMIN and Master user on the primary instance

RDSADMIN is a monitoring and orchestrator database user in the RDS Custom DB instance. This user must exist in Amazon RDS Custom for Oracle after the Data Guard switchover, so you must create it on the primary database instance.

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;

   SQL
2. Execute script recreate_custom_resources_output.sql which was created as an output file on the RDS Custom instance in the pre-requisites section to create RDSADMIN and master user, tablespaces and profile.

   SQL>@recreate_custom_resources_output.sql

   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

   SQL

Perform an RMAN online backup of the primary database

In this example, we use /rdsdbdata/backup as the backup file location.

1. Create the directory /rddbdata/backup:

   -bash-4.2$ mkdir /rdsdbdata/backup

   Bash
2. Backup the data files and archive logs:

   -bash-4.2$ rman target /
   connected to target database: ORCL (DBID=1614918219)
   RMAN> run
       {
       backup as compressed backupset
       filesperset 2
       format '/rdsdbdata/backup/db_%U'
       database;
       sql 'alter system archive log current';
       backup as compressed backupset
       filesperset 50
       format '/rdsdbdata/backup/arch_%U'
       archivelog all;
       }
   
   Starting backup at 15-NOV-21
   …
   Finished backup at 15-NOV-21
   
   Starting Control File and SPFILE Autobackup at 15-NOV-21
   piece handle=/rdsdbbin/oracle/dbs/c-1614918219-20211115-05 comment=NONE
   Finished Control File and SPFILE Autobackup at 15-NOV-21

   SQL
3. Backup the control file for standby:

   -bash-4.2$ rman target /
   connected to target database: ORCL (DBID=1614918219)
   
   RMAN> backup current controlfile for standby format '/rdsdbdata/backup/standby.ctl';
   
   Starting backup at 15-NOV-21
   …
   Finished backup at 15-NOV-21
   
   Starting Control File and SPFILE Autobackup at 15-NOV-21
   piece handle=/rdsdbbin/oracle/dbs/c-1614918219-20211115-06 comment=NONE
   Finished Control File and SPFILE Autobackup at 15-NOV-21

   Bash

Create a parameter file on the primary instance

1. Login to the database as sysdba and create pfile.

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

   SQL
2. Validate the pfile creation by listing the file:

   ls -r /tmp/initORCL.ora

   SQL

Create a password file on the primary instance

Complete the following steps:

1. Create a password file using orapwd:

   -bash-4.2$ $ORACLE_HOME/bin/orapwd file=/rdsdbdata/config/orapw
   Enter password for SYS:

   Bash
2. Validate by listing the file:

   -bash-4.2$ ls /rdsdbdata/config/orapw
   /rdsdbdata/config/orapw

   Bash
3. Create a symbolic link:

   -bash-4.2$ ln -sf /rdsdbdata/config/orapw /rdsdbbin/oracle/dbs/orapwORCL

   Bash
4. Validate the symbolic link:

   -bash-4.2$ ls -ltra /rdsdbbin/oracle/dbs/orapwORCL
   lrwxrwxrwx 1 rdsdb rdsdb 23 Nov 15 23:02 /rdsdbbin/oracle/dbs/orapwORCL -> /rdsdbdata/config/orapw

   Bash

Copy the backup sets, parameter file, and password file to the S3 bucket

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

1. Create an S3 bucket named rds-rmanbackup:

   aws s3 mb s3://rds-rmanbackup --region us-west-2
   make_bucket: rds-rmanbackup

   Bash
2. Validate the bucket by listing the contents:

   $ aws s3 ls s3://rds-rmanbackup

   Bash
3. Copy all RMAN backup files and standby control file to the S3 bucket:

   -bash-4.2$ aws s3 cp /rdsdbdata/backup s3://rds-rmanbackup/ --recursive
   upload: ../../rdsdbdata/backup/db_0i0e8v7q_1_1 to s3://rds-rmanbackup/db_0i0e8v7q_1_1
   …

   Bash
4. Copy the parameter file and password file to the S3 bucket:

   -bash-4.2$ aws s3 cp /tmp/initORCL.ora  s3://rds-rmanbackup
   upload: ../../tmp/initORCL.ora to s3://rds-rmanbackup/initORCL.ora
   
   -bash-4.2$ aws s3 cp /rdsdbdata/config/orapw s3://rds-rmanbackup
   upload: ../../rdsdbdata/config/orapw to s3://rds-rmanbackup/orapw

   Bash

RDS Custom standby instance steps

In the following section, we walk through the steps to perform on the standby instance.

Copy all the backup files from the S3 bucket rds-rmanbackup to the standby instance

1. Create a directory /rddbdata/backup on standby instance:

   -bash-4.2$ mkdir /rdsdbdata/backup

   Bash
2. Copy the backup files from Amazon S3 to the standby instance:

   -bash-4.2$ aws s3 cp s3://rds-rmanbackup/ /rdsdbdata/backup --recursive
   download: s3://rds-rmanbackup/arch_0k0e8v7t_1_1 to 
   ../

   Bash

Pause automation on the standby instance

You must pause the automation mode on your standby instance before proceeding with the next steps to make sure the automation doesn’t interfere with the activities you perform. Pause the automation by running the following AWS CLI command:

aws rds  modify-db-instance --db-instance-identifier rds-s --automation-mode all-paused  --resume-full-automation-mode-minute 180 --region us-west-2 

Drop the ORCL empty database on Amazon RDS Custom

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

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

Remove the transactionLogUploadTracker.json metadata file on the standby instance

When we create a standby on Amazon RDS Custom from a database on Amazon EC2 or on premises by dropping the RDS Custom instance, Amazon RDS Custom automation fails to upload the archive logs. This is because the automation recognizes that there is a new incarnation so it tries to fetch all previous incarnation based on current incarnation. In this case, because there’s no relation between the incarnation of the current instance and the RDS Custom instance incarnation, the automation query fails and returns no rows. To overcome this error, we have to move or remove the transactionLogUploadTracker.json metadata file, which keeps track of the logs.

As the root user, check the status the RDS Custom agent to make sure it’s not running:

[root@ip-10-xx ec2-user]# service rdscustomagent status
Jan 31 20:40:50 systemd[1]: Stopped rdscustomagent.

If the agent is running, stop it using service rdscustomagent stop.

Rename the transactionLogUploadTracker.json file:

root@ip-10-xx ec2-user]# cd /opt/aws/rdscustomagent/config
[root@ip-10-xxconfig]# mv transactionLogUploadTracker.json transactionLogUploadTracker.json.bak

Copy the parameter file of the primary instance to the standby instance

Copy the parameter file from Amazon S3 with the following command:

-bash-4.2$ aws s3 cp s3://rds-rmanbackup/initORCL.ora $ORACLE_HOME/dbs/
download: s3://rds-rmanbackup/initORCL.ora to ../../rdsdbbin/oracle/dbs/initORCL.ora

Copy the password file of the primary instance to the standby instance

1. Copy the password file from Amazon S3 with the following command:

   -bash-4.2$ aws s3 cp s3://rds-rmanbackup/orapw /rdsdbdata/config/
   download: s3://rds-rmanbackup/orapw to ./orapw

   Bash
2. Create a symbolic link:

   -bash-4.2$ ln -sf /rdsdbdata/config/orapw /rdsdbbin/oracle/dbs/orapwORCL

   Bash
3. Validate the file by listing it:

   -bash-4.2$ ls -ltra /rdsdbbin/oracle/dbs/orapwORCL
   lrwxrwxrwx 1 rdsdb rdsdb 23 Nov 16 00:42 /rdsdbbin/oracle/dbs/orapwORCL -> /rdsdbdata/config/orapw

   Bash

Edit the parameter file on the standby instance

Using a text editor, open $ORACLE_HOME/dbs/initORCL.ora, change db_unique_name to ORCL_B, and replace every occurrence of ORCL_A in the paths with ORCL_B. You should 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_B/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_B'
	*.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_B/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
	*.spfile='/rdsdbbin/oracle/dbs/spfileORCL.ora'
	*.undo_tablespace='UNDO_T1'
	*.use_large_pages='FALSE'

Create two subdirectories

Create two subdirectories with the following commands:

-bash-4.2$ mkdir -p /rdsdbdata/db/ORCL_B/controlfile
-bash-4.2$ ls /rdsdbdata/db/ORCL_B/controlfile

-bash-4.2$ mkdir -p /rdsdbdata/db/ORCL_B/arch/
-bash-4.2$ ls /rdsdbdata/db/ORCL_B/arch/

Make sure the following paths exists (if not, create them):

$ ls /rdsdbdata/admin/ORCL/adump
$ ls /rdsdbdata/db/ORCL_B/controlfile
$ ls /rdsdbdata/db
$ ls /rdsdbdata/log
$ ls /rdsdbdata/db/ORCL_B/arch/

Create the server parameter file from the parameter file on the standby instance

1. Create the server parameter file with the following sql command:

   SQL> create spfile = '/rdsdbdata/admin/ORCL/pfile/spfileORCL.ora' from pfile;

   SQL
2. Create a link to spfile and validate it:

   -bash-4.2$ ln -sfn /rdsdbdata/admin/ORCL/pfile/spfileORCL.ora /rdsdbbin/oracle/dbs/spfileORCL.ora
   	-bash-4.2$ ls -ltra /rdsdbbin/oracle/dbs/spfileORCL.ora
   	lrwxrwxrwx 1 rdsdb rdsdb 42 Nov 15 23:40 /rdsdbbin/oracle/dbs/spfileORCL.ora -> /rdsdbdata/admin/ORCL/pfile/spfileORCL.ora

   Bash
3. Move the init file to /tmp:

   mv $ORACLE_HOME/dbs/initORCL.ora /tmp

   SQL

Primary and standby instance steps

In this section, we perform the following steps on both the primary and standby instances to complete the migration

Configure Oracle listeners on both instances

To configure Oracle listeners on both instances, complete the following steps:

1. On the primary instance, stop the listener:

   cd $ORACLE_HOME/network/admin/
   -bash-4.2$ $ORACLE_HOME/bin/lsnrctl stop L_ORCL_001
   …
   The command completed successfully

   Bash
2. Append the following to listener.ora and substitute primary_instance_ipv4 with your standby instance IP address:

   ADR_BASE_L_ORCL_DG=/rdsdbdata/log
   SID_LIST_L_ORCL_DG=(SID_LIST = (SID_DESC = (SID_NAME = ORCL)(GLOBAL_DBNAME = ORCL) (ORACLE_HOME = /rdsdbbin/oracle)))
   L_ORCL_DG=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = <primary_instance_ipv4>)))
   SUBSCRIBE_FOR_NODE_DOWN_EVENT_L_ORCL_DG=OFF

   Bash
3. Start the Data Guard local listener:

   -bash-4.2$ $ORACLE_HOME/bin/lsnrctl start L_ORCL_DG
   …
   Services Summary...
   Service "ORCL" has 1 instance(s).
     Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
   The command completed successfully

   Bash
4. Check the listener status:

   $ORACLE_HOME/bin/lsnrctl status L_ORCL_DG

   SQL
5. On the standby instance, stop the original listener:

   -bash-4.2$ /rdsdbbin/oracle/bin/lsnrctl stop L_ORCL_001
   …
   The command completed successfully

   Bash
6. Use the following AWS CLI command to get the standby private IP address by substituting <standby_instance_resource_id>:

   aws ec2 describe-instances --region us-west-2    --filters Name=tag:Name,Values=<standby_instance_resource_id>    --query 'Reservations[*].Instances[*].NetworkInterfaces[*].[PrivateIpAddress]'    --output text

   Bash

You can find the value for the resource ID on the Amazon RDS Custom console in the Summary section under Configuration, or by using the AWS CLI.

7. Append the following to listener.ora and substitute standby_instance_ipv4 with your primary instance IP address:

   ADR_BASE_L_ORCL_DG=/rdsdbdata/log
   SID_LIST_L_ORCL_DG=(SID_LIST = (SID_DESC = (SID_NAME = ORCL)(GLOBAL_DBNAME = ORCL) (ORACLE_HOME = /rdsdbbin/oracle)))
   L_ORCL_DG=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(PORT = 1140)(HOST = <standby_instance_ipv4>)))
   SUBSCRIBE_FOR_NODE_DOWN_EVENT_L_ORCL_DG=OFF

   Bash
8. Start the Data Guard local listener:

   -bash-4.2$ /rdsdbbin/oracle/bin/lsnrctl start L_ORCL_DG
   Starting /rdsdbbin/oracle/bin/tnslsnr: please wait...
   …
   Services Summary...
   Service "ORCL" has 1 instance(s).
     Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
   The command completed successfully

   Bash
9. Check the status of the Data Guard local listener:

   bash-4.2$ /rdsdbbin/oracle/bin/lsnrctl status L_ORCL_DG
   …
   Services Summary...
   Service "ORCL" has 1 instance(s).
     Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
   The command completed successfully

   Bash

Configure tnsnames.ora on both instances

Put the following entry in tnsnames.ora. Replace ORCL_A and ORCL_B with the TNS address names you choose. Replace <primary_instance_ipv4> with your primary instance IP, and replace <standby_instance_ipv4> with the IP in the previous step. The port for the standby instance is 1140.

ORCL_A =(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<primary_instance_ipv4>)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
ORCL_B =(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<standby_instance_ipv4>)(PORT=1140)))(CONNECT_DATA=(SID=ORCL)))

Verify the networking connection and TNS entries by performing the following commands on both instances. A successful configuration will return OK.

-bash-4.2$ tnsping ORCL_A
…
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= 10.x.x.x)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))

OK (0 msec)

-bash-4.2$ tnsping ORCL_B
…
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.y.y.y)(PORT=1140)))(CONNECT_DATA=(SID=ORCL)))

OK (0 msec)

Perform restore and recovery on the standby database

To restore and recover the standby database, complete the following steps:

1. On the standby instance, start the standby database with NOMOUNT mode and restore the standby control file:

   SQL> startup nomount
   ORACLE instance started.
   
   -bash-4.2$ rman target /
   …
   connected to target database: ORCL (not mounted)
   RMAN> restore standby controlfile from '/rdsdbdata/backup/standby.ctl';
   Starting restore at 16-NOV-21
   …
   Finished restore at 16-NOV-21
   
   RMAN> alter database mount;
   released channel: ORA_DISK_1
   Statement processed

   SQL
2. Use report schema to verify DB_FILE_NAME_CONVERT and that the data file path and name look accurate:

   RMAN> report schema;
   
   RMAN-06139: warning: control file is not current for REPORT SCHEMA
   Report of database schema for database with db_unique_name ORCL_B
   
   List of Permanent Datafiles
   ===========================
   File Size(MB) Tablespace           RB segs Datafile Name
   ---- -------- -------------------- ------- ------------------------
   1    0        SYSTEM               ***     /rdsdbdata/db/ORCL_A/datafile/o1_mf_system_jqofgf5c_.dbf
   2    0        SYSAUX               ***     /rdsdbdata/db/ORCL_A/datafile/o1_mf_sysaux_jqofggq7_.dbf
   3    0        UNDO_T1              ***     /rdsdbdata/db/ORCL_A/datafile/o1_mf_undo_t1_jqofgh9j_.dbf
   4    0        USERS                ***     /rdsdbdata/db/ORCL_A/datafile/o1_mf_users_jqofghkz_.dbf
   5    0        RDSADMIN             ***     /rdsdbdata/db/ORCL_A/datafile/o1_mf_rdsadmin_jqogj43b_.dbf
   
   List of Temporary Files
   =======================
   File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
   ---- -------- -------------------- ----------- --------------------
   1    100      TEMP                 102400      /rdsdbdata/db/ORCL_A/datafile/o1_mf_temp_jrv5wjq1_.tmp

   Bash
3. Restore the database:

   RMAN> restore database;
   Starting restore at 16-NOV-21
   …
   Finished restore at 16-NOV-21

   Bash

If the standby instance has the different directory structure for backup pieces, you need to catalog the files manually and use set newname.

4. Get the last available sequence number from the archive log backup set:

   RMAN> list backup of archivelog all;
   …
     List of Archived Logs in backup set 19
     Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
     ---- ------- ---------- --------- ---------- ---------
     1    1154    1207702    15-NOV-21 1207875    15-NOV-21
     1    1155    1207875    15-NOV-21 1207883    15-NOV-21
     1    1156    1207883    15-NOV-21 1208312    15-NOV-21
     1    1157    1208312    15-NOV-21 1208320    15-NOV-21
     1    1158    1208320    15-NOV-21 1208978    15-NOV-21
     1    1159    1208978    15-NOV-21 1208986    15-NOV-21

   Bash
5. Recover the database:

   RMAN> recover database until sequence 1160;
   Starting recover at 16-NOV-21
   …
   Finished recover at 16-NOV-21

   Bash

Update the symbolic links on the standby instance

Update the symbolic links as below:

-bash-4.2$ ln -sfn /rdsdbdata/log/diag/rdbms/orcl_b/ORCL/trace /rdsdbbin/oracle/log/trace
-bash-4.2$ ls -ltra  /rdsdbbin/oracle/log/trace
lrwxrwxrwx 1 rdsdb rdsdb 43 Nov 16 00:27 /rdsdbbin/oracle/log/trace -> /rdsdbdata/log/diag/rdbms/orcl_b/ORCL/trace

-bash-4.2$ ls -ltra  /rdsdbbin/oracle/log/incident
lrwxrwxrwx 1 rdsdb rdsdb 46 Nov 16 00:28 /rdsdbbin/oracle/log/incident -> /rdsdbdata/log/diag/rdbms/orcl_b/ORCL/incident

Start the Oracle Data Guard broker on both instances

Start the Oracle Data Guard broker with the following command:

SQL> ALTER SYSTEM SET dg_broker_start=true;
System altered.

Enable Oracle Data Guard configuration

To enable Oracle Data Guard, complete the following steps:

1. On the primary instance, connect to the Data Guard broker:

   $ dgmgrl /
   ...
   Connected to "ORCL_A"
   Connected as SYSDG.
   DGMGRL>

   Bash
2. Inside the software interface, create the configuration for the primary instance. For this post, the database unique name for the primary instance is ORCL_A:

   DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS ORCL_A CONNECT IDENTIFIER IS ORCL_A
   > ;
   Configuration "my_dg_config" created with primary database "orcl_a"
   DGMGRL>

   Bash
3. Add the standby instance to this configuration. For this post, the database unique name for the standby instance is ORCL_B:

   DGMGRL> ADD DATABASE ORCL_B AS CONNECT IDENTIFIER IS ORCL_B MAINTAINED AS PHYSICAL;
   Database "orcl_b" added

   Bash
4. Check the configuration was created successfully (ORCL_A with the primary database role and ORCL_B with the physical standby database role):

   DGMGRL> SHOW CONFIGURATION VERBOSE;
   Configuration - my_dg_config
     Protection Mode: MaxPerformance
     Members:
     orcl_a - Primary database
       orcl_b - Physical standby database
     Properties:
       FastStartFailoverThreshold      = '30'
       OperationTimeout                = '30'
       TraceLevel                      = 'USER'
       FastStartFailoverLagLimit       = '30'
       CommunicationTimeout            = '180'
       ObserverReconnect               = '0'
       FastStartFailoverAutoReinstate  = 'TRUE'
       FastStartFailoverPmyShutdown    = 'TRUE'
       BystandersFollowRoleChange      = 'ALL'
       ObserverOverride                = 'FALSE'
       ExternalDestination1            = ''
       ExternalDestination2            = ''
       PrimaryLostWriteAction          = 'CONTINUE'
       ConfigurationWideServiceName    = 'ORCL_CFG'
   Fast-Start Failover:  Disabled
   Configuration Status:
   DISABLED

   Bash
5. Set the static connect identifiers for both databases. Replace <primary_instance_ipv4> and <standby_instance_ipv4> with the IP addresses:

   DGMGRL> edit database orcl_a set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=<primary_instance_ipv4>))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))';
   Property "staticconnectidentifier" updated
   
   DGMGRL> edit database orcl_b set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1140)(HOST=<standby_instance_ipv4>))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))';
   Property "staticconnectidentifier" updated

   Bash
6. Enable the Oracle Data Guard configuration (the following command could take a few minutes to complete):

   DGMGRL> ENABLE CONFIGURATION;
   Enabled.

   Bash

Configure fal_server and fal_client on the standby instance

Enter the following commands to configure fal_server and fal_client:

SQL> alter system set fal_server = 'ORCL_A';
System altered.
SQL> alter system set fal_client = 'ORCL_B';
System altered.

Update the local_listener parameter on both instances

On the primary instance, enter the following sql command:

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(PORT = 8200)(HOST = 127.0.0.1)),(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = <primary_instance_ipv4> ))';
System altered.

On the standby instance, enter the following sql command:

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(PORT = 8200)(HOST = 127.0.0.1)),(ADDRESS = (PROTOCOL = TCP)(PORT = 1140)(HOST = <standby_instance_ipv4> ))';
System altered.

Configure the standby redo log files on both instances

On the standby instance, add n+1 standby redo log files. In this post, the primary instance and standby instance have four redo log files each:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('slog1.rdo') SIZE 128M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('slog2.rdo') SIZE 128M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('slog3.rdo') SIZE 128M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('slog4.rdo') SIZE 128M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('slog5.rdo') SIZE 128M;

Recover the standby instance

Enter the following sql command:

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Perform the manual switchover

To perform the manual switchover, complete the following steps:

1. On the primary instance, connect to the Data Guard broker and validate if both databases are ready for switchover:

   DGMGRL> VALIDATE DATABASE ORCL_A
     Database Role:    Primary database
     Ready for Switchover:  Yes
     Flashback Database Status:
       orcl_a:  Off
     Managed by Clusterware:
       orcl_a:  NO
       Validating static connect identifier for the primary database orcl_a...
       The static connect identifier allows for a connection to database "orcl_a".
   
   DGMGRL> VALIDATE DATABASE ORCL_B
     Database Role:     Physical standby database
     Primary Database:  orcl_a
     Ready for Switchover:  Yes
     Ready for Failover:    Yes (Primary Running)
     Flashback Database Status:
       orcl_a:  Off
       orcl_b:  Off
     Managed by Clusterware:
       orcl_a:  NO
       orcl_b:  NO
       Validating static connect identifier for the primary database orcl_a...
       The static connect identifier allows for a connection to database "orcl_a".

   SQL
2. Switch over from the primary database to the standby database:

   DGMGRL> SWITCHOVER TO ORCL_B;
   Performing switchover NOW, please wait...
   Operation requires a connection to database "orcl_b"
   Connecting ...
   Connected to "ORCL_B"
   Connected as SYSDBA.
   New primary database "orcl_b" is opening...
   Operation requires start up of instance "ORCL" on database "orcl_a"
   Starting instance "ORCL"...
   Connected to an idle instance.
   ORACLE instance started.
   Connected to "ORCL_A"
   Database mounted.
   Connected to "ORCL_A"
   Switchover succeeded, new primary is "orcl_b"

   SQL
3. Verify the switchover is successful and check to make sure database roles are changed:

DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration - my_dg_config
  Protection Mode: MaxPerformance
  Members:
  orcl_b - Primary database
    orcl_a - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'ORCL_CFG'
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS

Resume automation on the RDS Custom instance

After all the activities are completed, we can resume the automation by running the following AWS CLI command:

aws rds  modify-db-instance --db-instance-identifier flex-instance-s --automation-mode full --region us-west-2

You have now set up a Data Guard standby instance on Amazon RDS Custom with the primary instance on Amazon EC2.

Conclusion

In this series of posts, we learned how to perform a physical migration of an Oracle database to Amazon RDS Custom using Oracle native tools like Oracle Data Guard and RMAN duplicate to reduce migration downtime and also get the benefits of a managed service. 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.