Unable to connect to Oracle user in pluggable database

Question

for some reason, i am unable to connect with any user that I create. their account status is also unlocked.

I want to mention one thing; the pdbs are clone of the main pdb. (as shown by their name below). Only adding this for additional info.

//Edit: i was testing, and I fount out the common user is able to authenticate/connect. Don't know why local user is unable to connect/

    [oracle@oracle pdb1]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 5 19:44:16 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
SQL> ALTER SESSION SET CONTAINER = PDB1;

Session altered.

SQL> create user test1 identified by test1;

User created.

SQL> GRANT CREATE SESSION TO test1;

Grant succeeded.

SQL> connect test1/test1;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

---

    SQL> ALTER SESSION SET CONTAINER = PDB1;

Session altered.

SQL> select name from v$database;

NAME
---------
ORCLDB



SQL> ALTER SESSION SET CONTAINER = PDB2;

Session altered.

SQL>  select name from v$database;

NAME
---------
ORCLDB

// New Edit - Listener Status -

SQL> !lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-SEP-2023 17:14:56

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                06-SEP-2023 17:14:18
Uptime                    0 days 0 hr. 0 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/19.0.0/db_1/admin/orcldb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "0053f52b9a9509e9e06562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "005414da85570a0ae06562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "fa038b2801752bdae05562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcldb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Answer 1

-- without specifying service_name, you will connect to the CDB (root con)
SQL>connect u/p<NOTHING_HERE>


-- the service_name 'PDB1' will be made available when pdb is created
SQL>!lsnrctl status
SQL>connect u/p@PDB1

Best of luck!

Answer 2

1. from the output of listener-status above, I see that PDB1 is a known service for you listener. means a connect: sqlplus uer/pwd@PDB1 'should' work.
2. You have configuered security, using a wallet. Are you sure, the configuration is done well?
3. v$database gives you the name of the CDB (not the PDB). simply use: show con_name
4. if you like to connect to the PDB without using the syntax "...@PDB1" you need to set additionally the variable: export ORACLE_PDB_SID=PDB1 (this does not work on Windows) Be careful with that. You will not see automatically to which PDB you are connected. You can set an SQL-prompt, but this will only show you the CDB. SQL> set sqlprompt "&&_CONNECT_IDENTIFIER SQL> "

Hope this helps. Please rate my answer and mark the question as "answered" when it is ok for you.

b.r. Dieter