X

Best Practices from Oracle Development's A‑Team

Data Sync and the Autonomous Data Warehouse Cloud (ADWC) and Exadata Express Cloud Service

For other A-Team articles by Richard, click here

Introduction

The Data Sync tool provides the ability to extract from both on-premise, and cloud data sources, and to load that data into BI Cloud Service (BICS), Oracle Analytics Cloud Service (OAC), and other relational databases.

In the latest release, the tool adds the ability to both load into, and extract from, the Autonomous Data Warehouse Cloud (ADWC).

This article will walk through the steps to set up the connection to ADWC, and show how to create a simple mapping to load data into ADWC.

Please note - that while this article will concentrate on ADWC, the same steps apply to setting up a connection with Exadata Express Cloud Service.

 

Download The Latest Version of Data Sync Tool

Be sure to download and install the latest version of the Data Sync Tool from OTN through this link.  The Autonomous Data Warehouse REQUIRES data sync 2.4 of later.

Data Sync requires JDK8.  You can download that through this link.

(Note - customers still using BI Cloud Service (BICS) and loading into the schema service database, can not use this version of Data Sync.  The version for BICS is available to download here, but does not have all the functionality of the OAC / OAAC version.)

For further instructions on configuring Data Sync, see this article.  If a previous version of Data Sync is being upgraded, use the documentation on OTN.

 

Download the Java Cryptography Extension

Unless the JDK you are using for Data Sync already has the unlimited strength version of JCE, you will need to download it.

Go to this link on the Oracle Technical Network, then download the file and extract it.

Find the JAVA_HOME that data sync is using.  If you are unsure, open the 'config.bat' or 'config.sh' files (depending on the OS) and see where the JAVA_HOME is pointing to.

From the extracted folder you just downloaded, take the 'local_policy.jar' and the 'US_export_policy.jar' files, and copy those into the $JAVA_HOME/jre/lib/security directory.  If the files already exist, overwrite them.

If Data Sync was open already, you need to shut it down fully.  Close the application, then find the item in the menu bar and select 'exit'.  Then reopen the tool.

 

Download the ADWC Credentials

Within the Autonomous Data Warehouse Cloud console, go to the Service Console, and then he Administration tab, and then click 'Download Client Credentials'.  Use a password to protect the zip file, then download that locally, unzip it, and make a note of the path.  This will be required when setting up the connection in Data Sync.

ds

<INTERIM STEP>

Any version of the Data Sync tool after 2.4 should come with these files already.  Check in the /Lib folder in the Data Sync directory.  If you see the files ojdbc8.jar, oraclepki.jar, osdt_cert.jar, osdt_core.jar files - then skip this step and move on to setting up the ADWC Connection in Data Sync.

If those files are not present, and instead you see 'odbc7.jar', firstly DELETE that odbc7.jar, and then download this zip file (click link to download), unzip the contents, and move them to the /lib directory.

You should then shut down the Data Sync application, and also close it in the menu bar icon, then start it up again.

 

Setup ADWC Connection in Data Sync

Before you can set up the connection, you need to get the details of the host, service and port for your ADWC database.

1. In the unzipped folder created in the last step with the client credentials, open the tnsnames.ora file in a text editor.

d

It's possible there will be many connections within this file.  For each individual connection, there are 6 entries.  These are for different connection priorities 'Low', 'Medium' and 'High' and also for different connection types.

For this exercise any of the 6 connection strings can be used.  What we are interested in is the port, the host and the service_name.  You may need to check with your ADWC administrator to see which connection priority they want you to use.  In this case we're going to use the 'medium' one - it is defined as part of the service_name.  Any will work.

I've obfuscated the details in this screenshot of my environment, but you can see the 3 sections of information we're interested in.

5

2. Take those values and create a string in this format below.  Replace only the $HOST, $PORT, and $SERVICE_NAME variables with the values from your TNSNAMES.ORA file.  Create the rest of the string the same.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=$HOST)(PORT=$PORT))(CONNECT_DATA=(SERVICE_NAME=$SERVICE_NAME)))

3. In the Data Sync tool, go to the Connections menu and hit 'New' to create a new connection.

4. Give the connection a name, select the 'Oracle (Thin)' connection type, and select 'Known URL and Driver', then 'OK' to exit the wizard.

s

5. Go to the details of the new connection created in the lower part of the screen, and enter the User and Password for your ADWC connection.  Additionally enter that same user name into the box for 'Schema / Table Owner'.  This schema table value should be in UPPERCASE.

6.Open the URL edit screen by clicking the symbol at the right of the box.  In the editor box, paste in the connection string you created in the step (2) above.

f

7. Then click OK to close the editor box.

8. Select the 'Advanced Properties' tab and change the value in the 'Use database merge for upsert' to be 'true'

Also update the 'Number of rows for triggering database merge' to be 1000000 (one million).

4

9. Finally - within the same 'Advanced Properties' tab, double click in the 'Additional JDBC driver Properties' tab to open the editor box.

This is where you need to enter the path to the cwallet.sso file in the client credentials folder that you unzipped.

Replace the $FULL_PATH_TO_CWALLET.SSO_FILE value in this string below with your path.

oracle.net.ssl_version=1.2 oracle.net.wallet_location=file:$FULL_PATH_TO_CWALLET.SSO_FILE

as an example - if your wallet was saved in this path:  C:\users\oracle\client_creds\cwallet.sso - then the entry would look like this:

oracle.net.ssl_version=1.2 oracle.net.wallet_location=file:C:\users\oracle\client_creds\cwallet.sso

Paste the string into the editor box.

d

Then hit 'OK'

10. Go back to the 'Edit' tab and test the connection.

If everything has been entered correctly, you should get a 'Connection Successful' dialogue box.  If you don't, go back and confirm all steps above.  Save the connection.

d

 

Create a New Job to Load into ADWC

1. Create source objects as you would normally.  These can be from any source that Data Sync supports.  For more information, see this previous article on Data Sync.

2. Once you have your mappings set up, you need to create a new job and define ADWC as the target.

3. Go to the 'Jobs' tab and right click somewhere in the empty space and select 'Update Job'

fg

4. Create a New Job, and give it a suitable name.

f

5. In the next screen, select your 'ADWC' connection in the Target to over-ride the default.

g

6. Click 'Finish' to complete the job creation process.

7. Delete the original job (not the one you just created) by selecting it and hitting 'Delete'

8. Finally - with the new job you had just created, be sure to uncheck the box 'Drop/Create Indices'.  The Autonomous Data Warehouse controls all indexing itself, and does not allow the Data Sync tool to drop and create indexes as part of the load.  Any time you create a new job that loads ADWC, you need to be sure to uncheck this box.  If this step is missed, the job will fail.

5

9 Run the job and confirm it works.  If you have errors, trouble-shoot the logs.  Make sure all the steps above have been followed.

Cursor_and_Windows7_x64

 

You can use the same process to extract data from ADWC.  Be sure to select the ADWC connection when you are defining the source objects.  More information can be found in the previously referenced article.

 

Summary

This article walked through the steps to set up the Data Sync tool to work with the Autonomous Data Warehouse Cloud (ADWC).

For other A-Team articles by Richard, click here

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha