Solved

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

Posted on 2011-03-23
23
2,431 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:Florescu
  • 8
  • 7
  • 5
  • +1
23 Comments
 
LVL 4

Expert Comment

by:m_walker
ID: 35204666
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.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 300 total points
ID: 35205418
With 11g you can use a more simple format, without even using TNSNames.Ora, when connecting:
    sqlplus user/pwd@server/instance
in your case
    sqlplus user/pwd@172.31.0.184/IIH
 
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35206175
>>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.
0
 

Author Comment

by:Florescu
ID: 35208164
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
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 35209257
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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35209313
>>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.
0
 

Author Comment

by:Florescu
ID: 35209659
[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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35209937
>>[oracle@IIH instantclient_11_2]$ ls

I don't see sqlplus...

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

Author Comment

by:Florescu
ID: 35210321
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>
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35210331
>>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?
0
 

Author Comment

by:Florescu
ID: 35210340
I guess it's working now... I have no idea why.  I don't think I did anything different.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35210387
Verify you are actually connected to where you think you are:
select global_name from global_name;
0
 

Author Comment

by:Florescu
ID: 35210587
SQL> select global_name from global_name;

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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35211070
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.
0
 

Author Comment

by:Florescu
ID: 35220597
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?
0
 
LVL 4

Expert Comment

by:m_walker
ID: 35220780
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
0
 
LVL 4

Expert Comment

by:m_walker
ID: 35220899
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.
0
 

Author Comment

by:Florescu
ID: 35222550
[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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35223734
>>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.
0
 
LVL 4

Assisted Solution

by:m_walker
m_walker earned 200 total points
ID: 35224192
I dont think the : should be on the end of the $TNS_ADMIN value.

from you post you had
export TNS_ADMIN==/u01/app/oracle/instantclient_11_2

Note the two "="

try to re-export the value with
export TNS_ADMIN=/u01/app/oracle/instantclient_11_2

and do the
echo $TNS_ADMIN

I think your issue is the TNS_ADMIN is pointing to an invlaid path.  Once you get that fixed, try the sqlplus connection again.




0
 

Author Comment

by:Florescu
ID: 35224247
that fixed it, thanks a bunch
0
 
LVL 4

Expert Comment

by:m_walker
ID: 35224269
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.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 35224272
Good catch, m_walker. I completely missed those two successive equal signs, and apparently it wasn't me alone ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Char delimited error 5 30
Add 0 to end of Number 21 72
automatic email alert 1 22
Oracle Next Available Number 2 20
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now