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\d b_1\networ k\admin\tn snames.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\d b_1\networ k\admin\li stener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\d b_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
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\d
# 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\d
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\d
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
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'
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'
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-exchang e.com/Data base/Oracl e/Q_251502 10.html
I answered a question just like this the other day. You need to add the instance to the listener.ora file.
http://www.experts-exchang
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1)))
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\d b_1\networ k\admin\li stener.ora
Log messages written to C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening on: (DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
Listening on: (DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=xxxx xxxxx.xxxx xxxxxx.com )(PORT=152 1)))
Connecting to (DESCRIPTION=(ADDRESS=(PRO TOCOL=IPC) (KEY=EXTPR OC1)))
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\d b_1\networ k\admin\li stener.ora
Listener Log File C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=xxxx xxxxx.xxxx xxxxxxx.co m)(PORT=15 21)))
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1)))
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\d b_1\networ k\admin\li stener.ora
Listener Log File C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=xxxx xxxx.xxxxx xxxxxx.com )(PORT=152 1)))
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=(PRO TOCOL=IPC) (KEY=EXTPR OC1)))
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\d b_1\networ k\admin\li stener.ora
Listener Log File C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=xxxx xxxxx.xxxx xxxxx.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\d b_1\networ k\admin\li stener.ora
Log messages written to C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening on: (DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
Listening on: (DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=127. 0.0.1)(POR T=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PRO TOCOL=IPC) (KEY=EXTPR OC1)))
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\d b_1\networ k\admin\li stener.ora
Listener Log File C:\oracle\product\10.2.0\d b_1\networ k\log\list ener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (PIPENAME= \\.\pipe\E XTPROC1ipc )))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=127. 0.0.1)(POR T=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\d b_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = C:\oracle\product\10.2.0\d b_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!
fluglash: I typed lsnrctl stop, start and status on my windows machine and here are the results.
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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\d
Log messages written to C:\oracle\product\10.2.0\d
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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\d
Listener Log File C:\oracle\product\10.2.0\d
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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=(PRO
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\d
Listener Log File C:\oracle\product\10.2.0\d
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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=(PRO
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\d
Listener Log File C:\oracle\product\10.2.0\d
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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\d
Log messages written to C:\oracle\product\10.2.0\d
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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\d
Listener Log File C:\oracle\product\10.2.0\d
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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\d
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = C:\oracle\product\10.2.0\d
)
)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would suggest to check Oracle alert log file. Look for error message and paste it here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
ASKER
Also, I was able to connect briefly after stop and restarting the Oracle and Listener services, but soon after I lost my connection.
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
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
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