Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

java

jdbc

We are 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?

like image 886
Vini Avatar asked Oct 29 '09 21:10

Vini


People also ask

What is the correct format of JDBC URL?

Typically, in the database URL, you also specify the name of an existing database to which you want to connect. For example, the URL jdbc:mysql://localhost:3306/mysql represents the database URL for the MySQL database named mysql .

How do I find the URL of a JDBC connection?

The DatabaseMetaData#getURL Method In the above example, we first obtain the Connection instance. Then, we call the getMetaData method on our Connection to get the DatabaseMetaData. Finally, we call the getURL method on the DatabaseMetaData instance. As we'd expect, it returns the URL of our database.

What is the JDBC URL for Oracle?

Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe where jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which oracle is running, we may also use IP address, 1521 is the port number and XE is the Oracle service name.

What is RAC connection?

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing clustering. In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk.


2 Answers

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.

like image 112
Pascal Thivent Avatar answered Nov 03 '22 00:11

Pascal Thivent


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

like image 22
SquishFish Avatar answered Nov 03 '22 02:11

SquishFish