Search

Oracle

Version 22.0.8483

Oracle

This page explains how to set up both the Oracle Database and the Oracle Autonomous Database as CData Sync destinations:

Configuring Oracle Database as a CData Sync Destination

To connect to Oracle, select the Oracle Database option and set the following properties:

  • Server: The host name or IP of the server that hosts the Oracle database.

  • Port: The port on which the Oracle database runs.

  • User: The user Id that is provided for authentication with the Oracle database.

  • Password: The password that is provided for authentication with the Oracle database.

  • SID: The service name of the Oracle database.

Windows Edition

Sync uses the Oracle Database client driver to connect to your Oracle Server. If you have not done so already, download the client from the Oracle ODAC Downloads page.

When you download the Oracle Database client, ensure that the bitness matches the bitness of CData Sync. In addition, when you install the client, select either the Runtime or the Administrator installation type.

Java Edition

The Java edition of 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 and restart the server.

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

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

Configuring Oracle Autonomous Database as a CData Sync 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: 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 Oracle Database 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 like 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.

These steps complete the installation of the required DLLs and libraries. You can move on to Step 2: Adding Oracle Autonomous Database as a CData Sync Destination.

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

  1. Download and install the Oracle Data Access Components (ODAC) for Windows.

    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.

  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 like 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.

These steps complete the installation of the required DLLs and libraries. You can move on to Step 2: Adding Oracle Autonomous Database as a CData Sync Destination.

Java Edition

For the Java edition of 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.

  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 move on to step 2 in the next section.

Step 2: Adding 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.

Setting 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 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

Now you can add the destination connection in Sync, as follows:

  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 the values below for the following fields on the Connections Settings page:

    • Name: ConnectionName (a name of your choosing)

    • Provider Class: Oracle.ManagedDataAccess.Client

    • Settings Format: Property List

    • Connection Type: Data Source (choose from the drop-down list)

    • User: YourDatabaseUserName

    • Password: YourPassword

    • Data Source: 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:

  4. Click Test Connection 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).