Home » Other » Client Tools » sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified (10.2)
sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444874] Wed, 24 February 2010 10:43 Go to next message
sandrino82
Messages: 6
Registered: February 2010
Junior Member
Hi everyone, i'm having some trouble trying to connect the db via sqlplus.
it seems to me that the file tnsnames.ora is not read when i execute sqlplus.
whit the command: sqlplus username/password@servicename i receive the error ORA-12154, while if i pass the whole connection string i can connect without problems
sqlplus username/password@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oradev10)(PORT = 10520))(CONNECT_DATA = (SERVICE_NAME = D10)))

i set the ORACLE_HOME environment variable correctly... what more i have to do?
following is my tnsnames.ora

TOTEMPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ttf19.cern.ch)(PORT = 1521))
)
(CONNECT_DATA =
(SID = totemdb)
)
)
TOTEM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradev10.cern.ch)(PORT = 10520))
)
(CONNECT_DATA =
(SERVICE_NAME = D10)
)
)

my SQLNET.ora is:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME,TNSNAMES,ONAMES)


any help will be very appreciated
Regards

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444875 is a reply to message #444874] Wed, 24 February 2010 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you have more than 1 oracle home?

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444877 is a reply to message #444875] Wed, 24 February 2010 10:51 Go to previous messageGo to next message
sandrino82
Messages: 6
Registered: February 2010
Junior Member
No, i have only 1 ORACLE_HOME.
if i run from cmd prompt echo %ORACLE_HOME% i get
C:\oracle\product\10.2.0\client_1 which is the correct path

PS
i'm using windows xp, and i have a client version of oracle 10.2, and i'm trying to connect to remote db

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444878 is a reply to message #444874] Wed, 24 February 2010 11:01 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
On the command propmt type:
tnsping totem

see what that gives.
You do realise that the host in the tnsnames.ora you posted is not the same as the connect string you posted with the sqlplus call?

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444879 is a reply to message #444874] Wed, 24 February 2010 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
ORA-12154: 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.

No listener on this host is listening for this service on this port.

Regards
Michel

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444880 is a reply to message #444879] Wed, 24 February 2010 11:21 Go to previous messageGo to next message
sandrino82
Messages: 6
Registered: February 2010
Junior Member
Ok, i found i am not using the folder specified in the variable ORACLE_HOME;
with tnsping i found sqlplus is using a different folder to look for tnsnames.ora and sqlnet.ora (it's like i have two oracle home...) if i use the service name specified in the other tnsnames.ora file, i can connect without problems... my question now is: how can i change the path sqlplus use to look for tnsnames.ora?

Report message to a moderator

Re: sqlplus error ORA-12154: TNS:could not resolve the connect identifier specified [message #444881 is a reply to message #444880] Wed, 24 February 2010 11:22 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Set TNS_ADMIN variable.

Regards
Michel

Report message to a moderator

Previous Topic: Export Data in nested table
Next Topic: Data Export Tool
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 06:39:05 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.