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"

Advertisement

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 , , , ,