Resolving Pluggable Database (PDB) Opening in RESTRICTED MODE (ORA-65177) in Oracle 19c

Introduction

Encountering, ORA-65177 ,a Pluggable Database (PDB) that opens in RESTRICTED mode can cause operational challenges, especially when working in a multitenant environment. This issue is often linked to synchronization failures between the Container Database (CDB) and its associated PDBs.

We will delve into the root cause of the problem and provide a step-by-step resolution.

Identifying the Problem

When attempting to open a PDB, You might have received the following warning.:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;
Warning: PDB altered with errors.

To check the current status of your PDBs, use:

The RESTRICTED column showing YES indicates that the database is not fully available for normal operations.

To identify the root cause, query the PDB_PLUG_IN_VIOLATIONS view:

SQL> col name for a10
SQL> col time for a18
SQL> col cause for a15
SQL> col message for a80
SQL> col status for a12
SQL> set line 300
SQL> SELECT TIME, 
            NAME, 
            TYPE, 
            CAUSE, 
            STATUS, 
            MESSAGE
       FROM PDB_PLUG_IN_VIOLATIONS
      WHERE STATUS <> 'RESOLVED';

The output you expect is like here:

TIME                NAME   TYPE  CAUSE         STATUS   MESSAGE
------------------- ------ ----- ------------- -------- ----------------------------------------------
30-JAN-25 12:45:10 PDB1   ERROR Sync Failure  PENDING  Sync PDB failed with ORA-65177 during 'ALTER USER DATAVERSE ACCOUNT UNLOCK'

Understanding the Root Cause

At the time of opening a PDB, Oracle does perform a dictionary check for common objects. This leads to the check and sync for common user as well. It tries to sync whatever is not found in PDB. However, The failure of this sync process occurs when a PDB has user with same name as common user. That is why when an ALTER USER command is executed in the CDB cannot be applied to the PDB due to this common user already found in PDB but with different entity. In this case, the user DATAVERSE exists in both the root container and the PDB, but as separate entities. At root, it is exists as common user but at PDB it is exists as PDB user. Since PDBs manage their own users independently (unless they are common users prefixed with C##), synchronization fails.

Solution

To resolve this issue, the pending synchronization command must be removed from the PDB_SYNC$ table in both the CDB and the affected PDB.

Step 1: Connect to the CDB Root Container

SQL> SHOW CON_NAME;
CON_NAME
------------------------------
CDB$ROOT

Step 2: Identify Pending Synchronization Statements

SQL> col name for a10
SQL> col sqlstmt for a80
SQL> set line 300
SQL> SELECT CTIME, 
            SQLSTMT, 
            NAME, 
            FLAGS, 
            OPCODE, 
            REPLAY#
     FROM PDB_SYNC$
     WHERE SQLSTMT LIKE 'alter user dataverse %';

Example output:

CTIME               SQLSTMT                            NAME      FLAGS OPCODE REPLAY#
------------------  --------------------------------  --------  ----- ------ -------
30-JAN-25 11:30:22 ALTER USER DATAVERSE ACCOUNT UNLOCK APP_USER  0     5      45

Step 3: Delete the Pending Synchronization Entry in CDB

SQL> DELETE FROM PDB_SYNC$
     WHERE SQLSTMT LIKE 'ALTER USER DATAVERSE%';

SQL> COMMIT;

Step 4: Switch to the Affected PDB

SQL> ALTER SESSION SET CONTAINER=PDB1;
Session altered.

Step 5: Repeat Steps 2 and 3 in the PDB

SQL> SQL> col name for a10
SQL> sqlstmt for a80
SQL> set line 300
SQL> SELECT CTIME, 
            SQLSTMT, 
            NAME, 
            FLAGS, 
            OPCODE, 
            REPLAY#
     FROM PDB_SYNC$
     WHERE SQLSTMT LIKE 'ALTER USER DATAVERSE %';

CTIME               SQLSTMT                            NAME      FLAGS OPCODE REPLAY#
------------------  --------------------------------  --------  ----- ------ -------
30-JAN-25 11:30:22 ALTER USER DATAVERSE ACCOUNT UNLOCK APP_USER  0     5      45


SQL> DELETE FROM PDB_SYNC$
     WHERE SQLSTMT LIKE 'ALTER USER DATAVERSE ACCOUNT UNLOCK%';

SQL> COMMIT;

Step 6: Close and Reopen the PDB

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;
Pluggable database altered.

Step 7: Verify the PDB Status

SQL> SHOW PDBS;

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

If RESTRICTED is now NO, the issue has been resolved.

Lesion Learned

To avoid similar issues in the future:

1. Use Common Users (C## Prefix): If a user needs to exist across all PDBs, create it as a common user. CREATE USER C##APP_USER IDENTIFIED BY password CONTAINER=ALL;
2. Apply Changes in the Correct Context: Ensure user management commands are executed in the intended container to prevent unintended propagation.
3. Monitor Plugin Violations Regularly: SELECT * FROM PDB_PLUG_IN_VIOLATIONS WHERE STATUS <> 'RESOLVED';

Conclusion

By diagnosing and resolving a pluggable database being stuck in RESTRICTED mode due to failed user synchronization, database administrators can ensure seamless multitenant operations. Careful management of common and local users, combined with regular monitoring of database violations, helps prevent and swiftly address such issues within an Oracle Database environment.