How to handle message "Expected XDB Resources are not there" when OLAP API is invalid after an upgrade

Background:
After an upgrade, you have invalid components in you registry.
To fix the OLAP components you have followed "Oracle Olap API Invalid After Adding or Upgrading OLAP (Doc ID 466363.1)" and created the

xoq_validate_verbose

procedure in the SYS schema.

You run it with the following output:

exec xoq_validate_verbose
compatible:11.2.0.4.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False

PL/SQL procedure successfully completed. 

Now, reload the OLAP API:

sqlplus / as sysdba@$ORACLE_HOME/olap/admin/xoqrelod.sql 

If the output from the above script shows that the index XDB.XDB$ACL_XIDX is disabled, make sure to enable it first:

alter index xdb.XDB$ACL_XIDX enable;

Then rerun the xoqrelod.sql script.


Check the registry, OLAP API should now be valid:

select comp_id, comp_name, version,status 
from dba_registry
where comp_id='XOQ'
/

COMP_ID              COMP_NAME                                VERSION                        STATUS
-------------------- ---------------------------------------- ------------------------------ -----------
XOQ                  Oracle OLAP API                          11.2.0.4.0                     VALID

How to fix error ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors when installing OLAP into a 11g database

You are installing OLAP.
You receive the following error at the end of the olap.sql script:

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL> execute cwm2_olap_metadata_refresh.mr_refresh;
BEGIN cwm2_olap_metadata_refresh.mr_refresh; END;

*
ERROR at line 1:
ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"OLAPSYS.CWM2_OLAP_METADATA_REFRESH"
ORA-06512: at line 1

Solution:
Drop the OLAPSYS user and rerun the script:

$ sqlplus '/ as sysdba'
SQL> drop user OLAPSYS cascade
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> @?/rdbms/admin/utlrp.sql

Sources: Doc ID 726253.1 "Errors: ORA-04063, ORA-06508, ORA-06512 When Adding OLAP Using the Script 'olap.sql SYSAUX TEMP;'"

How to enable OLAP in an Oracle 11gR2 database

First, check that the prerequisites are fulfilled. The components below must be installed and valid before proceeding.

select comp_id,  comp_name, version,status from dba_registry where comp_id in ('XDB','XML','JAVAVM','EXF');

COMP_ID    COMP_NAME                      VERSION    STATUS
---------- ------------------------------ ---------- ----------
XDB        Oracle XML Database            11.2.0.4.0 VALID
EXF        Oracle Expression Filter       11.2.0.4.0 VALID
JAVAVM     JServer JAVA Virtual Machine   11.2.0.4.0 VALID
XML        Oracle XDK                     11.2.0.4.0 VALID

If java virtual machine is not installed:

sqlplus / as sysdba
set echo on
spool JServer.log
@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
spool off

If XML DB is not yet install, you need to follow "Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)" from Oracle Support


If Oracle Expression Filter is not installed:

sqlplus / as sysdba
@?/rdbms/admin/catexf.sql

1. Start by shutting down the database:

sqlplus / as sysdba
shutdown immediate

2. Prepare your ORACLE_HOME by relinking the "oracle" executable.

For Oracle 11R2 and above, use the chopt utility. It was provided to help DBAs add new options and implicitly relink the binaries.

chopt enable olap

Writing to /u01/oracle/product/11204/install/enable_olap.log...
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk olap_on ORACLE_HOME=/u01/oracle/product/11204
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/oracle/product/11204

For versions prior to 11gR2:

cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk olap_on
  make -f ins_rdbms.mk ioracle

3. Open your database:

sqlplus / as sysdba
startup

4. Set up OLAP

cd $ORACLE_HOME/olap/admin
sqlplus / as sysdba
spool install_olap.log
@olap.sql SYSAUX TEMP

5. Verify that OLAP was installed:

select * from v$option where parameter='OLAP';

PARAMETER                      VALUE
------------------------------ ------------------------------
OLAP                           TRUE

How to check if the OLAP option is installed in the database

select comp_name, version, status from dba_registry  where comp_name like '%OLAP%' order by 1 DESC;

Typical output if OLAP is installed:

COMP_NAME	VERSION	STATUS
Oracle OLAP API	11.2.0.4.0	VALID
OLAP Catalog	11.2.0.4.0	VALID
OLAP Analytic Workspace	11.2.0.4.0	VALID

You can also use:

select * from v$option where parameter='OLAP';

PARAMETER	VALUE
OLAP	TRUE