TNS-03505: Failed to resolve name for pluggable database
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.
TNS Ping Utility for 64-bit Windows: Version 126.96.36.199.0 - Production on 18-SEP-2019 16:19:13
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = NODE1.oracle.com)(PORT = 1521))
(SERVICE_NAME = pdb1)
3. Check PDBs database is listed in Listener services command.
Service "pdb1" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
"DEDICATED" established:6 refused:0 state:ready
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.
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:
--- Add the TNS entry in tnsnames.ora file for PDB1 database
(ADDRESS = (PROTOCOL = TCP)(HOST = Node1.Oracle.com)(PORT = 1521))
(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
SQL> alter session set container=PDB2;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ------ --------------- ---------- ---------- 5 PDB2 READ WRITE NO
SQL> alter system register;
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
3. Choose the Database Services on Right window from drop down
4. Add the Database fields as shown below:
Global Database Name: PDB/CDB name
Oracle Home Directory: Path of Oracle home
SID: Instance name
5. Save the services setting by clicking FILE --> Save Network Configuration.
6. Reload the services and check the lsnrctl services command for new entry:
Service "PDB5" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:0 refused:0
LISTENER.ora Manually make entry for Servies
Open the listener file and make entry in the listener
Add entry in SID_LIST_LISTENER bracket.
(GLOBAL_DBNAME = PDB5)
(ORACLE_HOME = C:\Oracle\dbhomeXE)
(SID_NAME = XE)