Copy data from Amazon RDS for Oracle by using Azure Data Factory or Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use the copy activity in Azure Data Factory to copy data from an Amazon RDS for Oracle database. It builds on the copy activity overview.

Supported capabilities

This Amazon RDS for Oracle connector is supported for the following capabilities:

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources or sinks by the copy activity, see the Supported data stores table.

Specifically, this Amazon RDS for Oracle connector supports:

• The following versions of an Amazon RDS for Oracle database for version 2.0:
  • Amazon RDS for Oracle 19c or later
• The following versions of an Amazon RDS for Oracle database for version 1.0:
  • Amazon RDS for Oracle 19c R1 (19.1) and higher
  • Amazon RDS for Oracle 18c R1 (18.1) and higher
  • Amazon RDS for Oracle 12c R1 (12.1) and higher
  • Amazon RDS for Oracle 11g R1 (11.1) and higher
• Parallel copying from an Amazon RDS for Oracle source. See the Parallel copy from Amazon RDS for Oracle section for details.

Prerequisites

If your data store is located inside an on-premises network, an Azure virtual network, or Amazon Virtual Private Cloud, you need to configure a self-hosted integration runtime to connect to it.

If your data store is a managed cloud data service, you can use the Azure Integration Runtime. If the access is restricted to IPs that are approved in the firewall rules, you can add Azure Integration Runtime IPs to the allow list.

You can also use the managed virtual network integration runtime feature in Azure Data Factory to access the on-premises network without installing and configuring a self-hosted integration runtime.

For more information about the network security mechanisms and options supported by Data Factory, see Data access strategies.

The integration runtime provides a built-in Amazon RDS for Oracle driver. Therefore, you don't need to manually install a driver when you copy data from Amazon RDS for Oracle.

Get started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

• The Copy Data tool
• The Azure portal
• The .NET SDK
• The Python SDK
• Azure PowerShell
• The REST API
• The Azure Resource Manager template

Create a linked service to Amazon RDS for Oracle using UI

Use the following steps to create a linked service to Amazon RDS for Oracle in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Amazon RDS for Oracle and select the Amazon RDS for Oracle connector.

   

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define entities specific to the Amazon RDS for Oracle connector.

Linked service properties

The Amazon RDS for Oracle connector version 2.0 supports TLS 1.3. Refer to this section to upgrade your Amazon RDS for Oracle connector version from version 1.0. For the property details, see the corresponding sections.

• Version 2.0
• Version 1.0

Version 2.0

The Amazon RDS for Oracle linked service supports the following properties when apply version 2.0:

More connection properties you can set in linked service per your case:

Example:

{
    "name": "AmazonRdsForOracleLinkedService",
    "properties": {
        "type": "AmazonRdsForOracle",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>", 
            "username": "<user name>", 
            "password": "<password>", 
            "authenticationType": "<authentication type>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: store password in Azure Key Vault

{
    "name": "AmazonRdsForOracleLinkedService",
    "properties": {
        "type": "AmazonRdsForOracle",
        "version": "2.0",
        "typeProperties": {
            "server": "<server name>", 
            "username": "<user name>", 
            "authenticationType": "<authentication type>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                }, 
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

server property configuration

For server property, you can specify it in one of the following three formats:

The following list shows the supported parameters used in server. If you use parameters that are not in the following list, your connection fails.

• When using the Azure integration runtime:

  HOST
  PORT
  PROTOCOL
  SERVICE_NAME
  SID
  INSTANCE_NAME
  SERVER
  CONNECT_TIMEOUT
  RETRY_COUNT
  RETRY_DELAY
  SSL_VERSION
  SSL_SERVER_DN_MATCH
  SSL_SERVER_CERT_DN

• When using the self-hosted integration runtime:

  HOST
  PORT
  PROTOCOL
  ENABLE
  EXPIRE_TIME
  FAILOVER
  LOAD_BALANCE
  RECV_BUF_SIZE
  SDU
  SEND_BUF_SIZE
  SOURCE_ROUTE
  TYPE_OF_SERVICE
  COLOCATION_TAG
  CONNECTION_ID_PREFIX
  FAILOVER_MODE
  GLOBAL_NAME
  HS
  INSTANCE_NAME
  POOL_BOUNDARY
  POOL_CONNECTION_CLASS
  POOL_NAME
  POOL_PURITY
  RDB_DATABASE
  SHARDING_KEY
  SHARDING_KEY_ID
  SUPER_SHARDING_KEY
  SERVER
  SERVICE_NAME
  SID
  TUNNEL_SERVICE_NAME
  SSL_CLIENT_AUTHENTICATION
  SSL_CERTIFICATE_ALIAS
  SSL_CERTIFICATE_THUMBPRINT
  SSL_VERSION
  SSL_SERVER_DN_MATCH
  SSL_SERVER_CERT_DN
  WALLET_LOCATION
  CONNECT_TIMEOUT
  RETRY_COUNT
  RETRY_DELAY
  TRANSPORT_CONNECT_TIMEOUT
  RECV_TIMEOUT
  COMPRESSION
  COMPRESSION_LEVELS

Version 1.0

The Amazon RDS for Oracle linked service supports the following properties when apply version 1.0:

If you have multiple Amazon RDS for Oracle instances for failover scenario, you can create Amazon RDS for Oracle linked service and fill in the primary host, port, user name, password, etc., and add a new "Additional connection properties" with property name as AlternateServers and value as (HostName=<secondary host>:PortNumber=<secondary port>:ServiceName=<secondary service name>) - do not miss the brackets and pay attention to the colons (:) as separator. As an example, the following value of alternate servers defines two alternate database servers for connection failover: (HostName=AccountingAmazonRdsForOracleServer:PortNumber=1521:SID=Accounting,HostName=255.201.11.24:PortNumber=1522:ServiceName=ABackup.NA.MyCompany).

More connection properties you can set in connection string per your case:

To enable encryption on Amazon RDS for Oracle connection, you have two options:

• To use Triple-DES Encryption (3DES) and Advanced Encryption Standard (AES), on the Amazon RDS for Oracle server side, go to Oracle Advanced Security (OAS) and configure the encryption settings. For details, see this Oracle documentation. The Amazon RDS for Oracle Application Development Framework (ADF) connector automatically negotiates the encryption method to use the one you configure in OAS when establishing a connection to Amazon RDS for Oracle.

• To use TLS:

  1. Get the TLS/SSL certificate info. Get the Distinguished Encoding Rules (DER)-encoded certificate information of your TLS/SSL cert, and save the output (----- Begin Certificate … End Certificate -----) as a text file.

     openssl x509 -inform DER -in [Full Path to the DER Certificate including the name of the DER Certificate] -text
     

     Example: Extract cert info from DERcert.cer, and then save the output to cert.txt.

     openssl x509 -inform DER -in DERcert.cer -text
     Output:
     -----BEGIN CERTIFICATE-----
     XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
     XXXXXXXXX
     -----END CERTIFICATE-----
     

  2. Build the keystore or truststore. The following command creates the truststore file, with or without a password, in PKCS-12 format.

     openssl pkcs12 -in [Path to the file created in the previous step] -out [Path and name of TrustStore] -passout pass:[Keystore PWD] -nokeys -export
     

     Example: Create a PKCS12 truststore file, named MyTrustStoreFile, with a password.

     openssl pkcs12 -in cert.txt -out MyTrustStoreFile -passout pass:ThePWD -nokeys -export  
     

  3. Place the truststore file on the self-hosted IR machine. For example, place the file at C:\MyTrustStoreFile.

  4. In the service, configure the Amazon RDS for Oracle connection string with EncryptionMethod=1 and the corresponding TrustStore/TrustStorePasswordvalue. For example, Host=<host>;Port=<port>;Sid=<sid>;User Id=<username>;Password=<password>;EncryptionMethod=1;TrustStore=C:\\MyTrustStoreFile;TrustStorePassword=<trust_store_password>.

Example:

{
    "name": "AmazonRdsForOracleLinkedService",
    "properties": {
        "type": "AmazonRdsForOracle",
        "typeProperties": {
            "connectionString": "Host=<host>;Port=<port>;Sid=<sid>;User Id=<username>;Password=<password>;"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Example: store password in Azure Key Vault

{
    "name": "AmazonRdsForOracleLinkedService",
    "properties": {
        "type": "AmazonRdsForOracle",
        "typeProperties": {
            "connectionString": "Host=<host>;Port=<port>;Sid=<sid>;User Id=<username>;",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

This section provides a list of properties supported by the Amazon RDS for Oracle dataset. For a full list of sections and properties available for defining datasets, see Datasets.

To copy data from Amazon RDS for Oracle, set the type property of the dataset to AmazonRdsForOracleTable. The following properties are supported.

Example:

{
    "name": "AmazonRdsForOracleDataset",
    "properties":
    {
        "type": "AmazonRdsForOracleTable",
        "schema": [],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        },
        "linkedServiceName": {
            "referenceName": "<Amazon RDS for Oracle linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

This section provides a list of properties supported by the Amazon RDS for Oracle source. For a full list of sections and properties available for defining activities, see Pipelines.

Amazon RDS for Oracle as source

To copy data from Amazon RDS for Oracle, set the source type in the copy activity to AmazonRdsForOracleSource. The following properties are supported in the copy activity source section.

Example: copy data by using a basic query without partition

"activities":[
    {
        "name": "CopyFromAmazonRdsForOracle",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Amazon RDS for Oracle input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AmazonRdsForOracleSource",
                "convertDecimalToInteger": false,
                "oracleReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Parallel copy from Amazon RDS for Oracle

The Amazon RDS for Oracle connector provides built-in data partitioning to copy data from Amazon RDS for Oracle in parallel. You can find data partitioning options on the Source tab of the copy activity.

When you enable partitioned copy, the service runs parallel queries against your Amazon RDS for Oracle source to load data by partitions. The parallel degree is controlled by the parallelCopies setting on the copy activity. For example, if you set parallelCopies to four, the service concurrently generates and runs four queries based on your specified partition option and settings, and each query retrieves a portion of data from your Amazon RDS for Oracle database.

You are suggested to enable parallel copy with data partitioning especially when you load large amount of data from your Amazon RDS for Oracle database. The following are suggested configurations for different scenarios. When copying data into file-based data store, it's recommended to write to a folder as multiple files (only specify folder name), in which case the performance is better than writing to a single file.

Example: query with physical partition

"source": {
    "type": "AmazonRdsForOracleSource",
    "query": "SELECT * FROM <TABLENAME> PARTITION(\"?AdfTabularPartitionName\") WHERE <your_additional_where_clause>",
    "partitionOption": "PhysicalPartitionsOfTable",
    "partitionSettings": {
        "partitionNames": [
            "<partitionA_name>",
            "<partitionB_name>"
        ]
    }
}

Example: query with dynamic range partition

"source": {
    "type": "AmazonRdsForOracleSource",
    "query": "SELECT * FROM <TABLENAME> WHERE ?AdfRangePartitionColumnName <= ?AdfRangePartitionUpbound AND ?AdfRangePartitionColumnName >= ?AdfRangePartitionLowbound AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column>",
        "partitionLowerBound": "<lower_value_of_partition_column>"
    }
}

Data type mapping for Amazon RDS for Oracle

When you copy data from and to Amazon RDS for Oracle, the following interim data type mappings are used within the service. To learn about how the copy activity maps the source schema and data type to the sink, see Schema and data type mappings.

Lookup activity properties

To learn details about the properties, check Lookup activity.

Upgrade the Amazon RDS for Oracle connector

Here are steps that help you upgrade the Amazon RDS for Oracle connector:

1. In Edit linked service page, select version 2.0 and configure the linked service by referring to Linked service properties version 2.0.

   For the authentication related properties including username and password, specify the original values in the corresponding fields in version 2.0. Other connection properties such as host, port, and Amazon RDS for Oracle Service Name/Amazon RDS for Oracle SID in version 1.0 are now parameters of the server property in version 2.0.

   For example, if you configure the version 1.0 linked service as shown below:

   

   { 
       "name": "AmazonRdsForOracleLinkedService", 
       "properties": { 
           "type": "AmazonRdsForOracle", 
           "typeProperties": { 
               "connectionString": "host=amazonrdsfororaclesample.com;port=1521;servicename=db1" 
           }, 
           "connectVia": { 
               "referenceName": "<name of Integration Runtime>", 
               "type": "IntegrationRuntimeReference" 
           } 
       } 
   }
   

   The identical version 2.0 linked service configuration using Easy Connect (Plus) Naming is:

   

   { 
       "name": "AmazonRdsForOracleLinkedService", 
       "properties": { 
           "type": "AmazonRdsForOracle", 
           "version": "2.0", 
           "typeProperties": { 
               "server": "amazonrdsfororaclesample.com:1521/db1",  
               "username": "<user name>",  
               "password": "<password>",  
               "authenticationType": "<authentication type>" 
           }, 
           "connectVia": { 
               "referenceName": "<name of Integration Runtime>", 
               "type": "IntegrationRuntimeReference" 
           } 
       } 
   } 
   

   The identical version 2.0 linked service configuration using Connector Descriptor is:

   

   { 
       "name": "AmazonRdsForOracleLinkedService", 
       "properties": { 
           "type": "AmazonRdsForOracle", 
           "version": "2.0", 
           "typeProperties": { 
               "server": "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= amazonrdsfororaclesample.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db1)))",  
               "username": "<user name>",  
               "password": "<password>",  
               "authenticationType": "<authentication type>" 
           }, 
           "connectVia": { 
               "referenceName": "<name of Integration Runtime>", 
               "type": "IntegrationRuntimeReference" 
           } 
       } 
   } 
   

   Tip

   Azure Key Vault is supported for the server property. You can edit the linked service JSON to add the Azure Key Vault reference, as shown below:
   

   Note that:

   • If you use Oracle Service Name in version 1.0, you can use Easy Connect (Plus) Naming or Connector Descriptor as the server format in version 2.0.

   • If you use Oracle SID in version 1.0, you need to use Connector Descriptor as the server format in version 2.0.

   • For some additional connection properties in version 1.0, we provide alternative properties or parameters in the server property in version 2.0. You can refer to the table below to upgrade the version 1.0 properties.

     Version 1.0	Version 2.0
     encryptionmethod	PROTOCOL (parameter in server)
     tnsnamesfile	TNS_ADMIN (environment variable supported on the self-hosted integration runtime)
     servername	server
     enablebulkload Value: 1, 0	enableBulkLoad Value: true, false
     fetchtswtzastimestamp Value: 1, 0	fetchTswtzAsTimestamp Value: true, false
     alternateservers	DESCRIPTION_LIST (parameter in server)
     arraysize	fetchSize
     cachedcursorlimit	statementCacheSize
     connectionretrycount	RETRY_COUNT (parameter in server)
     initializationstring	initializationString
     logintimeout	CONNECT_TIMEOUT (parameter in server)
     cryptoprotocolversion	SSL_VERSION (parameter in server)
     truststore	WALLET_LOCATION (parameter in server)

     For example, if you use alternateservers in version 1.0, you can set the DESCRIPTION_LIST parameter in the server property in version 2.0:

     Version 1.0 linked service using alternateservers:

     {
         "name": "AmazonRdsForOracleV1",
         "properties": {
             "type": "AmazonRdsForOracle",
             "typeProperties": {
                 "connectionString": "host=amazonrdsfororaclesample.com;port=1521;servicename=db1;alternateservers=(HostName= amazonrdsfororaclesample2.com:PortNumber=1521:SID=db2,HostName=255.201.11.24:PortNumber=1522:ServiceName=db3)"
             }
         }
     }
     

     Identical version 2.0 linked service using DESCRIPTION_LIST parameter in Connector Descriptor:

     {
         "name": "AmazonRdsForOracleV2",
         "properties": {
             "type": "AmazonRdsForOracle",
             "version": "2.0",
             "typeProperties": {
                 "server": "(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amazonrdsfororaclesample.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db1)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amazonrdsfororaclesample2.com)(PORT=1521))(CONNECT_DATA=(SID=db2)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=255.201.11.24)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=db3))))",
                 "username": "<user name>",  
                 "password": "<password>",  
                 "authenticationType": "<authentication type>" 
             }
         }
     }
     

2. The data type mapping for the Amazon RDS for Oracle linked service version 2.0 is different from that for the version 1.0. To learn the latest data type mapping, see Data type mapping for Amazon RDS for Oracle.

   An additional connection property supportV1DataTypes in version 2.0 can reduce upgrade difficulties caused by data type changes. Setting this property to true ensures that the data type in version 2.0 remains consistent with version 1.0.

Differences between Amazon RDS for Oracle version 2.0 and version 1.0

The Amazon RDS for Oracle connector version 2.0 offers new functionalities and is compatible with most features of version 1.0. The following table shows the feature differences between version 2.0 and version 1.0.

Related content

For a list of data stores supported as sources and sinks by the copy activity, see Supported data stores.