Unable to connect to pdb from sql developer while cdb is getting connected Oracle 19c

1.6k Views Asked by At

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

enter image description here

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

There are 1 best solutions below

3
On

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'