Overview

When new Oracle data sources are created with the standard provider drivers (eg: Oracle 12c_1_6 and Oracle 11_2), the TNS entry from tnsnames.ora file is not picked up, though the path is set via the JVM argument -Doracle.net.tns_admin. The WebSphere console connection test fails with the below error:

The test connection operation failed for data source TestDatasourceForTNSORA on server Server1 at node SBMNode with the following exception: java.sql.SQLException: Unknown host specified DSRA0010E: SQL State = 62000, Error Code = 118.

Solution

You will need to add the -Doracle.net.tns_admin in two locations: on the server (Server1 in our example) and on the EJB server. And you will need to restart both of them (together with the Portal Server) to have the changes picked up, before creating the data source using the TNS name from the tnsnames.ora file.

Follow the below steps:

1. Add the system property to below location:
   • Goto IBM Websphere Console -> Server Types -> click on Websphere Application Servers -> click on Server 1 -> Expand Java and Process Management under Server Infrastructure
   • Then, click on Process definition -> click on Java Virtual Machine under Additional Properties -> set the oracle.net.tns_admin JVM property to the location of the tnsnames.ora file under Generic JVM arguments as shown in the example below:
     -Doracle.net.tns_admin=C:\\Oracle\\Administrator\\product\\11.2.0\\dbhome_1\\NETWORK\\ADMIN
     
     
     

     Note: on Linux use / in the path.

2. Repeat Step 1 to set the JVM argument in the scope of the EJB server.
3. Restart the Server1, EJBServer, and the Portal server.
   This is important as the system property is getting picked up only after restart.
4. Go to the Websphere console and create a JDBC provider or use an existing one.
5. Create a Datasource. Use the provider created in Step 4. The URL should contain the name of TNS entry in tnsnames.ora as described in the Oracle Guide about Database URLs and Database Specifiers
   Ex: jdbc:oracle:thin:@tns_entry //where tns_entry is an entry in tnsnames.ora
6. Testing the connection with the WebSphere administration console should now be successful. 

Back to the top