Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, October 12, 2007

Invalid OLAPSYS and SYS objects after installing OLAP and Data Mining

We installed OLAP and Data Mining after upgrading the Apps Instance from to 11.5.9/9.2.0.8 to 11.5.10.2/10.2.0.3 by following point 19 in interop metalink note:362203.1 point 19:

To install Data Mining, use SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return AMD, then you do not have OLAP installed. To install OLAP, use SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP
We got the following invalids after running the above sqls:

SQL> /

OWNER OBJECT_TYPE OBJECT_NAME
---------- ------------------- ---------------------------
OLAPSYS PACKAGE BODY CWM2_OLAP_METADATA_REFRESH
OLAPSYS PACKAGE BODY CWM2_OLAP_OLAPAPI_ENABLE
OLAPSYS PACKAGE BODY DBMS_AWM
SYS PACKAGE DBMS_XSOQ_ODBO
SYS PACKAGE BODY DBMS_XSOQ_ODBO
PUBLIC SYNONYM DBMS_XSOQ_ODBO
SYS TYPE OLAPI_MDX_ROWSET_IMPL_T
SYS FUNCTION OLAPI_MDX_ROWSET_TABLE
PUBLIC SYNONYM OLAPI_MDX_ROWSET_IMPL_T
PUBLIC SYNONYM OLAPI_MDX_ROWSET_TABLE

10 rows selected.

I have logged an SR with Oracle for this. I'll update this post with the solution given by them.

Before Oracle could provide an answer, I reran this command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

All the invalids are gone after re-running the command. It is possible that the DBA who ran the command originally, may have got some errors, but carelessly he did not spool the run.

3 comments:

amresh said...

OLAP script always need a second run atleast on HP

birdman said...

Thanks!! This helped me!! we are upgrading 11.5.9 with 9.2.0.8 to R12 and got into the same error!!

Luqman Naseer said...

Well done Bro it works