Saturday, May 21, 2022
No menu items!
HomeDatabase ManagementPhysical migration of Oracle databases to Amazon RDS Custom using Data Guard

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

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

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

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

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

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

Configure Oracle listeners on both instances.
Configure tnsnames.ora on both instances.
Perform restore and recovery on the standby instance by starting it in NOMOUNT mode.
Update the symbolic links on the standby instance.
Start the Oracle Data Guard broker on both instances.
Enable Oracle Data Guard on the primary instance, then add the standby instance to the configuration.
Configure fal_server and fal_client on the standby instance.
Update the local_listener parameter on both instances.
Configure the standby instance redo logs on both instances.
Recover the standby instance.
Perform the manual switchover.
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:

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.
Configure the AWS Command Line Interface (AWS CLI) on both instances. For instructions, see Getting started with the AWS CLI.
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 the user RDSADMIN 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.

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;

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

Perform an RMAN online backup of the primary database

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

Create the directory /rddbdata/backup:

-bash-4.2$ mkdir /rdsdbdata/backup

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

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

Create a parameter file on the primary instance

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

Create a password file on the primary instance

Complete the following steps:

Create a password file using orapwd:

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

Validate by listing the file:

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

Create a symbolic link:

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

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

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.

Create an S3 bucket named rds-rmanbackup:

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

Validate the bucket by listing the contents:

$ aws s3 ls s3://rds-rmanbackup

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

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

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

Create a directory /rddbdata/backup on standby instance:

-bash-4.2$ mkdir /rdsdbdata/backup

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

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:

[[email protected] 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:

[email protected] ec2-user]# cd /opt/aws/rdscustomagent/config
[[email protected]]# 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

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

Create a symbolic link:

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

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

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

Create the server parameter file with the following sql command:

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

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

Move the init file to /tmp:

mv $ORACLE_HOME/dbs/initORCL.ora /tmp

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:

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

Append the following to listener.ora and substitute primary_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 = 1521)(HOST = <primary_instance_ipv4>)))
SUBSCRIBE_FOR_NODE_DOWN_EVENT_L_ORCL_DG=OFF

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

Check the listener status:

$ORACLE_HOME/bin/lsnrctl status L_ORCL_DG

On the standby instance, stop the original listener:

-bash-4.2$ /rdsdbbin/oracle/bin/lsnrctl stop L_ORCL_001

The command completed successfully

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

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.

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

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

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

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:

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

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

Restore the database:

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

Finished restore at 16-NOV-21

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

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

Recover the database:

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

Finished recover at 16-NOV-21

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:

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

$ dgmgrl /

Connected to “ORCL_A”
Connected as SYSDG.
DGMGRL>

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>

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

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

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

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

DGMGRL> ENABLE CONFIGURATION;
Enabled.

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:

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

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”

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

Create master user and grant privileges

Since the starter database was dropped and the standby database was restored from the primary the master user has to be re-created. In this example the master username is called admin.

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

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

Read MoreAWS Database Blog

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments