3 Configuring Oracle Warehouse Builder for Windows

About Configuring OWB Installed as a Standalone Instance

To properly configure Oracle Warehouse Builder and its repository on a host that runs an earlier version of the database, such as Oracle Database 10g Release 2, you must:

• Ensure that the database is fully installed and working; if it is not, see Oracle Database Installation Guide for Microsoft Windows.

• Note the host name, the port, and Oracle service name of this database. The example here uses the following connection information: localhost:1521:orcl.

• Ensure that you have username and password for an account with SYSDBA privileges. The SYSDBA role has the necessary privileges for making changes to the basic configuration of Oracle Database, Oracle Warehouse Builder, and OWB Repository.

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 Oracle Warehouse Builder 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.

Starting with Oracle Warehouse Builder 11.1, the metadata repository is stored in the OWBSYS schema, inside a workspace. This contrasts with the terminology of Oracle Warehouse Builder 10.2, where a workspace corresponds to a repository.

Oracle Warehouse Builder repositories for version 11.2 and 11.1 cannot coexist on the same database; creating an 11.2 repository overwrites the contents of the 11.1 repository in the database. Oracle recommends that you back up the older OWB repositories before installing the 11.2 repository or upgrading to the 11.2 repository.

Cleaning an Oracle Warehouse Builder Repository

To clean an existing Oracle Warehouse Builder repository:

1. On the Microsoft Windows desktop, click Start and select Run.

2. Start SQL*Plus:

   C:>sqlplus
   SQL*Plus: Release 11.2 - Production on Mon Jul 27 12:05:12 2009
    
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
   

3. When prompted for user name, log in as owbsys user.

   Enter user-name: owbsys
   Enter password: password
   
   Connected to:
   Oracle Database 11g Enterprise Edition Release 11.2 - Production
   With the Partitioning, OLAP and Data Mining options
   

4. Stop Oracle Warehouse Builder Control Center service.

   SQL> @c:\OWB112\owb\rtp\sql\stop_service.sql
   

   See "stop_service" for more information.

5. Connect as user with administrative privileges:

   SQL> connect sys as sysdba
   Enter password: password
   

6. Run the clean_owbsys.sql script to drop all the objects in the existing OWBSYS schema:

   SQL> @C:\OWB112\owb\UnifiedRepos\clean_owbsys.sql
   

Creating an OWBSYS Schema with Repository Objects

The process of creating (or refreshing) an OWBSYS schema generates the corresponding locked user account within the specified tablespace; in this example, the tablespace is USERS. When the script completes, you have to unlock the account and set its password; the instructions in "Configuring OWB Installed as Part of Oracle Database 11g Release 2" show how to unlock OWBSYS and OWBSYS_AUDIT accounts. Note that the OWBSYS schema is populated only when you run the Repository Assistant for the first time and create the first workspace in the repository.

To set-up an OWBSYS schema with repository objects:

1. On the Microsoft Windows desktop, click Start and select Run.

2. At the command prompt, start SQL*Plus:

   C:\>sqlplus
   SQL*Plus: Release 11.2 - Production on Mon Jul 27 12:18:03 2009
    
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
   

3. When prompted for user name, log in as a user with administrative privileges.

   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
   

4. Run the cat_owb.sql script to set up OWBSYS in a tablespace in your database. For example, this command sets up OWBSYS in the USERS tablespace:

   SQL> @C:\OWB112\owb\UnifiedRepos\cat_owb.sql USERS
   

Configuring the Control Center Service

If you are installing OWB 11.2 on an older Oracle Database release, or if you integrating OWB with Oracle Business Intelligence Standard Edition (Oracle Discoverer), you must run the reset_owbcc_home.sql script to ensure that the Control Center Service functions correctly.

To configure the Control Center service:

1. In Windows Explorer, open Oracle Warehouse Builder 11.2 Oracle Home directory to confirm the correct Oracle Home path, paying particular attention to capitalization. In this example, the path is C:\OWB112.

   While Windows is generally case insensitive, the path entered in the next steps must be an exact match to Oracle Warehouse Builder home directory, as displayed in the Windows Explorer address bar. To avoid errors, copy and paste the path.

2. On the Microsoft Windows desktop, click Start and select Run.

3. At the command prompt, start SQL*Plus:

   C:\>sqlplus
   SQL*Plus: Release 11.2 - Production on Mon Jul 27 12:31:53 2009
    
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
   

4. When prompted for user name, log in as a user with administrative privileges.

   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
   

5. Run the reset_owbcc_home.sql script to force the Control Center service to run from Oracle Warehouse Builder 11.2 installation:

   SQL> @C:\OWB112\owb\UnifiedRepos\reset_owbcc_home.sql C:\OWB112
   

Unlocking the OWBSYS and OWBSYS_AUDIT Accounts

To unlock OWBSYS and OWBSYS_AUDIT accounts:

1. On the Microsoft Windows desktop, click Start and select Run.

2. At the command prompt, start SQL*Plus:

   C:\>sqlplus
   SQL*Plus: Release 11.2 - Production on Mon Jul 27 12:37:08 2009
    
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
   

3. When prompted for user name, log in as a user with administrative privileges.

   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
   

4. Unlock the OWBSYS user account, and set its new password:

   SQL> alter user owbsys identified by password account unlock;
   
   User altered.
   

5. Unlock the OWBSYS_AUDIT user account, and set its new password:

   SQL> alter user owbsys_audit identified by password account unlock;
   
   User altered.
   

Setting the OWB Home for Remote Installations

You must create a database directory OWB_REMOTE_ADMIN that is accessed by remote installations, so they can read the server's OWB_HOME/owb/bin/admin directory.

1. On the Microsoft Windows desktop, click Start and select Run.

2. At the command prompt, start SQL*Plus:

   C:\>sqlplus
   SQL*Plus: Release 11.2 - Production on Mon Jul 27 12:42:27 2009
    
   Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
   

3. When prompted for user name, log in as a user with administrative privileges.

   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
   

4. Set the OWB_REMOTE_HOME variable using script remote_owb_install.sql:

   SQL> @remote_owb_install.sql OWB_REMOTE_HOME