Introduction:

For a customer I have to implement  Oracle Warehouse builder on several servers. So it became an interesting session cause during exploring, well actually it was already during installing found out that there are dependencies to  other products that need to be present in the database before you can finish the job successfully. But as always Google  is your friend so i was very happy in the end to  summarise the steps and put them to a blog.

Bottom-line is that the following components need be present in your database before you can start with the Oracle  Warehouse builder:

• Java virtual Machine
• Olap

The remarkable thing was that during exploring i came across the  fact that if you  create your database via DBCA these components are  installed. If you are a diehard  and use the command line  /  scripts for that well then you need to do just a bit extra to be victorious. The environments in this note were set up manually.

Java Virtual Machine.

First and for all is that i found out that the First requirement for the successful  installation of OWB is that the JVM  has been installed in your database and that it has a valid status. With the query below  you can do a quick check to see if you are all set for the next step:

So let us Check if Java Virtual Machine is present and happy:

select comp_name, version, status from dba_registry;

COMP_NAME                                                                        VERSION                        STATUS

——————————————————————————– —————————— ———–

Oracle Application Express                                                       4.1.1.00.23                    VALID

Oracle XML Database                                                              11.2.0.3.0                     VALID

Oracle Expression Filter                                                         11.2.0.3.0                     VALID

Oracle Database Catalog Views                                                    11.2.0.3.0                     VALID

Oracle Database Packages and Types                                               11.2.0.3.0                     VALID

JServer JAVA Virtual Machine                                                     11.2.0.3.0                     VALID

Oracle XDK                                                                       11.2.0.3.0                     VALID

Oracle Database Java Packages                                                    11.2.0.3.0                     VALID

8 rows selected.

A second check( well better safe than sorry is that  there need to be quite a large number of java objects in the sys schema:

select owner, status, count(*) from all_objects  where object_type like ‘%JAVA%’ group by owner, status;

OWNER                          STATUS    COUNT(*)

—————————— ——- ———-

SYS                            VALID        21580

EXFSYS                         VALID           48

Third check Checking roles with regard to Java:

SQL> select role from dba_roles where role like ‘%JAVA%’;

ROLE

——————————

JAVAUSERPRIV

JAVAIDPRIV

JAVASYSPRIV

JAVADEBUGPRIV

JAVA_ADMIN

JAVA_DEPLOY

6 rows selected.

Ok I am convinced that  Java is there and Happy  . Let’s proceed to the next step.

Note: If  JVM is not installed or not feels happy check : How to Reload the JVM in 11.2.0.x [ID 1112983.1]

Now on to the manual installation of the OWB

From the oracle Documentation I learned ad followed the steps below:

To create an Oracle Warehouse Builder repository in your database, you must first create an OWBSYS schema, and then install repository objects within it.

Oracle Warehouse Builder has command-level scripts to perform this task:

1. The clean_owbsys.sql script drops the contents of any existing OWBSYS schema installations, but leaves the schema otherwise intact.
2.  The cat_owb.sql script checks if an OWBSYS schema exists, and creates it if necessary, and then installs the objects required by OWB 11.2 repository.
3.  The reset_owbcc_home.sql script ensures that Oracle Warehouse Builder uses the 11.2 version of the Control Center Service.
4.  [Optional] The remote_owb_install.sql script, when executed on the server, ensures that a repository on a remote system can be installed; It sets the REMOTE_OWB_HOME variable for authentication by remote systems.

Implementation:

• Clean_owbsys.sql .

cd /opt/oracle/product/11203_ee_64/db/owb/UnifiedRepos/

@clean_owbsys.sql

• Before proceduring you will have to run the step to Install the OLAP option with parameters for tablespace default and  temp:

@/olap/admin/olap.sql myts  temp

• After that use cat_owb.sql  this will create the schemas in your database. Note: Add the tablespace where the objects need to be created as a parameter to the script.

@cat_owb.sql myts

• unlock both accounts:

alter user owbsys identified by password account unlock;

alter user owbsys_audit identified by password account unlo

• As the user sys  you then need to run:

@reset_owbcc_home.sql /opt/oracle/product/11203_ee_64/db

• Check for invalids:

select owner,object_type,count(object_type) from dba_objects where status = ‘INVALID’ group by  owner,object_type;

This last check showed me invalids in the sys schema so that was a pfff experience. So I was glad  that I found Mos note telling to delete double  entries after manual installation of package body in sys schema if Olap is there too. (ID 428354.1).Which I did see so indeed dropped them

drop package body SYS.CWM1_OLAP_VALIDATE_METADATA;

drop package body SYS.CWM2_OLAP_CLASSIFY;

drop package body SYS.CWM2_OLAP_TABLE_MAP;

drop package body SYS.CWM2_OLAP_VALIDATE;

drop package body SYS.CWM2_OLAP_VALIDATE_METADATA;

drop package body SYS.CWM2_OLAP_UTILITY;

drop package body SYS.CWM2_OLAP_VALIDATE_UTILITY;

drop package body SYS.CWM2_OLAP_LEVEL_ATTRIBUTE;

drop package body SYS.CWM2_OLAP_LEVEL;

drop package body SYS.CWM2_OLAP_DIMENSION_ATTRIBUTE;

drop package body SYS.CWM2_OLAP_METADATA_REFRESH;

• Then I reran my good friend:

@?/rdbms/admin/utlrp

And it worked no more invalids.

• After that I  created my first workspace with:

cd /opt/oracle/product/11203_ee_64/db/owb/bin/unix

./reposinst.sh

Happy reading  and Take care .

Mathijs