How to connect to Oracle Cloud (Autonomous) Database from Incorta On-Prem

[amit_kothari]

1. Refer to https://www.oracle.com/database/technologies/java-connectivity-to-atp.html
2. Download the Autonomous Data Warehouse Client Credentials (wallet) zip file
3. Unzip the wallet file and copy the credentials folder to a location on the Incorta server, they have to be copied to same location on every node in case of multi-node .
4. The wallet files need to be accessible by both the analytics and loader nodes, or you will get TNS errors when trying to load the data even if the connection works and you are able to create schemas in Incorta
5. If ADW is 19c download Oracle JDBC driver 19c (full version) from this URL (https://download.oracle.com/otn-pub/otn_software/jdbc/1916/ojdbc8-full.tar.gz)
6. Unzip tar.gz
7. Copy the below jar files to  <home>/IncortaAnalytics/IncortaNode/runtime/lib location. In a multi-node environment make sure to configure this on both the loader and the analytics nodes

• ojdbc8.jar
• ucp.jar
• osdt_cert.jar
• osdt_core.jar
• Oraclepki.jar

Note: Whenever you upgrade Incorta, please copy these jar files again to the same location. These files will be removed during the an upgrade.

• Restart analytics and loader services
• Define a new Oracle data source using one of jdbc connection defined in tnsnames.ora included in Oracle Wallet downloaded from the autonomous db in OCI console.
• Form the JDBC URL as follows. Use this URL in the Incorta data connection string.
• JDBC URL example

• jdbc:oracle:thin:@incortadb_low?TNS_ADMIN=/Users/<user>/Downloads/Wallet_incortadb

  • The value incortadb_low is the service name (find this in the tnsnames.ora file, which is in the credentials file.)
  • The path is the directory where the client credentials were unzipped on the Incorta server

You need to make sure to use ojdbc from the same oracle version. For e.g:- If the wallet files were from Oracle 18.3 and target DB version is also 18.3 use ojdbc8 jar from 18.3 version
 
This link from Oracle should help to understand the steps:
https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/connect-jdbc-thin-wallet.html#GUID-2...
 
Attached is a screenshot which shows the compatibility matrix for the jdbc

[amr_elwadiah]

Additional steps that might be required to run the connection if it did not work:

• Update the ojdbc.properties that is under the wallet folder as below:
  • Comment out the second line which starts with oracle.net.wallet_location
  • Uncomment the last four lines that start with javax.net.ssl
  • Write the wallet password for the variables javax.net.ssl.trustStorePassword and javax.net.ssl.keyStorePassword without quotes (It's the password you specified when downloading the wallet from OCI Console or the Service Console)