Connect with Oracle Cloud

Answered

How can I connect to the Oracle Cloud database if I have a wallet file.

6 comments
Comment actions Permalink
Official comment

ugo21

UPD:

The following articles describe the way to connect to Oracle using OCI or wallets:


Old workaround:

For connection using wallets, try setting connection settings via the JVM properties: you set them in VM Options field in Advanced tab of the Data Source dialog. As an example of which properties must be set please see Setting Java Properties with JDBC Thin Driver 12.2 section here: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-jdbc-thin-wallet.html#GUID-6D16D2FB-C001-4272-B3E0-1348F5A3EA1B

java 
-Doracle.net.tns_admin=/oracle/ADWC/wallet_sales
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=**********
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=************   
-Doracle.net.ssl_server_dn_match=true    
-Doracle.net.ssl_version=1.2

An alternative would be to download Oracle 18 jdbc driver version from https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html (all files) and add it for the Oracle data source in Driver settings: https://www.jetbrains.com/help/datagrip/data-sources-and-drivers-dialog.html#driver then use just jdbc url for connection, like shown in Using a JDBC URL Connection String with JDBC Thin Driver 18.1 or Higher section here: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-jdbc-thin-wallet.html#GUID-3256A467-B8D9-44AF-8908-4A9B3F9001B7

Comment actions Permalink

We don't support it yet

0
Comment actions Permalink

I resolved the problem (tested on Windows):

With the new driver there's no need to set the VM settings in the Advanced tab. But you do have to do something extra to get the connection to work.

1. Download the official Oracle drivers: https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19-8-c-downloads.html (ojdbc10-full.tar.gz)
2. Unzip all the jars to %USER%\AppData\Roaming\JetBrains\DataGrip2020.3\jdbc-drivers\Oracle\19.8.0.0 or wherever DataGrip's ojdbc8.jar is currently located on your system. This is because there are additional jar's needed to connect to ADW's and ATP's (oraclepki.jar, osdt_core.jar, and osdt_cert.jar)

3. Use connection type 'SID' in DataGrip. Only fill in the fields User, Password, and URL. In the URL field use an URL in the following format:

jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.xxx-1.oraclecloud.com))(connect_data=(service_name=xxx.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.xxx-1.oraclecloud.com,OU=Oracle ADB XXX,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

So after the @ you just put the same thing that is in your tnsnames.ora.

1
Comment actions Permalink

I think TNS connection type is more convenient: you basically specify TNSADMIN, TNS name (from tnsnames.ora), user and password. No need to "craft" the URL.

The only gotcha here is that you still need to create a driver manually, as the one, provided by DataGrip, is not working. Probably it lacks of some essential files, like oraclepki.jar, osdt_cert.jar and osdt_core.jar from the driver package available at Oracle's drivers downloads page.

0
Comment actions Permalink

Oracle JDBC driver 21.1 will be available in DataGrip 2021.1 and 2021 EAP and will contain all mentioned jar files

1

Please sign in to leave a comment.