Test JDBC SCAN connection from client side

Test JDBC SCAN connection from client side.

Environment:

Oracle Grid Infrastructure 11.2.0.2

Oracle database server 11.2.0.2

For SCAN (Single Client Access Name) to work properly, it is essential to follow certain best practices..  DBA’s try to keep up all required configurations like DNS configuration, local_listener, remote_listener, service configuration, service registration with local and remote listeners, etc.

But still at times we have end users complain about connectivity issue when they use a SCAN connection string. A regular connection string using VIP’s could work fine but SCAN can still fail.

A connection from sqlplus could be successful but a JDBC connection might fail.

This brief note can help troubleshoot connectivity issue when using JDBC and SCAN connection string.

It is easy to test OCI calls. We install Oracle client, add entry in tnsnames.ora and establish a connection using sqlplus. What if OCI calls from sqlplus are successful and we have end users complain the JDBC connections are failing. Here is a simple way of testing JDBC connection using SCAN connection string.

All you need to do is follow the below note from Oracle support.

Using SCAN With Oracle JDBC 11g Thin Driver [ID 1290193.1]

There is a demo there… the note will point you to java code that you can use to test the connectivity. It also has location from where you can download 11.2.0.2 jdbc driver. By following simple instructions, you can compile and execute the code to test JDBC with SCAN connection string..

The reason I’m putting this on blog is to direct someone to the support note and I have no intention to copy the note as blog.  I work as a DBA and no Java expert. I had to look for a while to get a handy code and procedure which I can use to test.

This will be the output for a successful connection.

[java@ web101 ]$ java -cp .:ojdbc6.jar:. LoadBalanceTestSCAN

Test Started at Mon Nov 26 14:18:49 EST 2012

Obtaining 5 connections

using URL : jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = salesscan.office.com)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SALES_OLTP) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))

=============

Database Product Name is ... Oracle

Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

=============

JDBC Driver Name is ........ Oracle JDBC driver

JDBC Driver Version is ..... 11.2.0.3.0

JDBC Driver Major Version is 11

JDBC Driver Minor Version is 2

=============

Connection #0 : instance[SALES1], host[slsrac04], service[SALES_OLTP]

Connection #1 : instance[SALES1], host[slsrac04], service[SALES_OLTP]

Connection #2 : instance[SALES3], host[slsrac10], service[SALES_OLTP]

Connection #3 : instance[SALES1], host[slsrac04], service[SALES_OLTP]

Connection #4 : instance[SALES3], host[slsrac10], service[SALES_OLTP]

Closing Connections

Test Ended at Mon Nov 26 14:18:51 EST 2012

[java@ web101 ]$