Thursday, April 5, 2012

ORA-00942 during database upgrade from 10.2.0.3 to 11.2.0.1 of Oracle Workspace Manager

Error: ORA-00942 during database upgrade from 10.2.0.3 to 11.2.0.1 of Oracle Workspace Manager

Environment: Solaris, Oracle 10.2.0.3se to 11.2.0.1se.

Got error when running @$ORACLE_HOME/rdbms/admin/utlu112s.sql after the upgrade:

Oracle Workspace Manager
.   ORA-00942: table or view does not exist
.   ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1575
.   ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1592
.   ORA-06512: at line 1
.                      VALID      11.2.0.1.0  00:01:54


Oracle document:
ORA-00942 during database upgrade of Oracle Workspace Manager [ID 1399508.1]

After the upgrade, dba_registry shows OWM is VALID, and there are no INVALID objects related to this component:

COMP_ID      STATUS      VERSION    COMP_NAME
------------ ----------- ---------- ----------------------------------------
CATALOG      VALID       11.2.0.3.0 Oracle Database Catalog Views
CATJAVA      VALID       11.2.0.3.0 Oracle Database Java Packages
CATPROC      VALID       11.2.0.3.0 Oracle Database Packages and Types
CONTEXT      VALID       11.2.0.3.0 Oracle Text
EXF          VALID       11.2.0.3.0 Oracle Expression Filter
JAVAVM       VALID       11.2.0.3.0 JServer JAVA Virtual Machine
ORDIM        VALID       11.2.0.3.0 Oracle Multimedia
OWM          VALID       11.2.0.3.0 Oracle Workspace Manager
RUL          VALID       11.2.0.3.0 Oracle Rules Manager
XDB          VALID       11.2.0.3.0 Oracle XML Database
XML          VALID       11.2.0.3.0 Oracle XDK

11 rows selected.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE;

no rows selected

Cause

In pre-10g, WMSYS-owned objects could be in tablespaces other than SYSAUX.  Beginning with 10g, WMSYS-owned objects are now in SYSAUX.  Hence, as part of the database upgrade, wmsys.owm_mig_pkg.moveWMMetaData is run to move any WMSYS-owned objects from tablespace_X to SYSAUX.

Therefore, these errors can be ignored

BEGIN wmsys.owm_mig_pkg.moveWMMetaData; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1579
ORA-06512: at "WMSYS.OWM_MIG_PKG", line 1596
ORA-06512: at line 1

Because the pre-upgrade database is => Oracle 10g and all WMSYS objects are already in SYSAUX, i.e. there are no objects that need to be moved to SYSAUX.

Solution

The errors can be safely ignored.