|
|
| 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   |
 |
EdStevens
Messages: 1376
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 #664659 is a reply to message #664657] |
Wed, 26 July 2017 07:48 |
 |
oracbeg
Messages: 16
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
