Understanding the Issue

When a PDB opens in restricted mode, it restricts access to only users with the RESTRICTED SESSION privilege.

This typically happens due to synchronization failures between the Container Database (CDB) and the PDB.

The errors ORA-01035 and ORA-00600 are common indicators of such issues.

Common Symptoms

• ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
• ORA-00600: internal error code, arguments: [kpdbGetReplayCount: no pdb_sync$ row]
• PDB remains in RESTRICTED mode even after attempting standard connection procedures.

Root Causes

Several factors can lead to the PDB entering restricted mode:

• Deletion of a common user
• Presence of duplicate local users in both CDB and PDB
• Issues during the creation or duplication of a PDB

Step-by-Step Solutions

To effectively resolve the restricted mode issue, follow these two main tasks. Start with Task 1, and if the problem persists, proceed to Task 2.

Task 1: Resolving ORA-01035 Error

This task addresses the ORA-01035 error by removing offending statements from the PDB_SYNC$ table in both CDB and PDB.

Steps to Follow:

1. Connect to CDB Root:
   sqlplus / as sysdba
2. Backup the PDB_SYNC$ Table:
   CREATE TABLE BKPPDB_SYNC$ AS SELECT * FROM PDB_SYNC$;
3. Delete Offending Rows:
   DELETE FROM PDB_SYNC$ WHERE SQLSTMT LIKE ('%alter user "xxx"%');
4. Commit the Changes:
   COMMIT;
5. Repeat the Process in PDB:
   • Set the container to PDB:
     ALTER SESSION SET CONTAINER=PDB;
   • Backup and delete offending rows as above.
   • Close and reopen the PDB:
     
ALTER PLUGGABLE DATABASE PDB CLOSE;
ALTER PLUGGABLE DATABASE PDB OPEN;


After completing Task 1, verify if the PDB is still in restricted mode. If it remains restricted, proceed to Task 2.

Task 2: Addressing ORA-00600 Error

Task 2 focuses on resolving the ORA-00600 error by ensuring the PDB_SYNC$ table contains the necessary rows.

Steps to Follow:

1. Start the Database Normally:
   sqlplus / as sysdba
2. Enable System App:
   ALTER SYSTEM SET "_enable_system_app"=1;
3. Attempt to Open the PDB:
   ALTER PLUGGABLE DATABASE <pdb> OPEN;
4. If Opening Fails:
   • Discard the PDB state:
     
ALTER PLUGGABLE DATABASE <pdb> DISCARD STATE;
SHUTDOWN IMMEDIATE;
STARTUP;

5. Insert Missing Rows:
   INSERT INTO pdb_sync$(scnwrp, scnbas, ctime, name, opcode, flags, replay#) VALUES (0, 0, SYSDATE, 'PDB$LASTREPLAY', -1, 0, 0);
6. Commit and Reopen:
   
COMMIT;
ALTER PLUGGABLE DATABASE <pdb> CLOSE;
ALTER SYSTEM SET "_enable_system_app"=2;
ALTER PLUGGABLE DATABASE <pdb> OPEN;


Once Task 2 is completed, your PDB should open without being restricted.

If it remains in restricted mode, review the PDB_PLUG_IN_VIOLATIONS table and follow additional steps to remove any remaining violations.

Conclusion

Encountering a PDB in restricted mode can be challenging, but by systematically following the steps outlined in Task 1 and Task 2, you can effectively resolve the underlying issues.

Always ensure to backup critical tables like PDB_SYNC$ before making changes, and consult Oracle Support documents for additional guidance.