We are excited to announce support for Binary Reader for Amazon RDS for Oracle and Oracle Active Data Guard Standby as sources for migration in AWS Database Migration Service (AWS DMS).

AWS DMS helps you migrate databases to AWS quickly and relatively securely. It also helps you migrate data within AWS. You can migrate your data to and from most widely used commercial and open-source databases, such as Oracle, Microsoft SQL Server, and PostgreSQL. The service supports homogeneous migrations, such as Oracle to Oracle. It also supports heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora MySQL or Oracle to Amazon RDS for MySQL.

This blog post gives you a quick overview of how to use AWS DMS with Binary Reader in Amazon RDS for Oracle. The same instructions also apply when using Oracle Active Data Guard Standby as a source for migration.

Background

AWS DMS is designed for heterogeneous migrations, but it also supports homogeneous migrations. For replication, this design requires access to the archive, redo, transaction, binary, and operation logs to capture the ongoing changes on the source instance.

When using Oracle as a source, for replicating ongoing changes AWS DMS offers two methods for reading the logs. These are Oracle LogMiner and Binary Reader.

By default, AWS DMS uses Oracle LogMiner for change data capture (CDC). Alternatively, you can use Oracle Binary Reader. Binary Reader bypasses LogMiner and reads the logs directly. The advantages to using Binary Reader with AWS DMS, instead of LogMiner, include the following:

For migrations with a high volume of changes, LogMiner might have some I/O or CPU impact on the computer hosting the Oracle source database. If multiple tasks replicate from the same source, using Oracle LogMiner is less efficient. This lowered efficiency occurs because LogMiner accesses the redo logs by using the database and because of this consumes additional database resources. Binary Reader has less chance of having I/O or CPU impact.

For migrations with a high volume of changes, CDC performance is usually much better when using Binary Reader than when using Oracle LogMiner. We define a high volume of changes as so: The volume of changes in the redo log is more than 30 GB/hour. The volume of changes is between 10 GB/hour and 30 GB/hour and the changes need to be processed (that is, replicated to the target) as fast as possible.

Binary Reader supports CDC for LOBS in Oracle version 12c, but LogMiner doesn’t.

You can use Binary Reader with Oracle sources that use Oracle transparent data encryption (TDE).

Binary Reader supports the following HCC compression types for both full load and continuous replication (CDC): QUERY HIGH ARCHIVE HIGH ARCHIVE LOW

Binary Reader supports the QUERY LOW compression type only for full load migrations.

Binary Reader also supports Basic and OLTP compressions.

Binary Reader supports migration from Oracle PDB databases. LogMiner doesn’t.

Binary Reader provides greatly improved performance and reduces the load on the Oracle server when compared with LogMiner.

AWS DMS uses Binary Reader by creating a server-level directory in the ALL_DIRECTORIES table on the source database. You can see two entries in this table, one where the REDO log is and one where the archive logs are generated and stored. In most situations, the archive log location is the directory location indicated by USE_DB_RECOVERY_FILE_DEST.

Using RDS for Oracle as a source with Binary Reader for CDC in AWS DMS

Following, you can find prerequisites and configuration steps for RDS for Oracle as a source when using Binary Reader for CDC in AWS DMS.

Prerequisites

Be sure to use the master user for setting up Binary Reader, and also use the same master user for DMS migration. Accessing transaction logs is supported in RDS for Oracle version 11.2.0.4.v11 and later, and 12.1.0.2.v7 and later.

Configuration

Sign in to RDS for Oracle as master user. Execute the following stored procedures as documented in the RDS User Guide to create the server level directories: exec rdsadmin.rdsadmin_master_util.create_archivelog_dir; exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir; Doing this results in creating the directories with the following path: Determine the location for the redo and archive logs by using the following queries: Redo Log SELECT * FROM V$LOGFILE; Archive Log SELECT * FROM V$ARCHIVED_LOG; The path where redo logs are stored should look like the following: The path where archive logs are stored should look like the following: The paths shown preceding are basically symbolic links with the database name that you have created when creating the RDS for Oracle instance. The following shows an example. Redo Logs: /rdsdbdata/db/{$DATABASE_NAME}_A/onlinelog Archive Logs: /rdsdbdata/db/{$DATABASE_NAME}_A/arch In the screenshot preceding, the database name is ORCL. Create the Oracle source endpoint. When creating the endpoint, make sure that you enter the following in the extra connection attribute field: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false ;useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/ ;usePathPrefix=/rdsdbdata/log/;replacePathPrefix=true In the preceding, replace ORCL with your RDS for Oracle database name. When done, create a TEST CONNECTION to make sure that connectivity is established.

Using Oracle Standby as a source with Binary Reader for CDC in AWS DMS

Following, you can find prerequisites and configuration steps for Oracle Standby as a source when using Binary Reader for CDS in AWS DMS.

Prerequisites

The Oracle user should have CREATE ANY DIRECTORIES permissions. AWS DMS currently supports using only Oracle Active Data Guard Standby.

Configuration

Log in to the primary server for the Standby instance with the user that has CREATE ANY DIRECTORIES permissions. Determine the redo and archive logs’ location by using the following queries: Redo Log SELECT * FROM V$LOGFILE; Archive Log SELECT * FROM V$ARCHIVED_LOG; Based on the output from the preceding, create the directories using the following command: CREATE OR REPLACE DIRECTORY "DMS_MIGRATION_REDO" AS '<<REDO_LOG_LOCATION>>' CREATE OR REPLACE DIRECTORY "DMS_MIGRATION_ARCHIVE" AS '<<ARCHIVE_LOG_LOCATION>>' When the server level directories have been created, confirm that this is the case by querying the ALL_DIRECTORIES table. When that’s confirmed, do an archive log switch on the primary server. Doing this makes sure that the changes to ALL_DIRECTORIES are also ported to the standby. Query this table on the standby to confirm that the changes were applied. Create a source endpoint for Oracle Standby by using the AWS DMS Management Console or AWS CLI. While creating the endpoint, specify the following extra connection attribute: useLogminerReader=N;useBfile=Y;archivedLogDestId=1;additionalArchivedLogDestId=2 When done, create a TEST CONNECTION to make sure that connectivity is established.

After completing this setup, you can create a target endpoint and a migration task to migrate from an RDS for Oracle source instance to any of our supported targets. For a step-by-step example demonstrating migration from Oracle to PostgreSQL, see Migrating an Oracle Database to PostgreSQL in the AWS DMS Step-by-Step Migration Guide.

Note: In DMS, you can specify if you want to migrate from the archive logs and not the redo logs. For details, see the extra connection attributes section of the AWS DMS User Guide.

Conclusion

With this release, you can continuously stream data from RDS for Oracle and use Oracle Standby as a source for any AWS DMS supported target without overloading the primary server. With this new source support, you can replicate data in new ways and make it available in multiple locations for further processing.

If you have questions or suggestions, kindly comment below.

Good luck and happy migrating!

About the Author

Abhinav Singh is a database engineer in Database Migration Service at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database migration projects, helping them improve the value of their solutions when using AWS.