Tns 03505 failed to resolve name ошибка

Failing to resolve TNS names could involve any one of the network configuration files, such as tnsnames.ora or sqlnet.ora. There’re several possible causes may throw TNS-03505:

1. Absent Local Naming Method
2. Missing tnsnames.ora File
3. Connect Identifier Mismatch
4. Searching for Wrong Domain

TNS-03505 due to Absent Local Naming Method

There’re several naming methods supported by Oracle in sqlnet.ora. Only local naming can be used for resolving TNS identifiers in tnsnames.ora.

What is Local Naming Method?

Local naming is a naming method (TNSNAMES) that supports alias-fashioned connect identifiers to represent lengthy connect descriptors locally. That is to say, each client should have its own tnsnames.ora to resolve TNS names. If we miss TNSNAMES in the list of naming method, we will see TNS-03505 once we use connect identifiers.

Additionally, local naming instructs clients to use the configuration file tnsnames.ora which may contain several connect identifiers like this:

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORA12C =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA12C)
    )
  )

Advantages of Local Naming

Take the first connect identifier for an example, connect identifier ORA12C will be resolved as its lengthy connect descriptor defined in the file at run-time.

By applying local naming, we can shorten our code if we use the identifier instead of its lengthy connect string in our application. Moreover, any programs that use aliases (connect identifier) won’t be affected by any changes on their own definitions (connect descriptors).

Reproduce TNS-03505

In contrast, if TNSNAMES is not in the order of the naming methods used for client name resolution lookup, then you can’t use local naming and receive TNS-03505:

C:\Users\ed>tnsping ora12c

Solutions

The solution to TNS-03505 is obvious. Just make sure that tnsnames.ora is existing and then add TNSNAMES to parameter NAMES.DIRECTORY_PATH in sqlnet.ora in order to support local naming.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
...
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNS-03505 due to Missing tnsnames.ora File

Sometimes, you might not notice that your tnsnames.ora is missing. Here I deliberately deleted the file from %TNS_ADMIN%.

C:\Users\ed>tnsping ora12c

No need to say, the solution is either to get one tnsnames.ora for yourself or to restore tnsnames.ora from the recycle bin.

In some rare situations, your «real» %TNS_ADMIN% might point to another location where has no tnsnames.ora file and you don’t know about it. I think you have to know where is your current %TNS_ADMIN% first. Perhaps, reconfiguring the environment variable if necessary.

TNS-03505 due to Connect Identifier Mismatch

Sometimes, users misspell the name of connect identifier to connect the database. So that tools like sqlplus, tnsping or Oracle JDBC driver fails to match a correct entry in tnsnames.ora so as to fail to resolve the given name.

Reproduce TNS-03505

For example, we can reproduce TNS-03505 by tnsping a deliberately misspelled the connect identifier like this:

C:\Users\ed>tnsping comdb

The utility can match no one named «comdb» in tnsnames.ora. The correct name is «compdb», I missed one letter on purpose.

Solution

How do we handle TNS-03505? Just make sure the name you typed matches the connect identifier in tnsnames.ora.

Further reading: TNSPING Errors Collections

TNS-03505 due to Searching for Wrong Domain

Another possible cause of TNS-03505 is that you have an entry NAMES.DEFAULT_DOMAIN in your sqlnet.ora. It may erroneously resolve your external TNS names.

Solution

The solution is easy, just comment it out by prefixing a pound sign (#).

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
...
#NAMES.DEFAULT_DOMAIN = example.com

DBA can rarely find out the root cause of this type of error, because TNS-03505 itself is a very broad error message in applicable range.

For more connection troubleshooting, you may refer to Oracle 18c Net Services Administrator’s Guide: 15 Testing Connections.

Please note that, all symptoms found in TNS-03505 will also be found in ORA-12154: TNS:could not resolve the connect identifier specified.

11

Every now and then when I created a new Oracle database in an existing Oracle base (be it 11g or 12c) – I get the error “TNS-03505: Failed to resolve name” whenever I try to do tnsping on a new database. You may receive this error even when not doing tnsping. For example, you may see this error when logging on the datamover in bootstrap mode (for example, using SYSADM).

99% of the time, this error turns out to be something that I overlooked and I could have resolved the error in a few minutes rather than wasting hours. As a result, I decided to document it here so everyone can benefit.

Additionally, if you know of any resolution to this error – let us know in the comments section below and we’ll include the solution for everyone’s benefit.

This error is documented on the Oracle website as:

TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.

I went through Net Manager and Net Configuration Manager about 1000 times. You know, instead of actually reading the error.

I thought I needed a Loopback Adapter. I didn’t.

I started to go through the files by hand, sqlnet.ora, listener.ora, tnsnames.ora (which I’m much more confident with surprisingly)…it had to be something small.

Appearing on its own, this essentially means the client has not even gotten to the point of attempting to make contact with a server.  The TNS-03505 is telling you that it simply cannot find any record of the database you are trying to establish a connection with.

Some things to check include the following:

• If you are using local naming (tnsnames.ora file) – make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (sqlnet.ora file). SQLNET.ORA sample provided below.
• Make sure the host, port and service name specified are correct and typo free.
• In the tnsnames.ora, verify that the database name is present and is spelled correctly. Also, check for any potential errors like missing out on “(” or “)”
• Check the environment variable TNS_ADMIN. If you have TNS_ADMIN declared, check the tnsnames.ora file in the directory, which it points to. Many a times it has turned out that this variable was pointing to a different location than the one I was looking in (especially since the release of PUM, DPKs etc).
• Verify that there are not multiple tsnnames.ora files present in the same location.
• Verify that the same entries are found in Oracle client installs.
• Verify that there is no firewall issue in connecting to the database server.
• If you did edit the tnsnames.ora or sqlnet.ora file recently – make sure that the file got saved properly (I noticed that whenever I open the file in Notepad++, it does not save properly using the save shortcut, Control+S). Best thing is to close down you editing software and if the file was not saved, it will prompt you to save or discard.

SQLNET.ORA file

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

TNSNAMES.ORA

HR92U018 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HR92U018)
       ))

PSHRDMO =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1521))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = PSHRDMO)
       ))
FSCM92 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = FSCM92)
       )
    )
EP92U019 =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = EP92U019)
       )
    )
	
HCMDEV =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = PSTUTORIAL)(PORT = 1521))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = HCMDEV)
       ))

• testomat.io управление авто тестами

• Python
• Реклама
• Работа
• Консультации
• Обучение

This simple two-part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems.

TEST COMMUNICATION BETWEEN THE CLIENT AND THE LISTENER

We will use tnsping to complete this step. It’s a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.

Here, we will use it to prove that:

a) the tnsnames.ora has the correct hostname and port

b) there is a listener listening on the specified host and port

Start by running tnsping:

• tnsping <your_tns_entry_name>

If it is successful you will see something like this:

If not, here are some common errors, and some suggestions for fixing them:

TNS-03505: FAILED TO RESOLVE NAME

The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn’t even got as far as trying to make contact with a server – it simply can’t find any record of the database that you are trying to tnsping. Make sure that you’ve spelled the database name correctly, and that it has an entry in the tnsnames.ora.

If you have a sqlnet.ora, look for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.

TNS-12545: CONNECT FAILED BECAUSE TARGET HOST OR OBJECT DOES NOT EXIST

The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with ‘ping <hostname>’. If ping returns ‘unknown host’, speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get ‘host unreachable’, again speak to your network person, the problem could be down to a routing or firewall issue.

TNS-12541: TNS:NO LISTENER

The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.

TNSPING HANGS FOR A LONG TIME

I’ve seen this happen in situations where there is something listening on the host/port – but it isn’t an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a ‘netstat -ap | grep 1521’ (or whatever port you are using) to find out what program is listening on that port.

ATTEMPT A CONNECTION TO THE INSTANCE

Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we’ll use sqlplus:

• sqlplus [username]/[password]@<your_tns_entry_name>

If it works you will successfully log into the instance. If not, here are some common errors:

ORA-01017: INVALID USERNAME/PASSWORD; LOGON DENIED

This is actually a good error in these circumstances! Even though you didn’t use the correct username or password, you must have successfully made contact with the instance.

ORA-12505: TNS:LISTENER DOES NOT CURRENTLY KNOW IF SID GIVEN IN CONNECT

Either the SID is misspelled in the tnsnames, or the listener isn’t listening for it. Check the tnsnames.ora first. If it looks ok, do a ‘lsnrctl status’ on your server, to see what databases the listener is listening for.

ORA-12514: TNS:LISTENER COULD NOT RESOLVE SERVICE_NAME GIVEN IN CONNECT

This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn’t one of the things that it was listening out for.

Begin by looking at your tnsnames.ora. In it, you will see a setting like SERVICE_NAME=<name>.

If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).

If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running ‘lsnrctl services’ on your server. If there isn’t an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.

And it’s as simple as that! Hopefully this helped you resolve the typical Oracle networking problems we’ve all faced at some point in our DBA careers!

Error
When i created the PDB database and make entry in the tnsnames.ora file manually by typing then i got the following error. In my case entry is not save then i used netca for making entry and its worked for me. In this blog i tried to give all possible solution for this problem.

C:\windows\system32>tnsping pdb1
TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 18-SEP-2019 16:19:13
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
C:\Oracle\dbhomeXE\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name


Cause:
Possible reason for this error as:
1. Check the tnsnames.ora file is exists.

2. Check the tnsnames.ora file has entry of PDB1 Database.

PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE1.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1)
)
)


3. Check PDBs database is listed in Listener services command.

lsnrctl services
..........................
..........................
Service "pdb1" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:6 refused:0 state:ready
LOCAL SERVER


Solution:
Note: It better to make TNS entry with NETCA instead of manually.
Check the tnsnames.ora file is exists
1. Create the tnsnames.ora file if not exists and check the TNSADMIN environment variable is set if yes then check path is correct. You can also check Oracle Home you are using is correct.

-- check tns_admin is set or not and pointing to which path.
echo %TNS_ADMIN%


Check the tnsnames.ora file has TNS entry and it’s correct
2. Create the TNS entry with hostname and service name listed in listener services command and View ALL_SERVICES from SQLPLUS.


--- Check services from sqlplus
col name for a15
col pdb for a10
SELECT NAME, PDB FROM cdb_services;

NAME            PDB
--------------- ----------
SYS$BACKGROUND  CDB$ROOT
SYS$USERS       CDB$ROOT
XEPDB1          XEPDB1
PDB1            PDB1

--- Add the TNS entry in tnsnames.ora file for PDB1 database
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1.Oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1)
)
)


Check PDBs database is listed in Listener services
3. If entry in not listed in listener services then add service entry manually:
1. Tried to add dynamic entry with Alter system command.
2. Tried to add with NETMGR utility.
3. Edit the LISTENER.ora file for manually entry and reload it.

Add dynamic entry with Alter system command

-- Connect with PDB database through CDB and try to add manually with Alter command:
SQLPLUS sys as sysdba
password

CON_ID CON_NAME        OPEN MODE  RESTRICTED
------ --------------- ---------- ----------
     5 PDB2            READ WRITE NO

NAME      PDB
--------- --------------
PDB2      PDB2

Add with NETMGR utility

1. Open NET MANAGER Oracle app in windows from START button.
2. Expand Local ---> Expand Listeners --> Click on Listener at left windows

6. Reload the services and check the lsnrctl services command for new entry:

lsnrctl services
.................
.................
Service "PDB5" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER


LISTENER.ora Manually make entry for Servies

Open the listener file and make entry in the listener
Add entry in SID_LIST_LISTENER bracket.