asked on

ORA-12154 TNS:could not resolve the connect identifier specified with Oracle 11G client on Linux

I'm using Red Hat Enterprise Linux 5 x64. I just downloaded and installed the Oracle 11G client for Linux x64 from the Oracle website

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

The installation steps were just to unzip the Basic client and the SQLPlus client and then set the environment variables. I unzipped all the files in:

/u01/app/oracle/instantclient_11_2

Then I set the variables

export ORACLE_HOME=/u01/app/oracle/instantclient_11_2
export LD_LIBRARY_PATH=/u01/app/oracle/instantclient_11_2:$LD_LIBRARY_PATH
export PATH=/u01/app/oracle/instantclient_11_2:$PATH
export TNS_ADMIN==/u01/app/oracle/instantclient_11_2

As soon as I launch SQLPLUS and try to connec to the database, I get the error message.

I've been Googling and researching but I can't seem to find the answer. A lot of people keep talking about /network/admin folder but there's no folder like that in my install. I did create a TNSNAMES.ORA fille in the $ORACLE_HOME and it looks like this:

IIH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.0.184)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = IIH)
)
)

Here's a directory listing of what's inside $ORACLE_HOME (/u01/app/oracle/instantclient_11_2)

[oracle@IIH instantclient_11_2]$ ls -l
total 181768
-rwxrwxr-x 1 oracle oinstall 25532 Sep 4 2010 adrci
-rw-rw-r-- 1 oracle oinstall 439 Sep 4 2010 BASIC_README
-rwxrwxr-x 1 oracle oinstall 46352 Sep 4 2010 genezi
-r--r--r-- 1 oracle oinstall 342 Sep 4 2010 glogin.sql
-rwxrwxr-x 1 oracle oinstall 52439406 Sep 4 2010 libclntsh.so.11.1
-r-xr-xr-x 1 oracle oinstall 7898628 Sep 4 2010 libnnz11.so
-rwxrwxr-x 1 oracle oinstall 1972824 Sep 4 2010 libocci.so.11.1
-rwxrwxr-x 1 oracle oinstall 115820762 Sep 4 2010 libociei.so
-r-xr-xr-x 1 oracle oinstall 164756 Sep 4 2010 libocijdbc11.so
-r-xr-xr-x 1 oracle oinstall 1503279 Sep 4 2010 libsqlplusic.so
-r-xr-xr-x 1 oracle oinstall 1471902 Sep 4 2010 libsqlplus.so
-r--r--r-- 1 oracle oinstall 2030135 Sep 4 2010 ojdbc5.jar
-r--r--r-- 1 oracle oinstall 2152051 Sep 4 2010 ojdbc6.jar
-r-xr-xr-x 1 oracle oinstall 9336 Sep 4 2010 sqlplus
-rw-rw-r-- 1 oracle oinstall 443 Sep 4 2010 SQLPLUS_README
-rwxrwxr-x 1 oracle oinstall 192341 Sep 4 2010 uidrvci
-rw-rw-r-- 1 oracle oinstall 66783 Sep 4 2010 xstreams.jar

I found a good article here but this doesn't fix my problem:

http://2muchtea.wordpress.com/category/oracle/

I'm kind of new to Oracle and not very familiar. I need to have this figured out by Friday morning as there's an application vendor waiting on me to get this client working so they can work on an upgrade.

m_walker

Its been a while. Maybe your TNSNAMES.ORA is in the wrong folder or wrong case (ie: maybe lower case)

on the linux box, update the locate db
updatedb

When the is done
locate tnsnames.ora
locate TNSNAME.ORA

see if any come back.

you should also be able to run
tnsping IIH
as a test.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>Here's a directory listing of what's inside $ORACLE_HOME (/u01/app/oracle/instantclient_11_2)

I don't see the tnsnames.ora file. I'm kind of with m_walker, in UNIX I've never seen the filename upper case. Try making it lower case.

The $ORACLE_HOME/network/admin folder is the 'default' location for the file. With the Instant Client you need to manually create this folder.

That said, the TNS_ADMIN environment variable you are setting tells sqlplus where to look for the file and over-rides the default location. You just need that file in that location.

Florescu

ASKER

Sorry, I pasted the directory listing prior to creating the TNSNAMES.ORA file. The file is in lower case.

I get command not found when I type TNSPING.

I was able to connect successfully using the format

sqlplus user/pwd@172.31.0.184/IIH

Qlemo

Commands and file names are lower case with Oracle, so you need to use tnsping for testing.
Since the Eazy Connect string works, it is an issue with the tnsnames.ora - most probably, it is not found. Do you want to continue on researching why, or are you satisfied with the user/pwd@172.31.0.184/IIH ?

slightwv (䄆 Netminder)

>>I get command not found when I type TNSPING.

I don't believe tnsping comes with the instant client.

>>I was able to connect successfully using the format

If you still wish to connect with the tnsnames.ora file please provide an updated directory listing and env variables from your current session.

Florescu

ASKER

[oracle@IIH ~]$ echo $ORACLE_HOME
/u01/app/oracle/instantclient_11_2

[oracle@IIH ~]$ echo $PATH
/u01/app/oracle/instantclient_11_2:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle

[oracle@IIH ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/instantclient_11_2:

[oracle@IIH ~]$ echo $TNS_ADMIN
/u01/app/oracle/instantclient_11_2:

[oracle@IIH instantclient_11_2]$ ls
adrci libclntsh.so.11.1 libocijdbc11.so ojdbc6.jar uidrvci
BASIC_README libnnz11.so libsqlplusic.so sqlplus xstreams.jar
genezi libocci.so.11.1 libsqlplus.so SQLPLUS_README
glogin.sql libociei.so ojdbc5.jar tnsnames.ora

slightwv (䄆 Netminder)

>>[oracle@IIH instantclient_11_2]$ ls

I don't see sqlplus...

anyway, please post the output of:
sqlplus scott/tiger@IIH

Florescu

ASKER

SQLPLUS is there... right next to libsqlplusic.so

[oracle@IIH ~]$ sqlplus scott/tiger@IIH

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 24 12:05:48 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

slightwv (䄆 Netminder)

>>SQLPLUS is there... right next to libsqlplusic.so

oops... see it now.

>>[oracle@IIH ~]$ sqlplus scott/tiger@IIH

Looks like you connected. No error. So it worked?

Florescu

ASKER

I guess it's working now... I have no idea why. I don't think I did anything different.

slightwv (䄆 Netminder)

Verify you are actually connected to where you think you are:
select global_name from global_name;

Florescu

ASKER

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
IIH.REGRESS.RDBMS.DEV.US.ORACLE.COM

slightwv (䄆 Netminder)

If that is the correct database then I believe everything is working.

If so, don't forget to close this question and award point to those posts that helped.

Florescu

ASKER

I installed the Oracle Instant Client 11G on another identical Linux box and now it's back to the error message.

What is causing this?

m_walker

When on the other linux box, are you trying to connect to a data base on the 2nd linux box, or back to the original database in the original box.

If on the other box, then you will need something to tell sql plus where the database is (eg: tnsnames.ora file setup and in the right location) or as Qlemo said
sqlplus user/pwd@172.31.0.184/IIH

m_walker

I have just been playing with 10G XE (not the same, but wanted to have a quick play for other reasons).

It got me thinking....

can you do this for me and post the response

echo $TNS_ADMIN

and

ls -l $TNS_ADMIN

Thanks.

Florescu

ASKER

[oracle@IIH instantclient_11_2]$ echo $TNS_ADMIN
/u01/app/oracle/instantclient_11_2/:

[oracle@IIH instantclient_11_2]$ ls -l $TNS_ADMIN
ls: /u01/app/oracle/instantclient_11_2/:: No such file or directory

I am trying to connect to the same database. DB IP address is 172.31.0.184

I do have the TNSNAMES.ORA file located in that directory.

slightwv (䄆 Netminder)

>>ls: /u01/app/oracle/instantclient_11_2/:: No such file or directory

That concerns me. Double check the full path is correct.

>>I installed the Oracle Instant Client 11G on another identical Linux box and now it's back to the error message

This is technically another question and should be asked as a related question.

SOLUTION

m_walker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Florescu

ASKER

that fixed it, thanks a bunch

m_walker

I cant confirm this, but maybe on the first computer when it just stated to work, you tested ourside the $TNS_ADMIN folder and it could not find the tnsnames.ora (some as above), then in all the checking and testing you changed your current working directory to the same folder where the tnsnames.ora file existed, and sqlplus found a copy in the "current directory", thus just started to work.

So check the original computer as well.

Qlemo

Good catch, m_walker. I completely missed those two successive equal signs, and apparently it wasn't me alone ...