Sign In to the Console
AWS Database Migration Service
User Guide (Version API Version 2016-01-01)

AWS Documentation » AWS Database Migration Service » User Guide » Working with AWS DMS Endpoints » Sources for Data Migration » Using an Oracle Database as a Source for AWS DMS
The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

Using an Oracle Database as a Source for AWS DMS

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.

For self-managed Oracle databases as sources, AWS DMS supports all Oracle database editions for versions 10.2 and later, 11g and up to 12.2, and 18c. For Amazon-managed Oracle databases provided by Amazon RDS, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c.

Note

Support for Oracle version 18c as a source is available in AWS DMS versions 3.3.0 and later.

You can use Secure Sockets Layer (SSL) to encrypt connections between your Oracle endpoint and your replication instance. For more information on using SSL with an Oracle endpoint, see Using SSL With AWS Database Migration Service.

The steps to configure an Oracle database as a source for AWS DMS source are as follows:

  1. If you want to create a CDC-only or full load plus CDC task, then you must choose either Oracle LogMiner or Oracle Binary Reader to capture data changes. Choosing LogMiner or Binary Reader determines some of the later permission and configuration tasks. For a comparison of LogMiner and Binary Reader, see the next section.

  2. Create an Oracle user with the appropriate permissions for AWS DMS. If you are creating a full-load-only task, then no further configuration is needed.

  3. Create a DMS endpoint that conforms with your chosen configuration.

For additional details on working with Oracle databases and AWS DMS, see the following sections.

Using Oracle LogMiner or Oracle Binary Reader for Change Data Capture (CDC)

Oracle offers two methods for reading the redo logs when doing change processing: Oracle LogMiner and Oracle Binary Reader. Oracle LogMiner provides a SQL interface to Oracle’s online and archived redo log files. Binary Reader is an AWS DMS feature that reads and parses the raw redo log files directly.

By default, AWS DMS uses Oracle LogMiner for change data capture (CDC). The advantages of using LogMiner with AWS DMS include the following:

  • LogMiner supports most Oracle options, such as encryption options and compression options. Binary Reader doesn't support all Oracle options, in particular options for encryption and compression.

  • LogMiner offers a simpler configuration, especially compared to Oracle Binary Reader's direct-access setup or if the redo logs are on Oracle Automatic Storage Management (ASM).

  • LogMiner fully supports most Oracle encryption options, including Oracle Transparent Data Encryption (TDE).

  • LogMiner supports the following HCC compression types for both full load and on-going replication (CDC):

    • QUERY HIGH

    • ARCHIVE HIGH

    • ARCHIVE LOW

    • QUERY LOW

    Binary Reader supports QUERY LOW compression only for full load replications, not ongoing (CDC) replications.

  • LogMiner supports table clusters for use by AWS DMS. Binary Reader doesn't.

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. Binary Reader has less chance of having I/O or CPU impact because the archive logs are copied to the replication instance and mined there.

  • For migrations with a high volume of changes, CDC performance is usually much better when using Binary Reader compared with using Oracle LogMiner.

  • Binary Reader supports CDC for LOBs in Oracle version 12c. LogMiner does not.

  • Binary Reader supports the following HCC compression types for both full load and continuous replication (CDC):

    • QUERY HIGH

    • ARCHIVE HIGH

    • ARCHIVE LOW

      The QUERY LOW compression type is only supported for full load migrations.

In general, use Oracle LogMiner for migrating your Oracle database unless you have one of the following situations:

  • You need to run several migration tasks on the source Oracle database.

  • The volume of changes or the redo log volume on the source Oracle database is high.

  • You are migrating LOBs from an Oracle 12.2 or later source endpoint.

  • Your workload includes UPDATE statements that update only LOB columns. In this case, use Binary Reader. These UPDATE statements aren't supported by Oracle LogMiner.

  • Your source is Oracle version 11 and you perform UPDATE statements on XMLTYPE and LOB columns. In this case, you must use Binary Reader. These statements aren't supported by Oracle LogMiner.

  • You are migrating LOB columns from Oracle 12c. For Oracle 12c, LogMiner doesn't support LOB columns, so in this case use Binary Reader.

Configuration for Change Data Capture (CDC) on an Oracle Source Database

When you use Oracle as a source endpoint either for full-load and change data capture (CDC) or just for CDC, you set extra connection attributes. These attributes specify whether to use LogMiner or Binary Reader to access the transaction logs. You specify extra connection attributes when you create the source endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space.

LogMiner is used by default, so you don't have to explicitly specify its use. To enable Binary Reader to access the transaction logs, add the following extra connection attributes. 

useLogMinerReader=N;useBfile=Y;

In AWS DMS versions 3.3.0 or later, if the Oracle source database is using Oracle Automatic Storage Management (ASM), use the Binary Reader. In addition, ensure that the extra connection attributes include effective settings for the number of parallel threads (parallelASMThreads) and the number of read-ahead buffers (readAheadBlocks). Setting these attributes together can significantly improve the performance of a CDC load using Oracle ASM. The settings shown in the following example provide good results for most Oracle ASM configurations: 

useLogMinerReader=N;useBfile=Y;parallelASMThreads=6;readAheadBlocks=150000;

For more information on the full range of values that these extra connection attributes support, see the description of each attribute in Extra Connection Attributes When Using Oracle as a Source for AWS DMS.

In AWS DMS versions earlier than 3.3.0, if the Oracle source database is using Oracle Automatic Storage Management (ASM), use the Binary Reader. In addition, ensure that the extra connection attributes include settings for the ASM user name and ASM server address. When you create the source endpoint, the Password request parameter must specify both passwords: the Oracle source endpoint password and the Oracle ASM password.

Use the following format for the extra connection attributes to access a server that uses Oracle ASM. 

useLogMinerReader=N;useBfile=Y;asm_user=asm_username;asm_server=first_RAC_server_ip_address:port_number/+ASM;

Set the source endpoint Password request parameter to both the Oracle user password and the ASM password, separated by a comma as follows. 

oracle_user_password,asm_user_password

In addition, the performance of a CDC load using Oracle ASM depends on other settings that you choose. These settings include your DMS extra connection attributes and the SQL settings to configure the Oracle source. For more information on using Oracle ASM with your Oracle source endpoint, see How to Migrate from Oracle ASM to AWS using AWS DMS.

Note

If you use an AWS DMS version 3.3.0 or later, you do not need to make any of the additional settings described for earlier versions in the previous paragraphs.

Limitations for CDC on an Oracle Source Database

The following limitations apply when using an Oracle database as a source for AWS DMS change data capture:

  • AWS DMS doesn't capture changes made by the Oracle DBMS_REDEFINITION package, such as changes to table metadata and the OBJECT_ID value.

  • AWS DMS doesn't support index-organized tables with an overflow segment in CDC mode when using BFILE. An example is when you access the redo logs without using LogMiner.

Working with a Self-Managed Oracle Database as a Source for AWS DMS

A self-managed database is a database that you configure and control, either a local on-premises database instance or a database on Amazon EC2. Following, you can find out about the privileges and configurations you need to set up when using a self-managed Oracle database with AWS DMS.

User Account Privileges Required on a Self-Managed Oracle Source for AWS DMS

To use an Oracle database as a source in an AWS DMS task, the user specified in the AWS DMS Oracle database definitions must be granted the following privileges in the Oracle database. When granting privileges, use the actual name of objects, not the synonym for the object. For example, use V_$OBJECT including the underscore, not V$OBJECT without the underscore. 

                   
GRANT SELECT ANY TRANSACTION to <dms_user>
GRANT SELECT on V_$ARCHIVED_LOG to <dms_user>
GRANT SELECT on V_$LOG to <dms_user>
GRANT SELECT on V_$LOGFILE to <dms_user>
GRANT SELECT on V_$DATABASE to <dms_user>
GRANT SELECT on V_$THREAD to <dms_user>
GRANT SELECT on V_$PARAMETER to <dms_user>
GRANT SELECT on V_$NLS_PARAMETERS to <dms_user>
GRANT SELECT on V_$TIMEZONE_NAMES to <dms_user>
GRANT SELECT on V_$TRANSACTION to <dms_user>
GRANT SELECT on ALL_INDEXES to <dms_user>
GRANT SELECT on ALL_OBJECTS to <dms_user>
GRANT SELECT on DBA_OBJECTS to <dms_user> (required if the Oracle version is earlier than 11.2.0.3)
GRANT SELECT on ALL_TABLES to <dms_user>
GRANT SELECT on ALL_USERS to <dms_user>
GRANT SELECT on ALL_CATALOG to <dms_user>
GRANT SELECT on ALL_CONSTRAINTS to <dms_user>
GRANT SELECT on ALL_CONS_COLUMNS to <dms_user>
GRANT SELECT on ALL_TAB_COLS to <dms_user>
GRANT SELECT on ALL_IND_COLUMNS to <dms_user>
GRANT SELECT on ALL_LOG_GROUPS to <dms_user>
GRANT SELECT on SYS.DBA_REGISTRY to <dms_user>
GRANT SELECT on SYS.OBJ$ to <dms_user>
GRANT SELECT on DBA_TABLESPACES to <dms_user>
GRANT SELECT on ALL_TAB_PARTITIONS to <dms_user>
GRANT SELECT on ALL_ENCRYPTED_COLUMNS to <dms_user>
GRANT SELECT on V_$LOGMNR_LOGS to <dms_user>
GRANT SELECT on V_$LOGMNR_CONTENTS to <dms_user>

When using ongoing replication (CDC), you need these additional permissions.

  • The following permission is required when using CDC so that AWS DMS can add to Oracle LogMiner redo logs for both 11g and 12c. 

    
Grant EXECUTE ON dbms_logmnr TO <dms_user>;

  • The following permission is required when using CDC so that AWS DMS can add to Oracle LogMiner redo logs for 12c only. 

    
Grant LOGMINING TO <dms_user>;

If you use the additional features listed following, the specified additional permissions are required:

  • If views are exposed, grant SELECT on ALL_VIEWS to <dms_user>.

  • If you use a pattern to match table names in your replication task, grant SELECT ANY TABLE.

  • If you specify a table list in your replication task, grant SELECT on each table in the list.

  • If you add supplemental logging, grant ALTER ANY TABLE.

  • If you add supplemental logging and you use a specific table list, grant ALTER for each table in the list.

  • If you are migrating from Oracle RAC, grant SELECT permissions on materialized views with the prefixes g_$ and v_$.

Configuring a Self-Managed Oracle Source for AWS DMS

Before using a self-managed Oracle database as a source for AWS DMS, you need to perform several tasks:

  • Provide Oracle account access – Provide an Oracle user account for AWS DMS. The user account must have read/write privileges on the Oracle database, as specified in the previous section.

  • Ensure that ARCHIVELOG mode is on – Oracle can run in two different modes, the ARCHIVELOG mode and the NOARCHIVELOG mode. To use Oracle with AWS DMS, the source database must be in ARCHIVELOG mode.

  • Set up supplemental logging – If you are planning to use the source in a CDC or full-load plus CDC task, then you need to set up supplemental logging to capture the changes for replication.

There are two steps to enable supplemental logging for Oracle. First, you need to enable database-level supplemental logging. Doing this ensures that the LogMiner has the minimal information to support various table structures such as clustered and index-organized tables. Second, you need to enable table-level supplemental logging for each table to be migrated.

To enable database-level supplemental logging

  1. Run the following query to determine if database-level supplemental logging is already enabled. The return result should be from GE to 9.0.0. 

    SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

  2. Run the following query. The returned result should be YES or IMPLICIT. 

    SELECT supplemental_log_data_min FROM v$database;

  3. Run the following query to enable database-level supplemental logging. 

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

There are two methods to enable table-level supplemental logging. In the first one, if your database user account has ALTER TABLE privileges on all tables to be migrated, you can use the extra connection parameter addSupplementalLogging as described following. Otherwise, you can use the steps following for each table in the migration.

To enable table-level supplemental logging

  1. If the table has a primary key, add PRIMARY KEY supplemental logging for the table by running the following command. 

    ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

  2. If no primary key exists and the table has multiple unique indexes, then AWS DMS uses the first unique index in alphabetical order of index name.

    Create a supplemental log group as shown preceding on that index’s columns.

  3. If there is no primary key and no unique index, supplemental logging must be added on all columns. Run the following query to add supplemental logging to all columns. 

    ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

In some cases, the target table primary key or unique index is different than the source table primary key or unique index. In these cases, add supplemental logging on the source table columns that make up the target table primary key or unique index. If you change the target table primary key, add supplemental logging on the selected index's columns. Don't add it on the columns of the original primary key or unique index.

Add additional logging if needed, such as if a filter is defined for a table. If a table has a unique index or a primary key, add supplemental logging on each column that is involved in a filter, if those columns are different than the primary key or unique index columns. However, if ALL COLUMNS supplemental logging has been added to the table, you don't need to add any additional logging. 

ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG GROUP <group_name> (<column_list>) ALWAYS;

Working with an Amazon-Managed Oracle Database as a Source for AWS DMS

An Amazon-managed database is a database that is on an Amazon service such as Amazon RDS, Amazon Aurora, or Amazon S3. Following, you can find the privileges and configurations that you need to set up when using an Amazon-managed Oracle database with AWS DMS.

User Account Privileges Required on an Amazon-Managed Oracle Source for AWS DMS

To grant privileges on Oracle databases on Amazon RDS, use the stored procedure rdsadmin.rdsadmin_util.grant_sys_object. For more information, see Granting SELECT or EXECUTE privileges to SYS Objects.

Grant the following privileges to the AWS DMS user account that is used to access the source Oracle endpoint: 

GRANT SELECT ANY TABLE to <dms_user>;
GRANT SELECT on ALL_VIEWS to <dms_user>;
GRANT SELECT ANY TRANSACTION to <dms_user>;
GRANT SELECT on DBA_TABLESPACES to <dms_user>;
GRANT SELECT on ALL_TAB_PARTITIONS to <dms_user>;
GRANT SELECT on ALL_INDEXES to <dms_user>;
GRANT SELECT on ALL_OBJECTS to <dms_user>;
GRANT SELECT on ALL_TABLES to <dms_user>;
GRANT SELECT on ALL_USERS to <dms_user>;
GRANT SELECT on ALL_CATALOG to <dms_user>;
GRANT SELECT on ALL_CONSTRAINTS to <dms_user>;
GRANT SELECT on ALL_CONS_COLUMNS to <dms_user>;
GRANT SELECT on ALL_TAB_COLS to <dms_user>;
GRANT SELECT on ALL_IND_COLUMNS to <dms_user>;
GRANT SELECT on ALL_LOG_GROUPS to <dms_user>;
GRANT LOGMINING TO <dms_user>;

In addition, run the following:

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$THREAD','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$NLS_PARAMETERS','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TIMEZONE_NAMES','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_REGISTRY','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('OBJ$','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('ALL_ENCRYPTED_COLUMNS','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','<dms_user>','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','<dms_user>','EXECUTE');

Configuring an Amazon-Managed Oracle Source for AWS DMS

Before using an Amazon-managed Oracle database as a source for AWS DMS, you need to perform several tasks:

  • Provide Oracle account access – Provide an Oracle user account for AWS DMS. The user account must have read/write privileges on the Oracle database, as specified in the previous section.

  • Set the backup retention period for your Amazon RDS database to one day or longer – Setting the backup retention period ensures that the database is running in ARCHIVELOG mode. For more information about setting the backup retention period, see the Working with Automated Backups in the Amazon RDS User Guide.

  • Set up archive retention – Run the following to retain archived redo logs of your Oracle database instance. Running this command lets AWS DMS retrieve the log information using LogMiner. Make sure that you have enough storage for the archived redo logs during the migration period.

    In the following example, logs are kept for 24 hours.

    
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

  • Set up supplemental logging – If you plan to use the source in a CDC or full-load plus CDC task, set up supplemental logging to capture the changes for replication.

    There are two steps to enable supplemental logging for Oracle. First, you need to enable database-level supplemental logging. Doing this ensures that the LogMiner has the minimal information to support various table structures such as clustered and index-organized tables. Second, you need to enable table-level supplemental logging for each table to be migrated.

    To enable database-level supplemental logging

    • Run the following query to enable database-level supplemental logging. 

      exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

    To enable table-level supplemental logging

    • Run the following command to enable PRIMARY KEY logging for tables that have primary keys. 

      exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

    For tables that don’t have primary keys, use the following command to add supplemental logging. 

    alter table <table_name> add supplemental log data (ALL) columns;

    If you create a table without a primary key, you should either include a supplemental logging clause in the create statement or alter the table to add supplemental logging. The following command creates a table and adds supplemental logging. 

    create table <table_name> (<column_list>, supplemental log data (ALL) columns);

    If you create a table and later add a primary key, you need to add supplemental logging to the table. Add supplemental logging to the table using the following command. 

    alter table <table_name> add supplemental log data (PRIMARY KEY) columns;

Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS

You can configure AWS DMS to use an Amazon RDS for Oracle instance as a source of CDC. You use Oracle Binary Reader with an Amazon RDS for Oracle source (Oracle versions 11.2.0.4.v11 and later, and 12.1.0.2.v7 and later).

Include the following extra connection attributes when you create the Amazon RDS for Oracle source endpoint. 


useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;
useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/;
usePathPrefix=/rdsdbdata/log/;replacePathPrefix=true

Note

There can be no white space following the semicolon separator (;) for multiple attribute settings.

Limitations on Using Oracle as a Source for AWS DMS

The following limitations apply when using an Oracle database as a source for AWS DMS:

  • AWS DMS supports Oracle transparent data encryption (TDE) tablespace encryption and AWS Key Management Service (AWS KMS) encryption when used with Oracle LogMiner. All other forms of encryption aren't supported.

  • Tables with LOBs must have a primary key to use CDC.

  • AWS DMS supports the rename table <table name> to <new table name> syntax with Oracle version 11 and higher.

  • AWS DMS doesn't replicate data changes resulting from partition or subpartition operations—data definition language (DDL) operations such as ADD, DROP, EXCHANGE, or TRUNCATE. To replicate such changes, reload the table being replicated. AWS DMS replicates any future data changes to newly added partitions without you having to reload the table. However, UPDATE operations on old data records in partitions fail and generate a 0 rows affected warning.

  • The DDL statement ALTER TABLE ADD <column> <data_type> DEFAULT <> doesn't replicate the default value to the target. The new column in the target is set to NULL. If the new column is nullable, Oracle updates all the table rows before logging the DDL itself. As a result, AWS DMS captures the changes to the counters but doesn't update the target. Because the new column is set to NULL, if the target table has no primary key or unique index, later updates generate a 0 rows affected warning.

  • AWS DMS supports all data changes on the target that result from running the CREATE TABLE AS statement on the source.

    Note

    All data changes on the target that result from running the CREATE TABLE AS statement on the source are supported in AWS DMS versions 3.1.3 and later. In earlier versions, CREATE TABLE AS creates a new table on the target but no data is migrated to it.

  • When limited-size LOB mode is enabled, AWS DMS replicates empty LOBs on the Oracle source as NULL values in the target.

  • When AWS DMS begins CDC, it maps a timestamp to the Oracle system change number (SCN). By default, Oracle keeps only five days of the timestamp to SCN mapping. Oracle generates an error if the timestamp specified is too old (greater than the five-day retention period). For more information, see the Oracle documentation.

  • AWS DMS doesn't support connections to an Oracle source by using an ASM proxy.

  • AWS DMS doesn't support virtual columns.

  • In a multitenant environment, AWS DMS can connect to a pluggable database (PDB) within a container (CDB). In this configuration, you must use Binary Reader for change data capture (CDC). LogMiner isn't supported for PDBs.

Extra Connection Attributes When Using Oracle as a Source for AWS DMS

You can use extra connection attributes to configure your Oracle source. You specify these settings when you create the source endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space.

The following table shows the extra connection attributes that you can use to configure an Oracle database as a source for AWS DMS.

Name Description

addSupplementalLogging

Set this attribute to set up table-level supplemental logging for the Oracle database. This attribute enables PRIMARY KEY supplemental logging on all tables selected for a migration task.

Default value: N

Valid values: Y/N

Example: addSupplementalLogging=Y;

Note

If you use this option, you still need to enable database-level supplemental logging as discussed previously.

additionalArchivedLogDestId

Set this attribute with archivedLogDestId in a primary/standby setup. This attribute is useful in the case of a failover. In this case, AWS DMS needs to know which destination to get archive redo logs from to read changes. This need arises because the previous primary instance is now a standby instance after failover.

useLogminerReader

Set this attribute to Y to capture change data using the LogMiner utility (the default). Set this option to N if you want AWS DMS to access the redo logs as a binary file. When you set this option to N, also add the setting useBfile=Y. For more information on this setting and using Oracle Automatic Storage Management (ASM), see Using Oracle LogMiner or Oracle Binary Reader for Change Data Capture (CDC).

Default value: Y

Valid values: Y/N

Example: useLogminerReader=N;useBfile=Y;

useBfile

Set this attribute to Y in order to capture change data using the Binary Reader utility. Set useLogminerReader to N to set this attribute to Y. To use the Binary Reader with an Amazon RDS for Oracle as the source, you set additional attributes. For more information on this setting and using Oracle Automatic Storage Management (ASM), see Using Oracle LogMiner or Oracle Binary Reader for Change Data Capture (CDC).

Default value: N

Valid values: Y/N

Example: useLogminerReader=N;useBfile=Y;

parallelASMThreads

Set this attribute to change the number of threads that DMS configures to perform a Change Data Capture (CDC) load using Oracle Automatic Storage Management (ASM). You can specify an integer value between 2 (the default) and 8 (the maximum). Use this attribute together with the readAheadBlocks attribute. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: 2

Valid values: An integer from 2 to 8

Example: parallelASMThreads=6;readAheadBlocks=150000;

readAheadBlocks

Set this attribute to change the number of read-ahead blocks that DMS configures to perform a Change Data Capture (CDC) load using Oracle Automatic Storage Management (ASM). You can specify an integer value between 1000 (the default) and 200,000 (the maximum). Use this attribute together with the readAheadBlocks attribute. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: 1000

Valid values: An integer from 1000 to 200,000

Example: parallelASMThreads=6;readAheadBlocks=150000;

accessAlternateDirectly

Set this attribute to false in order to use the Binary Reader to capture change data for an Amazon RDS for Oracle as the source. This tells the DMS instance to not access redo logs through any specified path prefix replacement using direct file access. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: true

Valid values: true/false

Example: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;
useAlternateFolderForOnline Set this attribute to true in order to use the Binary Reader to capture change data for an Amazon RDS for Oracle as the source. This tells the DMS instance to use any specified prefix replacement to access all online redo logs. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: false

Valid values: true/false

Example: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false; useAlternateFolderForOnline=true;
oraclePathPrefix Set this string attribute to the required value in order to use the Binary Reader to capture change data for an Amazon RDS for Oracle as the source. This value specifies the default Oracle root used to access the redo logs. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: none

Valid value: /rdsdbdata/db/ORCL_A/

Example: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false; useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/;
usePathPrefix Set this string attribute to the required value in order to use the Binary Reader to capture change data for an Amazon RDS for Oracle as the source. This value specifies the path prefix used to replace the default Oracle root to access the redo logs. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: none

Valid value: /rdsdbdata/log/

Example: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false; useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/; usePathPrefix=/rdsdbdata/log/;
replacePathPrefix Set this attribute to true in order to use the Binary Reader to capture change data for an Amazon RDS for Oracle as the source. This setting tells DMS instance to replace the default Oracle root with the specified usePathPrefix setting to access the redo logs. For more information, see Configuring Change Data Capture (CDC) for an Amazon RDS for Oracle Source for AWS DMS .

Default value: false

Valid values: true/false

Example: useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false; useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/db/ORCL_A/; usePathPrefix=/rdsdbdata/log/;replacePathPrefix=true;

retryInterval

Specifies the number of seconds that the system waits before resending a query.

Default value: 5

Valid values: Numbers starting from 1

Example: retryInterval=6;

archivedLogDestId

Specifies the destination of the archived redo logs. The value should be the same as the DEST_ID number in the $archived_log table. When working with multiple log destinations (DEST_ID), we recommend that you to specify an archived redo logs location identifier. Doing this improves performance by ensuring that the correct logs are accessed from the outset.

Default value:0

Valid values: Number

Example: archivedLogDestId=1;

archivedLogsOnly

When this field is set to Y, AWS DMS only accesses the archived redo logs. If the archived redo logs are stored on Oracle ASM only, the AWS DMS user account needs to be granted ASM privileges.

Default value: N

Valid values: Y/N

Example: archivedLogsOnly=Y;

numberDataTypeScale

Specifies the number scale. You can select a scale up to 38, or you can select FLOAT. By default, the NUMBER data type is converted to precision 38, scale 10.

Default value: 10

Valid values: -1 to 38 (-1 for FLOAT)

Example: numberDataTypeScale=12

afterConnectScript

Specifies a script to run immediately after AWS DMS connects to the endpoint.

Valid values: A SQL statement set off by a semicolon. Not all SQL statements are supported.

Example: afterConnectScript=ALTER SESSION SET CURRENT_SCHEMA = system;

failTasksOnLobTruncation

When set to true, this attribute causes a task to fail if the actual size of an LOB column is greater than the specified LobMaxSize.

If a task is set to limited LOB mode and this option is set to true, the task fails instead of truncating the LOB data.

Default value: false

Valid values: Boolean

Example: failTasksOnLobTruncation=true;

readTableSpaceName

When set to true, this attribute supports tablespace replication.

Default value: false

Valid values: Boolean

Example: readTableSpaceName=true;

standbyDelayTime

Use this attribute to specify a time in minutes for the delay in standby sync.

With AWS DMS version 2.3.0 and later, you can create an Oracle CDC task that uses an Active Data Guard standby instance as a source for replicating ongoing changes. Doing this eliminates the need to connect to an active database that might be in production.

Default value:0

Valid values: Number

Example: standbyDelayTime=1;

Source Data Types for Oracle

The Oracle endpoint for AWS DMS supports most Oracle data types. The following table shows the Oracle source data types that are supported when using AWS DMS and the default mapping to AWS DMS data types.

For information on how to view the data type that is mapped in the target, see the section for the target endpoint you are using.

For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.

Oracle Data Type

AWS DMS Data Type

BINARY_FLOAT

REAL4

BINARY_DOUBLE

REAL8

BINARY

BYTES

FLOAT (P)

If precision is less than or equal to 24, use REAL4.

If precision is greater than 24, use REAL8.

NUMBER (P,S)

When scale is less than 0, use REAL8

NUMBER according to the "Expose number as" property in the Oracle source database settings.

When scale is 0:

  • And precision is 0, use REAL8.

  • And precision is greater than or equal to 2, use INT1.

  • And precision is greater than 2 and less than or equal to 4, use INT2.

  • And precision is greater than 4 and less than or equal to 9, use INT4.

  • And precision is greater than 9, use NUMERIC.

  • And precision is greater than or equal to scale, use NUMERIC.

In all other cases, use REAL8.

DATE

DATETIME

INTERVAL_YEAR TO MONTH

STRING (with interval year_to_month indication)

INTERVAL_DAY TO SECOND

STRING (with interval day_to_second indication)

TIME

DATETIME

TIMESTAMP

DATETIME

TIMESTAMP WITH TIME ZONE

STRING (with timestamp_with_timezone indication)

TIMESTAMP WITH LOCAL TIME ZONE

STRING (with timestamp_with_local_ timezone indication)

CHAR

STRING

VARCHAR2

STRING

NCHAR

WSTRING

NVARCHAR2

WSTRING

RAW

BYTES

REAL

REAL8

BLOB

BLOB

To use this data type with AWS DMS, you must enable the use of BLOB data types for a specific task. AWS DMS supports BLOB data types only in tables that include a primary key.

CLOB

CLOB

To use this data type with AWS DMS, you must enable the use of CLOB data types for a specific task. During change data capture (CDC), AWS DMS supports CLOB data types only in tables that include a primary key.

NCLOB

NCLOB

To use this data type with AWS DMS, you must enable the use of NCLOB data types for a specific task. During CDC, AWS DMS supports NCLOB data types only in tables that include a primary key.

LONG

CLOB

The LONG data type isn't supported in batch-optimized apply mode (TurboStream CDC mode). To use this data type with AWS DMS, you must enable the use of LOBs for a specific task. During CDC, AWS DMS supports LOB data types only in tables that have a primary key.

LONG RAW

BLOB

The LONG RAW data type isn't supported in batch-optimized apply mode (TurboStream CDC mode). To use this data type with AWS DMS, you must enable the use of LOBs for a specific task. During CDC, AWS DMS supports LOB data types only in tables that have a primary key.

XMLTYPE

CLOB

Support for the XMLTYPE data type requires the full Oracle Client (as opposed to the Oracle Instant Client). When the target column is a CLOB, both full LOB mode and limited LOB mode are supported (depending on the target).

Oracle tables used as a source with columns of the following data types aren't supported and cannot be replicated. Replicating columns with these data types result in a null column.

  • BFILE

  • ROWID

  • REF

  • UROWID

  • Nested Table

  • User-defined data types

  • ANYDATA

Note

Virtual columns aren't supported.