Home » RDBMS Server » Networking and Gateways » ORA-12154: TNS:could not resolve service name (Remote connection using Oracle Provider for OLEDB 9.2.0.7.0 on W2k8 R2 to Oracle 9i R2 9.2.0.8.0 DB)
icon5.gif  ORA-12154: TNS:could not resolve service name [message #584586] Wed, 15 May 2013 17:46 Go to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
Hi,

So a decent amount of googling suggests that the subject matter error seems to be a fairly common issue. I have found various problem solving guides, but none of them seem to address the issue for me.

What I'm trying to do
Establish a remote connection to an Oracle 9i R2 9.2.0.8.0 database using "Oracle Provider for OLEDB 9.2.0.7.0" (I was unable to find an 9.2.0.8.0 equivalent) on a Windows Server 2008 R2 64-bit machine.

My configuration
sqlnet.ora
# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = B81DCS.COM

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)

tnsnames.ora
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

b81im1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ADVA)
    )
  )

PATH Environment Variable
D:\oracle\ora92\bin;D:\oracle\jre\1.1.8\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\

TNS_ADMIN Environment Variable
D:\oracle\ora92\network\ADMIN

Configuration / Application Screenshots
Oracle Net Manager
hxxp://imageshack.us/a/img268/6453/oraclenetmanager.png
Oracle Net Manager Test Connection
hxxp://imageshack.us/a/img854/1954/oraclenetmanagerconnect.png
TNSPING Connection Attempt
hxxp://imageshack.us/a/img542/928/tnsping.png
Error
hxxp://imageshack.us/a/img543/9921/interfacestartup.png

Any thoughts on what I could be doing wrong / what else I could try to make the connection work?

Thanks,

skyfx
Re: ORA-12154: TNS:could not resolve service name [message #584587 is a reply to message #584586] Wed, 15 May 2013 17:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Change your tnsnames.ora entry to include the default domain:
b81im1.B81DCS.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ADVA)
    )
  )


And, just to do my moderator bit, welcome to the forum. Please read our OraFAQ Forum Guide though I guess you must have done that already: thank you for using [code] tags.
Re: ORA-12154: TNS:could not resolve service name [message #584588 is a reply to message #584587] Wed, 15 May 2013 18:16 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
Hi John,

Thanks, glad to be here Smile.

We tried what you suggested and unfortunately the error remains. Any other thoughts?

-skyfx
Re: ORA-12154: TNS:could not resolve service name [message #584589 is a reply to message #584588] Wed, 15 May 2013 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
every connection request that gets to the listener is logged in listener.log file.

locate the line within listener.log file that contains the 12154 status code

post back here the line containing 12154 along with the 10 line above it & 10 lines below it;
because they will contain valuable debugging details.
Re: ORA-12154: TNS:could not resolve service name [message #584590 is a reply to message #584588] Wed, 15 May 2013 18:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've now looked at that screen shot you posted of the tnsping output. It is impossible to reconcile it with the tnsnames.ora and sqlnet.ora files you posted, and furthermore does not include an ora-12154 error.
I'm sorry, but I cannot take this any further: the information you have posted is contradictory.
Perhaps someone else can make sense of it.
Re: ORA-12154: TNS:could not resolve service name [message #584592 is a reply to message #584589] Wed, 15 May 2013 19:19 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
BlackSwan wrote on Wed, 15 May 2013 18:24
every connection request that gets to the listener is logged in listener.log file.

locate the line within listener.log file that contains the 12154 status code

post back here the line containing 12154 along with the 10 line above it & 10 lines below it;
because they will contain valuable debugging details.


Unfortunately, RDP is not enabled on the machine hosting the Oracle DB, so I am unable to look at the listener.log file. I will try to see if I can get access somehow. Let me know if you can think of anything else in the meantime.

John Watson wrote on Wed, 15 May 2013 18:27
I've now looked at that screen shot you posted of the tnsping output. It is impossible to reconcile it with the tnsnames.ora and sqlnet.ora files you posted, and furthermore does not include an ora-12154 error.
I'm sorry, but I cannot take this any further: the information you have posted is contradictory.
Perhaps someone else can make sense of it.


Thanks for looking anyways!

[Updated on: Wed, 15 May 2013 19:20]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584593 is a reply to message #584592] Wed, 15 May 2013 19:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
from the DB Server issue the following OS commands

lsnrctl status
lsnrctl service

COPY the results from above then PASTE all back here.

Please explain why you are ready, willing and able to upgrade the OS to newer & supported versions, but refuse to upgrade the DB.
V9 Oracle was obsoleted & unsupported long before Windows 2008 was ever released!
Why do you have any reasonable expectation that this combination of ancient DB & newest OS should even work together?
Re: ORA-12154: TNS:could not resolve service name [message #584628 is a reply to message #584593] Thu, 16 May 2013 07:08 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member

As soon as I get remote access to the machine, I will try running those commands and post back what I find.

To answer your question, I am not trying to establish this connection between Win2k8 R2 and Oracle v9 by choice, per se. We have a client who is using an ABB Distributed Control System (DCS) that is using this old version of Oracle, and it is not within our scope to alter this installation. However, the developer of the data interface we trying to use (shown trying to connect in my last screenshot above) has indicated that the interface works successfully with this version of Oracle, so I am confident that there is simply some configuration item we are missing.

[Updated on: Thu, 16 May 2013 10:51] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584634 is a reply to message #584628] Thu, 16 May 2013 08:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'll have one more try.

In your tnsping screen shot, you ping a tns servicename b81im1. This resolves to a listener on host=b81im1 and requests a database servicename b81im1. And it works.
In the tnsnames.ora file you posted, there is a tns servicename b81im1. OK so far. But this resolves to a listener on host 172.xxx.xxx.xxx and resquests a database servicename ADVA.

Can you see that there is no relationship between these results?
Re: ORA-12154: TNS:could not resolve service name [message #584654 is a reply to message #584634] Thu, 16 May 2013 09:58 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member

Hi John,

I see what you're saying. I'm not sure why the tnsping utility automatically requests the servicename b81im1, as opposed to ADVA. When I test the connection inside Oracle Net Manager, or indeed Oracle SQL Developer, I use ADVA as a servicename and the connection is successful. Just to be sure, I tried changing the database servicename in the tnsnames.ora to b81im1, but the connection problem persists.

[Updated on: Thu, 16 May 2013 10:51] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584657 is a reply to message #584654] Thu, 16 May 2013 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
how many different ORACLE_HOME directories exist on client system?
Re: ORA-12154: TNS:could not resolve service name [message #584664 is a reply to message #584657] Thu, 16 May 2013 10:40 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member

I have only installed the one Oracle Provider for OLEDB. Looking inside regedit, the value of HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\ALL_HOMES\HOME_COUNTER is "1". Is that what you were looking for?

[Updated on: Thu, 16 May 2013 10:51] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584780 is a reply to message #584593] Fri, 17 May 2013 10:44 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
BlackSwan wrote on Wed, 15 May 2013 19:28
from the DB Server issue the following OS commands

lsnrctl status
lsnrctl service

COPY the results from above then PASTE all back here.

Please explain why you are ready, willing and able to upgrade the OS to newer & supported versions, but refuse to upgrade the DB.
V9 Oracle was obsoleted & unsupported long before Windows 2008 was ever released!
Why do you have any reasonable expectation that this combination of ancient DB & newest OS should even work together?


So I was finally able to gain remote access to the DB server. Here are the results of running the commands you suggested:

lsnrctl status
LSNRCTL for 32-bit Windows: Version 9.2.0.8.0 - Production on 17-MAY-2013 08:40:07

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=b60im054)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.8.0 - Production
Start Date                16-MAY-2013 16:08:46
Uptime                    0 days 16 hr. 31 min. 21 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=b60im054.B60DCS.com)(PORT=1521)))
Services Summary...
Service "ADVA" has 1 instance(s).
  Instance "ADVA", status READY, has 1 handler(s) for this service...
Service "EHPROVIDER" has 1 instance(s).
  Instance "EHPROVIDER", 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...
The command completed successfully


lsnrctl service
LSNRCTL for 32-bit Windows: Version 9.2.0.8.0 - Production on 17-MAY-2013 08:40:17

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=b60im054)(PORT=1521)))
Services Summary...
Service "ADVA" has 1 instance(s).
  Instance "ADVA", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1926 refused:0 state:ready
         LOCAL SERVER
Service "EHPROVIDER" has 1 instance(s).
  Instance "EHPROVIDER", 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
The command completed successfully


Thoughts?

FYI - I also located the listener.log file you referred to in a previous post, opened it using GVIM, and searched it for "12154". It returned no search results. I assume this means the remote connection attempt is not even coming through to the machine?

[Updated on: Fri, 17 May 2013 11:29]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584792 is a reply to message #584780] Fri, 17 May 2013 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ORA-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
Re: ORA-12154: TNS:could not resolve service name [message #584807 is a reply to message #584792] Fri, 17 May 2013 15:31 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
Thanks for that link. Interesting to see what all could be causing this - goes to show how important it is to have descriptive error messages Sad.

To be honest, I'm not sure what to try anymore. I have listed all the different configuration permutations I tried below, along with the associated results. Any other thoughts?

tnsnames.ora using FQDN as alias and ADVA as service name
i) sqlnet.ora has default domain declared, authentication method includes TNSNAMES & HOSTNAME
ii) sqlnet.ora has default domain declared, authentication method includes TNSNAMES
iii) sqlnet.ora DOES NOT have default domain declared, authentication method includes TNSNAMES & HOSTNAME
iv) sqlnet.ora DOES NOT have default domain declared, authentication method includes TNSNAMES
Same result every time: ORA-12154 ("service name" error) when trying to start data interface. ORA-12705 when trying sqlplus connection to FQDN. Successful tnsping to FQDN (resolves to ADVA service name declared in tnsnames).

tnsnames.ora using b81im1 as alias and ADVA as service name
v) sqlnet.ora has default domain declared, authentication method includes TNSNAMES & HOSTNAME
Result: ORA-12154 ("service name" error) when trying to start data interface. ORA-12504 when trying sqlplus connection to B81IM1. Successful tnsping to B81IM1 and FQDN (resolves to respective service names, not ADVA Confused).

vi) sqlnet.ora has default domain declared, authentication method includes TNSNAMES
Result: ORA-12154 ("service name" error) when trying to start data interface. ORA-12154 ("connect identifier" error) when trying sqlplus connection to
B81IM1. TNS-03505 ("service name" error) when trying tnsping to B81IM1 and FQDN.

vii) sqlnet.ora DOES NOT have default domain declared, authentication method includes TNSNAMES & HOSTNAME
Result: ORA-12154 ("service name" error) when trying to start data interface. ORA-12705 when trying sqlplus connection to B81IM1. Successful tnsping to B81IM1 and FQDN (B81IM1 resolves to ADVA, FQDN resolves to FQDN Confused).

viii) sqlnet.ora DOES NOT have default domain declared, authentication method includes TNSNAMES
Result: ORA-12154 ("service name" error) when trying to start data interface. ORA-12705 when trying sqlplus connection to B81IM1. Successful tnsping to B81IM1, (resolves to ADVA); TNS-03505 ("service name" error) when trying tnsping to FQDN.

I looked up solutions to the new error codes I came across:
- ORA-12705, which states, "Cannot access NLS data files or invalid environment specified.", is supposed to be solved by creating an NLS_LANG environment variable, but unfortunately this did nothing for me
- ORA-12504, which states: "Listener was not given the SERVICE_NAME in CONNECT_DATA.", has a variety of suggested solutions too (e.g., ensuring the correct formatting of the tnsnames.ora file, but since tnsping works, I don't think the issue is there

[Updated on: Sat, 18 May 2013 00:46] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584808 is a reply to message #584807] Fri, 17 May 2013 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sqlplus user1/pass1@'dbserver:1521/orcl_sid'

above can make SQL*Net connection WITHOUT using tnsnames.ora file.

http://www.orafaq.com/wiki/EZCONNECT
Re: ORA-12154: TNS:could not resolve service name [message #584810 is a reply to message #584808] Fri, 17 May 2013 15:47 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
I tried:

sqlplus history/history@b81im1:1521/ADVA
sqlplus history/history@b81im1.b81dcs.com:1521/ADVA
sqlplus history/history@172.xxx.xxx.xxx:1521/ADVA
sqlplus history/history@'b81im1:1521/ADVA'
sqlplus history/history@'b81im1.b81dcs.com:1521/ADVA'
sqlplus history/history@'172.xxx.xxx.xxx:1521/ADVA'

...and they all return:
SQL*Plus: Release 11.2.0.2.0 Production on Fri May 17 13:42:58 2013

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

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified


Enter user-name: 


FYI - the Wiki you linked to states that you need an Oracle 10g database for EZCONNECT to work. Since we don't have that, I guess it's not surprise that this isn't working.

[Updated on: Sat, 18 May 2013 00:47] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584812 is a reply to message #584810] Fri, 17 May 2013 18:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I know how frustrating this sort of thing can be. I remember - from about twenty years ago.

The problem, I think, is clear: you are not using the tnsnames.ora file that you think you are using. This is why tnsping goes to a totally different place from whatever else you are using: it is using one file (which you have not displayed here) and your other tool is using a different one.
Re: ORA-12154: TNS:could not resolve service name [message #584814 is a reply to message #584812] Fri, 17 May 2013 19:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Just a minute: you said that you had only one Oracle Home installed, which was 9.2.0.7. But your last post is of SQL*Plus running from an 11.2 home. So now we know what is happening: as I said, you are using the wrong tnsnames file. So, set your environment correctly: you have your PATH set to include the 11g ORACLE_HOME/bin but it looks as though you have not set the actual ORACLE_HOME environment variable correctly. So set it, to the directory above that within which your 11g sqlplus executable is.

Then EZConnect will work if you give it the correct connect string, which I think is this:
sqlplus history/history@b60im054.B60DCS.com:1521/ADVA


[Updated on: Fri, 17 May 2013 19:11]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #584824 is a reply to message #584814] Sat, 18 May 2013 08:38 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
John, I apologize for the confusion, but the reason sqlplus is showing a version of 11.2 is because that is the version of sqlplus that I am using, not Oracle. The installation of Oracle Provider for OLEDB 9.2.0.7.0 did not include an sqlplus.exe, so I just used a copy of the instantclient 11.2 that I had, which included an sqlplus.exe. The environment variables are indeed set as per my first post. The instantclient is not an installed program, just an extracted directory, so there should be no conflicts as far as I can see.

[Updated on: Sat, 18 May 2013 09:04] by Moderator

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #585904 is a reply to message #584586] Thu, 30 May 2013 15:13 Go to previous messageGo to next message
skyfx
Messages: 11
Registered: May 2013
Junior Member
Hi all,

We figured out the issues Smile. In short:

i) The interface installation cannot reside in the "Program Files (x86)" directory, due to issues with parentheses in the directory name. It can reside in any directory that does not have a parenthesis (or potentially any other special characters) in the directory name.

ii) Oracle Provider for OLEDB 9.2.0.7.0 is not compatible with Windows Server 2008 R2. Instead, we had to use ODAC 10.2.0.2.21 (which includes Oracle Provider for OLE DB 10.2.0.2.20). According to Oracle's installation instructions, one needs to perform a special procedure to install this version on Windows Server 2008 as well, but we simply installed it using Windows Server 2003 SP1 compatibility mode and that did the trick Smile.

Hope this can help others with similar issues Smile. Thanks to everyone for trying to help!
Re: ORA-12154: TNS:could not resolve service name [message #585923 is a reply to message #585904] Fri, 31 May 2013 00:50 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and workaround.

Regards
Michel
Previous Topic: EXCEPTION HANDLING for DBLINK In TRIGGERS
Next Topic: Authentication for connected user database links
Goto Forum:
  


Current Time: Fri Mar 29 08:47:57 CDT 2024