4 Replies Latest reply on Mar 29, 2018 12:17 PM by EdStevens

    Req'ts for setting up SQL Dev connections to a PDB

    6String

      SLES 11 x86-64, Oracle EE 12.1.0.2

       

      I have a container with a couple of PDBs in it.  They are open read write.

       

      Can someone tell me how I setup the connectivity side of things so I can connect from a remote client, specifically SQL Developer?

       

      Do I need to add an entry to listener.ora?

       

      How can I make SQL Developer connect directly to the PDB?

       

      Thanks for your time,

        • 1. Re: Req'ts for setting up SQL Dev connections to a PDB

          1. use a basic connection

          2. use SERVICE_NAME and not SID

          3. make sure the PDB is registered with the listener

           

          If you need more help than that then SHOW US:

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

          • 2. Re: Req'ts for setting up SQL Dev connections to a PDB
            6String

            rp0428 thanks for the reply.

             

            I did what you specified and got ORA-12514.  This is exactly what I am doing.

             

            1) At the OS, SID=CDB name.

            2) Start sql*plus and issue "alter session set container=<PDB name>"; it responds, "Session altered."

            3) issue "alter system register"; it responds, "System altered."

            4) exit SQL*Plus.

            5) issue, "lsnrctl status listener", neither the SID for the CDB or the PDB appear in the list of service handlers.

            6) Go to SQL Developer and use basic connection.

              a) Hostname=my server (assume that the specific values I'm supplying have no typos and are correct)

              b) Port=1521

              c) SID field is blank and unselected

              d) Service Name field is PDB name

             

            Click, "Test".  SQL Developer gives error, "Listener refused connection with the following: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor."

             

            This is an 11.2 listener, by the way.  Do I need to use a 12.1 listener?  Listener.ora has no entry for the CDB or PDB.

             

            Humbly,

            A multitenant rookie

            • 3. Re: Req'ts for setting up SQL Dev connections to a PDB

              2) Start sql*plus and issue "alter session set container=<PDB name>"; it responds, "Session altered."

              That isn't even possible. You first have to create a connection before you can issue any commands

               

              5) issue, "lsnrctl status listener", neither the SID for the CDB or the PDB appear in the list of service handlers.

              Then you can't connect using the listener if they aren't registered with the listener.

               

              The above (#2 and #5) are EXACTLY why I ask you to SHOW US, not tell us:

               

              1. WHAT you do

              2. HOW you do it

              3. WHAT results you get

               

              You don't need entries in the ORA file - the DBs will register themselves.

               

              You are either checking/using the WRONG listener or the DBs are NOT registered. Until they are registered there isn't any point in trying to connect.

              • 4. Re: Req'ts for setting up SQL Dev connections to a PDB
                EdStevens

                6String wrote:

                 

                 

                This is an 11.2 listener, by the way. Do I need to use a 12.1 listener? Listener.ora has no entry for the CDB or PDB.

                 

                Humbly,

                A multitenant rookie

                You should always use the listener from the highest version.  The 12.1 listener will handle the 11.2 databases.  When I'm planning an upgrade, I always start running my one, single listener from the higher version home well ahead of actually upgrading the databases.