Manually Create OLAP in 9i or 10g Databases

For 9.X versions:

Execute all these steps as SYSDBA.

Step 1: Check the OLAP Option:

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

if TRUE, OLAP is turned on. Go to Step 2.
If FALSE, shutdown DB and turn it on:

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

Step 2: Check if cwmlite01.dbf exists in the directory containing the database files for this instance

If this file is present goto Step 3 otherwise issue the following SQL/PLUS commands:

SQL> CREATE TABLESPACE "CWMLITE" LOGGING DATAFILE '/ORACLE_BASE/oradata/mysid/cwmlite01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
SQL> @?/olap/admin/olap.sql mysid
SQL> connect SYS/sys_password as SYSDBA
SQL> @?/cwmlite/admin/oneinstl.sql CWMLITE TEMP;

Step 3: Check the component registry for AW's

SQL> select status from dba_registry where comp_id = 'APS';

if VALID or LOADED, go to Step 4.

if INVALID, execute AW installer script:

SQL> @?/olap/admin/olapaw.sql

Step 4: Check the component registry for OLAPI:

if VALID, go to Step 5.

SQL> select status from dba_registry where comp_id = 'XOQ';

if INVALID, execute AW installer script

SQL> @?/olap/admin/olapapi.sql 

Step 5: Check the component registry for OLAP Catalog:

SQL> select status from dba_registry where comp_id = 'AMD';

if INVALID, execute validation procedure:

SQL> execute olapsys.cwm2_olap_installer.Validate_CWM2_Install;

if still INVALID, drop and recreate the Catalog schema
(Note: You will need to recreate your OLAP Catalog data - applicable to 9i only.)

SQL> @?/cwmlite/admin/onedrop.sql
SQL> @?/cwmlite/admin/oneinstl.sql CWMLITE TEMP

For 10G:

There is no need to create the cwmlite tablespace as the catalog is now stored in the SYSAUX tablespace.
All one needs to do in 10g is run these scripts on Enterprise instances that require the OLAP option making certain to be connected '/ as sysdba'.

Step 1: Verify 'JServer JAVA Virtual Machine' is present and VALID in the database instance:

SQL> SELECT comp_id, comp_name, status, substr(version,1,10) as version from dba_registry where comp_id like 'JAVAVM';

if this is not present or INVALID then execute these commands when connected as SYSDBA:

SQL> set echo on
SQL> spool JServer.log
SQL> @?\javavm\install\initjvm.sql;
SQL>  

Once the JAVAVM is installed, without errors, and VALID then:
 
SQL> shutdown immediate
SQL> spool add_olap.log
SQL> startup
SQL> @?\olap\admin\olap.sql SYSAUX TEMP;
SQL> spool off
 Check the add_olap.log file for any errors.
Another Method to Add OLAP - Use DBCA (Database Configuration Creation Assistant):
  
  
Launch DBCA
  
  
On ‘DBCA Step 1’ page
  
  
            Select the ‘Configure Database Options’ button
         Click ‘Next’
  
  
 On ‘Step 2’ page
  
  
            Select the correct database
         Click ‘Next’
  
  
  
  
  
  
  
 On ‘Step 3 Database Content’ page
           Under 'Database Components'
        Check 'Oracle Olap’
                   if 'Oracle OLAP' is not an available option
                     then select 'Standard Database Components'
                        check 'Oracle JVM'
                   then select 'OK', returning to the previous page
                        now 'Oracle OLAP' should be available to select
        Click 'Next'
        Click 'Finish' 
  
Restart the database
  
  
Check the install with the following:
  
 
SQL> column comp_name format a35
SQL> select comp_name, status, substr(version,1,10) as version from dba_registry;
 
  
The required components for using OLAP are and should be VALID:
 
OLAP AnalyticWorkspace           9.2X or 10.X    VALID
Oracle OLAP API                  9.2X or 10.X    VALID
OLAP Catalog                     9.2X or 10.X    VALID
JServer JAVA Virtual Machine     10.x            VALID
Oracle XDK                       10.x            VALID
Oracle Database Java Packages    10.x            VALID