PDB is in restricted mode, datapatch puts pdb$seed to restricted mode

Container and Pluggable database 

SYMPTOMS

The PDB$SEED is in restricted mode.

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

When we try to open the PDB$SEED it shows the below error

alter pluggable database pdb$seed OPEN READ WRITE;

Warning: PDB altered with errors.

CAUSE :

select type, time,name,cause,status,message from PDB_PLUG_IN_VIOLATIONS where type = ‘ERROR’ and status != ‘RESOLVED’ order by name, type;

TYPE TIME NAME CAUSE STATUS MESSAGE
——— ————————————————– ——————– ———- ——— ————————————————–
ERROR 22-MAY-21 07.15.22.417417 PM PDB$SEED SQL Patch PENDING 19.10.0.0.0 Release_Update 2101130548: APPLY with status WITH ERRORS in the PDB

SOLUTION:

• conn / as sysdba
• alter session set container=PDB$SEED;
• show con_nameCON_NAME
  ——————————
  PDB$SEED
• select open_mode from v$database;OPEN_MODE
  ——————–
  READ ONLY
• alter session set “_oracle_script”=TRUE;Session altered.
• alter pluggable database pdb$seed close immediate instances=all;Pluggable database altered.
• select open_mode from v$database;OPEN_MODE
  ——————–
  MOUNTED
• alter pluggable database pdb$seed OPEN READ WRITE;Warning: PDB altered with errors.
• select open_mode from v$database;OPEN_MODE
  ——————–
  READ WRITE

Take the backup of registry$sqlpatch

• create table registry$sqlpatch_org_bkp_22may as select * from registry$sqlpatch;Table created.
• show con_nameCON_NAME
  ——————————
  PDB$SEED

Drop the registry$sqlpatch table and re-create

• exec dbms_pdb.exec_as_oracle_script(‘drop table registry$sqlpatch’);PL/SQL procedure successfully completed.
• @$ORACLE_HOME/rdbms/admin/catsqlreg.sqlSession altered.Table created.View created.

  Synonym created.

  Grant succeeded.

  PL/SQL procedure successfully completed.

  Grant succeeded.

  Synonym created.

  Table created.

  View created.

  Synonym created.

  Grant succeeded.

  PL/SQL procedure successfully completed.

  Grant succeeded.

  Synonym created.

  PL/SQL procedure successfully completed.

  Session altered.

re-run datapatch to complete the patch apply on pdb$seed.

• ./datapatch -verbose -pdbs PDB\$SEED
  SQL Patching tool version 19.10.0.0.0 Production on Sat May 22 22:16:47 2021
  Copyright (c) 2012, 2021, Oracle. All rights reserved.Log file for this invocation: /ora19c/app/cfgtoollogs/sqlpatch/sqlpatch_22348456_2021_05_22_22_16_47/sqlpatch_invocation.logConnecting to database…OK
  Gathering database info…doneNote: Datapatch will only apply or rollback SQL fixes for PDBs
  that are in an open state, no patches will be applied to closed PDBs.
  Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
  (Doc ID 1585822.1)

  Bootstrapping registry and package to current versions…done
  Determining current state…done

  Current state of interim SQL patches:
  No interim patches found

  Current state of release update SQL patches:
  Binary registry:
  19.10.0.0.0 Release_Update 210113054859: Installed
  PDB PDB$SEED:
  No release update patches installed

  Adding patches to installation queue and performing prereq checks…done
  Installation queue:
  For the following PDBs: PDB$SEED
  No interim patches need to be rolled back
  Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
  Apply from 19.1.0.0.0 Feature Release to 19.10.0.0.0 Release_Update 210113054859
  No interim patches need to be applied

  Installing patches…

  Patch installation complete. Total patches installed: 1

  Validating logfiles…done
  Patch 32218454 apply (pdb PDB$SEED): SUCCESS
  logfile: /ora19c/app/cfgtoollogs/sqlpatch/32218454/24047657/32218454_apply_HOMBETCN_PDBSEED_2021May22_22_17_21.log (no errors)

  Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: PDB$SEED

  SQL Patching tool complete on Sat May 22 22:23:44 2021

Bounce the database and see the PDB$SEED is in Non-Restricted mode

• SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED
  ———- —————————— ———- ———-
  2 PDB$SEED READ ONLY NO
  3 ORCL MOUNTED