Vishal desai’s Oracle Blog

July 3, 2013

What’s wrong with the Database?

Filed under: Troubleshooting — vishaldesai @ 8:29 pm

My Development team started complaining that application startup hangs when it tries to connect to database. To verify general connectivity, I asked them to verify hostname, port and service name. I converted JDBC URL to TNS entry and verified database connectivity from SQLPLUS. While I was working on this development issue for about 15 minutes, I was asked following questions via different modes of communication:

What’s wrong with the database? What changed on database since last week? Were there any patches applied to database server? What are top wait events on database? How is the performance of oracle database? Is listener up and running? Can DBA’s restart database?

We have thousands of database running at my client site and I was overwhelmed by number and kind of questions that I was asked for this issue. My development team was not very happy as I ignored all questions except for listener question because I was able to connect using SQLPLUS from my client PC.

Verified following items:

– Can connect to database from my client laptop to database server using database host, port and service name via SQLPLUS.

– telnet dbhostname port from application server was working fine

– From database audit log, active session history and listener logs, I did not notice any session requested/established from application server.

– Application server startup just hangs. It does not throw any ORA- errors.

That tells me that issue is outside database server. I was told that SQLPLUS test is not valid as application is using JDBC thin driver. I think that was a valid concern but I ran SQLPLUS test to prove I can connect to database from other client.

I started searching Google to find sample java programs to test database connectivity using JDBC. While I was searching for sample program, development team reported that System Administrator found some issue with application server OS and they have to reboot application server.

I found following link which could be very handy to test database connectivity using oracle JDBC driver.

http://www.mkyong.com/jdbc/connect-to-oracle-db-via-jdbc-driver-java/comment-page-1/#comment-135707

Sample program: OracleJDBC.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class OracleJDBC {
    public static void main(String[] argv) {
        System.out.println("-------- Oracle JDBC Connection Testing ------");
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return;
        }
        System.out.println("Oracle JDBC Driver Registered!");
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:oracle:thin:@xxx:port:servicename", "dbsnmp","monitor");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@//xxx:port/SID", "dbsnmp","monitor");
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;
        }
        if (connection != null) {
            System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }
    }
}

Make sure JDK is installed on your client PC. Set CLASSPATH and PATH variables.

c:\oracle\product>javac OracleJDBC.java

c:\oracle\product>java -cp .;C:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar OracleJDBC
-------- Oracle JDBC Connection Testing ------
Oracle JDBC Driver Registered!
Database connectivity is fine, take control of your Application now!

In firefighting situation, DBA’s can quickly run sample program to test database connectivity from client PC or application server to prove nothing is wrong with the Database. 🙂

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: