How to connect to Oracle Cloud Autonomous Database (ADW, ATP or ADB) from Incorta (using wallet files)

[amit_kothari]

Here are the steps to connect to an Oracle Cloud Autonomous Database (ADW, ATP or ADB).

1. Refer to this article on connecting to an Autonomous Oracle Database
2. Download & Unzip the Autonomous Data Warehouse Client Credentials (wallet) zip file from the Oracle Cloud Console
3. We need to download some for JAR files from Oracle DB driver url https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
   So based on the Oracle ATP version download the “Zipped JDBC driver (ojdbc8.jar) and Companion Jars”
   Example: If Oracle ATP DB version is 19.27.0.0 then we need to download ojdbc8-19.25.0.0.jar and Companion Jars 
4. Unzip tar.gz
5. Depending on the Oracle connector version installed in incorta any external Jars for the Oracle Connector need to be placed under path-to-node/extensions/connectors/sql-oracle-<version>
   , but make sure that the Oracle connector version installed is 2.0.1.3 or higher. In a multi-node environment make sure to configure this on both the loader and the analytics nodes.
6. The above folder should have versions of the jars below, if not we have to copy the correct versions of those

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

For eg  the following command returns those 5 files-

ls -ltrh   /home/incorta/IncortaAnalytics/IncortaNode/extensions/connectors/sql-oracle-2.2.2.3/  --node=loader

-rw-r----- 1 incorta incorta 4.4M May 20 19:02 ojdbc8-19.25.0.0.jar
-rw-r----- 1 incorta incorta 306K May 20 19:02 osdt_core-19.25.0.0.jar
-rw-r----- 1 incorta incorta 206K May 20 19:02 osdt_cert-19.25.0.0.jar
-rw-r----- 1 incorta incorta 303K May 20 19:02 oraclepki-19.25.0.0.jar
-rw-r----- 1 incorta incorta 1.7M May 20 19:02 ucp-19.25.0.0.jar

7. Upload the whole Wallet folder (eg Wallet_incortadb ) with its 9 files to both loader and analytics in path "<incorta home>/IncortaAnalytics/IncortaNode/ 

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

• Restart the analytics and loader services
• In Incorta define a new Oracle data connection using one of the JDBC connections defined in tnsnames.ora which is included in the Oracle Wallet downloaded from the autonomous DB in the OCI console.
• Form the JDBC URL as follows. Use this URL in the Incorta data connection string, please note that in the example below incortadb_low is the connection label in the tnsnames.ora

JDBC URL examples:

a)jdbc:oracle:thin:@incortadb_low?TNS_ADMIN="/home/incorta/IncortaAnalytics/IncortaNode/Wallet_incortadb"
b)jdbc:oracle:thin:@prodatpdb_high?TNS_ADMIN="/home/incorta/IncortaAnalytics/IncortaNode/Wallet_PRODATPDB"

• The value incortadb_low is the value of the service_name tag (find this in the tnsnames.ora file, which is in the credentials file) 
• The path is the directory that contains the wallet files in the backend in the ojdbc.properties.  This is where the client credentials were unzipped on the Incorta server

You need to make sure to use the ojdbc from the same oracle version. For example, if the wallet files were from Oracle 18.3 and the target DB version is also 18.3 use the ojdbc8 jar from 18.3 version.
 
This link from Oracle should help you to understand the required steps.
 
This screenshot 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)