Link to home
Start Free TrialLog in
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))
    )
  )
Avatar of fluglash
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
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.
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'
Avatar of 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
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would suggest to check Oracle alert log file. Look for error message and paste it here.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I have attached the error alert log

Thanks.
alert-orcl.log
Also, I was able to connect briefly after stop and restarting the Oracle and Listener services, but soon after I lost my connection.
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
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.