I have recently installed Oracle 19c enterprise edition on Virtual box with host as windows 10 while guest machine in Linux Oracle.
I am able to connect to CDB database both from sqlplus(Installed on Virtualbox i.e. guest machine) and SQLDeveloper installed on windows 10.
But I am unable to connect to PDB database from SQL Developer while I am able to connect it from SQL PLUS i.e. from guest machine using export ORACLE_PDB_SID=testpdb1
In SQLDeveloper I am getting below error message.
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
(CONNECTION_ID=+zZy85nyTtSOaamzG3UnBw==)
When using the service name raido button below error message apprears:
Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
(CONNECTION_ID=H5R80kOxSAy+Cv74InS7MQ==)
Below are my Listener.ora and Tnsnames.ora
testcdb =
(ADDRESS_LIST=
# (ADDRESS=(PROTOCOL=tcp)(HOST=atif.localdomain)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.29.71)(PORT=1521))
# (ADDRESS=(PROTOCOL=tcp)(HOST=atif)(PORT=1521))
# (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
# SID_LIST_<lsnr>
# List of services the listener knows about and can connect
# clients to. There is no default. See the Net8 Administrator's
# Guide for more information.
#
SID_LIST_testcdb=
(SID_LIST=
(SID_DESC=
# #BEQUEATH CONFIG
# (GLOBAL_DBNAME=salesdb.mycompany)
(SID_NAME=testcdb)
(ORACLE_HOME=/f01/app/oracle/product/19.3)
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
# )
)
)
STARTUP_WAIT_TIME_testcdb=0
CONNECT_TIMEOUT_testcdb=10
TRACE_LEVEL_testcdb=OFF
LOG_DIRECTORY_testcdb=/f01/app/oracle/product/19.3/network/admin
LOG_FILE_testcdb=testcdb
TRACE_DIRECTORY_testcdb=/f01/app/oracle/product/19.3/network/admin
TRACE_FILE_testcdb=testcdb
ADMIN_RESTRICTIONS_testcdb=ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_testcdb=OFF
Tnsnames.ora
testcdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.71)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = atif)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testcdb)
# (INSTANCE_NAME = testcdb)
)
)
testpdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.71)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = atif)(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testpdb1)
# (INSTANCE_NAME = testcdb)
)
)
You MUST use the PDB service name when connecting to a multi-tenanted pluggable database.
The SID will also be used to go back to a Container Database.
Change your radio button to 'Service Name' from 'SID'