[JAVA] Create a data source (connection pool) that connects from WLS (WebLogic) to Autonomous Database (ATP / ADW).

• (Added on July 4, 2019) This article has been revised as an article to create a data source that connects to Autonomous Database from a single WLS (WebLogic).

Try creating a data source (connection pool) that connects to Autonomous Database from WLS (WebLogic). 彡 (ﾟ) (ﾟ)

There are some points

-(Recommended) Store the latest version of JDBC Driver (Full version) on the WLS OS (* do not overwrite) and pass it through the CLASSPATH of various jars. -(Required) Store the Autonomous Database wallet on the WLS OS. -(Required) Specify the storage location of the wallet by some means. * This time, specify TNS_ADMIN as the JDBC URL.

That's it.

1. Download JDBC Driver 19.3 Full version

As of June 15, 2019, the latest version of the Oracle Database JDBC Driver is 19c (19.3), It will be updated from time to time, so be sure to check for the latest version before using it.

Oracle Database 19c (19.3) JDBC Driver & UCP Downloads https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/jdbc-ucp-19c-5460552.html

There are several types, but download "ojdbc8-full.tar.gz" corresponding to JDK8 of WLS 12.2.1.3.

2. Store and deploy JDBC Driver 19.3 Full version in WLS OS

Store the downloaded ojdbc8-full.tar.gz in the WLS (WebLogic) OS and unpack it. The directory / owner / group / permission that can be accessed by the WLS (WebLogic) startup user.

This time, extract the JDBC Driver to the directory / u01 / app / oracle / tools / home / oracle / work.

su - oracle
/u01/app/oracle/tools/home/oracle
mkdir work
cd work
cp -p 
cp -p /tmp/ojdbc8-full.tar.gz ./
tar xvzf ./ojdbc8-full.tar.gz

Various jar files will be expanded under / u01 / app / oracle / tools / home / oracle / work / ojdbc8-full.

$ pwd
/u01/app/oracle/tools/home/oracle/work/ojdbc8-full
$ ls -la
total 8644
drwxr-x---. 2 oracle oracle    4096 Apr 29 22:16 .
drwxr-x---. 3 oracle oracle    4096 Jun 15 02:39 ..
-r--r-----. 1 oracle oracle 4210517 Apr 24 21:07 ojdbc8.jar
-r-xr-x---. 1 oracle oracle   11596 Apr 24 21:07 ojdbc.policy
-r--r-----. 1 oracle oracle  144681 Apr 24 21:07 ons.jar
-r--r-----. 1 oracle oracle  306004 Apr 24 21:07 oraclepki.jar
-r--r-----. 1 oracle oracle 1661488 Apr 24 21:07 orai18n.jar
-r--r-----. 1 oracle oracle  205154 Apr 24 21:07 osdt_cert.jar
-r--r-----. 1 oracle oracle  306854 Apr 24 21:07 osdt_core.jar
-rw-r-----. 1 oracle oracle    2592 Apr 29 22:16 README.txt
-r-xr-x---. 1 oracle oracle   29205 Apr 24 21:07 simplefan.jar
-r--r-----. 1 oracle oracle 1680074 Apr 24 21:07 ucp.jar
-r--r-----. 1 oracle oracle  262664 Apr 24 21:07 xdb.jar

3. Download and unzip the Autonomous Database wallet file

For the Wallet file of Autonomous Database, refer to the following. 彡 (ﾟ) (ﾟ)

Download Client Credentials (Wallet) https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/atpug/connect-download-wallet.html#GUID-B06202D2-0597-41AA-9481-3B174F75D4B1

Try connecting to a second Autonomous Database (ADW or ATP) from Python (cx_Oracle) https://qiita.com/mikika/items/5d157c2fcdd80d560bd4

I tried connecting Tableau to Oracle Autonomous https://qiita.com/daisuke_high_185/items/fca12ae6fbc4c526f821

Store the downloaded wallet in the WLS OS and unpack it. After all, it is a directory / various privileges that can be accessed by the WLS startup user.

This time expand it to / u01 / app / oracle / tools / home / oracle / work / wallet.

su - oracle
cd /u01/app/oracle/tools/home/oracle/work
mkdir wallet
cd wallet
cp -p /tmp/Wallet_DB201906031608.zip ./
unzip Wallet_DB201906031608.zip

Various files are expanded under / u01 / app / oracle / tools / home / oracle / work / wallet.

$ pwd
/u01/app/oracle/tools/home/oracle/work/wallet
$ ls -la
total 68
drwxr-x---. 2 oracle oracle  4096 Jun 15 02:54 .
drwxr-x---. 4 oracle oracle  4096 Jun 15 02:53 ..
-rw-r-----. 1 oracle oracle  6661 Jun 15 02:53 cwallet.sso
-rw-r-----. 1 oracle oracle  6616 Jun 15 02:53 ewallet.p12
-rw-r-----. 1 oracle oracle  3242 Jun 15 02:53 keystore.jks
-rw-r-----. 1 oracle oracle    87 Jun 15 02:53 ojdbc.properties
-rw-r-----. 1 oracle oracle   114 Jun 15 02:53 sqlnet.ora
-rw-r-----. 1 oracle oracle  5638 Jun 15 02:53 tnsnames.ora
-rw-r-----. 1 oracle oracle  3336 Jun 15 02:53 truststore.jks
-rw-rw-r--. 1 oracle oracle 19912 Jun 15 02:53 Wallet_DB201906031608.zip
$

4. Edit the WLS (WebLogic) startup shell (specify the CLASSPATH)

Edit the WLS (WebLogic) startup shell and put the various JDBC Driver jars in your CLASSPATH. Edit setDomainEnv.sh under / / domains / / bin.

In the following part of setDomainEnv.sh …… 彡 (ﾟ) (ﾟ)

：
# ADD EXTENSIONS TO CLASSPATHS

if [ "${EXT_PRE_CLASSPATH}" != "" ] ; then
        if [ "${PRE_CLASSPATH}" != "" ] ; then
                PRE_CLASSPATH="${EXT_PRE_CLASSPATH}${CLASSPATHSEP}${PRE_CLASSPATH}"
                export PRE_CLASSPATH
        else
：

Add the following (EXT_PRE_CLASSPATH) to specify various JDBC Driver jars.

：
# ADD EXTENSIONS TO CLASSPATHS

EXT_PRE_CLASSPATH="/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/ojdbc8.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/ucp.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/oraclepki.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/osdt_core.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/osdt_cert.jar:";
export EXT_PRE_CLASSPATH

if [ "${EXT_PRE_CLASSPATH}" != "" ] ; then
        if [ "${PRE_CLASSPATH}" != "" ] ; then
                PRE_CLASSPATH="${EXT_PRE_CLASSPATH}${CLASSPATHSEP}${PRE_CLASSPATH}"
                export PRE_CLASSPATH
        else
：

Stop / start WLS and check the CLASSPATH of the java process with the jinfo command. It's going well 彡 (^) (^)

jinfo 5748 | grep -i java.class.path
java.class.path = /u01/app/oracle/tools/home/oracle/work/ojdbc8-full/ojdbc8.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/ucp.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/oraclepki.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/osdt_core.jar:/u01/app/oracle/tools/home/oracle/work/ojdbc8-full/osdt_cert.jar:/u01/app/oracle/middleware/oracle_common/modules/features/com.oracle.db.jdbc7-dms.jar:/u01/jdk/lib/tools.jar:/u01/app/oracle/middleware/wlserver/server/lib/weblogic.jar:/u01/app/oracle/middleware/wlserver/../oracle_common/modules/thirdparty/ant-contrib-1.0b3.jar:/u01/app/oracle/middleware/wlserver/modules/features/oracle.wls.common.nodemanager.jar:/u01/app/oracle/middleware/oracle_common/modules/oracle.jps/jps-manifest.jar:/u01/app/oracle/middleware/oracle_common/modules/internal/features/jrf_wlsFmw_oracle.jrf.wls.classpath.jar:/u01/app/oracle/middleware/wlserver/common/derby/lib/derbyclient.jar:/u01/app/oracle/middleware/wlserver/common/derby/lib/derby.jar:/u01/jdk/lib/tools.jar:/u01/app/oracle/middleware/oracle_common/modules/oracle.jps/jps-manifest.jar

5. Create a data source from the WLS (WebLogic) Administration Console

Log in to the WebLogic Administration Console.

Click "Lock and Edit" in the upper left ...

Click the "GridLink for RAC Data Source Configuration" link

"New" ⇒ "GridLink Data Source"

"Name" ⇒ arbitrary character string, "scope" ⇒ global, "JNDI name" ⇒ arbitrary character string, "database driver" ⇒ Oracles Driver (thin) for GridLink Connections;

No checks other than "1 phase commit"

Check "Enter full JDBC URL"

For "Complete JDBC URL", specify jdbc: oracle: thin: @ <tnsnames.ora connection string>? TNS_ADMIN = . This time, we will connect to ATP's TP service.

jdbc:oracle:thin:@db201906031608_tp?TNS_ADMIN=/u01/app/oracle/tools/home/oracle/work/wallet

"Database user name" is Autonomous Database user name, "Password" is Autonomous Database user password, "Protocol" is TCP, no oracle.jdbc.DRCPConnectionClass

When "Test all listeners" is executed on the next screen, the connection test is successful. I did it. 彡 (^) (^)

There are no FAN / ONS related settings this time.

Select the WLS cluster to deploy the data source and exit

Press "Activate Changes" and you're done.

6. Summary

The basics are the same as in the previous article! 彡 (゜) (゜)

Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure) https://qiita.com/ora_gonsuke777/items/91ec0e15848a78ede385

Utilizing WLS (WebLogic) and Autonomous Database more and more, 彡 (^) (^)

7. Reference document (* Added 2019/06/17)

Using the JDBC URL connection string with the JDBC Thin driver https://docs.oracle.com/cd/E83857_01/paas/atp-cloud/atpug/connect-jdbc-thin-wallet.html#GUID-F1D7452F-5E67-4418-B16B-B6A7B92F26A4 　　 Oracle Autonomous Database with Java Connectivity with JDBC 18.3 (Oracle ATP or Oracle ADW) https://www.oracle.com/technetwork/jp/database/application-development/jdbc/documentation/atp-5073445-ja.html 　　 How to upgrade the UCP and JDBC drivers included with WebLogic Server 10.3.6-12c (Document ID 2005250.1) * Login required https://support.oracle.com/epmos/faces/DocumentDisplay?id=2005250.1
　　 Starting With Oracle JDBC Drivers --Installation, Certification, and More! (Document ID 401934.1) * Login required https://support.oracle.com/epmos/faces/DocumentDisplay?id=401934.1