Restircted session due to sync filed with ora-65177

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.