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:
- The clean_owbsys.sql script drops the contents of any existing OWBSYS schema installations, but leaves the schema otherwise intact.
- 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.
- The reset_owbcc_home.sql script ensures that Oracle Warehouse Builder uses the 11.2 version of the Control Center Service.
- [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
