TNS-03505: Failed to resolve name for pluggable database

TNS-03505: Failed to resolve name for pluggable database

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

--Check services from listener services command:
lsnrctl services

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

SQL> alter session set container=PDB2;
Session altered.

SQL> show pdbs

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

SQL> alter system register;
System altered.

SQL> select name,pdb from all_services;

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

Netmanager 1

3. Choose the Database Services on Right window from drop down

Netmanger 2

4. Add the Database fields as shown below:
Global Database Name: PDB/CDB name
Oracle Home Directory: Path of Oracle home
SID: Instance name

Netmanager3

5. Save the services setting by clicking FILE --> Save Network Configuration.

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.

(SID_DESC =
(GLOBAL_DBNAME = PDB5)
(ORACLE_HOME = C:\Oracle\dbhomeXE)
(SID_NAME = XE)
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.