Error while trying to retrieve text for error ORA-12154

killbill1 · Post by **killbill1** » Wed Sep 21, 2005 3:31 pm

Hi all,

This error "Error while trying to retrieve text for error ORA-12154" occor's only with one of the projects we have on the server. This happens when we try to run a job with Oracle OCI stage and also appears when we are trying to import the plug in metadata defination from the manager.

This project had been working fine untill yesterday. I've tried connecting from the server to Oracle Db and it works fine. Looks like it's with the Oracle OCI plug ins or something to do with the tnsnames.ora file (tnsnames.ora file looks perfect with all the config info) which the OCI picks up while processing.

Any help is greatly appriciated.

Thanks-

ray.wurlod · Post by **ray.wurlod** » Wed Sep 21, 2005 4:52 pm

Can you use the oerr command successfully? For example

Code: Select all

oerr ORA 12154

trokosz · Post by **trokosz** » Wed Sep 21, 2005 5:42 pm

Well this could be a number of things.....

1. If things are working and suddenly you receive this ORA you may have lost your network connection so get out of Designer and go back in.

2. Or The DB Connection Name you provided in the OCI is not in the TNSNAMES.ora.

3. So with #2 are you using a local vs. server TNSNAMES.ora....may have changed.....

4. Or did someone change ONAMES.ora

rleishman · Post by **rleishman** » Wed Sep 21, 2005 6:37 pm

As Ray said, oerr will tell you more. I have included the oerr details below.
This is a problem with the (Oracle Net) communication between the Oracle client on your DS server and the Oracle database on your database server (this still applies if DS is on the database server). It is almost certainly NOT a problem with DS.
The setup of Oracle Net on the DS server is most likely your DBA's responsibility - not yours; I would be calling him/her in to rectify the problem. This should be a very simple fix for a DBA familiar with your environment.

Code: Select all

12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
//   - If you are using local naming (TNSNAMES.ORA file):
//      - Make sure that "TNSNAMES" is listed as one of the values of the
//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA)
//      - Verify that a TNSNAMES.ORA file exists and is in the proper
//        directory and is accessible.
//      - Check that the net service name used as the connect identifier
//        exists in the TNSNAMES.ORA file.
//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
//        file.  Look for unmatched parentheses or stray characters. Errors
//        in a TNSNAMES.ORA file may make it unusable.
//   - If you are using directory naming:
//      - Verify that "LDAP" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Verify that the LDAP directory server is up and that it is
//        accessible.
//      - Verify that the net service name or database name used as the
//        connect identifier is configured in the directory.
//      - Verify that the default context being used is correct by
//        specifying a fully qualified net service name or a full LDAP DN
//        as the connect identifier
//   - If you are using easy connect naming:
//      - Verify that "EZCONNECT" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Make sure the host, port and service name specified
//        are correct.
//      - Try enclosing the connect identifier in quote marks.
//
//   See the Oracle Net Services Administrators Guide or the Oracle
//   operating system specific guide for more information on naming.

killbill1 · Post by **killbill1** » Wed Sep 21, 2005 7:22 pm

This problem is not with the connection from DS server to Oracle as I've already tried connecting thru sqlplus and it worked fine.
Problem occor's only when I'm trying to connect using OCI stage from the designer or If I'm trying to import the table defination from the manager using plug in metadata import. Another strange thing is that when I try to import the table defination for OCI stage from the manager for this project, the dropdown box which should appear with the list of DSN names from the tnsnames.ora file does not appear at all. So I re-registered the Oracle OCI plug ins for this project assuming its the Oracle plug ins problem but no luck. Also, re installed the Oracle plug ins and tried... No Luck

My guess after all this troubleshooting is that thr's some problem where ever it tells the Oracle OCI plug in to point ot the tnsnames.ora file. If anyone has an idea of how this OCI plug in calles the tnsnames..please point me.

Also, we have several other projects on the same DS server and they all work fine.

rleishman · Post by **rleishman** » Wed Sep 21, 2005 7:47 pm

That sounds reasonable given your findings.
I believe that DS finds the TNSNAMES based on the $ORACLE_HOME environment variable setting. ie. $ORACLE_HOME/network/admin/

$ORACLE_HOME is set in the file $DSHOME/dsenv. On my server $DSHOME is /opt/Ascential/DataStage/DSEngine.

I'm not confident of a solution though. If $ORACLE_HOME were wrong, it would affect all projects.

Has the DBA updated tnsnames.ora by hand lately? Perhaps there is a subtle formating difference that DS cannot parse.

Mike · Post by **Mike** » Thu Sep 22, 2005 5:27 am

There does (did?) seem to be some kind of problem with DataStage parsing the tnsnames.ora file. About a year back I was working at a client site where the drop-down list of DSN names wasn't picking up a new manual entry. Typing the DSN name in worked even though that name wasn't available in the list.

I don't remember what the formatting difference in tnsnames actually was.

Mike

ray.wurlod · Post by **ray.wurlod** » Thu Sep 22, 2005 5:33 am

That you can connect via sqlplus does not necessarily mean that there is no error while trying to retrieve text from Oracle system tables.

killbill1 · Post by **killbill1** » Fri Sep 23, 2005 10:21 am

But what about the connections on the same server from a different project. I think this is already confirming that the database has no issue.