将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小时没有睡觉了,都没啥精神了。 把这个问题搞定,心里的石头也放下来了。 升级结束。
资料一
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.
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.
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.
- 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
资料二
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.
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.
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.
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表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
oracle 的essbase产品和OLAP产品的书籍,以及两个产品之间的对比书籍。
Oracle 9i OLAP Developer’s Guide to the OLAP API Release 2 (9.2)
Oracle 9i OLAP Developer’s Guide to the OLAP DML Release 2 (9.2)
Oracle Essbase & Oracle OLAP: The Guide to Oracle's Multidimensional Solution (Osborne ORACLE Press Series) By Michael Schrader, Dan Vlamis, Mike Nader, Chris Claterbos, Dave Collins, Mitch Campbell, ...
Oracle 9i OLAP User’s Guide Release 2 (9.2.0.2)
基于Oracle的OLTP与OLAP数据库内存设计和优化.pdf
2、这一次支持的是Oracle 9i与10g 的Olap Server,虽然Oracle提供了Java Olap API但是 <br>还存在一些版本差别,应该是release 4以上才比较一致把。9i低版本需要注意 <br>oracle.express.mdm....
Oracle9i Database Error Messages Release 2 (9.2) Contents Title and Copyright Information Send Us Your Comments Preface Audience Organization Related Documentation Conventions Documentation ...
Oracle olap用户手册,olap初学必看
oracle olap函数可以实现很多累计功能,前段时间学习了一下。在网上搜集了一些资料。
Oracle Hyperion Essbase - OLAP介绍 - 培训资料 After completing this lesson, you should be able to: Explain data transformation Describe the multidimensional data model Define online analytical ...
ORACLE_sql_tune 2 ORACLE_oracle体系结构1 3 ORACLE_oracle体系结构2 3 ORACLE_重做日志管理1 3 ORACLE_重做日志管理2 3 Oracle一个女人和五个男人的故事(数据库命名习惯) 3 oracle_内部事件(Oracle高级工具的...
Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent and other intellectual and industrial property...
参加ORACLE时的培训教材中文课件,主要讲解Oracle-OLAP 数据仓库的知识点,入门知识。也是ORACLE老师培训教程之一。以前参加ORACLE培训时,向老师要的。现在提供给大家。 如果好,一定要给好评!
基于Oracle的OLTP与OLAP数据库设计及实现.pdf
1.Oracle 产品,软件,数据结构以及Oracle9i,Oracle8i和Oracle8的整体结构 2.Oracle网络,管理,监控,性能优化,数据仓库和分布式数据库 3.多用户并发性,联机事务处理(OLAP),高可用性和硬件体系结构 4.Oracle与Web 5.最新...
Oracle数据库OLAP功能介绍.docx
分享一个自己编写的Oracle 9i、10g、11g通用的Range分区自动扩展存储过程,可以在输入相应参数后自动实现RANGE表分区的扩展,推荐在数据仓库、经营分析等OLAP数据库中使用。
oracle olap 简介 入门教程,可参考!
绍了OLAP和0I.TP处理系统的概念,根据这两种数据库应用在实时性、并发性及数据量大小等方面的 不同,数据库在设计方面侧重的技术各有不同,阐述了数据库设计技术,即内存设计、变量绑定、sQL并行执行、 表分区存储...