ORA-12514 listener does not currently know of service requested

mivey4

Hi,

First off, I am aware that this is a very heavily documented error and I have done my homework for throughly researching probable causes before deciding to post my problem here. At this point, I believe another set of eyes on the issue is merited.

I am a MSSQL DBA and somewhat new to ORACLE; but I have read the administrators manual having a basic thorough level of knowledge (Tho' I am still learning) and understanding of how to configure the database and get it successfully running. I believe I also have a fairly sound understanding of how connections are established to include (but not limited to) the use of the tnsnames.ora and listener.ora file.

My problem is I keep receiving ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
whenever I attempt to establish connection to a specific instance of ORACLE on my system.

I have no problem connecting by using the following conventions:

C:\>sqlplus administrator/admin
or
C:\>sqlplus /nolog
and then
C:\>connect administrator/admin

But I have 2 database instances of ORACLE running on this system and from my understanding, the proper way to connect to a
specific instance would be:

sqlplus administrator/admin@ORCL
or
sqlplus administrator/admin@PROD

But attempting to connect in this manner always throws the error. Could someone plz explain what it is I am doing wrong
since I have at this point fully exhausted looking through forums without a reasonable resolution.

So far these are the things I have done in the process of troubleshooting but none of them suggest theres a problem with my configuration -
Note: There is no firewall configuration on this system

================================================== =======
TNSPING ORCL1
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-MAR-2007 22:08:01

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet .ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT =

1521))) (CONNECT_DATA = (SERVICE_NAME = OracleServiceORCL)))
OK (60 msec)

================================================== =======
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 11-MAR-2007 15:12:37
Uptime 0 days 6 hr. 57 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listen er.ora
Listener Log File D:\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=workstat ion.development.com)(PORT=1521)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "PROD_XPT" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

================================================== =======
D:\>SC QUERY OracleServiceORCL

SERVICE_NAME: OracleServiceORCL
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

================================================== =======
D:\>SC QUERY OracleServicePROD

SERVICE_NAME: OracleServicePROD
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

================================================== =======
TNSNAMES.ora:
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServiceORCL)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServicePROD)
)
)

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

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

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
)

As far as I can tell, everything appears to be properly configured according to ORACLE documentation
and the services described in my tnsnames.ora file are being detected dynamically by the listener. I have tried
statically assigning them in the listener.ora file though I realize it isn't recommended but the results are the same
anyway.

Is there any other way to tell oracle that you want to connect to a specific database instance?
If more information is required I'll be happy to provide it.

THANKS!

vijaydiwakar

Quote:

Originally Posted by mivey4

Hi,

First off, I am aware that this is a very heavily documented error and I have done my homework for throughly researching probable causes before deciding to post my problem here. At this point, I believe another set of eyes on the issue is merited.

I am a MSSQL DBA and somewhat new to ORACLE; but I have read the administrators manual having a basic thorough level of knowledge (Tho' I am still learning) and understanding of how to configure the database and get it successfully running. I believe I also have a fairly sound understanding of how connections are established to include (but not limited to) the use of the tnsnames.ora and listener.ora file.

My problem is I keep receiving ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
whenever I attempt to establish connection to a specific instance of ORACLE on my system.

I have no problem connecting by using the following conventions:

C:\>sqlplus administrator/admin
or
C:\>sqlplus /nolog
and then
C:\>connect administrator/admin

But I have 2 database instances of ORACLE running on this system and from my understanding, the proper way to connect to a
specific instance would be:

sqlplus administrator/admin@ORCL
or
sqlplus administrator/admin@PROD

But attempting to connect in this manner always throws the error. Could someone plz explain what it is I am doing wrong
since I have at this point fully exhausted looking through forums without a reasonable resolution.

So far these are the things I have done in the process of troubleshooting but none of them suggest theres a problem with my configuration -
Note: There is no firewall configuration on this system

================================================== =======
TNSPING ORCL1
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-MAR-2007 22:08:01

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet .ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT =

1521))) (CONNECT_DATA = (SERVICE_NAME = OracleServiceORCL)))
OK (60 msec)

================================================== =======
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 11-MAR-2007 15:12:37
Uptime 0 days 6 hr. 57 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listen er.ora
Listener Log File D:\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=workstat ion.development.com)(PORT=1521)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODXDB" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "PROD_XPT" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully

================================================== =======
D:\>SC QUERY OracleServiceORCL

SERVICE_NAME: OracleServiceORCL
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

================================================== =======
D:\>SC QUERY OracleServicePROD

SERVICE_NAME: OracleServicePROD
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

================================================== =======
TNSNAMES.ora:
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServiceORCL)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServicePROD)
)
)

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

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

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.development.com)(PORT = 1521))
)
)

As far as I can tell, everything appears to be properly configured according to ORACLE documentation
and the services described in my tnsnames.ora file are being detected dynamically by the listener. I have tried
statically assigning them in the listener.ora file though I realize it isn't recommended but the results are the same
anyway.

Is there any other way to tell oracle that you want to connect to a specific database instance?
If more information is required I'll be happy to provide it.

THANKS!

first of all check thy services are running or not...
if running then try to use in run ->sqlplusw username/pwd@servicename
then just tell me the error

mivey4

Quote:

Originally Posted by vijaydiwakar

first of all check thy services are running or not...
if running then try to use in run ->sqlplusw username/pwd@servicename
then just tell me the error

I double-checked the services and ensured that they were running as the previous information confirmed via all the detailed steps and information I'd provided.

Nonetheless, I then I ran your suggested command it threw the same error, "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

The only difference I can see between running sqlplusw and sqlplus is one is a windows GUI and the other a command-line interface. I don't understand what was supposed to be accomplished by doing this?

Thanks.

mivey4

Quote:

Originally Posted by mivey4

I double-checked the services and ensured that they were running as the previous information confirmed via all the detailed steps and information I'd provided.

Nonetheless, I then I ran your suggested command it threw the same error, "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

The only difference I can see between running sqlplusw and sqlplus is one is a windows GUI and the other a command-line interface. I don't understand what was supposed to be accomplished by doing this?

Thanks.

Hey!

It looks as though I may have identified why I may be having this problem or at least what may be a contributing factor.

It seems as though I am missing the OracleDBConsole Service on my system which ORACLE documentation clearly says should be present. Is there any documentation anyone may no of that can tell me how I can create the service manually from a command prompt.

I found this article but it isn't very descriptive for the procedure and I have more than one SID on this system so simply running emca returns a help screen for parameters that for most of them I don't know what should be entered for appropriate values. The article makes the process seem more simple than what it apparently is using this method.

http://www.mcse.ms/message572094.html

Any help PLZ?

mivey4

Nevermind I found my own answer for this and the problem is now resolved.

Problem 1 Resolution: (Failure to resolve TNS listener error)

The serviceName I set in the tnsnames.ora file needed to be changed despite numerous examples on-line that seemed to suggest that explicitly giving the name of the instance's service name would be acceptable, it apparently didn't work in this case.

I was almost certain I'd tried this previously without success but for whatever reason, it works now.

So this:

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orl-mivey-ws1.ops.ivbasp.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServiceORCL)
)
)

was changed to this:

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orl-mivey-ws1.ops.ivbasp.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

Following this same naming convention for every database instance on my system so the instance name was also assigned as the SERVICE_NAME resolved the problem. For anyone else experiencing problems with the same error code, aside from extensive documentations elsewhere, you could review the steps I'd initially posted on your system as a troubleshooting reference.

Problem 2 Resolution: (No OracleDBConsole<INSTANCENAME> installed on the system)

After further research, apparently this service is only required if you intend to use the web-based Enterprise Manager program. But I always like to have everything that is supposed to be included in a typical setup to be present or I don't get that warm fuzzy feeling and tend to look at missing components like this for being probable culprits when other things don't work as expected.

So if anyone else ever needs to manually install or re-install this service, from a command prompt you can change to the directory that emca is located on your installation which for 10g is <systemDrive:>\oracle\product\10.2.0\db_1\BIN
and execute the following command:

emca -config dbcontrol db -repos create

I found more information available at http://www.stanford.edu/dept/itss/do...e.htm#CACBFHBB

Search the document for EMCA.

Thanks.

josuemonteiroviana

Hello everybody,

I had a similar trouble when I was setting up a new oracle server, the same oracle error message but the solution was another.
In my case, the problem was around SQLNET.ORA, that changed from this:
SQLNET.AUTHENTICATION_SERVICES= (NONE)

To this:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

After that everything goes fine.

Thanks
Josué Monteiro Viana

ORA-12514 listener does not currently know of service requested

re: ORA-12514 listener does not currently know of service requested

re: ORA-12514 listener does not currently know of service requested

re: ORA-12514 listener does not currently know of service requested

re: ORA-12514 listener does not currently know of service requested

re: ORA-12514 listener does not currently know of service requested

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about