Oracle 9i 升级后 OLAP Catalog 组件 Invalid

来源：互联网发布：java实现代理ip轮换编辑：程序博客网时间：2024/05/02 00:14

将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):

connect OLAPSYS
spool catalog_install.log
set echo on
set serveroutput on size 1000000
execute cwm2_olap_manager.Set_Echo_On;
execute cwm2_olap_manager.Log_Raw_Messages;

connect SYS/[password] AS SYSDBA;
execute sys.cwm2_olap_installer.validate_cwm2_install;

connect OLAPSYS;
execute cwm2_olap_metadata_refresh.mr_refresh;
execute cwm2_olap_manager.End_Log;
execute cwm2_olap_manager.Set_Echo_Off;
spool off

Inspect the dba registry:

col comp_id format a10
col comp_name format a25
col version format a15
col status format a15
select comp_id,comp_name, version, status from dba_registry;

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

COMP_ID    COMP_NAME                 VERSION         STATUS
---------- ------------------------- --------------- ---------------
APS        OLAP Analytic Workspace   10.2.0.4.0      VALID
XOQ        Oracle OLAP API           10.2.0.4.0      VALID
AMD        OLAP Catalog              10.2.0.4.0      VALID

资料二

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_ID COMP_NAME STATUS VERSION
---------- ------------------------------------ ------------------- ----------
AMD OLAP Catalog 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:

set linesize 90
column owner           format a20
column object_name     format a30
column object_type     format a20
column status          format a10

SELECT
    owner
  , object_name
  , object_type
  , status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, 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
spool validate_AMD.log
connect sys as sysdba
set echo on
set linesize 135
set pagesize 50
set serveroutput on size 1000000
--
-- 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');
--
col comp_id for a10
col comp_name for a40
col status for a20
col version for a20
select comp_id,comp_name, status, version from dba_registry where comp_name like '%OLAP%' order by 1;
execute cwm2_olap_manager.set_echo_off;
spool off

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