Oracle 9i 升级后 OLAP Catalog 组件 Invalid

将Oracle数据库从9.2.0.6升级到9.2.0.8,升级结束后，查看组件的信息，OLAP Catalog无效，然后又重新执行了一遍脚本，还是无效。

SQL> select comp_id,comp_name, version, status from dba_registry;

COMP_IDCOMP_NAMEVERSIONSTATUS

--------------- -------------------- ------------------------------ -----------

CATALOGOracle9i Catalog Vie 9.2.0.8.0VALID

CATPROCOracle9i Packages an 9.2.0.8.0VALID

OWMOracle Workspace Man 9.2.0.1.0VALID

JAVAVMJServer JAVA Virtual 9.2.0.8.0VALID

XMLOracle XDK for Java9.2.0.10.0VALID

CATJAVAOracle9i Java Packag 9.2.0.8.0VALID

ORDIMOracle interMedia9.2.0.8.0VALID

SDOSpatial9.2.0.8.0VALID

CONTEXTOracle Text9.2.0.8.0VALID

XDBOracle XML Database9.2.0.8.0VALID

WKOracle Ultra Search9.2.0.8.0VALID

COMP_IDCOMP_NAMEVERSIONSTATUS

--------------- -------------------- ------------------------------ -----------

ODMOracle Data Mining9.2.0.8.0VALID

APSOLAP Analytic Worksp 9.2.0.8.0UPGRADED

XOQOracle OLAP API9.2.0.8.0UPGRADED

AMDOLAP Catalog9.2.0.8.0INVALID

已选择15行。

在Metalink上找到2篇资料，与这个组件有关的。资料如下。资料一是10g的库，我的是9i，没有找到它说要删的对象，所以直接执行了资料一的其他步骤，再次查看组件状态，全部正常了。折腾了一晚，24小时没有睡觉了，都没啥精神了。把这个问题搞定，心里的石头也放下来了。升级结束。

资料一

Applies to:

Oracle OLAP - Version: 10.2.0.3 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

The OLAP Catalog (DBA Registry component ID: AMD) is shown with a status of INVALID.

However, utlrp.sql shows no invalid objects in either SYS, OLAPSYS or PUBLIC schemas.

An object namedCWM2INER_D1is present in theOLAPSYSschema.

Cause

An object namedOLAPSYS.CWM2INER_D1was defined by an internal OLAP routine and not properly cleaned up. This object now prevents the validation of the OLAP Catalog component.

Solution

1. Drop the object "OLAPSYS.CWM2INER_D1"
2. Re-try the validation of the modules
   (Note that you need to connect as OLAPSYS and then as SYSDBA to complete this step):

connectOLAPSYS
spoolcatalog_install.log
setechoon
setserveroutputonsize1000000
executecwm2_olap_manager.Set_Echo_On;
executecwm2_olap_manager.Log_Raw_Messages;

connectSYS/[password] AS SYSDBA;
executesys.cwm2_olap_installer.validate_cwm2_install;

connect OLAPSYS;
executecwm2_olap_metadata_refresh.mr_refresh;
executecwm2_olap_manager.End_Log;
executecwm2_olap_manager.Set_Echo_Off;
spooloff

1. Inspect the dba registry:

colcomp_idformata10
colcomp_nameformata25
colversionformata15
colstatusformata15
selectcomp_id,comp_name,version,statusfromdba_registry;

All OLAP components should now show a status of VALID as in the example below:

COMP_IDCOMP_NAMEVERSIONSTATUS
-----------------------------------------------------------------
APSOLAPAnalyticWorkspace10.2.0.4.0VALID
XOQOracleOLAPAPI10.2.0.4.0VALID
AMDOLAPCatalog10.2.0.4.0VALID

资料二

Applies to:

Oracle OLAP - Version: 10.2.0.2 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Purpose

This article intends to help getting further diagnostic information to find the cause why the OLAP Catalog component is not VALID in the DBA_REGISTRY.

E.g. the STATUS column of the DBA_REGISTRY shows that OLAP Catalog component is INVALID:

COMP_IDCOMP_NAME STATUS VERSION
----------------------------------------------------------------- ----------
AMDOLAPCatalog INVALID 10.2.0.4.0

Usually this is due to improper installation or state of OLAP Catalog objects, however the sys.cwm2_olap_installer.Validate_CWM2_Install procedure, which sets the OLAP Catalog to VALID or to INVALID in the DBA_REGISTRY, is wrapped, and may not output any diagnostic information (e.g. error) even when it sets the OLAP Catalog to INVALID.

Last Review Date

November 25, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

1. Make sure that the version of the OLAP Catalog component displayed in the DBA_REGISTRY is the same as the version of the database.
If not, verify whether the post installation steps of database patchset installation have been executed properly, particularly if the issue has been reported after database patchset installation. Otherwise OLAP objects need to be re-installed into the database, either all OLAP objects (if other OLAP components are not valid in the DBA_REGISTRY either), or just the OLAP Catalog related objects, if the issue is with the OLAP Catalog only. As user SYS AS SYSDBA run olap.sql in the former case (seeNote.736688.1for details), amdrelod.sql in the latter case.

2. Make sure there is no OLAP related invalid object in the database.
If there is, the following may help: reinstalling OLAP into the database, reloading the OLAP component where the invalid object(s) belongs, interpreting compilation error(s).

You can query invalid objects with the following query:

setlinesize90
columnownerformata20
columnobject_nameformata30
columnobject_typeformata20
columnstatusformata10

SELECT
owner
,object_name
,object_type
,status
FROMdba_objects
WHEREstatus<>'VALID'
ORDERBYowner,object_name
/

All the objects of the OLAP option in the database are owned by either SYS or OLAPSYS or PUBLIC.
These objects usually have descriptive names, so fairly easy to recognize that a particular object is part the OLAP option in the database or not, but if it is in doubt, you can get confirmation by searching the name of an object as a text in the content of the files located in the $ORACLE_HOME/olap/admin directory. The latter directory contains all the scripts that create all the OLAP 10gR2 specific objects in the database.

3. Do OLAP Catalog operations to get more specific information about the root cause
If the above are satisfied, and the OLAP Catalog is still invalid, then execute the following to get some further diagnostic information about the cause why the OLAP Catalog is set to INVALID in the DBA_REGISTRY.

sqlplus /nolog
spoolvalidate_AMD.log
connectsysassysdba
setechoon
setlinesize135
setpagesize50
setserveroutputonsize1000000
--
-- This procedure sets AMD valid or invalid
--
execute sys.cwm2_olap_installer.Validate_CWM2_Install;
--
-- Similar calls that Validate_CWM2_Install does in order to validate AMD
--
exec cwm2_olap_dimension.Create_Dimension('olapsys' ,'cwm2iner_d1' ,'display_name' ,'plural_name' ,'short_description' ,'description');
exec cwm2_olap_dimension_attribute.Create_Dimension_Attribute('olapsys' ,'cwm2iner_d1' ,'cwm2iner_da1' ,'display_name' ,'short_description' ,'description');
exec cwm2_olap_hierarchy.Create_Hierarchy('olapsys' ,'cwm2iner_d1' ,'cwm2iner_h1' ,'display_name' ,'short_description' ,'description' ,'SOLVED LEVEL-BASED');
exec cwm2_olap_level.Create_Level('olapsys' ,'cwm2iner_d1' ,'cwm2iner_l1' ,'display_name' ,'plural_name' ,'short_description' ,'description');
exec cwm2_olap_level_attribute.Create_Level_Attribute('olapsys' ,'cwm2iner_d1' ,'cwm2iner_da1' ,'cwm2iner_l1' ,'cwm2iner_la1' ,'display_name' ,'short_description' ,'description');
exec cwm2_olap_cube.Create_Cube('olapsys' ,'cwm2iner_c1','display_name' ,'short_description' ,'description');
exec cwm2_olap_measure.Create_Measure('olapsys' ,'cwm2iner_c1' ,'cwm2iner_m1','display_name' ,'short_description' ,'description');
--
-- ANY OF THE ABOVE calls fail, AMD will be set to INVALID,
-- otherwise it will be set to VALID
--
exec cwm2_olap_measure.Drop_Measure('olapsys' ,'cwm2iner_c1' ,'cwm2iner_m1');
exec cwm2_olap_cube.Drop_Cube('olapsys' ,'cwm2iner_c1');
exec cwm2_olap_level_attribute.Drop_Level_Attribute('olapsys' ,'cwm2iner_d1' ,'cwm2iner_da1' ,'cwm2iner_l1' ,'cwm2iner_la1');
exec cwm2_olap_level.Drop_Level('olapsys' ,'cwm2iner_d1' ,'cwm2iner_l1');
exec cwm2_olap_hierarchy.Drop_Hierarchy('olapsys' ,'cwm2iner_d1' ,'cwm2iner_h1');
exec cwm2_olap_dimension_attribute.Drop_Dimension_Attribute('olapsys' ,'cwm2iner_d1' ,'cwm2iner_da1');
exec cwm2_olap_dimension.Drop_Dimension('olapsys' ,'cwm2iner_d1');
--
colcomp_idfora10
colcomp_namefora40
colstatusfora20
colversionfora20
selectcomp_id,comp_name,status,versionfromdba_registrywherecomp_namelike'%OLAP%'orderby1;
executecwm2_olap_manager.set_echo_off;
spooloff

In most of the cases the above gives enough information (messages, errors) that you can research on to proceed with the investigation.

The cwm2_olap_installer.Validate_CWM2_Install procedure sets the OLAP Catalog to VALID or INVALID depending on whether its actions were successfully executed or not.

------------------------------------------------------------------------------