Why did my AWS DMS task fail when using Binary Reader for Amazon RDS for Oracle?
Why did my AWS Database Migration Service (AWS DMS) task fail when using Binary Reader for Amazon Relational Database Service (Amazon RDS) for Oracle?
"Why did my AWS Database Migration Service (AWS DMS) task fail when using Binary Reader for Amazon Relational Database Service (Amazon RDS) for Oracle?Short descriptionDuring the change data capture (CDC) phase, Oracle provides two methods to read the redo logs: Oracle LogMiner and Binary Reader. The Oracle LogMiner is an SQL interface that accesses the online and archived redo logs. The Binary Reader is an AWS DMS feature that reads and parses the redo logs directly.When using Binary Reader for migrations with a lot of changes, there is lower impact on the Oracle source when compared to using Oracle LogMiner. This is because the archive logs are copied and then parsed on the replication instance. For migrations that have a lot of changes, Binary Reader usually has better CDC performance than Oracle LogMiner. Be sure to provision sufficient network bandwidth to avoid network performance bottlenecks.If the prerequisites aren't met when using Binary Reader, you can receive two types of errors:Permissions errorsExtra connection attribute errorsResolutionPermissions errorsAWS DMS uses the Binary Reader by creating directories on the source database. So, the AWS DMS user account must have the required privileges to access the source Oracle endpoint and create necessary directories. If AWS DMS doesn't have permissions, then you see log entries similar to this:Messages"[SOURCE_CAPTURE ]E: OCI error 'ORA-00604: error occurred at rMeecursive SQL level 1 ORA-20900: Invalid path used for directory: /rdsdbdata/log/arch ORA-06512: at "RDSADMIN.RDSADMIN", line 321 ORA-06512: at line 2' [1022307] (oradcdc_bfilectx.c:164)"To resolve these errors, use the Amazon RDS primary user as the AWS DMS user. The directories are created automatically when an AWS DMS task starts running. If the directories aren't created, first log in to the Oracle database using the primary user. Then, run these commands to test if these directories can be created:SQL> exec rdsadmin.rdsadmin_master_util.create_archivelog_dir; SQL> exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;Check the results by querying the all_directories table:SQL> select directory_path from all_directories where directory_name in ('ONLINELOG_DIR','ARCHIVELOG_DIR');DIRECTORY_PATH--------------------------------------------------------------------------------/rdsdbdata/log/arch/rdsdbdata/log/onlinelogAfter you create the required directories ONLINELOG_DIR and ARCHIVELOG_DIR, restart your AWS DMS task.Extra connection attribute errorsIf you use the Binary Reader but you're missing the necessary extra connection attributes for your Oracle source, then you see this log entry:Messages"[TASK_MANAGER ]E: ORA-00604: error occurred at recursive SQL level 1 ORA-20900: Invalid path used for directory: awsdms_dir_test ORA-06512: at "RDSADMIN.RDSADMIN", line 321 ORA-06512: at line 2 ; Invalid RDS Oracle binary reader db settings, replacePathPrefix should be set to TRUE and usePathPrefix should be set to '/rdsdbdata/log/'; Invalid RDS Oracle binary reader db settings, useAlternateFolderForOnline should be set to TRUE; Invalid RDS Oracle binary reader db setting, oraclePathPrefix should not be empty; Invalid RDS Oracle binary reader db settings; Failed while preparing stream component 'st_0_4MGMBIOJCILNOU3UHICCDBCNFQ'.; Cannot initialize subtask; Stream component 'st_0_4MGMBIOJCILNOU3UHICCDBCNFQ' terminated [1020418] (replicationtask.c:2680)"To use the Binary Reader to capture change data for an Amazon RDS for Oracle source, add these extra connection attributes to the source endpoint:useLogMinerReader=N;useBfile=Y;replacePathPrefix=true;usePathPrefix=/rdsdbdata/log/;useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/;accessAlternateDirectly=falseRelated informationUser account privileges required on an AWS-managed Oracle source for AWS DMSAccessing online and archived redo logsMigrate an on-premises Oracle database to Amazon RDS for PostgreSQL using an Oracle bystander and AWS DMSFollow"