How to test JDBC connection to an Oracle Server?

book

Article ID: 106561

calendar_today

Updated On:

Products

CA Release Automation - Release Operations Center (Nolio) CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

The Release Automation Application Server cannot connect to the remote Oracle Database Server. 
The Firewall is open and Telnet indicates that the network connectivity is ok. 
However, the Application Server cannot connect to the Database Server and the following error is seen in nolio_dm_all.log.
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (IO Error: The Network Adapter could not establish the connection) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (IO Error: The Network Adapter could not establish the connection)

Is there a way to test the JDBC connection to the Oracle Database Server?

Environment

All versions of Release Automation
OS - Linux
Database - Oracle

Resolution

The oracle-jdbc-tester is a handy tool that can be used to test JDBC connection to an Oracle Server.

1. Download jdbc-tester-1.0.jar from https://github.com/aimtiaz11/oracle-jdbc-tester
2. Copy it to the folder where Java is installed.
3. Grant execute permissions to jdbc-tester-1.0.jar using 'chmod +x jdbc-tester-1.0.jar'
4. Execute the JAR file using the schema name, password and JDBC connection details:
Syntax: java -jar target/jdbc-tester-1.0.jar <schema_name> <schema_password> jdbc:oracle:thin:@//<host>:<port>/<SID>


Example 1 (Failed JDBC connection)

[[email protected] jdbctester]$ /opt/releaseautomation/jre/bin/java -jar ./jdbc-tester-1.0.jar RELAUTO password123 jdbc:oracle:thin:@//dbserver:1521/iroc
23:53:00.821 [main] INFO Main - arg 0 = RELAUTO
23:53:00.828 [main] INFO Main - arg 1 = password123
23:53:00.828 [main] INFO Main - arg 2 = jdbc:oracle:thin:@//dbserver:1521/iroc
23:53:00.974 [main] INFO Main - ****** Starting JDBC Connection test *******
23:53:09.157 [main] ERROR Main - Exception occurred connecting to database: IO Error: The Network Adapter could not establish the connection


Example 2 (Successful JDBC connection)
[[email protected] jdbctester]$ /opt/releaseautomation/scripts/jdbctester/jdk1.7.0_181/bin/java -jar ./jdbc-tester-1.0.jar RELAUTO password123 jdbc:oracle:thin:@//dbserver:1521/iroc
23:59:48.241 [main] INFO Main - arg 0 = RELAUTO
23:59:48.248 [main] INFO Main - arg 1 = password123
23:59:48.248 [main] INFO Main - arg 2 = jdbc:oracle:thin:@//dbserver:1521/iroc
23:59:48.399 [main] INFO Main - ****** Starting JDBC Connection test *******
23:59:59.293 [main] INFO Main - Running SQL query: [select sysdate from dual]
23:59:59.321 [main] INFO Main - Result of sql Query: [2018-07-08 23:59:59.0]
23:59:59.321 [main] INFO Main - JDBC connection test successful!


DISCLAIMER: This KB article is for information purpose only. oracle-jdbc-tester is a 3rd party tool with no link to CA Technologies. Use the tool at your own discretion.