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
This is one of the most common Oracle errors and it is basically telling you that you are trying to connect to a database name that is unknown to the system. The most common cause are typos in your connection string or network configuration files.
Go to the $ORACLE_HOME/network/admin directory and open the tnsnames. ora file and look for the connect identifier, verify that it's correct. You could have a typo or be missing the connection details in the tnsnames. ora file (on windows %ORACLE_HOME%\network\admin).
By default, the tnsnames. ora file is located in the ORACLE_HOME/network/admin directory.
The answer to this problem is very simple. Do not worry about the .ora files or any other configuration. Oracle does all these just perfect.
Only while connecting via command line, it gets confused with passwords that have a @ symbol in them.
Therefore while connecting through command line SQL, do not use a password with a '@' in it. Just use the web interface to create an account having a password without an '@' symbol in it.
That is!! Problem solved. I had been breaking my head for quite a few days, and now my problem is solved!!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With