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

We are connecting to Oracle from our code with a simple (custom) JDBC connector class. This class reads the connection pro开发者_如何转开发perties 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?

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.

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://<OID server name>:<OID port>/<DB SID or Service Name>,cn=OracleContext,dc=<yourdomain>

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

also you can use scan ip in oracle 11g r2 instead of your nodes ip:

  testi=(DESCRIPTION = 
(ADDRESS_LIST= 
 (ADDRESS= (PROTOCOL = TCP)(HOST = scan-ip-or-name)(PORT = 1521))
 (FAILOVER = on)
 (LOAD_BALANCE = on)
)   
(CONNECT_DATA=
 (SERVICE_NAME = testi)
) )

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_01/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.

I'm not sure if you solve your problem, but I faced the same problem and this solution works for me:

jdbc:oracle:oci:@(DESCRIPTION=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=**mi.host**)(PORT=**1521**))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=**serviceName**)))

this is what i used:

jdbc:oracle:thin:@ldap://xxx:389/yyy,cn=OracleContext,dc=zzz,dc=com 
ldap://xxx:389/yyy,cn=OracleContext,dc=zzz,dc=com 
ldap://xxx:389/yyy,cn=OracleContext,dc=zzz,dc=com