Home » RDBMS Server » Networking and Gateways » Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" (Windows 10 64 bit)
Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664647] Wed, 26 July 2017 06:09 Go to next message
oracbeg
Messages: 13
Registered: July 2017
Junior Member
I am posting this after searching whole internet and trying all the solutions getting from there but this error is not letting me go through. I am trying to connect excel 2016 32 bit with oracle 11gr2 64 bit on windows 10 64 bit.

When i connect i get this error

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

Things i have done before it are...

1) Installed oracle client (odbc drivers) 32 bit version
2)set up the tnsnames.ora as

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


3) Set the environment variable as tns_admin till the directory client_1 as well as setting its path to client_1

4) made the odbc 32 bit connection with tns service name (orcl) and the connection gets successful.


still getting the same error. Please respond if i am missing something or can try anything else. Thanks Smile
Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664649 is a reply to message #664647] Wed, 26 July 2017 06:19 Go to previous messageGo to next message
EdStevens
Messages: 848
Registered: September 2013
Senior Member
If your tnsnames.ora is in %ORACLE_HOME%\network\admin, then there is no reason to set TNS_ADMIN. If your error is anything other than ORA-12514, then there is no reason to set TNS_ADMIN. TNS_ADMIN is to tell the oracle client stack to look somewhere besides the default location to locate tnsnames.ora. Your error clearly indicates you are locating it.

Is your database on the same machine as your client? That's what "HOST = localhost" indicates. No request to route a network message to LOCALHOST (or its IP address of 127.0.0.1) ever leaves the requesting machine. But your error indicates that is not the problem in this case.

Quote:
4) made the odbc 32 bit connection with tns service name (orcl) and the connection gets successful.
Please explain in detail exactly how you "made the odbc 32 bit connection with tns service name (orcl)".

If you are able to connect until you throw Excel into the mix, then it makes sense to start debugging by looking at how you have the data source defined in Excel.

[Updated on: Wed, 26 July 2017 06:20]

Report message to a moderator

Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664650 is a reply to message #664649] Wed, 26 July 2017 06:37 Go to previous messageGo to next message
oracbeg
Messages: 13
Registered: July 2017
Junior Member
Hi Ed. Thank you for the instant response. As you said that it is not reqd to set enviornment variable. I have removed it as tnsnames.ora is in %oracle home%.

Didn't make difference to the error

"For making odbc connection"

I used odbc 32 version from administrative tools and i added user dsn and selected "oracle in oraclient 11g_home1 as driver.

Below fields populated and i filled them as:

data source name: orcl(userdefined)

tns_service_name: orcl

user id/pwd: hr/pwd for testing the connection. and it shows successful.



"I am connecting excel directly through New Query option where i select oracle database to connect and in server field i enter "localhost" and after that it throws me this error" Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"

Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664657 is a reply to message #664650] Wed, 26 July 2017 07:12 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
POST results from OS commands below

lsnrctl status
lsnrctl service
Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664659 is a reply to message #664657] Wed, 26 July 2017 07:48 Go to previous messageGo to next message
oracbeg
Messages: 13
Registered: July 2017
Junior Member
here it is

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 26-JUL-2017 17:25:38
Uptime 0 days 0 hr. 50 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\cbfr3\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File f:\app\cbfr3\diag\tnslsnr\DESKTOP-QDD9GB8\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, 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 "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully





lsnrctl service

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 26-JUL-2017 18:17:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
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 "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:94 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: DESKTOP-QDD9GB8, pid: 6792>
(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-QDD9GB8)(PORT=1555))
The command completed successfully
Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664661 is a reply to message #664659] Wed, 26 July 2017 08:01 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
this listener can & will only ever accept connection requests from localhost & never from any remote client

f:\app\cbfr3\diag\tnslsnr\DESKTOP-QDD9GB8\listener\alert\log.xml
above will contain data showing the connect request that failed with 12504 status
Please post excerpt of all records above & below records with 12504 value
Re: Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" [message #664683 is a reply to message #664661] Fri, 28 July 2017 06:17 Go to previous message
oracbeg
Messages: 13
Registered: July 2017
Junior Member
It worked if i use oledb connection for connecting. Thanks for your responses Smile
Previous Topic: Lsnrctl stop -> database crashes
Next Topic: setting of environment variables
Goto Forum:
  


Current Time: Mon Nov 20 09:14:41 CST 2017

Total time taken to generate the page: 0.01128 seconds