将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.
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
