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
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?