473,396 Members | 1,773 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

ORA-12514 listener does not currently know of service requested

37
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!
Mar 12 '07 #1
5 38807
vijaydiwakar
579 512MB
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
Mar 12 '07 #2
mivey4
37
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.
Mar 12 '07 #3
mivey4
37
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?
Mar 12 '07 #4
mivey4
37
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.
Mar 12 '07 #5
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
Jul 16 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: KULJEET | last post by:
i face problem in oracle backup. i take oracle logical backup after 3-4 days. but now the database is run 24*7 days so i set the oracle on archive mode at last night i take the hot backup by...
1
by: Adam Ruth | last post by:
I'm using OCI on Mac OS X and I've run into a strange problem with my TNSNAMES.ORA file. My TNSNAMES.ORA file has one entry INV4II and it works fine. However, it will only work if that is the...
6
by: bdj | last post by:
Hello! I have at set of tnsnames.ora. I wich to make an union, e.g. a single file of it. How can I do that easy? Greetings Bjørn
0
by: AKG | last post by:
Hi, This is Ali Kazim, Web Application Developer based in Pakistan. I'm developing a web application using ASP.net having Oracle 9i as database of choice. I have a developed a stored procedure...
2
by: mpatel6 | last post by:
I had this error in alert log and my instance was down, anybody can help me? Errors in file /u01/app/oracle/admin/sotstest/bdump/sotstest_p004_626740.trc: ORA-07445: exception encountered: core...
2
by: hemantmudaliar | last post by:
The Trigger is giving following exception java.sql.SQLException: ORA-04091: table PSCONTENT.VGNASCHANNEL is mutating, trigger/function may not see it ORA-06512: at...
3
nabh4u
by: nabh4u | last post by:
HI everyone, I an getting the following errors when i execute my procedure. SQL> exec PROCEDURE_NAME BEGIN PROCEDURE_NAME; END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error:...
0
by: nkumarin001 | last post by:
Hi, I got stuck up when i was doing recovery. Please help me. I took backup of all the datafiles,redogfiles and controlfiles. I opened the database and created table called student....
0
by: nkumarin001 | last post by:
Hi, I got stuck up when i was doing recovery. Please help me. I opened the database in noarchivelog mode and inserted 5 records and made logswitch purposely but I dint gave any DML statements...
0
by: basmgokul | last post by:
I am using oracle 10g in windows vista.. when starting DB it throws an error Errors in file c:\database\udump\practice_ora_440.trc: ORA-00704: bootstrap process failure ORA-39700: database...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.