4 Installation of Additional Components

Installing Oracle Warehouse Builder and Integrating with Oracle Workflow

If you are installing Oracle Warehouse Builder in an Oracle RAC environment, see the Oracle RAC specific instructions in Oracle Warehouse Builder Installation and Administration Guide (in the section, "Installing Oracle Warehouse Builder in Oracle RAC Environments").

To use Oracle Warehouse Builder process flows, you must use Oracle Workflow to enable deployment. To install Oracle Warehouse Builder and integrate Oracle Workflow, take the following steps:

1. Go to $ORACLE_HOME/owb/wf/install

2. Execute wfinstall.csh

   The Oracle Workflow Configuration Assistant opens.

3. Enter values for the Workflow account, Workflow, SYS password, and TNS Connect Descriptor.

   For TNS Connect Descriptor, use the following syntax where you replace local-host, port-number, and service-name with the appropriate values.

   (DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=local-host)(PORT = port-number))) (CONNECT_DATA = (SERVICE_NAME = service-name))
   

   Click Submit.

Installing Oracle Warehouse Builder OWBSYS Schema

Before installing OWBSYS schema, check if the OWBSYS schema is in the database. 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), then OWBSYS schema exists.

If the database already has OWBSYS schema, drop the OWBSYS and OWBSYS_AUDIT schemas as follows:

Connect to sqlplus as sysdba
sql> drop user owbsys cascade;
sql> drop user owbsys_audit cascade;

Install the OWBSYS and OWBSYS_AUDIT schemas, as follows:

1. Install OWBSYS and OWBSYS_AUDIT schemas by executing following script as sysdba from sqlplus:

   $ORACLE_HOME/owb/bin/unix/UnifiedRepos/cat_owb.sql
   

2. Unlock the OWBSYS and OWBSYS_AUDIT schemas:

   alter user owbsys account unlock identified by owbsys;
   alter user owbsys_audit account unlock;
   

On Oracle Database Single Instance or the Primary Node on Oracle RAC:

Perform following steps for the Oracle Database on a single instance or for the Primary Node on Oracle RAC.

1. Open Warehouse Builder repository assistant to seed OWBSYS schema by creating a sample workspace with a sample new user as the workspace owner:

   $ORACLE_HOME/owb/bin/unix/reposinst.sh
   

2. Enter database connection information (host, port, and service name) and click Next.

3. Select Manage Warehouse Builder workspaces. Click Next.

4. Select Create a new Warehouse Builder workspace. Click Next.

5. Select Create a workspace with a new user as workspace owner. Click Next.

6. Enter SYSDBA user name and user password. Click Next.

7. Enter workspace owner name, workspace owner password, and workspace name.

   For example:

   workspace owner = sample_owner, workspace name = sampleworkspace
   

8. Click Next, Click Next, and Click Finish.

On the Remaining Oracle RAC Nodes:

Perform following steps on remaining Oracle RAC nodes:

1. Open Warehouse builder repository assistant to register the db instance on the current node as Oracle RAC instance:

   $ORACLE_HOME/owb/bin/unix/reposinst.sh
   

2. Enter database connection information (host, port, and service name) and click Next.

3. Select Register a Real Application Cluster Instance. Click Next.

4. Enter OWBSYS schema password. Click Next and Click Finish.

5. Connect to OWBSYS schema using sqlplus on the primary node of Oracle RAC and execute the following script:

   $ORACLE_HOME/owb/rtp/sql/reset_repository.sql
   

   This step updates $ORACLE_HOME/owb/bin/admin/rtrepos.properties file on the primary node. Check the timestamp of this file to ensure that it is updated.

6. Copy the updated rtrepos.properties file to all remaining nodes to same directory ($ORACLE_HOME/owb/bin/admin).

Creating Oracle Warehouse Builder Workspace for Oracle Retail Data Model

To create an Oracle Warehouse builder workspace with new user as owner of the workspace, perform the following steps:

1. Open Warehouse builder repository assistant for creating an OWB workspace for Oracle Retail Data Model with a new user as the workspace owner:

   $ORACLE_HOME/owb/bin/unix/reposinst.sh
   

2. Enter database connection information (host, port, and service name) and click Next.

3. Select Manage Warehouse Builder workspaces. Click Next.

4. Select Create a new Warehouse Builder workspace. Click Next.

5. Select Create a workspace with a new user as workspace owner. Click Next.

6. Enter SYSDBA user name and user password. Click Next.

7. Enter workspace owner name, workspace owner password, and workspace name:

   workspace owner name = owb_owner &
   workspace name = ordmworkspace
   

8. Click Next, Click Next, and Click Finish.

Importing Oracle Retail Data Model Intra-ETL into Workflow

To import the Oracle Retail Data Model intra-ETL into Workflow, take the following steps:

1. Log into the Design Center of Oracle Warehouse Builder.

2. Select File, then Import, and then Warehouse Builder Metadata.

3. For file, specify the following value:

   $ORACLE_HOME/ordm/pdm/relational/intra_etl/owb/ORDM_Intra_ETL.mdl
   

4. Select Import all objects from file.

5. Select Create new metadata only.

6. Click Import.

7. After the import, you can see process flows under ORDM_INTRA_ETL project of Oracle Workflow.

8. Modify the location parameter for ordm_sys schema:

   Expand Locations -> Databases -> Oracle.

   Right click ORDM_SYS_LOCATION and click Open.

9. Enter ordm_sys schema password and database connection information. Click Test Connection to test the connection. Click OK if successful.

Configuring Oracle Warehouse Builder Workflow

To configure Oracle Warehouse Builder workflow to work with Oracle Retail Data Model, take the following steps:

1. In the Design Center of Oracle Warehouse Builder, select View, and then Location Navigator.

2. Expand Locations, then Process Flow and Schedules, then Oracle Workflow.

3. Right click OWF_LOCATION, select Open.

4. Edit the connection information.

5. Click Test Connection to test the connection; if successful, click OK.