Cannot connect new users to pluggable database

All we need is an easy explanation of the problem, so here it is.

I’m new to Oracle DB (coming from MS SQL Server).

For a start, I downloaded a Docker container for Oracle Database 18c XE.

When I run that container, after creating a pluggable database, I don’t seem to be able to connect with new users I create:

Cannot connect new users to pluggable database

As far as I can see I provided all necessary bits and bites to get these users at the ready. But what am I missing? I don’t get it, even after reading dozens of doc and help pages.

Here’s the output of lsnrctl status, run in the container:

> lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-MAY-2021 11:57:33

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                25-MAY-2021 11:11:15
Uptime                    0 days 0 hr. 46 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/5854b834e19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5854b834e19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f0bfe62f0aaee053030011ac1ec5" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f7dc51bd0b68e053030011acc9a8" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "mobydick" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

And this is my tnsnames.ora file content:

# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))  

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))    
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
  )



mobydick =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mobydick)
    )
  )

I created the pluggable database similar to this:

ALTER SESSION SET CONTAINER=CDB$ROOT;

CREATE PLUGGABLE DATABASE "mobydick" ADMIN USER "sa" IDENTIFIED BY "abc123" ROLES=(dba) FILE_NAME_CONVERT=('/opt/oracle/product/18c/dbhomeXE/dbs/mobydick/') STORAGE UNLIMITED

ALTER PLUGGABLE DATABASE mobydick OPEN READ WRITE

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is the SQL statement that SQL Developer generated:

CREATE USER "test" IDENTIFIED BY "abc123" ;

This makes the username case-sensitive, so the actual username can be used as "test".

You tried to login in SQL Developer using the username test. This is not case-sensitive syntax, and it is automatically treated as "TEST", but that user does not exist.

Use the case-sensitive syntax for username, so instead of test, provide "test" as username.

By the way, we almost never use case-sensitive usernames or object names. You could just create the user without double quotes, and your connection attempt would work:

CREATE USER test IDENTIFIED BY "abc123" ;

Given that I wanted to connect using a lowercase user name by means of sqlplus, what would I have to enter to connect as "test", for instance? I don’t seem to be able to use quotes in the CONNECT clause.

" is special character in bash shell, you need to escape it to use it in commandline arguments: sqlplus \"test\"/[email protected]. The connect command handles double quotes just fine: sqlplus /nolog then at SQL> prompt, connect "test"/[email protected].

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply