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_ID COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------ -----------
CATALOG Oracle9i Catalog Vie 9.2.0.8.0 VALID
CATPROC Oracle9i Packages an 9.2.0.8.0 VALID
OWM Oracle Workspace Man 9.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual 9.2.0.8.0 VALID
XML Oracle XDK for Java 9.2.0.10.0 VALID
CATJAVA Oracle9i Java Packag 9.2.0.8.0 VALID
ORDIM Oracle interMedia 9.2.0.8.0 VALID
SDO Spatial 9.2.0.8.0 VALID
CONTEXT Oracle Text 9.2.0.8.0 VALID
XDB Oracle XML Database 9.2.0.8.0 VALID
WK Oracle Ultra Search 9.2.0.8.0 VALID

COMP_ID COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------ -----------
ODM Oracle Data Mining 9.2.0.8.0 VALID
APS OLAP Analytic Worksp 9.2.0.8.0 UPGRADED
XOQ Oracle OLAP API 9.2.0.8.0 UPGRADED
AMD OLAP Catalog 9.2.0.8.0 INVALID

已选择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 named CWM2INER_D1 is present in the OLAPSYS schema.

Cause

An object named OLAPSYS.CWM2INER_D1 was defined by an internal OLAP routine and not properly cleaned up. This object now prevents the validation of the OLAP Catalog component.

Solution

• Drop the object "OLAPSYS.CWM2INER_D1"
  
• 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

• 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 (see Note.736688.1 for 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.




------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请