Avatar of studious01
studious01
 asked on

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!

This is a copy of my tnsnames.ora file:

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(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)
    )
  )



And, here is a copy of my listener.ora file:

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
Oracle Database

Avatar of undefined
Last Comment
studious01

8/22/2022 - Mon
fluglash

C:\> lsnrctl status

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=(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.

http://www.experts-exchange.com/Database/Oracle/Q_25150210.html

ASKER CERTIFIED SOLUTION
schwertner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
studious01

ASKER
Thank you everyone for responding.

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.

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    )
  )

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

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 = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I then tried to connect to the database via Toad and received the following error message:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

I will be up late tonight and will be checking back here frequently.  

Thanks to all!




SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mohammadzahid

I would suggest to check Oracle alert log file. Look for error message and paste it here.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
studious01

ASKER
schwertner:

I type the following and this was displayed on the screen:

SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             176163716 bytes
Database Buffers          427819008 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

Then, I tried to login through SQLPlus and received these error messages:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

I am working on providng the Oracle Alert log.
studious01

ASKER
I have attached the error alert log

Thanks.
alert-orcl.log
studious01

ASKER
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.