5 Replies Latest reply on Jan 6, 2020 9:16 PM by EdStevens

    User connection to PDB -18C

    851Pioneer

      Hi all,

      Sorry for this newb question. I have loaded 18C XE and want to connect to XEPDB1 via SQL Developer. I can connect to the CDB and then alter the session to get to XEPDB1, but I would like to create a connection that takes me straight to it with a user that works only in XEPDB1.

      I created user "Mary" while container = XEPDB1, but I do not see the user Mary in the Other Users list. When I try to create a connection for user Mary, I get an invalid user, password error.  Below are my LISTENER and TNSNAMES.ORA Files. I'm obviously not a DBA and am lost in the documentation and would really appreciate your help. Thanks so much!

       

       

       

       

      DEFAULT_SERVICE_LISTENER = XE

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = C:\18C\dbhomeXE)

            (PROGRAM = extproc)

            (ENVS = "EXTPROC_DLLS=ONLY:C:\18C\dbhomeXE\bin\oraclr18.dll")

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.126)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

        )

       

       

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.126)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

            (SID = XE)

          )

        )

       

      LISTENER_XE =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.126)(PORT = 1521))

       

      XEPDB1 =

        (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.126)(PORT = 1521))

            (CONNECT_DATA =

          (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

       

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

        • 1. Re: User connection to PDB -18C
          thatJeffSmith-Oracle

          Your service name looks wrong, it should probably be xepdb1

          1 person found this helpful
          • 2. Re: User connection to PDB -18C
            BPeaslandDBA

            I agree with Jeff. The service name you are connecting to is for the CDB. And the user MARY does not exist in the CDB. This is why you are getting the ORA-1017 error message.

             

            In Multitenant, all PDB's get a service name for just that PDB. By default, the PDB service name matches the PDB name. But you are free to create other services for that PDB as well. But for now, just change the service name to match the PDB name and I bet you get connected.

             

            Cheers,

            Brian

            1 person found this helpful
            • 3. Re: User connection to PDB -18C
              851Pioneer

              YES! Thank you both! I did that and logged out of everything and back in and Mary was there as a user. But I still couldn't create a connection, because Mary's password was null. So, using sys,  I altered the user and gave Mary a password, then made a connection with that, and when I checked the container name it was correct.  Now all I have to do is migrate my 11g database off my dying laptop. Thanks again.

              1 person found this helpful
              • 4. Re: User connection to PDB -18C
                thatJeffSmith-Oracle

                Look at the Data Pump Export and Import wizards in the DBA panel.

                 

                Then move the DMP file from your old laptop to your new laptop - should be fairly simple

                • 5. Re: User connection to PDB -18C
                  EdStevens

                  851Pioneer wrote:

                   

                  YES! Thank you both! I did that and logged out of everything and back in and Mary was there as a user. But I still couldn't create a connection, because Mary's password was null. So, using sys, I altered the user and gave Mary a password, then made a connection with that, and when I checked the container name it was correct. Now all I have to do is migrate my 11g database off my dying laptop. Thanks again.

                  And as a side observation, since your SQL Dev connection definition specifies a connection type of 'basic',  SQL Dev is using its build-in jdbc thin client to connect. That means it is NOT using tnsnames.ora to resolve connect information.  That said, in this case there was still value in showing the contents - not because SQL Dev was using it (it wasn't) but in that it gave a clue as to what information you should have provided to SQL Dev.

                   

                  Now, if you had specified a connection type of 'TNS', then SQL Dev would use tnsnames.ora, because it would be calling the normal tns stack.