Troubleshooting
Problem
DataStage job fails with error ORA-12154 when accessing an Oracle database via the Oracle run-time client. The following are typical errors, but multiple variations exist: Error while trying to retrieve text for error ORA-12154 APT_OraReadOperator: connect failed. ORA-12154: TNS:could not resolve the connect identifier. ORA-12154: TNS:could not resolve service name
Resolving The Problem
Error while trying to retrieve text for error ORA-12154
The above error means that not only did the connection to Oracle Database fail, but that Oracle was unable to retrieve the text of error message ORA-12154. This usually indicates that either the DataStage userid running the job does not have read access to the Oracle run-time client files, or that the environment variable ORACLE_HOME is not defined.
ORA-12154: TNS:could not resolve the connect identifier.
ORA-12154: TNS:could not resolve service name
The above 2 errors indicate that the connection identifier or service name specified in the DataStage job (or in the ORACLE_SID environment variable or ODBC definition) was not known to either the Oracle client or the Oracle server. The connection/service identifiers known to the Oracle run-time client are defined in the tnsnames.ora file, $ORACLE_HOME/network/admin/tnsnames.ora
Verify that the identifier specified for the failing Oracle connection has been defined in tnsnames.ora. If it is correctly defined, then next verify that the ORACLE_HOME environment variable is correctly defined, and that tnsnames.ora file has correct read permissions.
If the above items are configured correctly, also check the listener.log on Oracle server to confirm that the service id (or the database it maps to) are known to the Oracle server.
Setting up environment variables required to use Oracle run-time client
The Oracle client requires that the following environment variables be defined. These should be set in the .dsenv file in the DataStage DSEngine directory.
ORACLE_HOME=/home/oracle
LIBPATH=$LIBPATH:$ORACLE_HOME/lib:
PATH=$PATH:$ORACLE_HOME/bin
Change the path defined for ORACLE_HOME to the correct path for your system. ORACLE_HOME should be set to the absolute path to the home Oracle directory which is the directory level directly above the lib and bin directories.
Please also note that the name of the library path environment variable varies with different operating systems:
- AIX - use LIBPATH
- Solaris - use LD_LIBRARY_PATH
- HP-UX - use SHLIB_PATH
- Linux - use LD_LIBRARY_PATH
Was this topic helpful?
Document Information
Modified date:
23 June 2018
UID
swg21408097