Tns 03505 ошибка

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

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:windowssystem32>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:OracledbhomeXEnetworkadminsqlnet.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.

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!

I’ve been trying to build an image of OBIEE 11g recently, with not much success.

The process is as follows:
1. Fire up big-ass (to me anyway) Windows 2003/8 Server on Amazon.
2. Go through all the security hassle of Internet Explorer to download the software (I’ve since put it on it’s own volume, and then attach that to my instance, so I don’t have to do this 300 times)
3. Install 11gR2 database, software only.
4. DBCA to build database.
5. Net Manager to build the listener.
6. Create the weblogic metadata for OBIEE.
7. Install OBIEE.
8. Scream and yell when the configuration doesn’t work.

Rinse and repeat 12 times.

I’ve gotten quite good at it.

Then I ran into this lovely error…

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.

# tnsnames.ora Network Configuration File:
c:oracleproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = TESTING)
     (SID = TESTING)
   )
 )
LISTENER=(Address=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))

I name all my sandbox databases «testing.» Always have, probably always will. From the command line I issue:

SQLPLUS SYS/TESTING@TESTING AS SYSDBA

(Yes, I know I don’t need to specify the user/password@service_name).

Spot it yet?

Could it be that my alias says: ORACLR_CONNECTION_DATA?

Yup, foiled again. ID10T in the house.