Short description
For one-time bulk data transfer, use Oracle Data Pump with Amazon Simple Storage Service (Amazon S3) integration.
For ongoing replication with minimal downtime, use AWS DMS full load and Change Capture Data (CDC).
For heterogeneous database migrations, use AWS DMS with AWS Schema Conversion Tool (SCT).
Resolution
Configure Amazon RDS for Oracle as a source
AWS DMS full load automatically generates SELECT statements to migrate data directly from source tables. However, for continuous replication, AWS DMS must access the Oracle redo logs to capture and replicate changes in real time. To specify AWS DMS access to Oracle redo logs, configure Oracle LogMiner or AWS DMS Binary Reader.
Prerequisite: Choose LogMiner or Binary Reader for CDC. If you don't specify a CDC-related connection attribute, then AWS DMS automatically uses LogMiner to read the redo logs.
To configure Binary Reader for CDC, add extra connection attributes (ECAs) to your AWS DMS source endpoint.
For standard Oracle environments, use the following ECA:
useLogMinerReader=N;useBfile=Y;
For Oracle Real Application Clusters (Oracle RAC) with Oracle Automatic Storage Management (Oracle ASM) environments, use the following ECA:
useLogMinerReader=N;useBfile=Y;asm_user=asm_username;asm_server=RAC_server_ip_address:port_number/+ASM;parallelASMReadThreads=6;readAheadBlocks=150000;
Note: For the source endpoint Password parameter, enter both the Oracle user password and the Oracle ASM password, separated by a comma.
To add the ECAs, complete the following steps:
- Open the AWS DMS console.
- In the navigation pane, choose Migrate or replicate.
- Choose Endpoints, and then select your source endpoint.
- Choose Modify.
- Choose Endpoint settings.
- For Extra connection attributes, specify the parameters.
- Choose Save changes.
For more information, see How can I add or modify endpoint settings for AWS DMS endpoints?
Configure account permissions for LogMiner
To use LogMiner to access the redo logs, run the following commands to grant permissions to the Oracle user that you specified in the Oracle endpoint connection settings:
GRANT EXECUTE on DBMS_LOGMNR to dms_user;
GRANT SELECT on V_$LOGMNR_LOGS to dms_user;
GRANT SELECT on V_$LOGMNR_CONTENTS to dms_user;
GRANT LOGMINING to dms_user;
Turn on supplemental logging
Supplemental logging captures additional column data in the redo logs that's required for CDC. Without supplemental logging, the redo logs might not contain enough information for AWS DMS to identify and replicate changes. To use LogMiner for CDC, you must first turn on supplemental logging.
To turn on supplemental logging, run the following commands:
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
Use Binary Reader to configure CDC
Log in to your Amazon RDS for Oracle source database as the primary user and run the following stored procedures to create the server-level directories:
exec rdsadmin.rdsadmin_master_util.create_archivelog_dir;
exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir;
Run the following commands to grant permissions to the Oracle user account that you use to access the Oracle source endpoint:
GRANT READ ON DIRECTORY ONLINELOG_DIR TO dms_user;
GRANT READ ON DIRECTORY ARCHIVELOG_DIR TO dms_user;
Set the ECAs on the source endpoint
For Amazon RDS for Oracle versions 12.1 and earlier, use the following ECA:
useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;useAlternateFolderForOnline=true;
oraclePathPrefix=/rdsdbdata/db/{$DATABASE_NAME}_A/;usePathPrefix=/rdsdbdata/log/;replacePathPrefix=true;
For Amazon RDS for Oracle versions 12.2 and later, use the following ECA:
useLogminerReader=N;useBfile=Y;
Configure account permissions for Binary Reader
To use Binary Reader to access the redo logs, run the following commands to grant permissions to the Oracle user that you specified in the Oracle endpoint connection settings.
If you stored the redo logs in Oracle Automatic Storage Management (ASM) and AWS DMS accesses them from ASM, then run the following command:
GRANT SELECT on v_$transportable_platform to dms_user;
To allow AWS DMS to use Oracle BFILE read file access, run the following command:
GRANT CREATE ANY DIRECTORY to dms_user;
Note: The replication instance requires Oracle BFILE access when the instance doesn't have file-level access to the redo logs and the redo logs aren't in ASM storage.
To use the CopyToTempFolder and allow the redo log files to copy into a temporary folder, run the following command :
GRANT EXECUTE on DBMS_FILE_TRANSFER to dms_user;
To allow AWS DMS to manage Oracle BFILE, run the following command:
GRANT EXECUTE on DBMS_FILE_GROUP to dms_user;
Configure archive log retention
CDC requires archived logs. To set the log retention period, run the following command:
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',retention_in_hours);commit;
Note: Replace retention_in_hours with the number of hours that you want to retain archive logs.
Configure Amazon RDS for Oracle as a target
Manually create schemas and related database objects, or use the AWS SCT.
To grant permissions to the AWS DMS user, run the following commands:
GRANT CREATE SESSION, SELECT ANY TRANSACTION TO dms_user;
GRANT DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE TO dms_user;
GRANT UPDATE ANY TABLE, DELETE ANY TABLE TO dms_user;
GRANT UNLIMITED TABLESPACE, LOCK ANY TABLE, CREATE ANY INDEX TO dms_user;
Note: Replace dms_user with the name of the database user account that AWS DMS uses to connect to the target Oracle database.
Related information
Using an Oracle database as a source for AWS DMS
Using an Oracle database as a target for AWS Database Migration Services
Troubleshooting migration tasks in AWS Database Migration ServiceGRANT EXECUTE on DBMS_FILE_GROUP to dms_user;GRANT EXECUTE on DBMS_FILE_GROUP to dms_user;