Configuring Oracle Autonomous Database as a CData Sync Destination

The Oracle Autonomous Database is one of 250+ enterprise data sources supported for replication in CData Sync. As of CData Sync version 21.0.8179.0, Oracle Autonomous Database is also supported as a replication destination, meaning users can replicate any of their SaaS, Big Data, and NoSQL data sources to their Oracle Autonomous Database.

This article explains how to set up Oracle Autonomous Database as a destination in CData Sync.

1. Obtaining and Installing the Required DLLs and Libraries
   • Microsoft Windows (.NET) Edition
   • Java Edition
2. Adding Oracle Autonomous Database as a CData Sync Destination
   • Collecting Connection Information
   • Adding a Destination Connection

Obtaining and Installing 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. The following sections explain the procedure for each edition.

Microsoft Windows (.NET) Edition

If you use Oracle Database as a Sync destination already, and you can use Oracle.ManagedDataAccess.Client as the provider class for the connection destination, this first step is not necessary. However, if you have never set up Sync or have never used Oracle Database as a destination, you need to set up the necessary driver.

For the Windows edition of Sync, follow the steps in the sections below for the whichever of these scenarios is true for you.

The Oracle Database or the Oracle Database client:

• IS installed on the same server as Sync.
• IS NOT installed on the same server as Sync.

The Database or Client Is Installed on the Same Server as CData Sync

1. Locate the Oracle.ManagedDataAccess.dll file in the ORACLE_HOME\odp.net\managed\common folder. (The ORACLE_HOME folder typically specifies a location similar to C:\app\oracle\product\19.0.0\client_1).
2. Copy the Oracle.ManagedDataAccess.dll file and paste it into C:\Program Files\CData\CData Sync\www\bin.
3. Restart Sync.

Important: Verify that the paths for ORACLE_HOME and its \bin subfolder are set in the environment variables. These paths should be set already if you installed Oracle using normal procedures.

The Database or Client is Not Installed on the Same Server as CData Sync

1. Download and install the Oracle Data Access Components (ODAC) for Windows: https://www.oracle.com/database/technologies/net-downloads.html.

   Note: At the time of this writing, the 64-bit ODAC 19.3.1 file in the 64-bit ODAC OUI comes with release notes and installation instructions per the Oracle .NET and Visual Studio Downloads web page (linked above>.

2. Extract the downloaded file and execute setup.exe to install the components.
3. Locate the Oracle.ManagedDataAccess.dll file in the ORACLE_HOME\odp.net\managed\common folder. (The ORACLE_HOME folder typically specifies a location similar to C:\app\oracle\product\19.0.0\client_1).
4. Copy the Oracle.ManagedDataAccess.dll file and paste it into C:\Program Files\CData\CData Sync\www\bin.
5. Restart Sync.

Once you install the required DLLs and libraries you can add Oracle Autonomous Database as a destination in CData Sync.

Java Edition

For the Java edition of CData Sync, follow these steps:

1. Download the zipped JDBC driver (ojdbc8.jar) and companion JAR files (ojdbc8-full.tar.gz) for your version of Oracle Database: https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html.
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:
   • oraclepki.jar
   • osdt_core.jar
   • osdt_cert.jar
4. Restart Sync.

Once you install the required DLLs and libraries you can add Oracle Autonomous Database as a destination in CData Sync.

Adding Oracle Autonomous Database as a CData Sync Destination

With the appropriate DLLs and libraries installed, you can add Oracle Autonomous Database as a destination in CData Sync. This step involves connecting with Mutual TLS (mTLS) using client certificate authentication, requiring you 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.

• Gather connection information
• Add the destination connection

Collecting 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, opening the Database Connection modal.
3. Download the wallet file by selecting Wallet instance in the Wallet type field. Then, click Download wallet.

   When asked, set an appropriate password and 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 modal) 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.

Adding the Destination Connection

With the connection information gathered, you can add the destination connection in CData Sync.

1. Open the Sync application and click the Connections tab.
2. Click the Destination tab on the Connections page. Then, click the Oracle tile (for both the .NET and Java editions), as shown below.
3. Enter values for the following fields on the Connections Settings page:
   • Name: ConnectionName (a name of your choosing)
   • Connection Type: Data Source
   • User: YourDatabaseUserName
   • Password: YourPassword
   • Data Source: The connection string you copied in step 4 of the section "Gathering 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 C:\ProgramData\CData\sync\data. Then, specify that path in the connection string, as shown in this example:

After you configure your connection, click Test Connection to verify that the connection is successful.

If your connection is not successful, CData Sync may not be able read the wallet file. Make sure that the directory (folder) that you specify for the MY_WALLET_DIRECTORY connection property can be accessed by the user account of the process in which Sync is running.

Free Trial & More Information

At this point, you have configured Oracle Autonomous Database as a destination connection in CData Sync. Try CData Sync free for 30 days and start replicating all of your enterprise data to Oracle Autonomous Database (or any other supported destination) today.