Connecting via JDBC to the Oracle Cloud

blank

You can connect to an Oracle Autonomous Database in different ways. This is well documented here. It’s a bit different from what we know from on-premises environments. In this blog post, I show the steps to connect to an Autonomous Database from a third-party IDE like DataGrip.

From a JDBC perspective, this is just an ordinary JDBC URL with some driver-specific properties. Therefore, this approach should work for any JDBC-based IDE and also for any Java application.

Step 1 – Download the Wallet

Go to your Autonomous Database and click on the “DB Connection” button. A screen similar to the following appears:

blank

Press on the “Download Wallet” button and enter a password. This password is used to protect the key and the trust store. We will need it later. I named my instance “ATP21”. Therefore, in my case, a zip archive named “Wallet_ATP21.zip” was downloaded.

Step 2 – Unzip the Wallet

Unzip the downloaded zip file and move it to a location where want to keep it. The wallet contains the following files:

  • README
  • ewallet.p12
  • ojdbc.properties
  • tnsnames.ora
  • cwallet.sso
  • keystore.jks
  • sqlnet.ora
  • truststore.jks

Open the file “tnsnames.ora” in a text editor. It contains 5 entries. We need one of those entries to build the JDBC connection string. I highlighted the relevant part of the first entry in the next screenshot:

blank

Step 3a – Configure Connection in DataGrip (Legacy Driver)

Add a new connection in DataGrip and select “Oracle” as shown in the following screenshot:

0blank

In the “General” tab change the Connection type to “URL only”. Enter the user, the password and the complete JDBC URL as shown in the next screenshot:

blank

The URL starts with jdbc.oracle:thin:@. The rest is the text I’ve highlighted in the tnsnames.ora file above.

Then click on the “Advanced” tab and define the following properties:

  • javax.net.ssl.trustStore
  • javax.net.ssl.trustStorePassword
  • javax.net.ssl.keyStore
  • javax.net.ssl.keyStorePassword

Here are my settings (of course you need to amend the values to match the environment of your wallet):

blank

Step 3b – Configure Connection in DataGrip (Current Driver)

DataGrip automatically downloads the latest Oracle Database JDBC driver. In my case version 21.1.0.0. Since version 18.3 there is an easier way to connect. The JDBC driver can access the wallet directory and its files. As a result, you do not need to configure the java.net.ssl.* JDBC properties anymore. You just have to define one additional JDBC property “TNS_ADMIN” to define the path to the wallet directory.

And of course, you can pass this JDBC property directly in the JDBC URL as shown in the next screenshot:

blank

Conclusion

Establishing a connection to an Autonomous Database requires a wallet. The JDBC driver needs access to this wallet. This doesn’t make things easier, but it doesn’t make them overly complicated either.

However, you need to deal with this additional resource regularly because the wallet has a limited lifetime. This is documented in the README file.

Wallet Expiry Date
-----------------------
This wallet was downloaded on 2021-02-28 08:16:36.267 UTC.
The SSL certificates provided in this wallet will expire on 2023-03-19 21:43:22.0 UTC.
In order to avoid any service interruptions due to an expired SSL certificate, you must re-download the wallet before this date.

So I have to update my wallet in two years otherwise I won’t be able to connect anymore.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.