JDBC 11g: SQLException(“Io exception: Connection reset”)

October 7, 2019 Leave a comment

Problem:

Connection using 11g ojdbc was very slow and most of the time was failing with Connection reset error after 60s (default inbound connection timeout). Database alert log contained WARNING: inbound connection timed out (ORA-3136) errors.

Reason:

Oracle 11g JDBC drivers use random numbers during authentication. Those random numbers are generated by OS using /dev/random and if there is faulty/slow hardware or not too much activity on the system this generation can be slow, which causes slowness during jdbc connection.

Solution:

Instead of /dev/random indicate non-blocking /dev/urandom as java command line argument:

# java -Djava.security.egd=file:/dev/urandom -cp ojdbc8.jar:. JDBCTest "stbyrac-scan.example.com"

Filed under 11g, Java, JDBC Tagged with , , , , , , ,

Simple java code to test connection to Oracle database

September 30, 2019 4 Comments

Assuming that client computer does not have Oracle client installed.

1. Download necessary version of ojdbc jar file from Oracle. The latest version for now is ojdbc8.jar

2. Install java development tools:

# yum install java-devel * -y

3. Create a sample java code, which:
– connects to the database
– selects 1 from dual
– disconnects

# cat JDBCTest.java
import java.sql.*;
 class JDBCTest{
     public static void main(String args[]) throws SQLException {
         Connection con = null;
         try{
             Class.forName("oracle.jdbc.driver.OracleDriver");
             String dbURL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + args[0] + ")(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=orclgg)))";
             System.out.println("jdbcurl=" + dbURL);
             String strUserID = "system";
             String strPassword = "Oracle123";
             con=DriverManager.getConnection(dbURL,strUserID,strPassword);
             System.out.println("Connected to the database.");
             Statement stmt=con.createStatement();
             System.out.println("Executing query");
             ResultSet rs=stmt.executeQuery("SELECT 1 FROM DUAL");
             while(rs.next())
                 System.out.println(rs.getInt("1"));
             con.close();
         }catch(Exception e){ System.out.println(e);}
         finally {
             con.close();
         }
 }
 }

4. Compile java code and check that *.class file was generated:

# javac JDBCTest.java

# ll  JDBCTest.*
 -rw-r--r-- 1 root root 1836 Sep 27 11:45 JDBCTest.class
 -rw-r--r-- 1 root root  925 Sep 27 11:45 JDBCTest.java

5. Run code:

# java -Djava.security.egd=file:/dev/../dev/urandom -cp ojdbc8.jar:. JDBCTest "stbyrac-scan.example.com"

jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyrac-scan.example.com)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=orclgg)))

Connected to the database.
Executing query…
1

Please note that -Djava.security.egd=file:/dev/../dev/urandom parameter is required to have a stable connection. I will discuss the importance of this parameter in the next post.

Filed under DB, JDBC Tagged with , , , ,

JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516

February 24, 2019 Leave a comment

Problem:

JDBC 11g thin clients are not able to connect to the database over SCAN and NAT. JDBC OCI and SQLPLUS connections work fine. JDBC connections to local listener work fine, only connections to SCAN listener fail.

For visibility, I will use sql developer with 11g JDBC thin driver. Actually, the problem happens on all applications using that version of JDBC.

Cause:

SQL*Plus and OCI clients use a redirect count to inform the SCAN listener about a redirected connection, but JDBC 11g and below use a lower version of NS , which does not keep a redirect count and connections are rejected.

Solution:

There are several solutions:

1.  Upgrade to JDBC 12C
2. Apply Patch 17284368 to the JDBC Thin Driver

The first option may not be acceptable for you, because it requires some changes on application side. Let’s describe how to apply patch to the JDBC thin driver:

1. Download and unzip patch file on database node(s):

$ unzip p17284368_112040_Generic.zip

2. Apply patch on db node(s):

$ cd 17284368/ 
$ $ORACLE_HOME/OPatch/opatch apply  

Oracle Interim Patch Installer version 11.2.0.3.20
 Copyright (c) 2019, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
 Central Inventory : /u01/app/oraInventory
    from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
 OPatch version    : 11.2.0.3.20
 OUI version       : 11.2.0.4.0
 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
 Verifying environment and performing prerequisite checks…
 OPatch continues with these patches:   17284368  
 Do you want to proceed? [y|n]
 y
 User Responded with: Y
 All checks passed.
 Problem with accessing response file of "/u01/app/oracle/product/11.2.0/dbhome_1/ccr/bin/setupCCR".
 Backing up files…
 Applying interim patch '17284368' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
 Patching component oracle.dbjava.ic, 11.2.0.4.0…
 Patching component oracle.dbjava.jdbc, 11.2.0.4.0…
 Patch 17284368 successfully applied.
 Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2019-02-22_20-26-57PM_1.log
 OPatch succeeded.

3. Copy patched $ORACLE_HOME/jdbc/lib/ojdbc5.jar to client side.

4. Restart the application:

Instead of patching database side and then copying patched ojdbc to client side, you can manually patch ojdbc thin driver without touching database.

Please consider that, manual method is for testing purposes only and is explained in the next post.

Filed under JDBC, ORA-