Application is unable to connect the database due to restricted session.
sql> show pdbs;
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> col name for a10
SQL> col time for a10
SQL> col cause for a15
SQL> col message for a55
SQL> set linesize 205
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';
SQL> select time,name,type,cause,status,message from pdb_plug_in_violations where status <>'RESOLVED';
SQL> col username for a10
SQL> select con_id,username,account_status,created,common from cdb_users where username='DMN_APP';
CON_ID USERNAME ACCOUNT_STATUS CREATED COM
---------- ---------- ------------------ --------- ---
1 DMN_APP OPEN 15-JUN-24 YES
SQL> alter session set container=ORCL;
Session altered.
SQL>
SQL>select con_id,username,account_status,created,common from cdb_users where username='DMN_APP';
CON_ID USERNAME ACCOUNT_STATUS CREATED COM
---------- ---------- ----------------- --------- ---
3 DMN_APP OPEN 04-AUG-24 NO
When the pdb ORCL is being ipened it tries to synchronize the sql between pdb and codb$root .
user was created as common user in root container where as it creates as local user in pdb level with the same name.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> col name for a10
SQL> col sqlstmt for a40
SQL>
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
Session altered.
SQL>
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user DMN_APP account unlock%';
CTIME SQLSTMT NAME FLAGS OPCODE REPLAY#
------------------ ---------------------------------------- ---------- ---------- ---------- ----------
30-oct-24 12:20:32 alter user DMN_APP account unlock DMN_APP 0 5 21
SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user DMN_APP account unlock%');
1 row deleted.
SQL> commit;
Commit complete.
In pdb ORCL
SQL> alter session set container=ORCL;
Session altered.
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user DMN_APP account unlock%';
CTIME SQLSTMT NAME FLAGS OPCOD EREPLAY#
------------------ ---------------------------------------- ---------- ------- ---------- ----------
30-oct-24 12:28:42 alter user DMN_APP account unlock DMN_APP 0 5 21
SQL> delete from PDB_SYNC$ where sqlstmt like 'alter user DMN_APPaccount unlock%';
1 row deleted.
SQL> commit;
Commit complete.
reopen the pdb:
alter pluggable database orcl close;
alter pluggable database orcl open;
pdb has open without restrict session , it can appear again . better to drop the local user from pdb.
