How to fix XDB

If your database upgrade fails or if it’s just a rainy day outside, it’s possible that an Oracle XML Database (XDB) component will be marked as INVALID. Without a valid XDB, all the other components will also be invalid and your database will be out of order. What should you do? Restoring the database from a backup is a very time-consuming process. Also, the Oracle Primary Note for Oracle XML Database (XDB) Install/Deinstall (Doc ID 1292089.1) only describes steps up to 11g. What about 12c and 19c? There is a kind of hack below. These steps are applied to each PDB and CDB in the case of a multi-tenant database. The 21c case hasn’t been tested. The total duration of all steps is about 1 hour. Proceed at your own risk.

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

and get the list of the objects falling into: ORA-04023: Object ... could not be validated or authorized

b.
Check out XSDDIR directory has correct path. Check out XMLDIR directory has correct path. In case of need recreate the directories via conventional way
or dbms_pdb.exec_as_oracle_script

c.
SQL> alter pluggable database open upgrade;
SQL> @?/rdbms/admin/prvtnoqm.plb <- deinstall XDB, drop XDB user
SQL> delete from SYS.REGISTRY$ERROR where IDENTIFIER='XDB';
SQL> commit;

d.
Delete, by hands, all objects from the ORA-04023: Object ... could not be validated or authorized list. In case if need use dbms_pdb.exec_as_oracle_script.

e.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/catproc.sql <- yes, one more time
SQL> @?/rdbms/admin/utlrp.sql
SQL> alter pluggable database close;

Here XDB should have a valid status and other components should be valid as well.