|
|
| Re: ORA-12154: TNS:could not resolve service name [message #584587 is a reply to message #584586] |
Wed, 15 May 2013 17:52   |
John Watson
Messages: 8962
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 #584590 is a reply to message #584588] |
Wed, 15 May 2013 18:27 |
John Watson
Messages: 8962
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 |
 |
skyfx
Messages: 11
Registered: May 2013
|
Junior Member |
|
|
BlackSwan wrote on Wed, 15 May 2013 18:24every 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:27I'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 #584628 is a reply to message #584593] |
Thu, 16 May 2013 07:08 |
|
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 |
John Watson
Messages: 8962
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 |
|
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 #584780 is a reply to message #584593] |
Fri, 17 May 2013 10:44 |
|
skyfx
Messages: 11
Registered: May 2013
|
Junior Member |
|
|
BlackSwan wrote on Wed, 15 May 2013 19:28from 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 #584807 is a reply to message #584792] |
Fri, 17 May 2013 15:31 |
|
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  .
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  ).
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 ).
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 #584810 is a reply to message #584808] |
Fri, 17 May 2013 15:47 |
|
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 |
John Watson
Messages: 8962
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 |
John Watson
Messages: 8962
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 |
|
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
