4.04.2020

Connecting to an Autonomous Database using JDBC Thin client

I have discussed in my earlier posts about establishing SQL * Plus and using the SQLcl tool details. Today's blog post will take you through JDBC Thin client configuration details to be able to successfully connect with an autonomous database service. Will primarily focus on mandatory JDK, JDBC versions and client connect credentials requirement details. Let's get started.

To begin with, any Java application that uses JDBC Thin driver requires an Oracle wallet or JavaKeyStore(JKS). Luckily, these files are readily available on OCI console for download. Refer my earlier blog post to learn about downloading client connect credentials files and JavakeyStore files.

Prerequisites for JDBC Thin Driver

Below is the list of perquisites for JDBC Thin client connection:

  • Download Connect Credentials as explained in the previous post
  • Validate JDK version for security, if you are on less than JDK8u162
  • Check JDBC driver version
  • JDBC driver 18.3 or higher simplifies security credentials wallet connection configuration.
  • Specify the wallet directory in the JDBC connect string.
  • Include the wallet directory location in the JDBC url if no TNS_ADMIN environmental variable is set.
Connection string url

Part of client connect credentials downloads, you will have tnsnames.ora files which contains tns service names and connection strings. Below is an example of high level connect string entry:

dbname_high= (description=
      (address=(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com))(connect_data=(service_name=adw_jdbctest_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adw.oraclecloud.com,OU=Oracle
      US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
JDBC 18.3 Thin Driver connection string example, for Linux platform:

DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"

Using JavaKeyStore (JKS)

Below example shows the JDBC Thin driver connectivity using JKS to an ADB:

DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"
Subsequently, add the JKS related connection properties to ojdbc.properties file. To use SSL connectivity over Oracle Wallet, below properties should be set to ojdbc.properties files:


# Properties for using Java KeyStore (JKS)
oracle.net.ssl_server_dn_match=true
javax.net.ssl.trustStore==${TNS_ADMIN}/truststore.jks
javax.net.ssl.trustStorePassword=password
javax.net.ssl.keyStore==${TNS_ADMIN}/keystore.jks
javax.net.ssl.keyStorePassword=password

Once the properties are set, compile and run using the below example:

java –classpath ./lib/ojdbc8.jar:./lib/ucp.jar UCPSample
To go through your firewall with HTTP proxy requirement to connect to an internet, use JDBC Thin client 18.1 or higher. So, add the following to your tnsnames.ora file to go through HTTP proxy:

ADWC1_high =
       (description=
             (address=
                   (https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com)
             )
             (connect_data=(service_name=adwc1_high.adw.oraclecloud.com)
             )
             (security=(ssl_server_cert_dn="adw.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
             )
       )

In nutshell, we have walk through over JDBC thin driver client connection to an ADB requirements and prerequisites.

4.03.2020

Autonomous Database (ADB) connectivity with Oracle SQLcl

This blog post takes you through Oracle SQL Developer command line interface connectivity to an autonomous database (ADB). SQLcl is a free command line interface for Oracle database. It will allow interactive or batch execution of SQL and PL/SQL commands with in-line editing, statement completion, and command recall for a feature-rich experience capabilities. Download the tool from the below url:



With SQLcl 4.2 or higher you can also connect  to ADB database, either using Oracle Call Interface or JDBC thin connection. Whatever connection mode, OCI or JDBC, it has to be setup before you can use the SQLcl.

Use the below example to connect to a ADB using SQLcl oci connection:

sql -oci

SQLcl: Release 18.4 Production on Wed Apr 03 15:28:40 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Username? (''?) sales_dwh@adwc1_low
Password? (**********?) **************
Last Successful login time: Wed Apr 03 2019 15:29:19 -07:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>
Use the below example to use SQLcl with JDBC thin connection:

  • Start with sql /nolog 
  • Configure the session to use existing wallet connection: 
  • SQL> set cloudconfig /home/adwc/wallet_ADWC1.zip
    Wallet Password:  **********
  • Connect to ADB database , as shown below:
  • SQL> connect admin@adwc1_medium

In my next post, will discuss JDBC connectivity details.

SQL * Plus connectivity to Autonomous Database (ADB) procedure

Keeping myself pretty occupied learning Autonomous Database (ADB) concepts. Credit must go to Oracle for offering free Oracle cloud technology university courses and a chance to certify in the same domain for no cost. So planned to write a few important topics along the way while learning about ADB services. This particular post will focus on autonomous database (ADB) service connectivity options, downloading credentials and procedure to connect to an ADB database service using the SQL * Plus tool.

An Autonomous Database (ADB) service can be connected through SQL * Net, JDBC thick/thin or ODBC processes. ADB connection uses certificate authentication and SSL, whereas, certificate authentication uses encrypted key stored in a wallet files on both client and server. All connections to the database must use SSL for encryption and under any circumstances no unsecured connections are not allowed. 

To start with, every ADB database connection must be made over a public internet and application should use a secure connection (SSL).  Before establishing any sort of client connectivity to the ADB service, service client connect credentials must be downloaded from the OCI page for the respective database (instance) or region. Below procedure summarizes how to download wallet credentials and use SQL * Plus to connect to a ADB service.


Downloading connection credentials

As discussed earlier, ADB only accepts secure connections to an Autonomous Database. Therefore, Oracle client credential (wallet) files which contains your connection credentials should be downloaded first. The wallet can be downloaded either from ADB service console, or from the DB Connection button on instance page or through API calls. Use the following method to download the connection credentials from OCI console:

  • Navigate to ADB details page  and click on DB Connection button
  • Based on the connectivity requirement, choose either Instance or Regional wallet type and click on the Download button 
  • The credentials file is protected with a password. So, you will have to input a password before downloading the file. The password must be 8 characters long, with at least 1 letter and either 1 numeric character or 1 special character
  • Once the password is entered and confirmed, download button will be activated to download the connectivity zip file
  • Default name for the file is Wallet_databaename.zip, and the zip files contains following files:
    • tnsnames.ora & sqlnet.ora
    • cwallet.sso & ewallet.p12
    • keystore.jks & tuststore.jks
    • ojdbc.properties & 
Predefined DB Service Names

The tnsnames.ora files contains the following predefined 5 database services names:
  • high A high priority app connection for reporting and batch operations which runs in parallel, subject to queuing.
  • medium A typical connection service for reporting and batch operations with limited (4) parallel degree.
  • low A low priority connection service for reporting and batch operations with no parallelism options.
  • tp (for ATP only), typical application connection service for transaction processing operations with no parallelism.
  • tpurgent (ATP only) the highest priority application connection for time critical transaction processing operations. Can specify manual parallelism. 
Sessions that are idle for over 5 minutes will be automatically disconnected from the database to make room for more active sessions.

SQL * Plus connection to ADB

Unlike other Oracle cloud options, ADB doesn't provide connectivity to the compute node. So, you have to install Oracle client software 11.2.0.4 or higher version on your system. Once the client software is installed, download connection credentials using the procedure described above.

Unzip/uncompress the zip file downloaded into a secure folder on your computer.

Edit the sqlnet.ora file and replace ?/network/admin text with name of the folder containing the credentials, as shown in the below example on a unix/linux client:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/adw_credentials")))
SSL_SERVER_DN_MATCH=yes

 It is highly recommended to set TNS_ADMIN environmental variable and set the location of the credentials files.

Update the sqlnet.ora and tnsnames.ora files as per below example if you have a firewall connection and requires HTTP proxy to connect internet (this scenario only support clients with 12.2.0.1 or higher version):

  • sqlnet.ora should have this entry: SQLNET.USE_HTTPS_PROXY=on
  • And the tnsnames.ora entry should contains the following:
ADWC1_high =
       (description=
             (address=
                   (https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adwc.example.oraclecloud.com)
             )
             (connect_data=(service_name=adwc1_high.adwc.oraclecloud.com)
             )
             (security=(ssl_server_cert_dn="adwc.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
             )
       )
Once you have the client software and connect credentials ready, now you can use the sql * plus tool to connect to a ADB. Refer below syntax:

sqlplus sales_dwh@adwc1_low  

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 3 15:09:53 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 03 2019 14:50:39 -07:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>
 You can also see the last successful login time.


References;
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A

3.31.2020

Free Oracle Learning and Certifications for OCI and Autonomous database - Limited period

Hello All,

Oracle announced a limited period free access to Oracle Learning and Certifications on OCI and Autonomous Database technologies. 
Its absolutely free of cost for everyone. Indeed it is a very good chance everyone to learn something new to update Oracle knowledge and get certified. Use the below Oracle link for enrolling to courses and certification:

https://www.oracle.com/corporate/blog/free-certifications-oracle-oci-autonomous-033020.html

All the very best and happy learning.


3.15.2020

RAC DB upgrade from 11.2.0.4 to 12.1.0.2 ASM prerequisites fail with DUBA


A 3 node Oracle extended RAC has been successfully upgrade from 11.2.0.1 to 12.1.0.2 just last week. As part of the upgrade planning, we started off with GRID home upgrade process from 11.2.0.4 to 12.1.0.2, followed by DB upgrade from 11.2.0.1 to 11.2.0.4 and finally DB upgrade to 12.1.0.2. This was done over the 3 weeks. As there was no direct in-place database upgrade option for a database v11.2.0.1 to 12.1.0.2, we had to take the alternative route.

In previous two blog posts, I have shared the details about the challenges faced and resolved. So, here is another challenge encountered during the database upgrade. Encountered the below pre-requisites failure during the pre-requisites validation by DBUA:



Below is the details of the error:

  • ASM Upgrade Feasible Check: The database is using ASM as storage. Before you upgrade database, you need to upgrade ASM using Automatic Storage Management Configuration Assistant (ASMCA) from grid infrastructure home. ASM should be running from grid infrastructure home. Upgrade Storage Check :  Disk space usage summary Location +DATA_DG does not exist.

It was wired to see the message despite the fact that the GRID was successfully upgraded, including ASM. After a quick search we have come across of two MOS references , mentioned below, and the solution was just straight forward.

To expedite the post DB upgrade requirements, we did prepare complete environment for EB database.

The solution was to rename the sqlnet.ora file from the 12c network/admin directory. One we did this and resumed DBUA, it went through.


MOS References:

Upgrade database to 12.1.0.2.0, DBUA fails: COULD NOT FIND ANY DISKGROUPS [2039864.1]
Database Upgrade From 11.2.0.4 To 12.1.0.2 Pre-checks Failing while using DBUA [2317976.1]
https://oakinoracle.wordpress.com/2017/05/31/experienced-12c-grid-db-upgrade/