28 Haziran 2012 Perşembe

[EN] "Oracle XML Database", "Oracle Multimedia", "Oracle Database Packages and Types" components invalid after 11gR2 upgrade because of LIBPATH OS variable

Problem:

After 11.2.0.3 upgrade from 10.2.0.5, "Oracle XML Database", "Oracle Multimedia" and "Oracle Database Packages and Types" components become invalid. They were valid on 10.2.0.5.

===> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name
COMP_NAME                                SCHEMA                         STATUS      VERSION
---------------------------------------- ------------------------------ ----------- ----------
JServer JAVA Virtual Machine             SYS                            VALID       11.2.0.3.0
OLAP Analytic Workspace                  SYS                            VALID       11.2.0.3.0
OLAP Catalog                             OLAPSYS                        VALID       11.2.0.3.0
Oracle Data Mining                       DMSYS                          VALID       11.2.0.3.0
Oracle Database Catalog Views            SYS                            VALID       11.2.0.3.0
Oracle Database Java Packages            SYS                            VALID       11.2.0.3.0
Oracle Database Packages and Types       SYS                            INVALID     11.2.0.3.0
Oracle Enterprise Manager                SYSMAN                         VALID       11.2.0.3.0
Oracle Expression Filter                 EXFSYS                         VALID       11.2.0.3.0
Oracle Multimedia                        ORDSYS                         INVALID     11.2.0.3.0
Oracle OLAP API                          SYS                            VALID       11.2.0.3.0
Oracle Rule Manager                      EXFSYS                         VALID       11.2.0.3.0
Oracle Text                              CTXSYS                         VALID       11.2.0.3.0
Oracle Workspace Manager                 WMSYS                          VALID       11.2.0.3.0
Oracle XDK                               SYS                            VALID       11.2.0.3.0
Oracle XML Database                      XDB                            INVALID     11.2.0.3.0
Spatial                                  MDSYS                          VALID       11.2.0.3.0
17 rows selected.



===> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type
OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
SYS                            PACKAGE BODY                 4
SYS                            VIEW                                     23
XDB                            PACKAGE BODY                 1
3 rows selected.


===> select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;
OBJECT_NAME                              OWNER           OBJECT_TYPE      
---------------------------------------- --------------- -------------------
DBMS_CUBE_EXP                            SYS             PACKAGE BODY
DBMS_NETWORK_ACL_ADMIN                   SYS             PACKAGE BODY
XS$CATVIEW_UTIL                          SYS             PACKAGE BODY
DBMS_XS_PRINCIPAL_EVENTS_INT             SYS             PACKAGE BODY
KU$_10_1_IOTABLE_VIEW                    SYS             VIEW
KU$_10_1_PFHTABLE_VIEW                   SYS             VIEW
KU$_10_1_PIOTABLE_VIEW                   SYS             VIEW
KU$_10_2_FHTABLE_VIEW                    SYS             VIEW
KU$_ACPTABLE_VIEW                        SYS             VIEW
KU$_CLUSTER_VIEW                         SYS             VIEW
KU$_COLUMN_VIEW                          SYS             VIEW
KU$_FHTABLE_VIEW                         SYS             VIEW
KU$_IOTABLE_VIEW                         SYS             VIEW
KU$_M_VIEW_FH_VIEW                       SYS             VIEW
KU$_M_VIEW_IOT_VIEW                      SYS             VIEW
KU$_M_VIEW_LOG_FH_VIEW                   SYS             VIEW
KU$_M_VIEW_LOG_PFH_VIEW                  SYS             VIEW
KU$_M_VIEW_PFH_VIEW                      SYS             VIEW
KU$_M_VIEW_PIOT_VIEW                     SYS             VIEW
KU$_NT_PARENT_VIEW                       SYS             VIEW
KU$_OPQTYPE_VIEW                         SYS             VIEW
KU$_PCOLUMN_VIEW                         SYS             VIEW
KU$_PFHTABLE_VIEW                        SYS             VIEW
KU$_PIOTABLE_VIEW                        SYS             VIEW
KU$_VIEW_VIEW                            SYS             VIEW
KU$_10_1_FHTABLE_VIEW                    SYS             VIEW
KU$_XMLSCHEMA_ELMT_VIEW                  SYS             VIEW
DBMS_RESCONFIG                           XDB             PACKAGE BODY
28 rows selected.

Solution:

We did 11gR2 upgrade manual way using "catupgrd.sql" script. Before we execute this script we point all OS environment variables to 11gR2 home except "LIBPATH". Of course we noticed this after the upgrade.
Because of this "Oracle XML Database", "Oracle Multimedia", "Oracle Database Packages and Types" components become invalid after upgrade.

To validate these components we first shutdown the database with LIBPATH variable pointing to 10g home.
Then we point LIBPATH to 11gR2 home.

export LIBPATH=/u01/app/oracle/product/11.2.0/db_1/lib:/u01/app/oracle/product/11.2.0/db_1/lib32:/usr/lib

Then we deinstall XMLDB component using the following scripts. You can find these scripts in Oracle Support.


SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catnoqm.sql
SQL> spool off;
SQL> exit

Then we install XDB again and execute utlrp.sql script to validate all invalid objects on database.
You can find these scripts too in Oracle Support.

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catqm.sql XDB123 SYSAUX TEMP YES
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql
SQL> spool off
SQL> exit

Note that we execute utlrp on the same session.

But there was still warnings in the utlrp output;

@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2012-06-27 16:35:22
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2012-06-27 16:37:00
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
                          9
Function created.
PL/SQL procedure successfully completed.
Function dropped.
Warning: XDB now invalid, invalid objects found:
object_name                                 object_type
-------------------------------------------------------
DBMS_RESCONFIG                             PACKAGE BODY
ORDIM registered 5 XML schemas.
The following XML schemas are not registered:
http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0
http://xmlns.oracle.com/ord/dicom/anonymity_1_0
http://xmlns.oracle.com/ord/dicom/attributeTag_1_0
http://xmlns.oracle.com/ord/dicom/constraint_1_0
http://xmlns.oracle.com/ord/dicom/datatype_1_0
http://xmlns.oracle.com/ord/dicom/manifest_1_0
http://xmlns.oracle.com/ord/dicom/mapping_1_0
http://xmlns.oracle.com/ord/dicom/mddatatype_1_0
http://xmlns.oracle.com/ord/dicom/metadata_1_0
http://xmlns.oracle.com/ord/dicom/orddicom_1_0
http://xmlns.oracle.com/ord/dicom/preference_1_0
http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0
http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0
http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0
ORDIM DICOM repository has 0 documents.
The following default DICOM repository documents are not installed:
ordcman.xml
ordcmcmc.xml
ordcmcmd.xml
ordcmct.xml
ordcmmp.xml
ordcmpf.xml
ordcmpv.xml
ordcmsd.xml
ordcmui.xml
PL/SQL procedure successfully completed.

These warnings are related to "Oracle Multimedia" component which is still invalid. But Oracle XML Database and Oracle Database Packages and Types are valid now.

===> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name
COMP_NAME                                SCHEMA                         STATUS      VERSION
---------------------------------------- ------------------------------ ----------- ----------
JServer JAVA Virtual Machine             SYS                            VALID       11.2.0.3.0
OLAP Analytic Workspace                  SYS                            VALID       11.2.0.3.0
OLAP Catalog                             OLAPSYS                        VALID       11.2.0.3.0
Oracle Data Mining                       DMSYS                          VALID       11.2.0.3.0
Oracle Database Catalog Views            SYS                            VALID       11.2.0.3.0
Oracle Database Java Packages            SYS                            VALID       11.2.0.3.0
Oracle Database Packages and Types       SYS                            VALID       11.2.0.3.0
Oracle Enterprise Manager                SYSMAN                         VALID       11.2.0.3.0
Oracle Expression Filter                 EXFSYS                         VALID       11.2.0.3.0
Oracle Multimedia                        ORDSYS                         INVALID     11.2.0.3.0
Oracle OLAP API                          SYS                            VALID       11.2.0.3.0
Oracle Rule Manager                      EXFSYS                         VALID       11.2.0.3.0
Oracle Text                              CTXSYS                         VALID       11.2.0.3.0
Oracle Workspace Manager                 WMSYS                          VALID       11.2.0.3.0
Oracle XDK                               SYS                            VALID       11.2.0.3.0
Oracle XML Database                      XDB                            VALID       11.2.0.3.0
Spatial                                  MDSYS                          VALID       11.2.0.3.0
17 rows selected.

To validate Oracle Multimedia you can check Oracle Support note: How To Reload Oracle Multimedia Related Information When XML Database (=XDB) Has Been Reinstalled [ID 965892.1]. To validate Oracle Multimedia we followed the following procedure.

sqlplus / as sysdba
SQL> alter session set current_schema="ORDSYS";

SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxreg.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impbs.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/impvs.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imtyb.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/implb.sql;
SQL> @/u01/app/oracle/product/11.2.0/db_1/ord/im/admin/imxrepos.sql;
SQL> exit
sqlplus / as sysdba

SQL> set serveroutput on
SQL> exec validate_ordim;
SQL> exit

Now all of the components are valid.

===> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name
COMP_NAME                                SCHEMA                         STATUS      VERSION   
---------------------------------------- ------------------------------ ----------- ----------
JServer JAVA Virtual Machine             SYS                            VALID       11.2.0.3.0
OLAP Analytic Workspace                  SYS                            VALID       11.2.0.3.0
OLAP Catalog                             OLAPSYS                        VALID       11.2.0.3.0
Oracle Data Mining                       DMSYS                          VALID       11.2.0.3.0
Oracle Database Catalog Views            SYS                            VALID       11.2.0.3.0
Oracle Database Java Packages            SYS                            VALID       11.2.0.3.0
Oracle Database Packages and Types       SYS                            VALID       11.2.0.3.0
Oracle Enterprise Manager                SYSMAN                         VALID       11.2.0.3.0
Oracle Expression Filter                 EXFSYS                         VALID       11.2.0.3.0
Oracle Multimedia                        ORDSYS                         VALID       11.2.0.3.0
Oracle OLAP API                          SYS                            VALID       11.2.0.3.0
Oracle Rule Manager                      EXFSYS                         VALID       11.2.0.3.0
Oracle Text                              CTXSYS                         VALID       11.2.0.3.0
Oracle Workspace Manager                 WMSYS                          VALID       11.2.0.3.0
Oracle XDK                               SYS                            VALID       11.2.0.3.0
Oracle XML Database                      XDB                            VALID       11.2.0.3.0
Spatial                                  MDSYS                          VALID       11.2.0.3.0
17 rows selected.

Conclusion:

Before you upgrade your database version using the manual method always check OS environment variables pointing to new ORACLE HOME.
 

3 yorum:

  1. Thanks for providing such a great article, it was excellent and very informative.
    as a first time visitor to your blog I am very impressed.
    thank you :)

    YanıtlaSil
  2. You are the real problem solver, i was facing exact issue...

    YanıtlaSil