ORDIM – Oracle Multimedia – Invalid after upgrade

ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the catctl.pl completion.

Serial   Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
    Time: 49s

------------------------------------------------------
Phases [0-73]         End Time:[2015_08_08 08:06:45]
------------------------------------------------------

Grand Total Time: 4848s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

 1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
 2. Execute the post upgrade script as described in Chapter 3
    of the Database Upgrade Guide.


REASON:
catuppst.sql unable to run in Database: TESTSX Id: 0
        ERRORS FOUND: during upgrade CATCTL ERROR COUNT=7
------------------------------------------------------
Identifier ORDIM 15-08-08 07:56:26 Script = /oracle/app/product/12.1.0.2/rdbms/admin
ERROR = [ORA-06550: line 76, column 17: PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 76, column 5:
PL/SQL: SQL Statement ignored
]

Multiple insert and update sqls failed for tables in MDSYS schema.At this point, dba_registry showed

SQL> @dba_registry

COMP_ID  SCHEMA       STATUS       VERSION      COMP_NAME
-------- ------------ ------------ ------------ -----------------------------------
APS      SYS          REMOVED      11.1.0.7.0   OLAP Analytic Workspace
CATALOG  SYS          UPGRADED     12.1.0.2.0   Oracle Database Catalog Views
CATJAVA  SYS          VALID        12.1.0.2.0   Oracle Database Java Packages
CATPROC  SYS          INVALID      12.1.0.2.0   Oracle Database Packages and Types
CONTEXT  CTXSYS       VALID        12.1.0.2.0   Oracle Text
JAVAVM   SYS          VALID        12.1.0.2.0   JServer JAVA Virtual Machine
ORDIM    ORDSYS       INVALID      12.1.0.2.0   Oracle Multimedia
OWM      WMSYS        VALID        12.1.0.2.0   Oracle Workspace Manager
RAC      SYS          OPTION OFF   12.1.0.2.0   Oracle Real Application Clusters
SDO      MDSYS        REMOVED      11.1.0.7.0   Spatial
XDB      XDB          VALID        12.1.0.2.0   Oracle XML Database
XML      SYS          VALID        12.1.0.2.0   Oracle XDK
XOQ      SYS          REMOVED      11.1.0.7.0   Oracle OLAP API

Running UTLRP.sql would show below in the end :-

Locator INVALID OBJECTS: CS_SRS_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_DELETE_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_INSERT_TRIGGER – INVALID – TRIGGER

The reason for this is the missing VIEW “MDSYS”.”SDO_COORD_REF_SYSTEM”.

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW MDSYS.SDO_COORD_REF_SYSTEM ("SRID", "COORD_REF_SYS_NAME",
"COORD_REF_SYS_KIND", "COORD_SYS_ID", "DATUM_ID", "GEOG_CRS_DATUM_ID", "SOURCE_GEOG_SRID", "PROJECTION_CONV_ID",
"CMPD_HORIZ_SRID","CMPD_VERT_SRID", "INFORMATION_SOURCE", "DATA_SOURCE", "IS_LEGACY", "LEGACY_CODE", "LEGACY_WKTEXT",
"LEGACY_CS_BOUNDS", "IS_VALID", "SUPPORTS_SDO_GEOMETRY") AS SELECT SRID,COORD_REF_SYS_NAME,COORD_REF_SYS_KIND,COORD_SYS_ID,
DATUM_ID,GEOG_CRS_DATUM_ID,SOURCE_GEOG_SRID,PROJECTION_CONV_ID,CMPD_HORIZ_SRID,CMPD_VERT_SRID,INFORMATION_SOURCE,DATA_SOURCE,
IS_LEGACY,LEGACY_CODE,LEGACY_WKTEXT,LEGACY_CS_BOUNDS,IS_VALID,SUPPORTS_SDO_GEOMETRY FROM MDSYS.SDO_COORD_REF_SYS;

After creating the view, recompile the triggers and run utlrp.

grant SELECT on MDSYS.SDO_COORD_REF_SYSTEM to PUBLIC;

ALTER TRIGGER MDSYS.CS_SRS_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_DELETE_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_INSERT_TRIGGER COMPILE;
show errors

On production, I created the view as part of pre-task and then upgraded the database.The only change in view ddl was I had to remove “NONEDITIONABLE” keyword to create it as compatible parameter was set to 11.2.0.3.

On Production

SQL> @find_obj
Enter value for object_name: SDO_COORD_REF_SYSTEM
old   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('&object_name')
new   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('SDO_COORD_REF_SYSTEM')

OWNER        OBJECT_NAME                OBJECT_ID OBJECT_TYPE        STATUS  CREATED                   LAST_DDL_TIME
------------ ------------------------- ---------- ------------------ ------- ------------------------- -------------------------
PUBLIC       SDO_COORD_REF_SYSTEM        39533538 SYNONYM            VALID   18-08-15 01:30:26         18-08-15 01:30:26
MDSYS        SDO_COORD_REF_SYSTEM        39500022 VIEW               VALID   17-08-15 18:28:03         18-08-15 01:30:26

Advertisement