ORA-12154: TNS: could not resolve the connect identifier specified

[StryderKC]

Hi guys, I am not an absolute newbie, and have been using the oracle database for a long time. All of a sudden today I am getting the error.

ORA-12154: TNS: could not resolve the connect identifier specified

This error is obviously related to tnsnames.ora file and not correct Service and SID identifiers. However what's baffling me is that both my tnsnames.ora and listener.ora have the correct entries and even the listener service is running.

Here's my tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

and here's the listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Oracle10g)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
(SID_NAME = orcl)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

I am using the service ORCL to connect to the database using SQL Plus. I connected yesterday and it was fine. So i am completely baffled! HAs anyone faced anything like this before?

[simply_dba]

and what does listener status show ? Is the database registered ?

[StryderKC]

lsnrctl stat returns the following

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 20-FEB-2009 15:52
:39

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)) )
STATUS of the LISTENER
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date 20-FEB-2009 15:52:11
Uptime 0 days 0 hr. 0 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.1.0\Db_1\network\admin\listen er.ora
Listener Log File C:\oracle\product\10.1.0\Db_1\network\log\listener .log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\ pipe\EXTPROCipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0. 1)(PORT=1521)))
Services Summary...
Service "OracleTest" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[simply_dba]

So the problem zeroes down to tnsnames. Can you reconfigure it using netmgr and see whether the problem resolves or not ?

[simply_dba]

Also, you can try to connect with sid.db domain, if you have one.

[alhanani]

here you have to type the IP of your client at your server TNS as well copy the ip of your server TNS name on you Client
that set

[StryderKC]

hi guys yes I had resolved that error. Actually like alhanani said i used the IP of my database server in my tnsnames file and it was fine.

[alhanani]

did you configure your sqlnet.ora if not
ad your ip of the client or commit all the restriction