JDBC: What is the correct JDBC URL to connect to a RAC database

Question

Hi,

We were connecting to Oracle from our code with a simple (custom) JDBC connector class. This class reads the connection properties from a resource file and tries to make a connection to Oracle (thin connection).

However, recently the database have moved to a RAC and now the application is unable to connect to the DB.

Here is the TNSPING output:

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=tst-db1.myco.com)(PORT=1604))
(ADDRESS=(PROTOCOL=TCP)(HOST=tst-db2.myco.com)(PORT=1604)))(CONNECT_DATA=    
SERVICE_NAME=mydb1.myco.com)(SERVER=DEDICATED)))
OK (80 msec)

What would be the correct URL to specify in this case.

Regards,

• Ashish

Answer 1

The URL should look like the following:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=tst-db1.myco.com)(PORT=1604))
(ADDRESS=(PROTOCOL=TCP)(HOST=tst-db2.myco.com)(PORT=1604)))
(CONNECT_DATA=SERVICE_NAME=mydb1.myco.com)(SERVER=DEDICATED)))

Actually, just copy the tnsentries from your tnsnames.ora.

Answer 2

You may want to look at the OCI drivers. I believe they are supposed to be better performing and handle RAC better.

from http://download.oracle.com/docs/cd/B10500%5F01/appdev.920/a96590/adg01dev.htm#429762

OCI provides significant advantages over other methods of accessing an Oracle database:

* More fine-grained control over all aspects of the application design.
* High degree of control over program execution.
* Use of familiar 3GL programming techniques and application development tools such as browsers and debuggers.
* Support of dynamic SQL,method 4.
* Availability on the broadest range of platforms of all the Oracle programmatic interfaces.
* Dynamic bind and define using callbacks.
* Describe functionality to expose layers of server metadata.
* Asynchronous event notification for registered client applications.
* Enhanced array data manipulation language (DML) capability for array INSERTs, UPDATEs, and DELETEs.
* Ability to associate a commit request with an execute to reduce round-trips.
* Optimization for queries using transparent prefetch buffers to reduce round-trips.
* Thread safety so you do not have to use mutual exclusive locks (mutex) on OCI handles.
* The server connection in nonblocking mode means that control returns to the OCI code when a call is still executing or could not complete.

Answer 3

The point of a tnsnames file, the older Oracle Names server, and the newer, recommended LDAP directory server method of resolving database names is to avoid having to hardcode hostnames, addresses, ports, etc. into your connection string. The DBAs should be able to move the database to a new host or port without breaking anything.

The best way to set your thin connect URL is with the following syntax:

jdbc:oracle:thin:@ldap://:/,cn=OracleContext,dc=

So in your case, if "oid" were the DNS-resolvable name of the OID server at your company, and it used port 5000, it would be:

jdbc:oracle:thin:@ldap://oid:5000/mydb1,cn=OracleContext,dc=myco,dc=com

If your DBAs have not yet set up OID, they are woefully behind. Time to get new DBAs.

-squish