Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure)

As the title suggests, Java's JDBC Thin Driver for Autonomous DB (ADW / ATP) I will try to connect. Try it with the following configuration 彡 (゜) (゜)

Compute(Java, JDBC Thin Driver) ⇒ Autonomous DB(ADW/ATP)

This is a continuation of the previous article.

Java JDBC Thin Driver for OCI Database (DBaaS) PDB Try to connect. (Oracle Cloud Infrastructure) https://qiita.com/ora_gonsuke777/items/0e6c7d25d9977de45531

1. What is Autonomous DB (ADW / ATP)?

Autonomous DB is a Cloud Service for fully managed databases provided by ORACLE.

With ADW (Autonomous Data Warehouse) for DWH There is ATP (Autonomous Transaction Processing) suitable for OLTP. 彡 (ﾟ) (ﾟ)

Autonomous Data Warehouse https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/

Autonomous Transaction Processing https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/

2. Points when connecting to Autonomous DB (ADW / ATP) (* Wallet required)

The difference between connecting to a regular Oracle Database and connecting to Autonomous DB (ADW / ATP) is One point is that encrypted communication using a wallet is essential.

About connecting to Autonomous Data Warehouse Instance https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/connect-intorduction.html#GUID-CD4C10A6-1C1E-4969-8F67-1433B6CE626A

About connecting to Autonomous Transaction Processing Instance https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/atpug/connect-intorduction.html#GUID-CD4C10A6-1C1E-4969-8F67-1433B6CE626A

For connection with sqlplus or SQL Developer, refer to the following manual 彡 (ﾟ) (ﾟ)

Connect with Oracle SQL Developer (18.2) or above https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/connect-sql-dev182.html#GUID-14217939-3E8F-4782-BFF2-021199A908FD

Connect with SQL*Plus https://docs.oracle.com/cd/E83857_01/paas/autonomous-data-warehouse-cloud/user/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A

Specifying the wallet by some means is a requirement for ADW / ATP connection. In this article, I will try some connections with JDBC Thin driver + TNS_ADMIN. 彡 (ﾟ) (ﾟ)

3. Download and unpack the wallet, JDBC Driver Full version

There are several steps, but execute "Plain JDBC using Oracle Wallets" in the following manual.

Java Connectivity with Autonomous Database (ATP or ADW) using 18.3 JDBC https://www.oracle.com/technetwork/database/application-development/jdbc/documentation/atp-5073445.html

Please refer to the following for downloading and expanding the wallet.

Connect to an autonomous database (Autonomous Transaction Processing) using Golang https://qiita.com/sugimount/items/69e11c116a895c9feb97

#Deploy the Autonomous DB Wallet
unzip Wallet_xxxxxx.zip

The latest version (18.3) of JDBC Driver is as follows at the moment (February 26, 2019), but please refer to the latest version.

Oracle Database 18c (18.3) JDBC Driver & UCP Downloads https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/jdbc-ucp-183-5013470.html

Download ojdbc8-full.tar.gz and unpack it on Compute (tar xvzf ~).

#Expand JDBC Driver Full version
tar xvzf ojdbc8-full.tar.gz

The wallet is in /home/opc/app/opc/product/18.0.0/client_1/network/admin, It is assumed that the JDBC Driver is expanded to / home / opc / work / ojdbc8-full respectively.

# Wallet(TNS_ADMIN)
ls -la /home/opc/app/opc/product/18.0.0/client_1/network/admin

total 76
drwxr-xr-x.  3 opc opc  4096 Feb 25 16:26 .
drwxr-xr-x. 10 opc opc  4096 Jan 17 06:46 ..
-rw-rw-r--.  1 opc opc  6661 Feb 25 15:01 cwallet.sso
-rw-rw-r--.  1 opc opc  6616 Feb 25 15:01 ewallet.p12
-rw-rw-r--.  1 opc opc  3242 Feb 25 15:01 keystore.jks
-rw-rw-r--.  1 opc opc    87 Feb 25 15:01 ojdbc.properties
drwxr-xr-x.  2 opc opc  4096 Jan 17 06:44 samples
-rw-r--r--.  1 opc opc  1441 Aug 26  2015 shrept.lst
-rw-rw-r--.  1 opc opc   114 Feb 25 15:01 sqlnet.ora
-rw-rw-r--.  1 opc opc   155 Feb  4 11:23 sqlnet.ora.bak
-rw-rw-r--.  1 opc opc  2771 Feb 25 15:01 tnsnames.ora
-rw-rw-r--.  1 opc opc  3336 Feb 25 15:01 truststore.jks
-rw-rw-r--.  1 opc opc 19823 Feb 25 15:01 Wallet_xxxxxx.zip

# JDBC Driver full
ls -la /home/opc/work/ojdbc8-full

total 8328
drwx------.  2 opc opc    4096 Aug 20  2018 .
drwxrwxr-x. 22 opc opc    4096 Feb 25 15:33 ..
-r--r--r--.  1 opc opc 4161744 Aug  2  2018 ojdbc8.jar
-r-xr-xr-x.  1 opc opc   11596 Aug  2  2018 ojdbc.policy
-r--r--r--.  1 opc opc  144428 Aug  2  2018 ons.jar
-r--r--r--.  1 opc opc  307817 Aug  2  2018 oraclepki.jar
-r--r--r--.  1 opc opc 1661545 Aug  2  2018 orai18n.jar
-r--r--r--.  1 opc opc  205152 Aug  2  2018 osdt_cert.jar
-r--r--r--.  1 opc opc  306854 Aug  2  2018 osdt_core.jar
-rw-r--r--.  1 opc opc    2595 Aug 20  2018 README.txt
-r--r--r--.  1 opc opc   29103 Aug  2  2018 simplefan.jar
-r--r--r--.  1 opc opc 1398331 Aug  2  2018 ucp.jar
-r--r--r--.  1 opc opc  262415 Aug  2  2018 xdb6.jar

4. Method 1: Write the wallet path (TNS_ADMIN) in the JDBC URL

In JDBC 18.3, the wallet path is TNS_ADMIN It can be described in the JDBC URL.

Specified in the URL. Here is an example: https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/atpug/connect-jdbc-thin-wallet.html#GUID-F1D7452F-5E67-4418-B16B-B6A7B92F26A4 final static String DB_URL="jdbc:oracle:thin:@atpconnection_low?TNS_ADMIN=.";

In this writing, the connection string uses the descriptor in tnsnames.ora. The sample is below

import java.sql.*;

public class GetContainerName {
    public static void main(String[] args) {
        final String path = "jdbc:oracle:thin:@xxxxxx_high?TNS_ADMIN=/home/opc/app/opc/product/18.0.0/client_1/network/admin";
        final String id = "ADMIN";  //ID
        final String pw = "xxxxxxxxxxxxx";  //password

        try (
            Connection conn = DriverManager.getConnection(path, id, pw);
            Statement  stmt = conn.createStatement();
            ResultSet  rs   = stmt.executeQuery("SELECT NAME FROM V$CONTAINERS");
        ) {
            while (rs.next()) {
                String cn = rs.getString("name");
                System.out.println("Container Name => " + cn);
            }
        } catch(SQLException ex) {
            ex.printStackTrace();  //Error
        }
    }
}

Compile and run below. Description of Manual Street oraclepki.jar, Include osdt_core.jar and osdt_cert.jar in the classpath. 彡 (ﾟ) (ﾟ)

export JAVA_HOME=/home/opc/work/jdk-11.0.2
export PATH=${JAVA_HOME}/bin:${PATH}

javac GetContainerName.java

java -classpath /home/opc/work/ojdbc8-full/ojdbc8.jar:\
/home/opc/work/ojdbc8-full/ucp.jar:\
/home/opc/work/ojdbc8-full/oraclepki.jar:\
/home/opc/work/ojdbc8-full/osdt_core.jar:\
/home/opc/work/ojdbc8-full/osdt_cert.jar:. \
GetContainerName

Container Name => VRWV9351YZ4NXNS_XXXXXX

Success (`・ ω ・) Ъ

5. Method 2: Specify the wallet path (TNS_ADMIN) as an environment variable

Specify the wallet path in the TNS_ADMIN environment variable. In this case, it is not necessary to describe TNS_ADMIN in the JDBC URL, and the source is the following 彡 (ﾟ) (ﾟ)

import java.sql.*;

public class GetContainerName {
    public static void main(String[] args) {
        final String path = "jdbc:oracle:thin:@xxxxxx_high";
        final String id = "ADMIN";  //ID
        final String pw = "xxxxxxxxxxxxx";  //password

        try (
            Connection conn = DriverManager.getConnection(path, id, pw);
            Statement  stmt = conn.createStatement();
            ResultSet  rs   = stmt.executeQuery("SELECT NAME FROM V$CONTAINERS");
        ) {
            while (rs.next()) {
                String cn = rs.getString("name");
                System.out.println("Container Name => " + cn);
            }
        } catch(SQLException ex) {
            ex.printStackTrace();  //Error
        }
    }
}

The compilation and execution results are as follows.

export JAVA_HOME=/home/opc/work/jdk-11.0.2
export PATH=${JAVA_HOME}/bin:${PATH}

javac GetContainerName.java

export TNS_ADMIN=/home/opc/app/opc/product/18.0.0/client_1/network/admin

java -classpath /home/opc/work/ojdbc8-full/ojdbc8.jar:\
/home/opc/work/ojdbc8-full/ucp.jar:\
/home/opc/work/ojdbc8-full/oraclepki.jar:\
/home/opc/work/ojdbc8-full/osdt_core.jar:\
/home/opc/work/ojdbc8-full/osdt_cert.jar:. \
GetContainerName

Container Name => VRWV9351YZ4NXNS_XXXXXX

Success again (`・ ω ・) Ъ

6. Method 3: Specify the wallet path (TNS_ADMIN) as a system property with -D

Set the wallet path as the system property oracle.net.tns_admin Specify with the -D option. The java source is the same as 5. above, so it is omitted.

The execution command is as follows.

export JAVA_HOME=/home/opc/work/jdk-11.0.2
export PATH=${JAVA_HOME}/bin:${PATH}

javac GetContainerName.java

unset TNS_ADMIN

java -classpath /home/opc/work/ojdbc8-full/ojdbc8.jar:\
/home/opc/work/ojdbc8-full/ucp.jar:\
/home/opc/work/ojdbc8-full/oraclepki.jar:\
/home/opc/work/ojdbc8-full/osdt_core.jar:\
/home/opc/work/ojdbc8-full/osdt_cert.jar:. \
-Doracle.net.tns_admin=/home/opc/app/opc/product/18.0.0/client_1/network/admin \
GetContainerName

Container Name => VRWV9351YZ4NXNS_XXXXXX

Success three times (`・ ω ・) Ъ

7. Summary

By specifying the classpath of oraclepki.jar, osdt_core.jar, osdt_cert.jar I'm addicted to it ... First of all, the royal road is to follow the manual. 彡 (-) (-)

Recommended Posts

Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure)
Try connecting to the Autonomous Database with JDK6 (Java) + JDBC OCI Driver (type 2).
Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure)
Try connecting to the Autonomous Database with JDK6 (Java) + JDBC OCI Driver (type 2).
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
How to create a JDBC URL (Oracle Database, Thin)
Connecting to a database with Java (Part 1) Maybe the basic method
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin