Use Oracle Real Application Clusters as a source for AWS DMS

AWS Database Migration Service (AWS DMS) is a managed migration and replication service that helps you move your database to AWS quickly, securely, and with minimal downtime and zero data loss. You can migrate data from one or many Oracle databases using AWS DMS. With an Oracle database as a source, you can migrate data to any of the targets supported by AWS DMS.

Oracle Real Application Clusters (RAC) is a technology that allows an Oracle database to run across multiple servers. The main benefits are increased availability and horizontal scalability. RAC enables user sessions to fail over and replay changes on another server in the event of an outage, without requiring any modifications to applications.

With the high availability features of Oracle RAC, you can achieve a more reliable migration when using AWS DMS. However, because Oracle RAC has multiple hostnames and IP addresses, there may be confusion over which host or IP address to configure in AWS DMS for Oracle RAC database.

In this post, we explore the steps to configure Oracle RAC as a source for AWS DMS.

Solution overview

There are three options to configure Oracle RAC as a source endpoint:

• Use Amazon Route 53 Resolver and the RAC Single Client Access Name(SCAN)
• Use the RAC SCAN IP
• Create a replication instance with the AWS Command Line Interface (AWS CLI) to use on-premises DNS for RAC scan name resolution.

We discuss the steps for all three options in this post. You should choose the option that is best suited for your use case.

The following diagram shows a RAC database with a two node cluster configured with three SCAN IP’-s and SCAN name. AWS DMS resolves the SCAN name to Three IP addresses using Amazon Route 53, and connects to the source RAC database using the SCAN listener.

To illustrate the configuration of the endpoint, we use the Oracle RAC configuration shown in the following table.

Prerequisites

Complete the following prerequisite steps:

1. Create an Oracle user with the appropriate permissions for AWS DMS to access your Oracle source database. In this example, we create a user called dmsuser. Make sure you grant the following privilege that is specific to Oracle RAC:

   GRANT SELECT ON gv_$transaction TO dmsuser;

2. Make sure the on-premises firewall accepts connections to the RAC SCAN hostname or RAC SCAN IP’-s from the AWS DMS replication instance IP. Refer to Setting up a network for a replication instance for more details
3. If you want to run the pre-migration assessment as part of the migration, grant the following permissions to the Oracle user:

GRANT SELECT on gv_$parameter to dmsuser;

GRANT SELECT on v_$instance to dmsuser;

GRANT SELECT on v_$version to dmsuser;

GRANT SELECT on gv_$ASM_DISKGROUP to dmsuser;

GRANT SELECT on gv_$database to dmsuser;

GRANT SELECT on dba_db_links to dmsuser;

GRANT SELECT on gv_$log_History to dmsuser;

GRANT SELECT on gv_$log to dmsuser;

GRANT SELECT ON DBA_TYPES TO dmsuser;

GRANT SELECT ON DBA_USERS to dmsuser;

GRANT SELECT ON DBA_DIRECTORIES to dmsuser;

GRANT SELECT on gv_$parameter to dmsuser;

GRANT SELECT on v_$instance to dmsuser;

GRANT SELECT on v_$version to dmsuser;

GRANT SELECT on gv_$ASM_DISKGROUP to dmsuser;

GRANT SELECT on gv_$database to dmsuser;

GRANT SELECT on dba_db_links to dmsuser;

GRANT SELECT on gv_$log_History to dmsuser;

GRANT SELECT on gv_$log to dmsuser;

GRANT SELECT ON DBA_TYPES TO dmsuser;

GRANT SELECT ON DBA_USERS to dmsuser;

GRANT SELECT ON DBA_DIRECTORIES to dmsuser;

In AWS DMS, there are two methods for reading the redo logs when doing change data capture (CDC) for Oracle as a source: Oracle LogMiner and AWS DMS Binary Reader. LogMiner is an Oracle API to read the online redo logs and archived redo log files. Binary Reader is a AWS DMS method that reads and parses the raw redo log files directly.

AWS DMS uses LogMiner by default. LogMiner is often slower and might consume more resources on the source database, which could lead to source latency. If the archive generation size is more than 20 GB per hour, we recommend Binary Reader. When you use Oracle RAC as a source, we also recommend Binary Reader.

Use the following extra connection attributes (ECA) if you want to use Binary Reader:-

useLogMinerReader=N;useBfile=Y

For Binary Reader, AWS DMS will read the archive and redo log files from the Oracle Automatic Storage Management(ASM) instance or a database cluster file system based on the Oracle archive log location.

Use Route 53 Resolver and the RAC SCAN name

In this option, you use the Route 53 Resolver and the RAC SCAN name to configure the AWS DMS endpoint. Route 53 Resolver helps you resolve the on-premises DNS records of the source Oracle RAC system. After you configure Route 53 Resolver, you can resolve the SCAN IPs from your AWS DMS replication instance’s VPC. Complete the following steps:

1. Configure Route 53 Resolver.
2. Run the nslookup command on an Amazon Elastic Compute Cloud (Amazon EC2) instance that is on the same VPC and subnet as the AWS DMS replication instance to make sure the DNS resolution is working correctly.

3. On the AWS DMS console, under Migrate data in the navigation pane, choose Endpoints.
4. Choose Create endpoint.

5. For Endpoint identifier, enter a name for your endpoint.
6. Choose Oracle for Source engine.
7. For Access to endpoint database, select Provide access information manually or select AWS Secrets Manager
8. Provide the rest of the details, including the sever name (SCAN name), port, user name and password, SSL mode, and SID or service name.

You can also use any name for Server name, and specify the SCAN name in the Extra connection attributes section. ECAs always override the parameters mentioned under Source engine.

To add an ECA, use the following code:

serverServer=scan.cluster.xxxx:1521/orcl_a

Alternatively, use the following code:

server=(DESCRIPTION=(CONNECT_TIMEOUT=8)(ENABLE=BROKEN)(LOAD_BALANCE=ON)(FAILOVER=ON) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST= scan.cluster.xxxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL_A)))

9. Test the endpoint connection.

Use the RAC SCAN IP

With this option, you don’t need to configure Route 53 Resolver. Instead, you use the RAC SCAN IP. Complete the following steps:

1. On the AWS DMS console, under Migrate data in the navigation pane, choose Endpoints.
2. Choose Create endpoint.
3. For Endpoint identifier, enter a name for your endpoint.
4. Choose Oracle for Source engine.
5. For Access to endpoint database, select Provide access information manually.
6. For Server name, enter a name.
7. Provide the rest of the details, including port, user name and password, SSL mode, and SID or service name.
8. Specify the SCAN IPs for Extra connection attributes.

The following is a sample connect string. In this example, you specify the SCAN IPs of the source RAC system, and specify the port and service name of the source database:

server=(DESCRIPTION=(CONNECT_TIMEOUT=8)(ENABLE=BROKEN)(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.3.84)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.3.176)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.3.253)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL_A)))

9. Test the connection.

Create a replication instance with the AWS CLI to use on-premises DNS for RAC SCAN name resolution-

Use the following AWS CLI command to create the replication instance using the on-premises DNS server:-

serveraws dms create-replication-instance
--replication-instance-identifier my-repl-instance
--replication-instance-class dms.t2.micro
--allocated-storage 50 --dns-name-servers "DNS IP1,DNS IP2,DNS IP3,DNS IP4"

serverlsnrctl status LISTENER_SCAN2

Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2))) 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.3.253)(PORT=1521))) 
Services Summary... Service "ORCL_A" has 2 instance(s). 
Instance "ORCL1", status READY, has 1 handler(s) for this service... 
Instance "ORCL2", status READY, has 1 handler(s) for this service... 
The command completed successfully

SQL> show parameter remote_listener

NAME                   TYPE            VALUE

remote_listener  string

SQL> alter system set remote_listener='scan.cluster:1521';

System altered.

SQL> alter system register;

lsnrctl status LISTENER_SCAN2

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.3.253)(PORT=1521)))

Services Summary... Service "+ASM" has 2 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Instance "+ASM2", status READY, has 1 handler(s) for this service...

Service "+ASM_DATA" has 2 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Instance "+ASM2", status READY, has 1 handler(s) for this service...

Service "+ASM_OCR" has 2 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Instance "+ASM2", status READY, has 1 handler(s) for this service...

Service "ORCL_A" has 2 instance(s).

Instance "ORCL1", status READY, has 1 handler(s) for this service...

Instance "ORCL2", status READY, has 1 handler(s) for this service...

The command completed successfully.

asm_user=dmsasm;asm_server=scan.cluster-xxxx:1521/+ASM;

oracle_user_password,asm_user_password

parallelASMReadThreads=6;readAheadBlocks=150000; asmUsePLSQLArray=true;