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\produ ct\10.2.0\db_1\ network\admin\s qlnet .ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT =

1521))) (CONNECT_DATA = (SERVICE_NAME = OracleServiceOR CL)))
OK (60 msec)

=============== =============== =============== ===== =======
LSNRCTL> STATUS
Connecting to (DESCRIPTION=(A DDRESS=(PROTOCO L=IPC)(KEY=EXTP ROC1) ))
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\produ ct\10.2.0\db_1\ network\admin\l isten er.ora
Listener Log File D:\oracle\produ ct\10.2.0\db_1\ network\log\lis tener .log
Listening Endpoints Summary...
(DESCRIPTION=(A DDRESS=(PROTOCO L=ipc)(PIPENAME =\\.\ pipe\EXTPROC1ip c)))
(DESCRIPTION=(A DDRESS=(PROTOCO L=tcp)(HOST=wor kstat 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 OracleServiceOR CL

SERVICE_NAME: OracleServiceOR CL
TYPE : 10 WIN32_OWN_PROCE SS
STATE : 4 RUNNING
(STOPPABLE,PAUS ABLE,ACCEPTS_SH UTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CO DE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

=============== =============== =============== ===== =======
D:\>SC QUERY OracleServicePR OD

SERVICE_NAME: OracleServicePR OD
TYPE : 10 WIN32_OWN_PROCE SS
STATE : 4 RUNNING
(STOPPABLE,PAUS ABLE,ACCEPTS_SH UTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CO DE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

=============== =============== =============== ===== =======
TNSNAMES.ora:
EXTPROC_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServiceOR CL)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServicePR OD)
)
)

=============== =============== =============== ===== =======
# listener.ora Network Configuration File: D:\oracle\produ ct\10.2.0\db_1\ network\admin\l isten er.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTEN ER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\produ ct\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LI ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 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]

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\produ ct\10.2.0\db_1\ network\admin\s qlnet .ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT =

1521))) (CONNECT_DATA = (SERVICE_NAME = OracleServiceOR CL)))
OK (60 msec)

=============== =============== =============== ===== =======
LSNRCTL> STATUS
Connecting to (DESCRIPTION=(A DDRESS=(PROTOCO L=IPC)(KEY=EXTP ROC1) ))
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\produ ct\10.2.0\db_1\ network\admin\l isten er.ora
Listener Log File D:\oracle\produ ct\10.2.0\db_1\ network\log\lis tener .log
Listening Endpoints Summary...
(DESCRIPTION=(A DDRESS=(PROTOCO L=ipc)(PIPENAME =\\.\ pipe\EXTPROC1ip c)))
(DESCRIPTION=(A DDRESS=(PROTOCO L=tcp)(HOST=wor kstat 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 OracleServiceOR CL

SERVICE_NAME: OracleServiceOR CL
TYPE : 10 WIN32_OWN_PROCE SS
STATE : 4 RUNNING
(STOPPABLE,PAUS ABLE,ACCEPTS_SH UTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CO DE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

=============== =============== =============== ===== =======
D:\>SC QUERY OracleServicePR OD

SERVICE_NAME: OracleServicePR OD
TYPE : 10 WIN32_OWN_PROCE SS
STATE : 4 RUNNING
(STOPPABLE,PAUS ABLE,ACCEPTS_SH UTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CO DE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

=============== =============== =============== ===== =======
TNSNAMES.ora:
EXTPROC_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServiceOR CL)
)
)
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleServicePR OD)
)
)

=============== =============== =============== ===== =======
# listener.ora Network Configuration File: D:\oracle\produ ct\10.2.0\db_1\ network\admin\l isten er.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTEN ER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\produ ct\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LI ST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = workstation.dev elopment.com)(P ORT = 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]

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]

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 = OracleServiceOR CL)
)
)

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.AUTHENTI CATION_SERVICES = (NONE)

To this:
SQLNET.AUTHENTI CATION_SERVICES = (NTS)
NAMES.DIRECTORY _PATH= (TNSNAMES, EZCONNECT)

After that everything goes fine.

Thanks
Josué Monteiro Viana