Home » RDBMS Server » Server Administration » How to fix XDB (12c, 19c and possible 21c)
How to fix XDB [message #689943] Wed, 07 August 2024 09:37
Olexandr Siroklyn
Messages: 41
Registered: September 2018
Location: USA
Member
Not quite sure if this is a right place for the how-to note below. If the forum administrators find a better place, please move it there.

So, if your database upgrade fails, or it's just a rainy day outside, it's possible that you'll get INVALID status of an Oracle XML Database (XDB) component. Without the valid XDB, all other components will get invalid status and your database will be out of order. What to do? Restoring the database from a backup can be very time-consuming way. Also 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. The steps below are applied to each PDB and CDB in the case of a multi-tenant database. A 21c case hasn't been tested. Total duration of all steps is 1 hour about. 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.

[Updated on: Wed, 07 August 2024 10:51]

Report message to a moderator

Previous Topic: What is the function of the folders rdbms and tnslsnr on Oracle database
Goto Forum:
  


Current Time: Mon Sep 09 13:41:08 CDT 2024