19C : Pluggable database in restricted mode due to datapatch failure

=> One of our DBA has applied latest CPU patches to database and ran datapatch, data patch completed with errors, without looking at log files he restarted the database, then the SEED PDB and PROD PDB database went into restricted mode.

=> After looking at datapatch logfile ($ORACLE_HOME/cfgtoollogs/sqlpatch/sqlpatch_#######/sqlpatch_invocation.log) noticed that SYSAUX tablespace is full and due to which data patch failed.

=> In PDB plugin violations table, it reported that datapatch failed for CPU patches and suggested to rerun datapatch.

=> We have added space to SYSAUX tablespace and ran datapatch again, but it failed since SEED PDB is also in restricted mode

CON_ID CON_NAME OPEN MODE RESTRICTED
------ ---- ------ ----------- -------------
2 PDB$SEED READ ONLY YES

=> To fix the SEED PDB issue we have to open the database in read write mode and compile invalid objects 

=> Check invalid object count

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
        40
SQL>

=> OPEN SEED PDB in read write mode to compile invalid objects

SQL> conn / as sysdba

SQL> alter session set container=PDB$SEED;

SQL> show con_name

SQL> select open_mode from v$database;

SQL> alter session set "_oracle_script"=TRUE;

SQL> alter pluggable database pdb$seed close immediate instances=all;

SQL> select open_mode from v$database;

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

SQL> show con_name;

SQL> select open_mode from v$database;

=> Run utlrp.sql to compile invalids

SQL> @?/rdbms/admin/utlrp.sql

=> Check invalid count

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0

SQL>

=> Close SEED PDB and open in READ ONLY mode, now the restricted mode will be lifted for PDB$SEED

SQL> alter pluggable database pdb$seed close immediate instances=all;

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

SQL> show con_name;

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

SQL> select open_mode from v$database;

SQL> alter session set "_oracle_script"=FALSE;

=> To lift the restricted mode for PROD PDB run datapatch and restart the database. Datapatch completes successfully as the underlying tablespace issue is fixed.