Troubleshooting
Problem
When using a type 2 driver to access an Oracle database, in your resources.xml file for the data source, you define a URL to access Oracle which includes an alias to the database. The database alias is looked up in a file called TNSNAMES.ORA that resides in the ORACLE_HOME directory on each ORACLE client or database. If you do not point WebSphere to the correct ORACLE_HOME, it will give you a message that is represented in the title of this technote. This is not a WebSphere product defect but a configuration error.
Cause
This error can be caused any time you are not pointing WebSphere to the TNSNAMES.ORA file or to the correct TNSNAMES.ORA file where the alias can be looked up and a connect string made for it.
Some things that can cause this error:
- No Oracle Client on the same machine as the application server. In other words, no TNSNAMES.ORA file exists for WebSphere to look at
- TNSNAMES.ORA does not have the alias for the database that is defined in the URL. In other words, the alias has not been defined for the database.
- More than one Oracle Client is on the machine and WebSphere is looking at the wrong one.
- ORACLE_HOME is not defined in WebSphere variables.
Resolving The Problem
- In your administrative console, click on environment.
- When the menu expands, locate Managed WebSphere Variables.
- Once you have done that, click on that entry. You will see WebSphere Variables page appear.
- Look in the present WebSphere Variables and see if you see one named ORACLE_HOME.
- If you do not, please create a new one that says ORACLE_HOME.
- The name should be ORACLE_HOME.
- The value should be the path to the ORACLE_HOME that contains the TNSNAMES.ORA file that has the entry in it for the database you desire to connect to.
Further information:
Oracle has a file that is known as TNSNAMES.ORA that resides in the <ORACLE_HOME>/network/admin directory. This file takes the alias that is used in the URL for connecting to the Oracle database for type 2 JDBC™ and translates it into a viable set of connection information for connecting to the database.
If this TNSNAMES.ORA file cannot be found by WebSphere or if the alias is not identified in the TNSNAMES.ORA file that is found by WebSphere, the message shown in the title will be given.
In the resources.xml file for WebSphere, the alias looks like the following: value="jdbc:oracle:oci:@WAS5TEST"
In the TNSNAMES.ORA file, the entry looks like this:
WAS5TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 2373-PU6xppro)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = was5test)
)
)
Note that the port number is not identified in the URL and even though there is a port entry in the actual resources.xml:
name="portNumber" type="java.lang.Integer" value="1521", The connection to the Oracle database information is obtained from the TNSNAMES.ORA file so the port number in the resources.xml file does not matter.
This may be another place for a problem to occur in the setup if you change the port in the resources.xml and do not change it in the TNSNAMES.ORA file.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21219987