Version 24.3.9120

• Prerequisite
• Add the Oracle Connector
• Authenticate to Oracle
  • Security Identifier (SID)
  • Service Name
  • Data Source
• Complete Your Connection
• Configure Oracle Autonomous Database as a Destination
  • Step 1: Obtain and Install the Required DLLs and Libraries
  • Step 2: Add Oracle Autonomous Database as a CData Sync Destination
    • Set Up Connection Information
    • Add the Oracle Destination Connector
    • Authenticate to Oracle

You can use the Oracle (Native) connector from the CData Sync application to move data from any supported source to the Oracle (Native) destination. This page explains how to set up both the Oracle database and the Oracle Autonomous Database as Sync destinations.

Prerequisite

CData Sync uses the Java Database Connectivity (JDBC) driver to connector to your Oracle Server. If you have not done so already, download the driver from the Oracle JDBC and UCP Downloads page.

Copy the JDBC driver into the libs directory (based on your operating system) and restart the server.

• Windows: C:\ProgramData\CData\sync\libs

• UNIX or Mac OS X: ~/cdata/sync/libs

Add the Oracle Connector

To enable Sync to use data from Oracle, you first must add the connector, as follows:

1. Open the Connections page of the Sync dashboard.

2. Click Add Connection to open the Select Connectors page.

3. Click the Destinations tab and locate the Oracle (Native) row.

4. Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the Oracle (Native) connector. For more information about installing new connectors, see Connections.

Authenticate to Oracle

After you add the connector, you need to set the required properties.

On the New Connection page, enter the connection name of your choice.

CData Sync supports authenticating to Oracle in several ways. Select your authentication method below to proceed to the relevant section that contains the authentication details.

• Security Identifier (default)
• Service Name
• Data Source

Security Identifier (SID)

To connect to Oracle resources by using a SID, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• SID - Enter the SID for your Oracle database. The default SID is ORCL.

Service Name

To connect to Oracle resources by using a service name, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Server - Enter the host name or IP address of the server that hosts the Oracle database. The default server is localhost.

• Port - Enter the port number for the Oracle server. The default port is 1521.

• Service - Enter the service name for your Oracle database. The default SID is ORCL.

  To obtain the service name, submit the following query from the Oracle SQL*PLUS command line: global_name (select * from global_name)

Data Source

To connect to Oracle resources by using a data source, specify the following settings:

• User - Enter the username that you use to authenticate to the Oracle database.

• Password - Enter the password that you use to authenticate to the Oracle database.

• Data Source - Enter the data source for your Oracle database.

Complete Your Connection

To complete your connection:

1. Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)

2. Click Create & Test to create your connection.

Configure Oracle Autonomous Database as a Destination

The Oracle Autonomous Database has been supported for some time as a source for CData Sync software, via the OCI driver. Now, you can set up the database as a destination as well in Sync 21.0.8179.0 or later, as explained in the following sections.

Step 1: Obtain and Install the Required DLLs and Libraries

The first step is to obtain and install the required DLLs and libraries. This procedure differs depending on whether you use the Microsoft Windows (.NET) edition or the Java edition of Sync.

To obtain and install the DLLs and libraries:

1. Download the zipped JDBC driver (ojdbc8.jar) and companion JAR files (ojdbc8-full.tar.gz) for your version of Oracle Database.

2. Extract the downloaded ojdbc8-full.tar.gz file.

3. Copy the following files from the extracted file into the libs subdirectory of the Sync installation directory:

   • ojdbc8.jar

   • oraclepki.jar

   • osdt_core.jar

   • osdt_cert.jar

4. Restart Sync.

These steps complete the installation of the required DLLs and libraries. You can now move on to step 2.

Step 2: Add Oracle Autonomous Database as a CData Sync Destination

Now, you can add Oracle Autonomous Database as a Sync destination. This step involves connecting with Mutual TLS (mTLS), with client certificate authentication. So, you need to download and set up the wallet for mTLS. If you choose to use a TLS connection (without client certificate authentication) instead, you do not need to set up a wallet. However, to use the TLS connection, you must limit the connection source via the access control list (ACL) in the database settings.

For step 2, you need to perform the following tasks:

• Set up connection information.

• Add the destination connection.

Set Up Connection Information

To obtain the connection information that you need to create an Oracle Autonomous Database destination:

1. Navigate to the Autonomous Database management window for the database that you want to connect to in Oracle Cloud.

2. Click the DB Connection tab in the Autonomous Database Details window.

3. Download the wallet file by selecting Wallet instance in the Wallet type field. Then, click Download wallet.

   

   You will be asked for the password to encrypt the private key. Set an appropriate password in the Download Wallet modal. Then, click Download to download a ZIP file that contains the wallet.

   Note: The password that you set here is not used in Sync.

   

   After you click Download, the Database Connection modal is displayed.

4. Save a copy of the string (in the Connection Strings section of the dialog box) for the Transparent Network Substrate (TNS) name to which you want to connect. You need this string later in the process.

   

   At this point, your connection information is ready.

Add the Oracle Destination Connector

To enable Sync to use data from the Oracle Autonomous database, you first must add the connector, as follows:

1. Open the Connections page of the Sync dashboard.

2. Click Add Connection to open the Select Connectors page.

3. Click the Destinations tab and locate the Oracle (Native) row.

4. Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the Oracle (Native) connector. For more information about installing new connectors, see Connections.

Authenticate to Oracle

1. Set values for the following properties on the Connections Settings page:

   • Name - Enter a connection name of your choosing.

   • Provider Class - Enter the value Oracle.ManagedDataAccess.Client.

   • Settings Format - Enter the value Property List.

   • Connection Type - Select Data Source from the drop-down list.

   • User - Enter the username that you use to authenticate to the Oracle autonomous database.

   • Password - Enter the password that you use to authenticate to the Oracle autonomous database.

   • Data Source - Enter the connection string you copied in step 4 of the section “Setting Up Connection Information.” The key is setting the connection string in the data source. At this point, you need to unzip the wallet ZIP file that you downloaded earlier to the C:\ProgramData\CData\sync\data\ directory (folder). Then, specify that path in the connection string, as shown in this example:

     

2. Click Create and Test to verify that the connection is successful.

   If your connection is not successful, the problem might be that Sync cannot read the wallet file. Make sure that the directory (folder) that you specify in for the MY_WALLET_DIRECTORY= parameter can be accessed by the user account of the process in which Sync is running.

   Note: The wallet ZIP file that you downloaded contains various files. However, you only need the cwallet.sso file to connect with Sync. The other key files in the wallet have the private key encrypted with the password that you set in the downloading process. However, because the cwallet.sso file is stored with the private key unencrypted, you can connect to the client certificate without entering the password. Therefore, in this example, you can connect just by specifying the C:\ProgramData\CData\sync\data\Wallet_XXX\cwallet.sso directory (folder).