Error ORA-12514: The listener does not currently know of service requested in connect descriptor
Hi All,
I hoping that someone can assist me with this error that I have started receiving whenever
I attempt to connect to an Oracle database using Toad or Mysql. This all started after doing
a hard shutdown of my machine. And, I have researched this and still haven't found a solution.
Any suggestions that you can provide will be greatly appreciated!
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-FEB-2010 10:31
:38
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ravchenko-xp.msk.binbank
.ru)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
then issue
C:\> lsnrctl start
P.S. same for linux/unix
mohammadzahid
Please check SERVICE_NAME = orcl between listener.ora and tnsnames.ora file. Both values should be same. Case sensitive in the UNIX environment. you may have to modify listener.ora file or tnsnames.ora file. If adjustment is made in the listener.ora then restart is recommended.
k3pa
I assume you are connecting within your localhost.
Well, Check this out :)
echo $ORACLE_SID -> It should be 'orcl' (case sensitive)
tnsping orcl -> It should return OK
sqlplus / as sysdba
SQL> show parameter service_name -> It should be 'orcl'
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
slightwv (䄆 Netminder)
There's a 'feature' in early 10g where the instance doesn't automatically register with the listener like it is supposed to.
I answered a question just like this the other day. You need to add the instance to the listener.ora file.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
fluglash: I typed lsnrctl stop, start and status on my windows machine and here are the results.
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx.xxxxxxxxxx.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 18-MAR-2010 18:33:45
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx.xxxxxxxxxxx.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> lsnrctl status
NL-00853: undefined command "lsnrctl". Try "help"
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 18-MAR-2010 18:33:45
Uptime 0 days 0 hr. 45 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxx.xxxxxxxxxxx.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 18-MAR-2010 18:33:45
Uptime 0 days 1 hr. 37 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx.xxxxxxxxx.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
And, it doesn't even looking like my SID=orcl is even being detected.
mohammadzahid: I didn't even see an entry for the service_name in the listner.ora file. So, I followed
schwertner's suggestions and added it.
schwertner: I added the entry below. And, here are the results after doing so.
lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-MAR-2010 20:34:53
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 18-MAR-2010 20:34:56
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
But, I don't know why status is indicated as "UNKNOWN" for each.
k3pa: I type what you suggested and received the following results.
>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 18 20:39:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> show parameter service_name
ORA-01034: ORACLE not available
Now after making the necessary changes, here is how my tnsora and listener.ora file appear.
Also, I was able to connect briefly after stop and restarting the Oracle and Listener services, but soon after I lost my connection.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
studious01
ASKER
Problem solved!
And, here is what I did. I changed my host name to my full computer name, which includes the domain name.
I kept shutting down and retarting the database. I then tried to connect to the datbase
and did so sucessfully. But, the connection keep being dropped. And, I would then exit
out of SQLplus and do everything all over again.
Then, I did a system restore to see if that would resolve the problem. And, I didn't
so I reversed the restore. Then, finally I logged in via sqlplus / as sysdba one
last time. I type in startup and everything went well with the database being mounted
and available for use. So, I then attempted to connect with my software that connects to the
a database schema and that went well.
So, I then attempted connect to a database schema using Toad, and that went well.
And, I can even connect using SQLDeveloper. So, it looks like everything is back to normal.
I can you tell that I am a developer, because we keep banging and banging at it, until we
solve the problem.
Thanks everyone for your help on this one!
Studious1
studious01
ASKER
Also, I have to mention that ran a pc cleaning software on my machine, which found and deleted 6,000 temporary files from my cache.
if the output is something like:
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-FEB-2010 10:31
:38
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PRO
.ru)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
then issue
C:\> lsnrctl start
P.S. same for linux/unix