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 (for example, oneSetting=oneValue;thenAnother=anotherValue).
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
addSupplementalLoggingSet 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.additionalArchivedLogDestIdSet this attribute with archivedLogDestId in a primary-Standby setup.
This attribute is useful in a switchover when Oracle Data Guard database is used as a source. In this case, AWS DMS needs to know which destination to get archive redo logs from to read changes. This is because the previous primary is now a Standby instance after switchover.
Although AWS DMS supports the use of the Oracle RESETLOGS option to open the database, never use RESETLOGS unless necessary. For additional information about RESETLOGS, see RMAN Data Repair Concepts in the Oracle® Database Backup and Recovery User's Guide.
allowSelectNestedTablesSet this attribute to true to enable replication of Oracle tables containing columns that are nested tables or defined types. For more information, see Replicating nested tables using Oracle as a source for AWS DMS (p. 127).
Default value: false Valid values: true/false
Example: allowSelectNestedTables=true;
ExtraArchivedLogDestIdsSpecifies the IDs of one more destinations for one or more archived redo logs. These IDs are the values of the dest_id column in the v$archived_log view. Use this setting with the archivedLogDestId extra connection attribute in a primary-to-single setup or a primary-to-multiple-standby setup.
This setting is useful in a switchover when you use an Oracle Data Guard database as a source. In this case, AWS DMS needs information about what destination to get archive redo logs from to read changes. AWS DMS needs this because after the switchover the previous primary is a standby instance.
Using Oracle as a source
Name Description
Example: archivedLogDestId=1;
ExtraArchivedLogDestIds=[2,3,4]
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 AWS DMS Binary Reader for CDC (p. 103).
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 AWS DMS Binary Reader for CDC (p. 103).
Default value: N Valid values: Y/N
Example: useLogminerReader=N;useBfile=Y;
parallelASMReadThreadsSet this attribute to change the number of threads that DMS configures to perform change data capture (CDC) 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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
Default value: 2
Valid values: An integer from 2 to 8
Example: parallelASMReadThreads=6;readAheadBlocks=150000;
readAheadBlocks Set this attribute to change the number of read-ahead blocks that DMS configures to perform CDC 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 parallelASMReadThreads attribute. For more information, see
Configuring a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
Default value: 1000
Valid values: An integer from 1000 to 200,000
Example: parallelASMReadThreads=6;readAheadBlocks=150000;
Using Oracle as a source
Name Description
accessAlternateDirectlySet 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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
Default value: true Valid values: true/false Example:
useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;
useAlternateFolderForOnlineSet 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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
Default value: none
Valid value: /rdsdbdata/log/
Example:
useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;
useAlternateFolderForOnline=true;oraclePathPrefix=/
rdsdbdata/db/ORCL_A/; usePathPrefix=/rdsdbdata/log/;
Using Oracle as a source
Name Description
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 a CDC task to use Binary Reader with an RDS for Oracle source for AWS DMS (p. 115).
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 ID of the destination for the archived redo logs. This value should be the same as a number in the dest_id column of the v$archived_log view. If you work with an additional redo log destination, we recommend that you use the additionalArchivedLogDestId attribute to specify the additional destination ID. Doing this improves performance by ensuring that the correct logs are accessed from the outset.
Default value: 1 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;
Using Oracle as a source
Name Description
numberDataTypeScale Specifies the number scale. You can select a scale up to 38, or you can select -1 for FLOAT, or -2 for VARCHAR. By default, the NUMBER data type is converted to precision 38, scale 10.
Default value: 10
Valid values: -2 to 38 (-2 for VARCHAR, -1 for FLOAT) Example: numberDataTypeScale=12
Note
Select a precision-scale combination, -1 (FLOAT) or -2 (VARCHAR).
DMS supports any precision-scale combination supported by Oracle. If precision is 39 or above, select -2 (VARCHAR). The numberDataTypeScale setting for the Oracle database is used for the NUMBER data type only (without the explicit precision and scale definition).
failTasksOnLobTruncationWhen 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;
enableHomogenousTablespaceSet this attribute to enable homogenous tablespace replication and create existing tables or indexes under the same tablespace on the target.
Default value: false Valid values: true/false
Example: enableHomogenousTablespace=true
Using Oracle as a source
Name Description
standbyDelayTime Use this attribute to specify a time in minutes for the delay in standby sync.
If the source is an Active Data Guard standby database, use this attribute to specify the time lag between primary and standby databases.
In AWS DMS, 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;
securityDbEncryptionNameSpecifies the name of a key used for the transparent data encryption (TDE) of the columns and tablespace in the Oracle source database. For more information on setting this attribute and its associated password on the Oracle source endpoint, see Supported encryption methods for using Oracle as a source for AWS DMS (p. 124).
Default value: ""
Valid values: String
securityDbEncryptionName=ORACLE.SECURITY.DB.ENCRYPTION.Adg8m2dhkU/0v/
m5QUaaNJEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
spatialSdo2GeoJsonFunctionNameFor Oracle version 12.1 or earlier sources migrating to PostgreSQL targets, use this attribute to convert SDO_GEOMETRY to GEOJSON format.
By default, AWS DMS calls the SDO2GEOJSON custom function which must be present and accessible to the AWS DMS user. Or you can create your own custom function that mimics the operation of SDOGEOJSON and set spatialSdo2GeoJsonFunctionName to call it instead.
Default value: SDO2GEOJSON Valid values: String
Example:
spatialSdo2GeoJsonFunctionName=myCustomSDO2GEOJSONFunction;
exposeViews Use this attribute to pull data once from a view; you can't use it for ongoing replication. When you extract data from a view, the view is shown as a table on the target schema.
Default value: false Valid values: true/false Example: exposeViews=true
Using Oracle as a source