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?
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 .
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With