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

Question

I am trying to connect to oracle 11g installed on Linux EL 5 and and getting the following error

SQL> connect sys/password@ud06 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

my listener.ora under network/admin is as follows

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=ud06)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (SID_NAME=orcl))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11g)
      (PROGRAM=extproc)))

MY tnsnames.ora is as follows

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

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

UD06=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ud06)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

MY lsnrctl status shows as follows:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-FEB-2010 16:23:06
Uptime                    0 days 0 hr. 12 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11g/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/11g/log/diag/tnslsnr/ud06/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ud06.us.server.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Answer 1

Can you ping ud06 successfully (as ud06, not ud06.us.server.com)?

What does the command

lsnrctl services

show?

EDIT: It sounds to me like maybe the database instance name isn't actually "orcl"? What the lsnrctl services output tells me is that the "orcl" service, although defined in the listener.ora file, is not actually running.

Can you log on with a direct connection on the server? If so, what do you use as the ORACLE_SID environment variable value? Log in as the SYS user and issue the command:

ALTER SYSTEM REGISTER;

Then issue the lsnrctl services command again and see if an additional instance doesn't show up.

Also, as Alex points out, the tnsping command is reporting a fully qualified service name. Edit the sqlnet.ora file and set the NAMES.DEFAULT_DOMAIN value to NULL if it has a value.

EDIT 2: Does tnsping ud06 on the server work? Or is my assumption that client and server are on different systems wrong?

Answer 2

The SERVICE_NAME in the tnsping output doesn't match the entry in tnsnames.ora; is that file from the Windows box or the Linux box? It looks like you don't have a local (Windows) tnsnames.ora entry for u06 and it's guessing what the service name should be expanding it - I think that's what the reference to the hostname adapter means.