OraFAQ Forum: Networking and Gateways » ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT

Home » RDBMS Server » Networking and Gateways » ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA (3 Merged) (Oracle 11g,Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA (3 Merged) [message #525686]

Tue, 04 October 2011 11:25

sowmya1388
Messages: 5
Registered: October 2011

Junior Member

I am trying to connect to Oracle 11g using ODBC.

I created a data source name from Admistrative Tools->ODBC Data Sources.I selected Oracle in OraClient11g_home1 as the driver. I tested the connection and it was succesful.

I tried to connect from visual studio. I used the Data Source as Microsoft ODBC Data SOurce. Selected my DSN from drop down (LORAC). I gave the user name and password and got the followign errors:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

This is my tnsnames.ora located in C:\oracle\product\11.1.0\client_1\network\admin directory.

# tnsnames.ora Network Configuration File: C:\oracle\product\11.1.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LORAC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lorac.chem.tamu.edu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lorac.chem.tamu.edu)
)
)

STOCKROOM2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lorac.chem.tamu.edu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lorac.chem.tamu.edu)
)
)

When i do tnsping LORAC, i get OK. When I do tnsping of the other service name i get Failed. However when I try to connect to that service from Visual studio I get a differnt set of errors:

ORA-12154: TNS:could not resolve the connect identifier specified

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

I had assumed that as the test connection from ODBC Administrator had worked I would be able to connec to my database from viusal studio. Is there something wrong in my tnsnames.ora? The content or its directory location?

ANy help wpuld be greatly appreciated

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #525690 is a reply to message #525686]

Tue, 04 October 2011 11:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORA-12154: TNS:could not resolve the connect identifier specified

Means the connect alias could not be resolved; typically could not be found in tnsnames.ora file.

post content of sqlnet.ora file from client system; if it exists

>When I do tnsping of the other service name i get Failed
using COPY & PASTE show what command entered & how Oracle responded

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #525820 is a reply to message #525690]

Wed, 05 October 2011 13:09

sowmya1388
Messages: 5
Registered: October 2011

Junior Member

This is the sqlnet.ora file in directory C:\oracle\product\11.1.0\client_1\network\admin. I am not sure if this is what you mean when you say client system or not.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Here is the tnsping command and output:

C:\Users\Sowmya>tnsping Stockroom2

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 05-OCT-2
011 13:06:25

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

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\Users\Sowmya>

Do you have any idea why these erros are coming:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

Because Tnsping worked for this service and the connection was successful when i tested from odbc admistrator.

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #525821 is a reply to message #525820]

Wed, 05 October 2011 13:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Used parameter files:
>C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Based upon lines above it appears to me that tnsping was issued from DB Server system. Is this correct?
I am confused.
How many systems involved & what are their hostname?
What Oracle software is installed on which system?
Did SQL*Net ever work without error? If so, what changed?

12504, 00000, "TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"
// *Cause:  The listener was not configured with a default service and
//  SERVICE_NAME was missing from the CONNECT_DATA received by the listener.
// *Action: Possible solutions are:
//  - Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service
//    name. Reload the listener parameter file using reload [<listener_name>].
//    This should be done by the LISTENER administrator.
//  - If using a service name, Check that the connect descriptor corresponding
//    to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component
//    in the CONNECT_DATA.

from DB Server system issue following OS commands

lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #525919 is a reply to message #525821]

Thu, 06 October 2011 20:26

sowmya1388
Messages: 5
Registered: October 2011

Junior Member

Yes I believe the tnsping was issued from server system.

I am trying to connect to a database hosted on a different system. I was given the following information by the Database Administrator:

Database Hostname: Lorac.chem.tamu.edu

Database Instance: Stockroom2

Username/Password: Inventory_mgmt/invmgmt

Oracle 11g Express Edition is installed in the system.

This is a brand new system and as far as I know SQL *Net has never been used till this application.

Here are the results of lsnrctl status and lsnrctl service commands:

C:\oraclexe\app\oracle\product\11.2.0\server>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 06-OCT-2011 20:17
:01

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date 05-OCT-2011 14:57:23
Uptime 1 days 5 hr. 19 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Programmer2-PC\lis
tener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Programmer2-PC)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Programmer2-PC)(PORT=8080))(Presenta
tion=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, 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 "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\oraclexe\app\oracle\product\11.2.0\server>lsnrctl service

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 06-OCT-2011 20:17
:19

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: PROGRAMMER2-PC, pid: 2852>
(ADDRESS=(PROTOCOL=tcp)(HOST=Programmer2-PC)(PORT=49164))
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #525920 is a reply to message #525919]

Thu, 06 October 2011 20:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Database Hostname: Lorac.chem.tamu.edu
compare above with below. The DB Server reports it resides on "Programmer2-PC" not "Lorac.chem.tamu.edu"
>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Programmer2-PC)(PORT=1521)))

Things won't work when they are different. The actual host name does not matter, but the two sides must use the same name.
ping <hostname>
ping <host_IP#>
both commands above must work from both DB Server & from client system for successful packet exchange

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #526032 is a reply to message #525920]

Fri, 07 October 2011 12:58

sowmya1388
Messages: 5
Registered: October 2011

Junior Member

Here are the results of the ping command. I gave it from ORACLE_HOME
C:\oraclexe\app\oracle\product\11.2.0\server>ping lorac.chem.tamu.edu

Pinging lorac.chem.tamu.edu [165.91.176.54] with 32 bytes of data:
Reply from 165.91.176.54: bytes=32 time=1ms TTL=64
Reply from 165.91.176.54: bytes=32 time=14ms TTL=64
Reply from 165.91.176.54: bytes=32 time<1ms TTL=64
Reply from 165.91.176.54: bytes=32 time<1ms TTL=64

Ping statistics for 165.91.176.54:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 14ms, Average = 3ms

Do I have to check that the same works from DB server. I don't actually have access to that.

Also can I get a small clarification: when you say that DB server resides on Programmer2-PC- that is simplay another user on my system. The actual DB resides at lorac.chem.tamu.edu as given by both the DB adminstrator and Oracle Enterprise Manager that I use to view tables. What can I do to ensure that it connects with that server?

I notice that my service name stockroom2 is not appearing when i give the lsnrctl service or lsnrctl status commands.Here is the tnsnames.ora from the actual oracle home C:\oraclexe\app\oracle\product\11.2.0\server:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Programmer2-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

Do I need to make any changes to this? Include my service perhaps?

Here is listener.ora from the same directory:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = Programmer2-PC)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

Does this need to be configured or changed in any way?

Here is sqlnet.ora from oracle_home

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Let me know if I need to change anything.

Thanks

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #526037 is a reply to message #526032]

Fri, 07 October 2011 13:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Do I have to check that the same works from DB server. I don't actually have access to that.

you can not troubleshoot network problem without access to both ends.

do you have listener running on your client PC?

Report message to a moderator

Re: ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA [message #526044 is a reply to message #526037]

Fri, 07 October 2011 14:17

sowmya1388
Messages: 5
Registered: October 2011

Junior Member

I tried connecting to the database through sqlplus and that is working. So can I assume packet exchange is occuring.I am simply not able to connect to the databse through my visual studio application.

Yes I do have a listener running on client machine.

Report message to a moderator

Previous Topic:	status:failure-lo exception:The network adapter could not establish the connection
Next Topic:	listener.ora , tnsnames.ora

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 11:16:17 CST 2025