Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Friday, February 06, 2009

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

No comments:

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Recent Comments