Limitations on Oracle as a target User account privileges for Oracle as a target Configuring Oracle as a target Endpoint settings Target data types for Oracle

Using an Oracle database as a target for Amazon Database Migration Service

You can migrate data to Oracle database targets using Amazon DMS, either from another Oracle database or from one of the other supported databases. You can use Secure Sockets Layer (SSL) to encrypt connections between your Oracle endpoint and the replication instance. For more information on using SSL with an Oracle endpoint, see Using SSL with Amazon Database Migration Service. Amazon DMS also supports the use of Oracle transparent data encryption (TDE) to encrypt data at rest in the target database because Oracle TDE does not require an encryption key or password to write to the database.

For information about versions of Oracle that Amazon DMS supports as a target, see Targets for Amazon DMS.

When you use Oracle as a target, we assume that the data is to be migrated into the schema or user that is used for the target connection. If you want to migrate data to a different schema, use a schema transformation to do so. For example, suppose that your target endpoint connects to the user RDSMASTER and you want to migrate from the user PERFDATA1 to PERFDATA2. In this case, create a transformation like the following.



{
   "rule-type": "transformation",
   "rule-id": "2",
   "rule-name": "2",
   "rule-action": "rename",
   "rule-target": "schema",
   "object-locator": {
   "schema-name": "PERFDATA1"
},
"value": "PERFDATA2"
}

When using Oracle as a target, Amazon DMS migrates all tables and indexes to default table and index tablespaces in the target. If you want to migrate tables and indexes to different table and index tablespaces, use a tablespace transformation to do so. For example, suppose that you have a set of tables in the INVENTORY schema assigned to some tablespaces in the Oracle source. For the migration, you want to assign all of these tables to a single INVENTORYSPACE tablespace in the target. In this case, create a transformation like the following.


{
   "rule-type": "transformation",
   "rule-id": "3",
   "rule-name": "3",
   "rule-action": "rename",
   "rule-target": "table-tablespace",
   "object-locator": {
      "schema-name": "INVENTORY",
      "table-name": "%",
      "table-tablespace-name": "%"
   },
   "value": "INVENTORYSPACE"
}

For more information about transformations, see Specifying table selection and transformations rules using JSON.

If Oracle is both source and target, you can preserve existing table or index tablespace assignments by setting the Oracle source extra connection attribute, enableHomogenousTablespace=true. For more information, see Endpoint settings when using Oracle as a source for Amazon DMS

For additional details on working with Oracle databases as a target for Amazon DMS, see the following sections:

Topics

Limitations on Oracle as a target for Amazon Database Migration Service
User account privileges required for using Oracle as a target
Configuring an Oracle database as a target for Amazon Database Migration Service
Endpoint settings when using Oracle as a target for Amazon DMS
Target data types for Oracle

Limitations on Oracle as a target for Amazon Database Migration Service

Limitations when using Oracle as a target for data migration include the following:

Amazon DMS doesn't create schema on the target Oracle database. You have to create any schemas you want on the target Oracle database. The schema name must already exist for the Oracle target. Tables from source schema are imported to the user or schema, which Amazon DMS uses to connect to the target instance. To migrate multiple schemas, you can create multiple replication tasks. You can also migrate data to different schemas on a target. To do this, you need to use schema transformation rules on the Amazon DMS table mappings.
Amazon DMS doesn't support the Use direct path full load option for tables with INDEXTYPE CONTEXT. As a workaround, you can use array load.
With the batch optimized apply option, loading into the net changes table uses a direct path, which doesn't support XML type. As a workaround, you can use transactional apply mode.
Empty strings migrated from source databases can be treated differently by the Oracle target (converted to one-space strings, for example). This can result in Amazon DMS validation reporting a mismatch.
You can express the total number of columns per table supported in Batch optimized apply mode, using the following formula:
```
2 * columns_in_original_table + columns_in_primary_key <= 999
```
For example, if the original table has 25 columns and its Primary Key consists of 5 columns, then the total number of columns is 55. If a table exceeds the supported number of columns, then all of the changes are applied in one-by-one mode.
Amazon DMS doesn't support Autonomous DB on Oracle Cloud Infrastructure (OCI).

User account privileges required for using Oracle as a target

To use an Oracle target in an Amazon Database Migration Service task, grant the following privileges in the Oracle database. You grant these to the user account specified in the Oracle database definitions for Amazon DMS.

SELECT ANY TRANSACTION
SELECT on V$NLS_PARAMETERS
SELECT on V$TIMEZONE_NAMES
SELECT on ALL_INDEXES
SELECT on ALL_OBJECTS
SELECT on DBA_OBJECTS
SELECT on ALL_TABLES
SELECT on ALL_USERS
SELECT on ALL_CATALOG
SELECT on ALL_CONSTRAINTS
SELECT on ALL_CONS_COLUMNS
SELECT on ALL_TAB_COLS
SELECT on ALL_IND_COLUMNS
DROP ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
CREATE ANY VIEW
DROP ANY VIEW
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
DELETE ANY TABLE

For the following requirements, grant these additional privileges:

To use a specific table list, grant SELECT on any replicated table and also ALTER on any replicated table.
To allow a user to create a table in a default tablespace, grant the privilege GRANT UNLIMITED TABLESPACE.
For logon, grant the privilege CREATE SESSION.
If you are using a direct path (which is the default for full load), GRANT LOCK ANY TABLE to dms_user;.
If schema is different when using “DROP and CREATE” table prep mode, GRANT CREATE ANY INDEX to dms_user;.
For some full load scenarios, you might choose the "DROP and CREATE table" or "TRUNCATE before loading" option where a target table schema is different from the DMS user's. In this case, grant DROP ANY TABLE.
To store changes in change tables or an audit table where the target table schema is different from the DMS user's, grant CREATE ANY TABLE and CREATE ANY INDEX.

Read privileges required for Amazon Database Migration Service on the target database

The Amazon DMS user account must be granted read permissions for the following DBA tables:

SELECT on DBA_USERS
SELECT on DBA_TAB_PRIVS
SELECT on DBA_OBJECTS
SELECT on DBA_SYNONYMS
SELECT on DBA_SEQUENCES
SELECT on DBA_TYPES
SELECT on DBA_INDEXES
SELECT on DBA_TABLES
SELECT on DBA_TRIGGERS
SELECT on SYS.DBA_REGISTRY

If any of the required privileges cannot be granted to V$xxx, then grant them to V_$xxx.

Premigration assessments

To use the premigration assessments listed in Oracle assessments with Oracle as a Target, you must add the following permissions to the dms_user database user on the target database:


GRANT SELECT ON V_$INSTANCE TO dms_user;

Configuring an Oracle database as a target for Amazon Database Migration Service

Before using an Oracle database as a data migration target, you must provide an Oracle user account to Amazon DMS. The user account must have read/write privileges on the Oracle database, as specified in User account privileges required for using Oracle as a target.

Endpoint settings when using Oracle as a target for Amazon DMS

You can use endpoint settings to configure your Oracle target database similar to using extra connection attributes. You specify the settings when you create the target endpoint using the Amazon DMS console, or by using the create-endpoint command in the Amazon CLI, with the --oracle-settings '{"EndpointSetting": "value", ...}' JSON syntax.

The following table shows the endpoint settings that you can use with Oracle as a target.

Name	Description
`EscapeCharacter`	Set this attribute to an escape character. This escape character allows you to make a single wildcard character behave as a normal character in table mapping expressions. For more information, see Wildcards in table mapping. Default value: Null Valid values: Any character other than a wildcard character Example: `--oracle-settings '{"EscapeCharacter": "#"}'`
`UseDirectPathFullLoad`	When set to `Y`, Amazon DMS uses a direct path full load. Specify this value to enable the direct path protocol in the Oracle Call Interface (OCI). This OCI protocol enables the bulk loading of Oracle target tables during a full load. Default value: `true` Valid values: `true`/`false` Example: `--oracle-settings '{"UseDirectPathFullLoad": false}'`
`DirectPathParallelLoad`	When set to `true`, this attribute specifies a parallel load when `UseDirectPathFullLoad` is set to `Y`. This attribute also only applies when you use the Amazon DMS parallel load feature. For more information, see the description of the `parallel-load` operation in Table and collection settings rules and operations. A limitation on specifying this parallel load setting is that the target table cannot have any constraints or indexes. For more information on this limitation, see Enabling Constraints After a Parallel Direct Path Load. If constraints or indexes are enabled, setting this attribute to `true` has no effect. Default value: `false` Valid values: `true`/`false` Example: `--oracle-settings '{"DirectPathParallelLoad": true}'`
`DirectPathNoLog`	When set to `true`, this attribute helps to increase the commit rate on the Oracle target database by writing directly to tables and not writing a trail to database logs. For more information, see Direct-Load INSERT. This attribute also only applies when you set `UseDirectPathFullLoad` to `Y`. Default value: `false` Valid values: `true`/`false` Example: `--oracle-settings '{"DirectPathNoLog": true}'`
`CharLengthSemantics`	Specifies whether the length of a character column is in bytes or in characters. To indicate that the character column length is in characters, set this attribute to `CHAR`. Otherwise, the character column length is in bytes. Default value: Not set to `CHAR` Valid values: `CHAR` Example: `--oracle-settings '{"CharLengthSemantics": "CHAR"}'`
`AlwaysReplaceEmptyString`	Amazon DMS adds an extra space to replicate an empty string when migrating to an Oracle target. In general, Oracle doesn't have a notation for an empty string. When you insert an empty string on varchar2, you load empty strings as NULL. If you want to insert the data as NULL on Oracle, set this attribute to FALSE. Default value: `true` Valid values: `true`/`false` Example: `--oracle-settings '{"AlwaysReplaceEmptyString": false}'`

Target data types for Oracle

A target Oracle database used with Amazon DMS supports most Oracle data types. The following table shows the Oracle target data types that are supported when using Amazon DMS and the default mapping from Amazon DMS data types. For more information about how to view the data type that is mapped from the source, see the section for the source you are using.

Amazon DMS data type	Oracle data type
BOOLEAN	NUMBER (1)
BYTES	RAW (length)
DATE	DATETIME
TIME	TIMESTAMP (0)
DATETIME	TIMESTAMP (scale)
INT1	NUMBER (3)
INT2	NUMBER (5)
INT4	NUMBER (10)
INT8	NUMBER (19)
NUMERIC	NUMBER (p,s)
REAL4	FLOAT
REAL8	FLOAT
STRING	With date indication: DATE With time indication: TIMESTAMP With timestamp indication: TIMESTAMP With timestamp_with_timezone indication: TIMESTAMP WITH TIMEZONE With timestamp_with_local_timezone indication: TIMESTAMP WITH LOCAL TIMEZONE With interval_year_to_month indication: INTERVAL YEAR TO MONTH With interval_day_to_second indication: INTERVAL DAY TO SECOND If length > 4000: CLOB In all other cases: VARCHAR2 (length)
UINT1	NUMBER (3)
UINT2	NUMBER (5)
UINT4	NUMBER (10)
UINT8	NUMBER (19)
WSTRING	If length > 2000: NCLOB In all other cases: NVARCHAR2 (length)
BLOB	BLOB To use this data type with Amazon DMS, you must enable the use of BLOBs for a specific task. BLOB data types are supported only in tables that include a primary key
CLOB	CLOB To use this data type with Amazon DMS, you must enable the use of CLOBs for a specific task. During change data capture (CDC), CLOB data types are supported only in tables that include a primary key. STRING An Oracle VARCHAR2 data type on the source with a declared size greater than 4000 bytes maps through the Amazon DMS CLOB to a STRING on the Oracle target.
NCLOB	NCLOB To use this data type with Amazon DMS, you must enable the use of NCLOBs for a specific task. During CDC, NCLOB data types are supported only in tables that include a primary key. WSTRING An Oracle VARCHAR2 data type on the source with a declared size greater than 4000 bytes maps through the Amazon DMS NCLOB to a WSTRING on the Oracle target.
XMLTYPE	The XMLTYPE target data type is only relevant in Oracle-to-Oracle replication tasks. When the source database is Oracle, the source data types are replicated as-is to the Oracle target. For example, an XMLTYPE data type on the source is created as an XMLTYPE data type on the target.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Targets for data migration

Using SQL Server as a target