Sample Header Ad - 728x90

Unable to connect to Oracle user in pluggable database

0 votes
2 answers
1321 views
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
Asked by dbafix (64 rep)
Sep 5, 2023, 11:53 PM
Last activity: Sep 9, 2023, 10:24 AM