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 offAfter 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.
Comments