OWB - 11gR1 Installation and configuration on Windows

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

This article talk about the installation and configuration of OWB in a Single Computer Topology architecture.

It's the standard installation that you will find on a laptop.

This architecture is composed of three parts :

  • The “Design Client” permit to design the data integration process (mappings) (source, target, data transformation,…)
  • The OWB Control Center Service is the part of the software which deploy the mappings (program) and execute them.
  • The OWB Browser which permit to browse the metadata (table,column, mapping, …)

For the migration possibilties, you can read this part of the Oracle reference documentation

In a production environment, it's recommended to run the repository database in ARCHIVELOG mode for backup purpose.

Advertising

3 - Prerequisites

4 - Installation

Oracle Warehouse Builder is part of every database installation on hosts running Oracle Database 11g Release 2. It's why you can read in the download page of OWB :

Oracle Warehouse Builder 11g Release 2 (11.2.0.1.0) Standalone Software

This software is automatically installed when you install Oracle Database 11g Release 2.

IMPORTANT: It is not necessary to download this standalone software unless you have any of the following needs:
  * You are installing OWB 11.2 and hosting an OWB Repository on one of: Oracle Database 10g Release 1, 
Oracle Database 10g Release 2, or Oracle Database 11g Release 1.
  * You are running the OWB 11.2 Design Client on a developer's computer.
  * You are installing OWB 11.2 on a computer that runs the Control Center Agent.

As we are not in one of this case, we don't need to download it. The software installation is embedded in the database installation.

To confirm that you have Oracle Warehouse Builder installed, check that OWBSYS schema exists, log in to the Database as DBA and issue the following statements:

SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME='OWBSYS';

If this query returns a value larger than zero (0), OWBSYS schema exists.

If the OWBSYS schema does not exist, take the following steps:

  • Go to the $ORACLE_HOME/owb/UnifiedRepos directory.
  • Login to the Database as SYSDBA.
  • Execute the following SQL statement.
@cat_owb.SQL
  • When prompted to enter a tablespace name, input USERS
Advertising

5 - Configuration

5.1 - Unlock Account

Once Oracle Database 11g Release 2 is installed, you only have to unlock two accounts:

  • OWBSYS
  • and OWBSYS_AUDIT.

At the command prompt, start SQL*Plus:

C:\>sqlplus
SQL*Plus: RELEASE 11.2 - Production ON Mon Jul 27 11:34:06 2009
 
Copyright (c) 1982, 2009, Oracle.  ALL rights reserved.
 
Enter user-name: sys AS sysdba
Enter password: password
 
Connected TO:
Oracle Database 11g Enterprise Edition RELEASE 11.2 - Production
WITH the Partitioning, OLAP AND Data Mining options
 
SQL> ALTER USER owbsys identified BY password account unlock;
 
      USER altered.
 
SQL> ALTER USER owbsys_audit identified BY password account unlock;
 
      USER altered.

If you want to install OWB 11g on an Oracle Database 10g, you must download the software and follow this section Configuring OWB Installed as a Standalone Instance

5.2 - Creating a standard Workspace in the Repository

6 - Integrating with Oracle Workflow

6.1 - Install the Workflow Server

6.2 - Install the Workflow Client [Optional]

The installation of Oracle Workflow client is optional because the Process Flow Editor in Warehouse Builder replaces its functionality. You may want to install Oracle Workflow client if you want to view the deployed Warehouse Builder processes in Oracle Workflow.

On the computer that hosts the Warehouse Builder client, install Oracle Workflow client from Oracle Workflow client CD.

Advertising

6.2.1 - Create a Workflow Proxy User.

When the Workflow instance is not on the same computer that hosts the Warehouse Builder repository, you need to create a proxy user.

Within the database hosting the repository, use SQL Plus to create a user and grant it the OWB_USER role as a default. This enables the remote Oracle Workflow instance to connect to the services provided by the Control Center.

7 - Others

7.1 - Right on the File System Directory

7.2 - Recompilation

To ensure that all objects are valid, log in to the database with a DBA id and password and recompile all objects in the OWB owner schema:

exec utl_recomp.recomp_serial('OWBSYS');
exec utl_recomp.recomp_serial('OWBSYS_AUDIT');

8 - Documentation/Reference