X

Best Practices from Oracle Development's A‑Team

Data Sync and Oracle Autonomous Data Warehouse (ADW) and Exadata Express Cloud Service

* This blog was last tested on Oracle Analytics Cloud Data Sync (2.6.1) + ADW 18c by Jay Pearson (Oracle A-Team) * 

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 a variety of data sources such as Oracle Autonomous Data Warehouse (ADW).

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

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

 

Download The Latest Version of Data Sync Tool

The Autonomous Data Warehouse REQUIRES Data Sync 2.4+.

Be sure to download and install the latest version of the Data Sync Tool from Oracle Analytics Downloads.

For general instructions on configuring Data Sync, see this article.

For further information on the Data Sync Tool, upgrade instructions, OAC Data Sync readme (Data Sync release notes and known issues), and OAC Data Sync Documentation (Data Sync install and user guide) can be found here.

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

Note - customers still using the legacy/limited DBCS-Schema database, can not use the latest 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.

 

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 ADW Credentials

Within the Autonomous Database Details, click on DB Connection, then Download Wallet. 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.

<INTERIM STEP>

*** This step is NOT required for Data Sync 2.6.1 ***

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 ADW 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 ADW 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 ADW 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 3 entries.  These are for different connection priorities 'Low', 'Medium' and 'High' and also for different connection types.

For this exercise any of the 3 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 ADW 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 browse to select the wallet [cwallet.sso] file location, then 'OK' to exit the wizard.

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

7. Then click OK to close the editor box.

8. Select the 'Advanced Properties' tab confirm that 'Use database merge for upsert' is set to 'true'

and that 'Number of rows for triggering database merge' is set to 1000000 (one million).

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

Confirm that the wallet location has been set correctly.

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.

 

Create a New Job to Load into ADW

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 ADW 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 'ADW' connection in the Target to over-ride the default.

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 ADW, you need to be sure to uncheck this box.  If this step is missed, the job will fail.

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 ADW.  Be sure to select the ADW 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 (ADW).

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