Friday, October 30, 2020

Pluggable database opened with RESTRICTED SESSION due to Sync PDB failed with ORA-65177 during 'alter user'

 When open pluggable database, got following error
SQL> alter pluggable database ORCL open;

Warning: PDB altered with errors.

SQL> 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE YES

The pluggable databse ORCL is opened with RESTRICTED SESSION. Check the view pdb_plug_in_violations for error details,
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> col name for a10
SQL> col time for a18
SQL> col cause for a15
SQL> col message for a75
SQL> set linesize 255
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';

Session altered.

SQL> select time,name,type,cause,status,message from pdb_plug_in_violations where status <>'RESOLVED';

TIME               NAME       TYPE      CAUSE           STATUS    MESSAGE
------------------ ---------- --------- --------------- --------- ---------------------------------------------------------------------------
30-oct-20 12:00:52 ORCL       ERROR     Sync Failure    PENDING   Sync PDB failed with ORA-65177 during 'alter user ABCDE_APP account unlock'

When the pdb ORCL is being opened, it tries to synchronize the sql "alter user ABCDE_APP account unlock" between pdb and cdb$root, because the sql was run in cdb$root but not in pdb. Check the status of user ABCDE_APP
SQL> col username for a10
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';

    CON_ID USERNAME   ACCOUNT_STATUS     CREATED   COM
---------- ---------- ------------------ --------- ---
         1 ABCDE_APP  OPEN               11-JUN-19 YES

SQL> alter session set container=ORCL;

Session altered.

SQL>
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';

    CON_ID USERNAME   ACCOUNT_STATUS    CREATED   COM
---------- ---------- ----------------- --------- ---
         3 ABCDE_APP  OPEN              04-AUG-20 NO


The user ABCDE_APP was created as common user in root container, but in pdb user ABCDE_APP was created as local user. Both user are using same user name, but are totally different user account in the database. When DBA run sql 'alter user ABCDE_APP account unlock' in root container, the database tries to sync the sql with all pdbs, but the common user cannot be synced in pdb ORCL because the user ABCDE_APP is not the same user.

AS a workaround, the issue can be temporarily solved by deleting the sync-pending statement (alter user ABCDE_APP account unlock) from PDB_SYNC$ in both CDB and PDB 

In CDB root container,
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 ABCDE_APP account unlock%';

CTIME              SQLSTMT                                  NAME            FLAGS      OPCODE     REPLAY#
------------------ ---------------------------------------- ---------- ---------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock      ABCDE_APP           0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user ABCDE_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 ABCDE_APP account unlock%';

CTIME              SQLSTMT                                  NAME         FLAGS      OPCOD      EREPLAY#
------------------ ---------------------------------------- ---------- ------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock      ABCDE_APP        0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like 'alter user ABCDE_APP account unlock%';

1 row deleted.

SQL> commit;

Commit complete.

Re-open the pdb
SQL> alter pluggable database ORCL close;

Pluggable database altered.

SQL> alter pluggable database ORCL open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO

The pdb is open without RESTRICT SESSION. However, the issue may happen again when you run 'alter user ABCDE_APP' statement in root container. The permanent solution is that drop local user from pdb and grant common user access to the pdb.

3 comments:

Anonymous said...

Thanks a lot !!!

I deleted all plsql's(~10) with same user from Your select and my PDB opened without restricted mode:
select ctime,sqlstmt,name,flags,opcode,replay#;




Elroi said...
This comment has been removed by the author.
Grace said...

Thanks you saved my life!!