Oracle 12c datapatch puts pdb$seed to restricted

After applying RU (28828733) patch and running datapatch -verbose on two node RAC cluster, which contains 3 database instances, PDB$SEED of one of the databases was on restricted mode. Also, below sys objects in CDB level got invalidated causing the RMAN backups to fail. Querying pdb_plug_in_violations showed datapatch errors and recommended to rerun the datapatch verbos. But datapath re run only updated the same error in pdb_plug_in_violations view and the PDB$SEED remained in restricted mode.

Invalidted sys objects -->

 SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID';  
 OWNER OBJECT_NAME OBJECT_TYPE STATUS  
 -------- -------------------- --------------- --------  
 SYS DBMS_RCVMAN PACKAGE BODY INVALID  
 SYS INITJVMAUX PACKAGE BODY INVALID  
 PUBLIC SDO_VERSION SYNONYM INVALID  
 WMSYS LT_EXPORT_PKG PACKAGE BODY INVALID  
 MDSYS SDO_VERSION FUNCTION INVALID  
 MDSYS SDO_3GL PACKAGE BODY INVALID  
 MDSYS SDO_NETWORK_TRIG_INS TRIGGER INVALID  
 MDSYS MD_NET PACKAGE BODY INVALID  

After contacting support and some digging, we had drop and recreate the registry$sqlpatch table in order to rectify the issue by following steps below.

 01:09:08 SQL> show pdbs;  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            READ ONLY YES  
      3 MOBPDB             READ WRITE NO  
 01:09:48 SQL>  
 01:23:13 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 CDB$ROOT  
 01:30:04 SQL> select name, open_mode from v$database;  
 NAME   OPEN_MODE  
 --------- --------------------  
 MOBCDB  READ WRITE  
 01:30:33 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 CDB$ROOT  
 01:30:47 SQL> show pdbs;  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            READ ONLY YES  
      3 MOBPDB             READ WRITE NO  
 01:30:52 SQL>  
 01:31:04 SQL> alter session set "_oracle_script"=TRUE;  
 Session altered.  
 01:31:06 SQL> alter pluggable database pdb$seed close immediate instances=all;  
 Pluggable database altered.  
 01:31:33 SQL> show pdbs;  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            MOUNTED  
      3 MOBPDB             READ WRITE NO  
 01:32:02 SQL>  
 01:32:19 SQL> alter pluggable database pdb$seed OPEN READ WRITE;  
 Warning: PDB altered with errors.  
 01:32:44 SQL> show pdbs;  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            READ WRITE YES  
      3 MOBPDB             READ WRITE NO  
 01:34:40 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 CDB$ROOT  
 01:34:48 SQL> alter session set container=PDB$SEED;  
 Session altered.  
 01:35:06 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 PDB$SEED  
 01:35:10 SQL>  
 01:35:12 SQL> column owner format a10  
 01:36:13 SQL> column object_type format a15  
 01:36:28 SQL> column object_name format a20  
 01:36:43 SQL> select owner, object_name, object_type from dba_objects where lower(object_name)='registry$sqlpatch';  
 OWNER   OBJECT_NAME     OBJECT_TYPE  
 ---------- -------------------- ---------------  
 SYS    REGISTRY$SQLPATCH  TABLE  
 01:36:56 SQL> create table registry$sqlpatch_PDB as select * from registry$sqlpatch;  
 Table created.  
 01:37:26 SQL> select owner, object_name, object_type from dba_objects where lower(object_name)='registry$sqlpatch_PDB';  
 no rows selected  
 01:38:20 SQL> select owner, object_name, object_type from dba_objects where lower(object_name)='registry$sqlpatch_pdb';  
 OWNER   OBJECT_NAME     OBJECT_TYPE  
 ---------- -------------------- ---------------  
 SYS    REGISTRY$SQLPATCH_PD TABLE  
       B  
 01:38:47 SQL> select count(*) from registry$sqlpatch_pdb;  
  COUNT(*)  
 ----------  
      1  
 01:41:35 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 PDB$SEED  
 01:41:42 SQL> drop table registry$sqlpatch;  
 Table dropped.  
 01:41:55 SQL> !  
 ora12c@racaprdb1:~$ cd $ORACLE_HOME/rdbms/admin  
 ora12c@racaprdb1:/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin$ ls -lrt catsqlreg*  
 -rw-r--r--  1 ora12c  oinstall  4527 Jan 8 2019 catsqlreg.sql  
 ora12c@racaprdb1:/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin$ exit  
 exit  
 01:43:02 SQL> show con_name;  
 CON_NAME  
 ------------------------------  
 PDB$SEED  
 01:43:08 SQL>  
 01:43:10 SQL> @$ORACLE_HOME/rdbms/admin/catsqlreg.sql  
 Session altered.  
 Table created.  
 View created.  
 Synonym created.  
 Grant succeeded.  
 PL/SQL procedure successfully completed.  
 Grant succeeded.  
 Synonym created.  
 PL/SQL procedure successfully completed.  
 Session altered.  
 01:43:51 SQL>  

Then datapatch -verbose was rerun to properly complete the patch apply on pdb$seed.

Related Articles --

• Switching between pluggable database – alter session set container
• Undo Tablespace currently in use during PDB open
• Create new service for PDB using DBMS_SERVICE