XOQ Oracle OLAP API 11.2.0.3.0 VALID

18 rows selected.

MOS 有篇文档提供了重建OLAP组件的方法：

How To Remove Or To Reinstall The OLAPOption To 10g And 11g [ID 332351.1]

这里提供了2种解决方法：

（1）在ORACLE_HOME 级别重建

--删除OLAP

备份DB和oraInventory,在关闭ORACLE_HOME上的所有Instance，使用Universal Installer (OUI) 移除ORACLE_HOME的OLAP 选项。在OUI 中选择升级已经存在的db，在selectoptions 选项可以控制是否启用OLAP。如下图：

在Unix 和Linux 平台上，也可以直接relink olap_off 的方式来删除，这样就避免使用OUI。

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk olap_off
make -f ins_rdbms.mk ioracle

非注册的OLAP将不在db 的banner 中限制，同时v$option 也会显示为FALSE.

--重新安装OLAP

1）在OUI中添加OLAP 组件，参考上图。

2）添加完毕之后执行如下脚本，在db 级别进行添加。

sqlplus /nolog
SQL> conn /as sysdba
SQL> spool add_olap.log
SQL> @ /olap/admin/olap.sql SYSAUX TEMP;
SQL> @ /rdbms/admin/utlrp.sql
SQL> spool off

（2）在Database 级别重建

--删除OLAP

--在db server 上开一个session，设定相关的变量，并用sys 用户执行如下脚本：

ORACLE_HOME=your_oracle_home
ORACLE_SID=your_db_sid
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL> conn / as sysdba

----> Remove OLAP Catalog
SQL> @ /olap/admin/catnoamd.sql

----> Remove OLAP API
SQL> @ /olap/admin/olapidrp.plb
SQL> @ /olap/admin/catnoxoq.sql

----> Deinstall APS - OLAP AW component
SQL> @ /olap/admin/catnoaps.sql
SQL> @ /olap/admin/cwm2drop.sql

----> Recompile invalids
SQL> @ /rdbms/admin/utlrp.sql

catnoamd.sqlused above will drop the OLAPSYS schema (which is completely OLAP specific).

--catnoamd.sql 脚本用来drop OLAPSYS 用户。

cwm2drop.sqlneeds to be run only in 11g. In 10g, catnoamd.sql already calls it.

--cwm2drop.sql 只需要在11g中执行。在10g中，catnoamd.sql 脚本中已经包含了该脚本，所以不用执行。

If you connectto the database in SQLPLUS, the banner still shows the OLAP option,this happensbecause the Oracle executable is still aware of OLAP, it is linked with OLAP, andalso the OLAP files are still present in the ORACLE_HOME.

Once running thedeinstall scripts, the utlrp.sql could report invalid objects with SYS andPUBLIC owners. If so, the following SQL can be used to report more details onthat:

--删除之后，可能导致一些无效的对象，可以使用如下SQL 来查看具体信息：

SQL> select owner, object_name,object_type, status from dba_objects where status='INVALID';

You could getsome invalid objects under SYS and PUBLIC owner, and they are the old duplicate OLAPSYS objects copied under these schemas when Olap has been installedpreviously.

Generally, theOlap objects are named with context like %OLAP%, %AWM%, or other Olap word keys intheir "object_name" field, however, if it needs a help to recognizethem, then please contact Oracle Support and create a Service Request toget assistance for this question.

Note that catnoadm.sql could fail from 10.1.0.5 to 11.1.0.7release.

Due to the factthat it refers to three scripts which don't get shipped until 11.2 this scriptwill fail.

Besides that itwill error on 7 non-existing synonyms to drop.

Prior 11.2, execute these three dropsynonym statements:

SQL> drop public synonym OlapFactView;
SQL> drop public synonym OlapDimView;
SQL> drop public synonym DBMS_ODM;

If there arefurther invalid OLAP specific objects after the above removal steps, pleaseconsult Note1060023.1, Note565773.1

If you do notwant to install OLAP into a newly created database, then in the DatabaseConfiguration Assistant (dbca), select a custom database template and uncheckOracle OLAP in the Database Components wizard step.

--添加OLAP