Home » SQL & PL/SQL » Client Tools » Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" ( Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production)
Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679793] Sat, 28 March 2020 10:41 Go to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

Hi all,
After installing the database 12c on OEL 7.7 ( described in this thread ==> http://www.orafaq.com/forum/t/206586/ ),
I cannot connect to it from a client that is in the same domain.


I can see the listener listening on port 1521 on the db server, service name is MYDB :


LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-MAR-2020 19:06:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                28-MAR-2020 18:45:17
Uptime                    0 days 0 hr. 21 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/MY_ORCALE_MACHINE/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.140.70)(PORT=1521)))
Services Summary...
Service "MYDB" has 1 instance(s).
  Instance "MYDB", status READY, has 1 handler(s) for this service...
Service "MYDBXDB" has 1 instance(s).
  Instance "MYDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@MY_ORACLE_MACHINE admin]$
My client is a Windows Server 2012 machine.

I installed Oracle 12c client on it.

Then configured tnsnames.ora like this:

MYDB = 
(description =
     (address = (protocol = tcp)(host = 10.1.140.70)(port = 1521))
     (connect_data = (service_name = MYDB))
   )
I can successfully connect to port 1521 with TELNET ( it is entering the telnet interface when I press enter, which is an indication of successful connection ) :

C:\Users\user1>telnet 10.1.140.70 1521
However, when I try to tnsping or connect with sqlplus , I get the following error:

C:\Users\user1>
C:\Users\user1>tnsping MYDB

TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 28-MAR-2020 17:34:49

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

Used parameter files:
D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\Users\user1>


I have checked that the service name is MYDB in v$services, or the Linux OS of MYDB.

What can be the issue with connectivity between client and server ?
What else should I check ?


Many thanks in advance,
Andrey

[Updated on: Sat, 28 March 2020 10:42]

Report message to a moderator

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679794 is a reply to message #679793] Sat, 28 March 2020 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 26700
Registered: January 2009
Location: SoCal
Senior Member
>I cannot connect to it from a client that is in the same domain.
Please SHOW us using COPY & PASTE what actually happens when you try to connect from a client that is in the same domain
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679795 is a reply to message #679793] Sat, 28 March 2020 11:32 Go to previous messageGo to next message
John Watson
Messages: 8267
Registered: January 2010
Location: Global Village
Senior Member
What if you try this:
sqlplus username/password@10.1.140.70:1521/mydb
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679796 is a reply to message #679794] Sat, 28 March 2020 11:33 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Sat, 28 March 2020 19:08
>I cannot connect to it from a client that is in the same domain.
Please SHOW us using COPY & PASTE what actually happens when you try to connect from a client that is in the same domain

Here:

C:\Users\user1>sqlplus system/Newproj2020@MYDB

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 28 18:28:35 2020

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

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


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\user1>
C:\Users\user1>
C:\Users\user1>
C:\Users\user1>whoami
my-domain\user1

C:\Users\user1>

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679797 is a reply to message #679796] Sat, 28 March 2020 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26700
Registered: January 2009
Location: SoCal
Senior Member
First you need to find & fix source of ORA-12154 error

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]
 https://edstevensdba.wordpress.com/2018/09/19/troubleshooting-ora-12154/
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679798 is a reply to message #679795] Sat, 28 March 2020 11:43 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sat, 28 March 2020 19:32
What if you try this:
sqlplus username/password@10.1.140.70:1521/mydb

This worked.
But I don't understand why the result was different with using TNSNAMES entry as mentioned above...

I thought that it had to do with authentication method from SQLNET.ORA but it seems fine:

# sqlnet.ora Network Configuration File: D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# 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)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679799 is a reply to message #679798] Sat, 28 March 2020 11:53 Go to previous messageGo to next message
John Watson
Messages: 8267
Registered: January 2010
Location: Global Village
Senior Member
What is your D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\tnsnames.ora file?
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679800 is a reply to message #679793] Sat, 28 March 2020 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26700
Registered: January 2009
Location: SoCal
Senior Member
Andrey_R wrote on Sat, 28 March 2020 08:41
Hi all,
After installing the database 12c on OEL 7.7 ( described in this thread ==> http://www.orafaq.com/forum/t/206586/ ),
I cannot connect to it from a client that is in the same domain.


I can see the listener listening on port 1521 on the db server, service name is MYDB :


LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-MAR-2020 19:06:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                28-MAR-2020 18:45:17
Uptime                    0 days 0 hr. 21 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/MY_ORCALE_MACHINE/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.140.70)(PORT=1521)))
Services Summary...
Service "MYDB" has 1 instance(s).
  Instance "MYDB", status READY, has 1 handler(s) for this service...
Service "MYDBXDB" has 1 instance(s).
  Instance "MYDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@MY_ORACLE_MACHINE admin]$
My client is a Windows Server 2012 machine.

I installed Oracle 12c client on it.

Then configured tnsnames.ora like this:

MYDB = 
(description =
     (address = (protocol = tcp)(host = 10.1.140.70)(port = 1521))
     (connect_data = (service_name = MYDB))
   )
TNSNAMES.ORA above looks different from sample below; some () appear to be lacking

<addressname> =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <service_name>)
 )
)

[Updated on: Sat, 28 March 2020 11:57]

Report message to a moderator

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679801 is a reply to message #679799] Sat, 28 March 2020 11:58 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Sat, 28 March 2020 19:53
What is your D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\tnsnames.ora file?
My D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\tnsnames.ora file was actually D:\oracle\app\oracle\product\12.1.0\client_1\network\admin\tnsnames.ora.txt

As a default, extension is not shown, and as I named tnsnames.ora file, I didn't pay attention that I initially created it as a text file, but I did not remove the .txt suffix,
As at that time it wasn't visible to me..


So - problem was indeed in tnsnames.ora file..
After removing the bad suffix - it works fine..

Many thanks to you and BlackSwan for your time and efforts.

Best Regards,
Andrey

[Updated on: Sat, 28 March 2020 11:59]

Report message to a moderator

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679802 is a reply to message #679801] Sat, 28 March 2020 15:30 Go to previous messageGo to next message
EdStevens
Messages: 1206
Registered: September 2013
Senior Member
That's one good reason why I always set my Windblows machines to 'show file name extensions'.

While this one was solved that way, for future reference, please note that your client's sqlnet.ora (not sql.net.ora.txt !) file can also come into play. See this article that I wrote, and cited by BlackSwan. Two key entries in sqlnet.ora are NAMES.DIRECTORY_PATH and NAMES.DEFAULT_DOMAIN.
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679804 is a reply to message #679802] Sat, 28 March 2020 16:32 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

EdStevens wrote on Sat, 28 March 2020 23:30
That's one good reason why I always set my Windblows machines to 'show file name extensions'.

While this one was solved that way, for future reference, please note that your client's sqlnet.ora (not sql.net.ora.txt !) file can also come into play. See this article that I wrote, and cited by BlackSwan. Two key entries in sqlnet.ora are NAMES.DIRECTORY_PATH and NAMES.DEFAULT_DOMAIN.
Thank you.

I've actually went over it and got to the point where I'm examining tnsnames.ora with attention, which helped me find the problem.
If I could - I would make it a factory default to have extensions always shown Smile

Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #679808 is a reply to message #679798] Sun, 29 March 2020 09:50 Go to previous messageGo to next message
EdStevens
Messages: 1206
Registered: September 2013
Senior Member
Andrey_R wrote on Sat, 28 March 2020 11:43
John Watson wrote on Sat, 28 March 2020 19:32
What if you try this:
sqlplus username/password@10.1.140.70:1521/mydb
This worked.
But I don't understand why the result was different with using TNSNAMES entry as mentioned above...


Becuase this type of connect string does not require a reference to tnsnames.ora. It directly provided the key information that _would_ have been found in tnsnames.ora

tnsnames.ora is like a phone book. Your original error meant that it either could not find the phone book, or it found it but could not find the requested entry. Your successful connection above means someone gave you the necessary information so you didn't have to rely on the phone book.
Re: Client fails to connect to new DB server with "TNS-03505: Failed to resolve name" [message #680250 is a reply to message #679804] Thu, 30 April 2020 08:56 Go to previous message
EdStevens
Messages: 1206
Registered: September 2013
Senior Member
Andrey_R wrote on Sat, 28 March 2020 16:32
<snip>
If I could - I would make it a factory default to have extensions always shown Smile

Just one more reason why I abhor Windows! Smile
Previous Topic: Error while executing the sql query in pl/sql developer
Next Topic: Toad Access violation issue merged)
Goto Forum:
  


Current Time: Mon May 25 07:10:28 CDT 2020