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

[amit_kothari]

Here are the steps to connect to an Oracle Cloud (Autonomous) Database.

1. Refer to this article on connecting to an Autonomous Oracle Database
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 the Oracle JDBC driver 19c (full version) from this download site 
6. Unzip tar.gz
7. Any external Jars for the Oracle Connector need to be placed under path-to-node/extensions/connectors/shared-libs/sql-oracle, 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.

• 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 an upgrade.

• Restart the analytics and loader services
• Define a new Oracle data source 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

JDBC URL example:

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

• 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)