top of page
Search

Datapatch failed because SDO became invalid and PLS-00905: object MDSYS.SDO_UTIL is invalid reported

As I was running DATAPATCH after applying the Jul23 Oracle DB patches, it got failed saying the the SDO component became invalid.


Patch 35320081 apply (pdb ANWPROD): WITH ERRORS.....

The below errors are captured in the log file. /u01/app/oracle/cfgtoollogs/sqlpatch/35320081/25314491/35320081_apply_EBSDB_ANWPROD_2023Aug20_07_21_50.log (errors) -> Error at line 81403: script md/admin/sdoutlh.sql - ORA-00604: error occurred at recursive SQL level 1 - ORA-00001: unique constraint (SYS.I_IDL_SB41) violated -> Error at line 81416: script md/admin/sdoutlh.sql - ORA-04042: procedure, function, package, or package body does not exist -> Error at line 81470: script md/admin/sdoutlh.sql - ORA-00604: error occurred at recursive SQL level 1 - ORA-00001: unique constraint (SYS.I_IDL_SB41) violated -> Error at line 81483: script md/admin/sdoutlh.sql - ORA-04042: procedure, function, package, or package body does not exist -> Error at line 82548: script md/admin/sdoutlh.sql - Warning: Package created with compilation errors. -> Error at line 82556: script md/admin/sdoutlh.sql - 0/0 PL/SQL: Compilation unit analysis terminated -> Error at line 82557: script md/admin/sdoutlh.sql - 189/9 PLS-00201: identifier 'MDSYS.VERTEX_SET_TYPE' must be declared -> Error at line 83278: script md/admin/prvtgmd.plb - Warning: Trigger created with compilation errors. -> Error at line 83285: script md/admin/prvtgmd.plb - 16/3 PL/SQL: Statement ignored -> Error at line 83286: script md/admin/prvtgmd.plb - 16/8 PLS-00905: object MDSYS.SDO_UTIL is invalid -> Error at line 83287: script md/admin/prvtgmd.plb - 27/3 PL/SQL: Statement ignored -> Error at line 83288: script md/admin/prvtgmd.plb - 33/11 PLS-00905: object MDSYS.SDO_UTIL is invalid -> Error at line 83369: script md/admin/prvtgmd.plb - Warning: Trigger created with compilation errors. -> Error at line 83376: script md/admin/prvtgmd.plb - 8/3 PL/SQL: Statement ignored

Below is the component status at CDB Level:

COMP_ID    COMP_NAME                             VERSION    STATUS
---------- ------------------------------------- ---------- ---------------
CATALOG    Oracle Database Catalog Views         19.0.0.0.0 VALID
CATPROC    Oracle Database Packages and Types    19.0.0.0.0 VALID
JAVAVM     JServer JAVA Virtual Machine          19.0.0.0.0 VALID
ORDIM      Oracle Multimedia                     19.0.0.0.0 VALID
SDO        Spatial                               19.0.0.0.0 VALID
XDB        Oracle XML Database                   19.0.0.0.0 VALID
6 rows selected.

Below is the status at the PDB level:

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANWPROD                        READ WRITE YES
SQL> alter session set container="ANWPROD";
Session altered.
SQL> set pages 200
col comp_id format A10
col comp_name format A37
col version like comp_id
col status for a15
select comp_id, comp_name, version, status from dba_registry
where comp_id in ('CATALOG','CATPROC','JAVAVM','ORDIM','XDB','SDO')
order by 1SQL> SQL> SQL> SQL> SQL>   2    3
  4  /
COMP_ID    COMP_NAME                             VERSION    STATUS
---------- ------------------------------------- ---------- ---------------
CATALOG    Oracle Database Catalog Views         19.0.0.0.0 VALID
CATPROC    Oracle Database Packages and Types    19.0.0.0.0 VALID
JAVAVM     JServer JAVA Virtual Machine          19.0.0.0.0 VALID
ORDIM      Oracle Multimedia                     19.0.0.0.0 VALID
SDO        Spatial                               19.0.0.0.0 INVALID
XDB        Oracle XML Database                   19.0.0.0.0 VALID
6 rows selected.

Followed the below resolution to resolve the issue

Please run below in PDB:-
-- Reinstall Spatial
-- Deinstall Spatial
connect / as sysdba
alter session set current_schema="MDSYS";
@?/md/admin/deinssdo.sql
drop user MDSYS cascade;
-- Reinstall Spatial on 19
connect / as sysdba
spool spatial_installation.lst
set echo on
@?/md/admin/mdinst.sql
@?/md/admin/mdprivs.sql
Then utlrp.sql
spool off

After the spatial component became valid, reran datapatch and it was successful.


The possible reason that caused the issue as per the log

"PLS-00905: object MDSYS.SDO_UTIL is invalid" typically indicates a problem in an Oracle database environment. It suggests that the referenced object, in this case, 'MDSYS.SDO_UTIL,' is not in a valid state for usage. This could be due to various reasons such as compilation errors, permissions, or conflicts within the database. To resolve this issue, a thorough examination of the object's status and associated dependencies is essential, followed by appropriate corrective actions to restore its validity and ensure the proper functioning of the Oracle database system.

 

We are providing training on Oracle Database/Oracle E-Business Suite, feel free to reach out on WhatsApp at +91 8985030907.


20 views0 comments

Recent Posts

See All

Commentaires


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page