5 Replies Latest reply on Jan 5, 2016 5:08 PM by rp0428

    Unable to connect PDB from SQL Developer

    Md. Budrul Hasan Bhiuyan

      Hi,

       

      I am unable to connect to my PDB1 from SQL Developer.

       

      oracle@solaris:~$ lsnrctl status

      LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 04-JAN-2016 13:50:37

      Copyright (c) 1991, 2014, Oracle.  All rights reserved.

      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

      STATUS of the LISTENER

      ------------------------

      Alias                     LISTENER

      Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 - Production

      Start Date                04-JAN-2016 10:57:08

      Uptime                    0 days 2 hr. 53 min. 29 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   /u01/12.1.0/grid/network/admin/listener.ora

      Listener Log File         /u01/app/grid/diag/tnslsnr/solaris/listener/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris)(PORT=1521)))

        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

      Services Summary...

      Service "+ASM" has 1 instance(s).

        Instance "+ASM", status READY, has 1 handler(s) for this service...

      Service "cdb01" has 1 instance(s).

        Instance "cdb01", status READY, has 1 handler(s) for this service...

      Service "cdb01XDB" has 1 instance(s).

        Instance "cdb01", status READY, has 1 handler(s) for this service...

      Service "pdb1" has 1 instance(s).

        Instance "cdb01", status READY, has 1 handler(s) for this service...

      The command completed successfully

      oracle@solaris:~$

       

      On SQL Developer:

      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 type is Basic Role is SYSDBA

       

       

      But I can connect to my CBD01 from SQL Developer.

       

      //

      Thanks

      Budrul

        • 1. Re: Unable to connect PDB from SQL Developer
          thatJeffSmith-Oracle

          you need to specify the PDB service, not the SID. the SID will always refer to the container

          • 2. Re: Unable to connect PDB from SQL Developer

            On SQL Developer:

            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

            As another responder said Oracle 12c requires SERVICE_NAME and not SID to connect to PDBs.

            http://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html

            What is a Container Database

            So, what is the big advantage in this setup, you may ask? Couldn't we just have created multiple plain vanilla databases instead of multiple PDBs? Yes, we could have; but then each of these plain "vanilla" databases would have has its own instance (processes and memory) overhead. PDBs do not have an instance associated with them, eliminating this overhead. Let's examine the setup with an example. Suppose you have a CDB (container database - the real database that houses other PDBs) called CONA which has a PDB calledPDB1. If you check the Oracle instance, you will see that there is only one - that of the CDB. Let's check the ORACLE_SID first:

            [oracle@prosrv1 ~]$ echo $ORACLE_SID CONA 

            That's it. There is just one SID; not one for each PDB. Next, let's check for the processes, specifically the very important one known as "pmon":

            See that last sentence above? See this line in the list of listener status that you posted?

            Service "pdb1" has 1 instance(s).

            PDB1 has a 'service' - use a SERVICE_NAME to connect to it and any other PDBs.

            • 3. Re: Unable to connect PDB from SQL Developer
              Md. Budrul Hasan Bhiuyan

              Hi,

               

              Do I need to add service_name=pdb1 to my client tnsname.ora file.

               

              Thanks

              Budrul

              • 4. Re: Unable to connect PDB from SQL Developer
                Gary Graham-Oracle

                If your connection details specify Connection Type: Basic, then you do not need to to add anything to the client tnsnames.ora.

                 

                If you wish to use Connection Type: TNS, however, then you need to add an entry for the PDB to the client tnsnames.ora and make sure SQL Developer can find it by setting the TNS Directory preference under Tools > Preferences > Database > Advanced.  For example, for the PDB and container DB, respectively, I use entries similar to...

                ADC122PB =

                  (DESCRIPTION =

                    (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = adc00xxx.us.oracle.com)(PORT = 1521))

                    )

                    (CONNECT_DATA =

                      (SERVICE_NAME = pdborcl.us.oracle.com)

                    )

                  )

                 

                ADC122  =

                  (DESCRIPTION =

                    (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = adc00xxx.us.oracle.com)(PORT = 1521))

                    )

                    (CONNECT_DATA =

                      (SERVICE_NAME = ora122.us.oracle.com)

                    )

                  )

                • 5. Re: Unable to connect PDB from SQL Developer

                  Do I need to add service_name=pdb1 to my client tnsname.ora file.

                  Look at the screenshot you posted.

                   

                  See the radio button for 'SID' and the pdb1 name you put there?

                   

                  See the radio button for 'Service Name'?

                   

                  You need to use 'Service Name'.

                  1 person found this helpful