Skip Headers
Oracle® Warehouse Builder Installation and Administration Guide
11g Release 1 (11.1) for Windows and UNIX

B31280-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

1 Installation Overview and Requirements

This chapter outlines the installation process, discusses the hardware and software requirements, and introduces the Oracle Warehouse Builder architecture and its components. This chapter includes the following topics:

What's New in Installing Warehouse Builder

This section includes important tips for users who are familiar with installing previous version of Warehouse Builder. If you are new to Warehouse Builder, please skip to "Warehouse Builder Architecture and Components".

Oracle Warehouse Builder Embedded in Oracle Database 11g

Beginning in this release, Warehouse Builder is automatically installed when you install the Oracle Database 11g Release 1 (11.1).

To install Warehouse Builder on client computers, download and install the standalone Warehouse Builder software. The standalone software is also required for certain cases such as installing Warehouse Builder 11g onto Oracle Database 10g. For more information, see "Downloading and Installing the Standalone Warehouse Builder Software".

SYSDBA Privileges No Longer Required for Installation

Previously, installing a Warehouse Builder repository on an Oracle Database required a user with SYSDBA privileges. Beginning in this release, this is no longer required. A schema OWBSYS is created while installing Oracle Database 11g Release 1. OWBSYS holds the metadata which is divided into workspaces. To start using Warehouse Builder, you create a new workspace. You do not need SYSDBA privileges.

Unified Warehouse Builder Repository with Multiple Workspaces

Previously, users accessed the repository as a whole. Beginning with this release, users are assigned to workspaces within the repository. Thus, instead of granting access to a repository, you grant access to a workspace.

Starting and Stopping the Browser Listener

In previous releases, Windows users could start and stop the Browser Listener from the program group. You could go to Start, Programs, OWB_ORACLE_HOME, Warehouse Builder and click Start Browser Listener and Stop Browser Listener. Beginning in this release, you can only stop and start the Browser Listener from a command line. This change improves usability and facilitates new password requirements.

Oracle Workflow Embedded within Warehouse Builder

Previously, if you wanted to utilize Oracle Workflow to manage job dependencies or if you wanted to deploy process flows, it was necessary to install Oracle Workflow. Beginning in this release, these additional installation steps are no longer required as Oracle Workflow components are embedded within Warehouse Builder.

Security Based on PL/SQL Package No Longer Supported

Beginning with Warehouse Builder11g Release 1 (11.1), the preferred method of implementing metadata security is through the user interface available in the Design Center and described in the Oracle Warehouse Builder Sources and Targets Guide. If, in a previous release, you implemented security using a PL/SQL package, Warehouse Builder 11g Release 1 (11.1) does support that implementation.

Warehouse Builder Architecture and Components

Oracle Warehouse Builder is an information integration tool that leverages the Oracle Database to transform data into high-quality information. The Oracle Database is a central component in the Warehouse Builder architecture because the Database hosts the Warehouse Builder repository and the code generated by Warehouse Builder.

Figure 1-1 illustrates the interaction of the major components of the Warehouse Builder software.

The Design Center is the user interface for designing, managing, scheduling, and deploying ETL processes for moving and transforming data. All metadata associated with the work done in the Design Center is stored in the Warehouse Builder Repository. The repository is hosted on an Oracle Database and you can use the Repository Browser to report on the metadata in the repository. Also hosted on an Oracle Database is the Target Schema to which Warehouse Builder loads data resulting from the ETL processes that you run through the Control Center Service.

Figure 1-1 Warehouse Builder Components

This graphic is described in the surrounding text.
Description of "Figure 1-1 Warehouse Builder Components"

Design Center

The Design Center provides the graphical interface for defining sources, designing targets, and designing ETL and other data transformation processes. As you create a design in the Design Center, you are working with logical designs only, not physical implementations.

Control Center Manager

The Control Center Manager is the console for managing deployment and job execution. Previously, in the Design Center, you created a logical design for transforming data. Your logical design may have introduced objects that do not yet exist, such as staging tables. Now in the Control Center Manager, you deploy the design. That is, you instruct Warehouse Builder to create the necessary physical objects such as the staging tables, for example. Subsequently, you execute the design. During execution, Warehouse Builder runs the code associated with extracting, transforming, and loading the data.

Target Schema

The target schema is the target to which you load your data and the data objects that you designed in the Design Center such as cubes, dimensions, views, and mappings. The target schema contains Warehouse Builder components such as synonyms that enable the ETL mappings to access the audit/service packages in the repository. The repository stores all information pertaining to the target schema such as execution and deployment information.

Notice that the target schema is not a Warehouse Builder software component but rather an existing component of the Oracle Database. As such, you can associate multiple target schemas with a single Warehouse Builder repository. You can have a 1 to 1 relationship or many target schemas to a single repository.

Warehouse Builder Repository

The repository schema stores metadata definitions for all the sources, targets, and ETL processes that constitute your design metadata. In addition to containing design metadata, a repository can also contains the runtime data generated by the Control Center Manager and Control Center Service.

As part of the initial installation of Warehouse Builder, you use the Repository Assistant to define the repository in an Oracle Database. You can host a Warehouse Builder 11g repository either on Oracle Database 10g Release 2 (10.2) or 11g.

About Workspaces

In defining the repository, you create one or more workspaces with each workspace corresponding to a set of users working on related projects. A common practice is to create separate workspaces for development, testing, and production. Using this practice, you can allow users such as your developers access to the development and testing workspaces but restrict them from the production workspace.

Later in the implementation cycle, you also use the Repository Assistant to manage existing workspaces or create new ones.

For examples of the options for implementing repositories, see "Implementation Strategies".

Repository Browser

The Repository Browser is a web browser interface for reporting on the repository. You can view the metadata, create reports, audit runtime operations and perform lineage and impact analysis. The Repository Browser is organized such that you can browse design-specific and control center-specific information.

Control Center Service

The Control Center Service is the component that enables you to register locations. It also enables deployment and execution of the ETL logic you design in the Design Center such as mappings and process flows.

Implementation Strategies

This section provides an overview of the various choices and considerations for implementing Warehouse Builder. Detailed instructions on how to implement each option are provided in subsequent sections.

The choices for implementing Warehouse Builder include the following:

Basic Implementation

The simplest option is to host the client and server components on a single local computer, which is suitable if you are performing a proof of concept or launching a pilot program.

If you install Oracle Database 11g, the most commonly used Warehouse Builder components are also installed for you. The next step is to start the Warehouse Builder Repository Assistant to define a workspace and workspace user.

Two Warehouse Builder components not included in the Oracle Database 11g installation are deployment to Discoverer and execution of runtime scripting commands. To access these components, see "Downloading and Installing the Standalone Warehouse Builder Software".

To implement a basic implementation with a pre-existing Oracle Database 10g Release 2 installation, see "Downloading and Installing the Standalone Warehouse Builder Software".

Traditional Client/ Server Implementation

This is the most commonly implemented strategy with client components residing on multiple client computers and server components residing on a single server.

When you install the Oracle Database 11g, the Database installation includes all the Warehouse Builder components necessary for the server. You need only start the Warehouse Builder Repository Assistant to define workspaces and workspace users. Subsequently, you download and install the Warehouse Builder software on the client computers.

Repository on Oracle Database 10g Release 2

When you install Oracle Database 11g, the Warehouse Builder server components are also installed.

However, you may choose to host the repository on Oracle Database 10g Release 2. There are no known limitations or restrictions for hosting a Warehouse Builder 11g repository on Oracle Database 10g Release 2, other than the fact that you will not have access to functionality new in Oracle Database 11g.

For instructions, see "Hosting the Repository on Oracle Database 10g Release 2" .

Separate Design and Runtime Environments Implementation

In an implementation such as shown in Figure 1-2, one repository stores the metadata necessary for designing in Warehouse Builder. This includes the metadata for sources, targets, and ETL processes that users access through the Design Center.

You can define a separate environment dedicated to job execution. Refer to this environment as the runtime environment. Notice that each runtime environment requires a single Control Center Service to manage the control center and its deployment and execution activities.

Runtime data is stored in audit tables. You can access the audit tables through the control center specific reports in the Repository Browser.

The only communication between the design repository and the control center repository occurs when you deploy objects to the target schema.

Figure 1-2 Split Repositories Implementation

This graphic is described in the surrounding text.
Description of "Figure 1-2 Split Repositories Implementation"

Development, Test, and Production Environments

Organizations typically maintain separate environments for developing and testing prior to releasing a project into a production environment. You can have multiple physical environments based on a common logical design in a single Warehouse Builder repository. Or, you can associate each physical environment with its own Warehouse Builder repository.

To implement either strategy, refer to Chapter 11, "Managing Multiple Environments from Development to Production".

Remote Runtime Environment Implementation

The Control Center Service is the Warehouse Builder server component that governs the deployment of objects to target schemas. Most commonly, the Control Center Service is installed on the computers hosting the target schemas.

In some cases, though, it may be desirable to run the Control Center on a computer that does not host an Oracle Database. You may want to implement a remote runtime for purposes of load balancing. Also consider remote runtime if your company security policies restrict you from installing additional software on the computer hosting the target schema.

To implement any of these scenarios, refer to "Implementing a Remote Runtime (Optional)".

General Steps for Installing Warehouse Builder

If you want to upgrade an existing installation, then refer to Chapter 3, "Upgrading to Oracle Warehouse Builder 11g Release 1 (11.1)".

If you want to begin a new installation, then use the following instructions to determine which topics to reference.

To begin a new installation:

  1. Determine your implementation strategy.

    To accommodate a variety of environments and customer needs, Warehouse Builder offers you flexibility in where you install server and client components.

    Review "Warehouse Builder Architecture and Components" and "Understanding the Installation Requirements" to develop an implementation strategy.

  2. Review the Oracle Warehouse Builder Release Notes, part number B40098, available at http://www.oracle.com/technetwork/index.html.

  3. Preparing the Server

  4. For Oracle RAC environments only, proceed to "Steps for Installing Warehouse Builder in Oracle RAC Environments". Otherwise, continue with the next step in these instructions.

  5. Preparing the Oracle Database

  6. If necessary, install the Warehouse Builder standalone software.

    Skip this step if the Warehouse Builder repository is hosted on Oracle Database 11g and you do not intend to integrate with Oracle Discoverer or utilize runtime scripting commands.

    Otherwise, refer to the instructions in "Downloading and Installing the Standalone Warehouse Builder Software".

  7. To utilize the default Warehouse Builder schema installed in Oracle Database 11g, first unlock the schema.

    Connect to SQL*Plus as the SYS or SYSDBA user. Run the following commands:

    SQL> ALTER USER OWBSYS ACCOUNT UNLOCK;

    SQL> ALTER USER OWBSYS IDENTIFIED BY owbsyspasswd;

  8. Define Warehouse Builder workspaces and workspace users on the Oracle Database.

    Start the Warehouse Builder Repository Assistant on the computer hosting the Oracle Database.

    To start the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant.

    To start the Repository Assistant on UNIX, run

    OWB_ORACLE_HOME/owb/bin/unix/reposinst.sh

    Follow the prompts in the Repository Assistant. Or, for detailed instructions, see Chapter 2, "Managing Workspaces and Workspace Users".

  9. Setting the Security Policy for the Repository

    When you install a repository, Warehouse Builder enforces a default metadata security policy. The default policy is a minimal security policy appropriate for proof-of-concept or pilot projects.

    You can override the default by selecting a maximum security policy. Alternatively, you can use the security interface in Warehouse Builder to design your own security policy. In either of these two cases, ensure that repository database has the Advanced Security Option (ASO) enabled.

  10. See Chapter 5, "Installing and Enabling Optional Components" for instructions on enabling optional components such as browsers, third party tools, and related Oracle products.

  11. Install the Warehouse Builder software on the client computers.

    Repeat the steps in "Installing the Warehouse Builder Software" on each computer to be used as a client.

  12. When you complete the installation process, verify that the Warehouse Builder components can be successfully launched as described in "Launching Warehouse Builder Components".

Understanding the Installation Requirements

Refer to this section as you develop your implementation strategy.

Table 1-1 lists the components required in an Oracle Warehouse Builder environment. Optionally, you can also integrate with the components listed in Table 1-2.

Required Components

Table 1-1 lists the components required in an Oracle Warehouse Builder environment. The table summarizes important considerations for installing each component and identifies where to look for further details.

Table 1-1 Required Components

Components Important Considerations

Server

The operating system can be any Windows or UNIX platform supported by Oracle Database.

For the most up-to-date list of certified hardware platforms and operating system versions, review the certification matrix on My Oracle Support (formerly Oracle MetaLink) at https://support.oracle.com

For Windows, both 32-bit and 64-bit architectures are supported. Ensure a minimum of 850 MB disk space, 768 MB available memory, and 768 MB of page file size, TMP, or swap space.

All UNIX platforms require 768 MB available memory and 1100 MB of page file size, TMP, or swap space. For Linux, ensure a minimum of 1100 MB disk space. More disk space is required for all other UNIX platforms.

See "Preparing the Server".

Oracle Database

The database can be any of the following versions:

  • Oracle Database 11g R1 Standard Edition

  • Oracle Database 11gR1 Enterprise Edition

  • Oracle Database 10g R2 Standard Edition

  • Oracle Database 10g R2 Enterprise Edition

Ensure that DB_BLOCK_SIZE is set to the most optimal value of 16384 or the largest block size the server allows. Optionally, you may need to change additional configuration settings as described in:

See "Preparing the Oracle Database".

Client computer

Client computers must have either a Windows or a Linux operating system.

For Windows, ensure that the computer has a minimum of 850 MB disk space, 768 MB available memory, and 1GB of page file size, TMP, or swap space.

For Linux 32-bit, ensure that the computer has a minimum of 1100 MB disk space, 768 MB available memory, and 1GB of page file size, TMP, or swap space.

See "Preparing Client Computers".

Oracle Universal Installer

Start the Universal Installer as described in "Installing the Warehouse Builder Software".

Be sure to specify a separate home directory for Warehouse Builder.

For Windows, ensure that you log onto the Windows system as an Administrator and then start the Universal Installer.

Oracle Warehouse Builder Components

  • Warehouse Builder Design Center for designing ETL processes

  • OMB Plus, the scripting language and interface

  • Warehouse Builder repository

  • Repository Assistant, for defining repositories

  • Control Center Service

  • Repository Browser for viewing and reporting on metadata and audit data in the repository.

For an overview, see "Warehouse Builder Architecture and Components".


Compatible Components

Table 1-2 lists some of the optional components that are compatible with an Oracle Warehouse Builder environment. The table does not list all compatible components, but does list those components that either effect how you install Warehouse Builder or require intervention by an Oracle Database Administrator.

The table summarizes important considerations for each optional component and identifies where to look for further details.

Table 1-2 Compatible Components

Components Important Considerations

Oracle Discoverer

See "Downloading and Installing the Standalone Warehouse Builder Software".

Oracle E-Business Suite

You have the option of making data and metadata from E-Business Suite available to Warehouse Builder users.

See "Enabling Integration with Oracle E-Business Suite"

You can also integrate with other applications such as PeopleSoft, SAP, and Siebel. Integrating with these products is discussed in Oracle Warehouse Builder User's Guide.

Oracle Workflow

You can use Oracle Workflow to manage job dependencies.

If you plan to use Warehouse Builder process flows, then enable Oracle Workflow to facilitate deployment.

Beginning with Oracle 11g Release 1, Oracle Workflow is shipped with the Warehouse Builder software and licensed for using Warehouse Builder with the Oracle 11g Database.

If the Warehouse Builder repository is hosted on Oracle 10g Release 2, you must install an appropriate version of Oracle Workflow 2.6.4 and follow "Enabling Integration with Oracle Workflow"

Third Party Name and Address Data

You can cleanse name and address data based on third party name and address data.

Requires the following from one of the certified vendors listed on Oracle Technology Network:


Preparing the Server

If you have yet to install an Oracle Database on the server, then consult the Oracle Database Installation Guide for your operating system. Be sure to install the required operating system patches before installing the Oracle Database.

Review the certification matrix on the My Oracle Support (formerly Oracle MetaLink) at https://support.oracle.com, for the most up-to-date list of certified hardware platforms and operating system versions. This Web site also provides compatible client and database versions, patches, and workaround information for bugs. My Oracle Support (formerly Oracle MetaLink) is available at the following URL:

https://support.oracle.com

If you intend to host the Warehouse Builder repository on Oracle Database 11g, no additional steps are required. Proceed with the next topic, "Preparing the Oracle Database".

If you intend to host the Warehouse Builder repository on Oracle Database 10g Release 2, proceed with either "UNIX Servers Hosting a Warehouse Builder Repository on Oracle 10g Release 2" or "Windows Servers Hosting a Warehouse Builder Repository on Oracle 10g Release 2".

UNIX Servers Hosting a Warehouse Builder Repository on Oracle 10g Release 2

On all UNIX platforms other than Linux, only the Warehouse Builder server components are supported. For Linux 32-bit platforms, however, you can install both server and client components. That is, you can install the Repository and Control Center Service on a UNIX server but the Design Center and Repository Browser require either a Windows or Linux 32-bit platform.

If you are installing only the server components, then ensure that the UNIX operating system meets the requirements listed in Table 1-3. If you are also installing the client components to be accessed by Linux, then see the additional hardware requirements listed in "Preparing Client Computers".

Table 1-3 UNIX Operating Environment Software Requirements

Requirement Value

Disk Space

1100 MB for Linux. All other UNIX platforms require more disk space.

Available Memory

768 MB for Linux.

Memory requirements increase depending on the functions being performed and the number of users connected.

Page File Size, TMP, or Swap Space

1 GB for Linux.


Setting Environmental Variables on a UNIX Server

When installing on UNIX, you must specify the environmental variable for the Oracle home, that is, the directory in which Warehouse Builder is to be installed.

Use the UNIX commands listed in Table 1-4 where full_path is the path into which you install Warehouse Builder.

Table 1-4 Setting Oracle home on a UNIX server

Variable C Shell Command Korn Shell Command Bourne Shell Command

ORACLE_HOME

setenv ORACLE_HOME full_path

export ORACLE_HOME=full_path

ORACLE_HOME=full_path; export ORACLE_HOME


Windows Servers Hosting a Warehouse Builder Repository on Oracle 10g Release 2

On Windows platforms, you can install either the Warehouse Builder server or client components or both components on the same computer. Table 1-5 contains the Windows operating system requirements. These requirements are in addition to the requirements of any other Oracle products you are installing on the same computer. Refer to the documentation for each Oracle product you are installing to determine complete system requirements.

Table 1-5 Windows Operating Environment Software Requirements

Requirement Value

Disk Space

850 MB

Available Memory

768 MB

Memory requirements increase depending on the functions being performed and the number of users connected.

Page File Size, TMP, or Swap Space

1 GB

System Architecture

32-bit and 64-bit

Note that while the OWB Design Center client is installed with the DB, only the 32-bit Windows and 32-bit Linux Design Center clients are certified and supported by Oracle. For Windows, Design Center is only supported on 32-bit client operating systems, not on Windows Server 2003.

Note also that Oracle provides both 32-bit and 64-bit versions of Warehouse Builder server components. The 32-bit version of Warehouse Builder must run on the 32-bit version of the operating system. The 64-bit version of Warehouse Builder must run on the 64-bit version of the operating system.

Operating System

Warehouse Builder server components are supported on the following operating systems:

  • Windows XP Professional

  • Windows 2000 with Service Pack 1 or higher. All editions, including Terminal Services and Windows 2000 MultiLanguage Edition (MLE), are supported.

  • Windows Server 2003. Note that the Design Center client is not supported in Windows Server 2003.

Oracle Warehouse Builder Design Center client is only supported on Linux x86 32-bit and the following Windows 32-bit platforms:

  • Windows XP Professional

  • Windows 2000 with Service Pack 1 or higher. All editions, including Terminal Services and Windows 2000 MultiLanguage Edition (MLE), are supported.


Preparing the Oracle Database

Warehouse Builder 11g Release 1 (11.1) is supported and certified for use with the following releases of the Oracle Database:

Note: Warehouse Builder has not been tested or certified and therefore is not supported for use with the Personal or Express Editions of the Oracle Database.

When you install Enterprise or Standard Editions of Oracle Database 11g, the installation provides you with an unpopulated schema, OWB_SYS, for use in Oracle Warehouse Builder 11g.

You must install the Oracle Database on any computer that you intend to create a Warehouse Builder design repository or a target schema as described in subsequent chapters.

The size requirements for the repository varies according to the character set. The tablespace usage of an empty repository with an AL32UTF8 character set, for example, is approximately 90 MB. To accommodate an average usage of the Warehouse Builder repository with a single-byte character set, Oracle recommends an additional 1330 MB for a total of 1420 MB recommended. For multibyte character sets, extrapolate a larger tablespace requirement.

To successfully create a Warehouse Builder repository, the following Oracle Database components are required:

If you intend to implement one of the metadata security options available in Warehouse Builder, then enable the Oracle Advanced Security option in the database. See "Setting the Security Policy for the Repository" for an overview of the metadata security options.

Database Configuration Settings for the Warehouse Builder Repository

Oracle 11g Database Configuration Settings

The Oracle 11g Database self tunes its configuration settings to optimize server resources for hosting both the design and runtime components. The only additional step you may need is "Configuring the Target Data File Path for Flat File Targets".

Oracle 10g Database Configuration Settings

As with Oracle 11g Database, the Oracle 10g Database also self tunes with the possible exception of "Configuring the Target Data File Path for Flat File Targets".

Additionally, you have the option of maintaining the Warehouse Builder design and runtime components in separate repositories. If so, then refer to "Configuring Oracle 10g Databases for Design and Runtime Repositories (Optional)".

Configuring the Target Data File Path for Flat File Targets

To configure the Target Data File Path for Flat file Targets, you set this path in the init.ora file of the warehouse instance. Set the UTL_FILE_DIR parameter to the directory for the flat file targets so that the database has access to it.

For example, for the output file location D:\Data\FlatFiles\File1.dat, set the UTL_FILE_DIR parameter in your init.ora file to:

UTL_FILE_DIR = D:\Data\FlatFiles 

For multiple valid file locations, such as both D:\Data\FlatFiles and E:\OtherData, set the parameter in init.ora to:

UTL_FILE_DIR = D:\Data\FlatFiles 
UTL_FILE_DIR = E:\OtherData 

These lines must be consecutive in the init.ora file.

You can bypass this checking of directories by using the following command:

UTL_FILE_DIR = * 

Configuring Oracle 10g Databases for Design and Runtime Repositories (Optional)

This section lists the configuration parameters that ensure performance when using Oracle 10g databases to separately host a design repository and a runtime repository for Warehouse Builder.

Parameters for the Design Repository Database Instance

The Oracle Database self tunes its configuration settings to optimize server resources. To ensure that Warehouse Builder performs effectively, verify that DB_BLOCK_SIZE is set to its optimal value.

Table 1-6 lists the initialization parameters for a Warehouse Builder design repository.

Table 1-6 Initialization Parameters for the Design Repository Instance

Initialization Parameter Set to Value Comments

COMPATIBLE

db value

Set this to value to equal the release number of the Oracle Database. For example, specify 10 for 10g.

If this parameter is not in the initialization file, then add it to the end of the file.

DB_BLOCK_SIZE

8192

This parameter is set when the database is created. It cannot be changed.

Warehouse Builder does not recommend a value higher than 8192 for a design repository.

DB_CACHE_SIZE

104877600

This is 100 MB.

LOCK_SGA

TRUE

Oracle recommends locking the design SGA in physical memory.

O7_DICTIONARY_ACCESSIBILITY

TRUE

Set this to TRUE as an alternative to setting REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE.

OPEN_CURSORS

300

You may specify a higher value.

REMOTE_LOGIN_PASSWORDFILE

EXCLUSIVE

To enable the user sys to connect as sysdba, set this parameter to EXCLUSIVE.

If, however, this parameter must be set to NONE, then set O7_DICTIONARY_ACCESSIBILITY to TRUE.


Parameters for the Runtime Repository Database Instance

To support the Warehouse Builder runtime component, you may need to modify the Oracle Database instance. Table 1-7 lists the database configuration parameters.

Table 1-7 Initialization Parameters for the Runtime Instance

Initialization Parameter Set to Value Comments

AQ_TM_PROCESSES

1

This parameter is required for the Warehouse Builder and Oracle Workflow advanced queuing system.

COMPATIBLE

db value

Set this to value to equal the release number of the Oracle Database.

If this parameter is not in the initialization file, then add it to the end of the file.

DB_BLOCK_SIZE

16384

This parameter is set when the database is created. Do not change it.

The recommended value is 16384. If your server does not allow a block size this large, then use the largest size available. If your computer has less than 512 MB of RAM, then a value of 9600 is recommended.

DB_CACHE_SIZE

314632800

Set this value to 300 MB or as high as the system permits. You may need to adjust operating system parameters to allow larger shared memory segments.

Do not set any value for the DB_CACHE_SIZE parameter if you set a value for the SGA_TARGET parameter.

DB_FILE_MULTIPLE_BLOCK_READ_COUNT

16

A value of 16 is recommended, but 32 is preferred.

DB_WRITER_PROCESSES

see comments

If you have fewer than 8 CPUs, then set DB_WRITER_PROCESSES to 1. Increase this parameter value by 2 for every additional 8 CPUs.

DBWR_IO_SLAVES

n

n is the number of CPUs.

Disable this parameter by setting it to 0 if:

  • DB_WRITER_PROCESSES has a value greater than 1. In this case, tuning the DBWR_IO_SLAVES parameter has no effect.

  • there is only 1 CPU, and the platform does not support asynchronous I/O.

DISK_ASYNCH_IO

TRUE

If the platform does not support asynchronous I/O, then set DBWR_IO_SLAVES to a positive number, such as 4, to simulate asynchronous I/O.

ENQUEUE_RESOURCES

3000 or higher if you are importing large MDL files.

A minimum setting of '1' is required for the install to complete without error.

JAVA_POOL_SIZE

20 MB

The minimum recommended value is 20 MB.

Do not set any value for the JAVA_POOL_SIZE parameter if you set a value other than 0 for the SGA_TARGET parameter.

JOB_QUEUE_PROCESSES

greater than 10

Optimal setting is 10. If JOB_QUEUE_PROCESSES is set to 0, then the Control Center Service does not run and produces error messages.

LARGE_POOL_SIZE

0

Do not set any value for this parameter if you set a value for the SGA_TARGET parameter. This parameter enables the server to set the LARGE_POOL_SIZE automatically.

Prerequisite: PARALLEL_AUTOMATIC_TUNING must be set to TRUE.

LOG_BUFFER

See comments

Set the value to larger than 512K and must be 128K times the number of CPUs.

LOG_CHECKPOINT_TIMEOUT

3000

This setting increases the timeout for performing checkpoints from the default 3 minutes to 5 minutes.

MAX_COMMIT_PROPAGATION_DELAY

0

This is only required when installing on Oracle RAC systems. If it is not set to 0, then data propagation delays may cause NO_DATA_FOUND errors in the Control Center Service.

OPEN_CURSORS

500

You may specify a higher value if you start multiple sessions or if you run multiple or complicated mappings in one session.

OPTIMIZER_MODE

all_rows

For other possible optimizer modes, see Oracle Designing and Tuning for Performance, Oracle Database Performance Tuning Guide and Reference, and Oracle Data Warehousing Guide.

PARALLEL_ADAPTIVE_MULTI_USER

TRUE

Set PARALLEL_AUTOMATIC_TUNING to TRUE as a prerequisite for this parameter.

PARALLEL_AUTOMATIC_TUNING

TRUE

This setting delegates the task of tuning parallel processing to the server.

Set this parameter for Oracle9i or Oracle8i databases only. For Oracle 10g and later, this parameter is not available and setting SGA_TARGET to a nonzero value is recommended.

PGA_AGGREGATE_TARGET

314572800

This is 300 MB. If you perform frequent sorting and aggregation, then you can increase this value. However, PGA_AGGREGATE_TARGET must be smaller than the available physical memory size.

PLSQL_OPTIMIZE_LEVEL

2

The PL/SQL compiler in Oracle Database can perform more elaborate optimization on PL/SQL code.

QUERY_REWRITE_ENABLED

TRUE

Set this parameter to TRUE if you plan to generate materialized views with the QUERY REWRITE option.

REMOTE_LOGIN_PASSWORDFILE

EXCLUSIVE

You must use the SYS account with SYSDBA privileges to access or create a runtime schema. The workspace user requires access to certain v_$ tables. These grants are made by the SYSDBA account when you create the workspace. This setting ensures that the SYSDBA privilege is granted to SYS.

RESOURCE_MANAGER_PLAN

plan_name

Oracle strongly recommends creating a resource plan for managing resource usages for Warehouse Builder runtime. Refer to the Oracle Database Administration Guide for information on resource plans.

SGA_TARGET

500 MB to 1 GB

The larger value, or as close to it as possible, is recommended if computer memory allows it.

If you set the SGA_TARGET parameter, do not set these following parameters which the server automatically adjusts:

  • JAVA_POOL_SIZE

  • DB_CACHE_SIZE

  • LARGE_POOL_SIZE

  • SHARED_POOL_SIZE

Alternatively, you can set the SGA_TARGET parameter to 0, which turns off the automatic sizing feature. In that case, follow the recommendations on sizing the preceding four parameters.

Note: For Oracle 10g and later, setting SGA_TARGET is recommended.

SHARED_POOL_SIZE

419430400

The recommended minimum value is 400 MB.

Do not set any value for the SHARED_POOL_SIZE parameter if you set a value for the SGA_TARGET parameter.

STATISTICS_LEVEL

TYPICAL

 

UNDO_MANAGEMENT

AUTO

With this setting, you do not have to create rollback segments.

UTL_FILE_DIR

*

Specifies the directories that PL/SQL can use for file input and output. UTL_FILE_DIR = * specifies that all directories can be used for file input and output. If you want to specify individual directories, then repeat this parameter on contiguous lines for each directory.

If you use flat file targets in Warehouse Builder, then set this parameter to the directory where you want to create the flat file target so that your database engine has access to it. Refer to "Configuring the Target Data File Path for Flat File Targets".

WORKAREA_SIZE_POLICY

AUTO

.


Preparing Client Computers

For Windows, ensure that the computer has a minimum of 850 MB disk space, 768 MB available memory, and 1GB of page file size, TMP, or swap space.

For Linux, ensure that the computer has a minimum of 1100 MB disk space, 768 MB available memory, and 1GB of page file size, TMP, or swap space. Ensure that you set the ORACLE_HOME variable.

If you previously deinstalled Warehouse Builder and the path OWB_ORACLE_HOME\owb\j2ee\owbb remains, then delete the owbb directory before installing Warehouse Builder again.

Setting Environmental Variables on the Linux Client

When installing client components on Linux, you must specify the environmental variable for the Oracle home.

Use the UNIX commands listed in Table 1-8 where full_path is the path into which you install Warehouse Builder.

Table 1-8 Setting Oracle home path on the Linux client

Environmental Variable C Shell Command Korn Shell Command Bourne Shell Command

ORACLE_HOME

setenv ORACLE_HOME full_path

export ORACLE_HOME=full_path

ORACLE_HOME=full_path; export ORACLE_HOME


Downloading and Installing the Standalone Warehouse Builder Software

Download the Warehouse Builder standalone software to complete any of the following tasks:

To download the standalone software, locate the software from the following link:

http://www.oracle.com/products/index.html

About the Oracle Universal Installer

When installing the standalone software, Oracle Warehouse Builder utilizes the Oracle Universal Installer to install components and to configure environment variables. The installer guides you through each step of the installation process.

About Oracle Home and Warehouse Builder

Oracle home is the top-level directory into which you install Oracle software. Some Oracle products enable you to share the same Oracle home. Or you can create separate homes and assign names to each home as you install each product.

Warehouse Builder, however, cannot share its home directory with any other Oracle product. When the Oracle Universal Installer prompts you to specify a home directory for Oracle Warehouse Builder, specify a directory different from the Oracle Database or any other Oracle product.

This separate directory is designated by the term OWB_ORACLE_HOME in the Warehouse Builder documentation.

For Linux, in addition to specifying the OWB_ORACLE_HOME, you also must set the ORACLE_HOME variable.

Installing the Warehouse Builder Software

Use the Oracle Universal Installer to install Warehouse Builder components.

To install the software, complete the following:

  1. Review and complete the Checklist: Before You Start the Universal Installer.

  2. Run the installer following either the instructions Running the Oracle Universal Installer for Warehouse Builder on Windows or Running the Oracle Universal Installer for Warehouse Builder on UNIX.

  3. If the Warehouse Builder repository is hosted on an Oracle 10g Database, complete the instructions "Hosting the Repository on Oracle Database 10g Release 2".

Next

When the software installation completes successfully, you can continue with the next step in General Steps for Installing Warehouse Builder.

Checklist: Before You Start the Universal Installer

This section contains additional points to address before launching the Universal Installer:

  • If you have not already done so, review the Oracle Warehouse Builder Release Notes either on the Oracle Warehouse Builder CD-ROM or, for the latest version, go to the Oracle Technology Network at http://www.oracle.com/technetwork/index.html.

  • Close all other open applications.

Running the Oracle Universal Installer for Warehouse Builder on Windows

To run the Oracle Universal Installer on Windows:

  1. Ensure that you are logged on to your system as a member of the Administrators group.

  2. Insert the Oracle Warehouse Builder CD-ROM.

  3. If your computer supports the autorun feature, then the autorun window launches the Oracle Warehouse Builder installation.

    If your computer does not support the autorun feature, then locate the executable setup.exe in the root directory of the CD-ROM or downloaded software. Start the installer by launching the setup.exe program.

  4. When prompted, specify a home directory to be used only for the Warehouse Builder installation.

    For example, you could specify a directory such as C:\oracle\owb11_1.

    For the sake of brevity, the directory you specify in this step is referred to as the OWB_ORACLE_HOME throughout this guide.

  5. Follow the on screen instructions.

When the software installation completes successfully, you can continue with the next step in General Steps for Installing Warehouse Builder.

Running the Oracle Universal Installer for Warehouse Builder on UNIX

To run the Oracle Universal Installer on UNIX:

You can run Oracle Universal Installer from the CD-ROM. Do not run the Installer while the CD-ROM directory is the current directory or you will be unable to unmount the next CD-ROM when prompted to do so.

  1. If you have not already done so, you must set the ORACLE_HOME environmental variable as described in "Setting Environmental Variables on a UNIX Server" and "Setting Environmental Variables on the Linux Client".

  2. Log in as the operating system user of the Oracle Database.

    For example, log in as the oracle user. If you choose to log in as the oracle user, you must configure the user environment by setting the default file mode creation mask (umask) to 022 in the shell startup file.

    Be sure you are not logged in as the root user when you start the Oracle Universal Installer. If you are, then only the root user would have permissions to manage Oracle Warehouse Builder.

  3. Start the installer by entering the following at the prompt:

    cd mount_point

    ./runInstaller

  4. As the installation proceeds, the Oracle Universal Installer prompts you to run several scripts. You must switch users and run the script as root.

Launching Warehouse Builder Components

The Oracle Warehouse Builder CD installs the client and server-side software at the same time. After you complete the installation, you can start the Warehouse Builder components listed in Table 1-8.

The components in Table 1-8 are listed in the order that you are likely to use the components directly after installation:

Table 1-9 Launching Oracle Warehouse Builder Components from Windows or Linux Clients

Warehouse Builder Component Windows:Select Start, Programs, Oracle, Warehouse Builder and then... Linux:Locate OWB_ORACLE_HOME/owb/bin/unix and then...

Repository Assistant

Enables you to manage the repository and its workspaces and workspace users.

Select Administration, and then Repository Assistant.

Run reposinst.sh

Design Center

Is the primary design interface.

Select Design Center.

Run owbclient.sh

Start Control Center Service

This command is only necessary when working in a remote runtime environment.

Select Administration then Start Control Center Service.

Run local_service_login.sh as follows:

local_service_login.sh -startup OWB_ORACLE_HOME

If the service fails to start, you can run OWB_ORACLE_HOME/owb/rtp/sql/service_doctor.sql.

Control Center Manager

Use this command to deploy and run in a remote runtime environment.

Start the Design Center. From the Tools menu, select Control Center Manager.

Run local_service_login.sh as follows:

local_service_login.sh -startup OWB_ORACLE_HOME

Stop Control Center Service

This command is only necessary when working in a remote runtime environment.

Select Administration then Stop Control Center Service.

Run local_service_login.sh as follows:

local_service_login.sh -closedown OWB_ORACLE_HOME

Start OWB Browser Listener

Run startOwbbInst.bat

The first time you invoke this listener, select and re-confirm a password for an oc4jadmin account.

Run startOwbbInst.sh

The first time you invoke this listener, select and re-confirm a password for an oc4jadmin account.

Repository Browser

Select Repository Browser.

Start the OWB Browser Listener and then run openRAB.sh.

Stop OWB Browser Listener

Type the command:

stopOwbbInst.bat oc4jadmin pwd

Type the command:

stopOwbbInst.bat oc4jadmin pwd

OMB Plus

Is the scripting utility that enables to perform all operations available in the graphical user interfaces.

Select OMB Plus.

Run OMBPlus.sh.


Hosting the Repository on Oracle Database 10g Release 2

To host a Warehouse Builder 11g repository on Oracle Database 10g Release 2, complete the following steps:

  1. Install Warehouse Builder 11g on to the computer hosting Oracle Database 10g Release 2 as described in "Installing the Warehouse Builder Software".

    You can locate the software from the following link:

    http://www.oracle.com/products/index.html

  2. For additional considerations for hosting the repository on Oracle 10g Database, refer to "Preparing the Oracle Database".

  3. Complete the steps in "Running Scripts to Create a Warehouse Builder Repository Schema".

  4. Start and complete the Repository Assistant.

    Use the Repository Assistant to create a Warehouse Builder 11g repository and workspace in the Oracle Database 10g.

  5. Complete the steps in "Enabling Access to Workspaces Hosted on Oracle 10g Databases".

Running Scripts to Create a Warehouse Builder Repository Schema

To create a Warehouse Builder repository on an Oracle 10g Release 2 Database, complete the following steps:

  1. Change the current directory to the OWB_ORACLE_HOME\owb\UnifiedRepos directory. For example:

    C:\> cd OWB_ORACLE_HOME\owb\UnifiedRepos\

  2. Run the version of SQL*Plus provided with Warehouse Builder, with SYSDBA privilege. This executable is located in the OWB_ORACLE_HOME/bin directory. For example, type the following:

    C:\OWB_ORACLE_HOME\owb\UnifiedRepos> OWB_ORACLE_HOME\bin\sqlplus sys/sys_password as sysdba;

  3. Run the cat_owb.sql script stored in the OWB_ORACLE_HOME/owb/UnifiedRepos directory.

    The script creates the repository and sets up the required roles and privileges on the 10g Release 2 Database.

    The script prompts you for the name of the default tablespace in which to create OWBSYS schema. For example, to install the OWBSYS schema into the USERS tablespace in a 10g Release 2 Database hosted on Windows, type the following:

    SQL> @cat_owb.sql

    Enter Tablespace Name for OWBSYS user:

    USERS

  4. Unlock the OWBSYS user and assign it a password. For example:

    SQL> alter user OWBSYS account unlock identified by owbsys_password;

  5. Run the script OWB_ORACLE_HOME/owb/UnifiedRepos/reset_owbcc_home.sql

    Use this script to ensure that the Control Center runs correctly from the Warehouse Builder 11.1 home. When prompted for the OWB_ORACLE_HOME, type the directory carefully. The entry is case-sensitive, does not accept a trailing slash, and requires forwards slashes only, regardless of the operating system. For example, for Windows, if the OWB_ORACLE_HOME is

    C:\Oracle\My_OWB_Home\>

    then type the following:

    C:/Oracle/My_OWB_Home

Enabling Access to Workspaces Hosted on Oracle 10g Databases

Warehouse Builder 11g clients connect to workspaces on an Oracle 11g Database by default. To access workspaces on an Oracle 10gRelease 2 Database, you must take additional steps.

To enable access to workspaces on a 10g Release 2 Database:

  1. On each client computer, locate the file OWB_ORACLE_HOME/owb/bin/admin/Preference.properties.

    If the file does not exist, you can create it based on the example file Preference.properties.tmp in the same directory.

  2. Edit Preference.properties, add a property REPOS_DB_VERSION_ALLOWED and set its value to one of the following:

    • Oracle 10g

    • Oracle 10g, Oracle 11g

    For example:

    REPOS_DB_VERSION_ALLOWED=Oracle 10g,Oracle 11g

    After you save the file, the client can access repositories stored in Oracle Database 10gRelease 2.

Steps for Installing Warehouse Builder in Oracle RAC Environments

The overall process for installing on an Oracle RAC environment is similar to the "General Steps for Installing Warehouse Builder". However, there are a few specific details to observe as noted in the following instructions:

To install in an Oracle RAC environment:

  1. Create the Oracle RAC environment as described in the Oracle Clusterware and Oracle Real Application Clusters Installation Guide specific to your platform.

  2. If you have not already done so, review the most recent Oracle Warehouse Builder Release Notes available at http://www.oracle.com/technetwork/index.html.

  3. Preparing host computers

    For each computer to host Warehouse Builder components, configure the tnsnames.ora file located in the OWB_ORACLE_HOME\owb\network\admin directory.

  4. Preparing the Oracle Database

    For Oracle RAC 10g environments, be sure to set the initialization parameter MAX_COMMIT_PROPAGATION_DELAY to a value of zero.

    Also configure tnsnames.ora for each Oracle Database server that will be a Warehouse Builder data source or target. If you fail to configure tnsnames.ora for any host or database server, you may encounter a repository connection error.

  5. If necessary, install the Warehouse Builder standalone software.

    If the Warehouse Builder repository is hosted on Oracle Database 11g and you do not intend to integrate with Oracle Discoverer, skip to the next step.

    Otherwise, complete the steps in "Downloading and Installing the Standalone Warehouse Builder Software".

  6. Chapter 2, "Managing Workspaces and Workspace Users"

    The Repository Assistant prompts you to define users and an owner for the repository.

    To start the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant.

    To start the Repository Assistant on UNIX, locate OWB_ORACLE_HOME/owb/bin/unix and run reposinst.sh.

  7. Register each Oracle RAC node.

    For each node, start the Repository Assistant and select the Advanced Set up option. Connect to the node using Host:Port:Sid, where Host is the physical node name. Select the option for registering the Oracle RAC instance.

  8. If the software is installed on separate disks, copy rtrepos.properties to each node in the cluster.

    If you did not install to a shared disk, then you must manually copy the file OWB_ORACLE_HOME/owb/bin/admin/rtrepos.properties from the primary node to each node in the cluster.

  9. Setting the Security Policy for the Repository.

  10. Chapter 5, "Installing and Enabling Optional Components" (Optional).

    Consider performing the optional step "Configuring Repository Browser Environments". The Repository Browser lets you nominate a node and register other nodes.

  11. Install the Warehouse Builder software on the client computers.

    Repeat "Installing the Warehouse Builder Software" for each computer to be used as a client.

  12. When complete the installation process, you can start all the Warehouse Builder components.

    When "Launching Warehouse Builder Components" such as the Design Center, Control Center Manager, and Repository Assistant, select the log on option Oracle Net Services connection and specify the net service name you assigned in the tnsnames.ora file.

    Because you can connect to Warehouse Builder repositories using a net service name, you can embed Oracle RAC properties into the connect string to utilize Oracle RAC capabilities such as connect time failover, load balancing on server and load balancing of connections.

  13. "Ensuring the Availability of Service Names for Oracle RAC Nodes"

    The Control Center Service requires that service names for the individual nodes in the cluster be available. If these are not present after the Oracle RAC installation, you must manually ensure the availability.

Installing Warehouse Builder on Each Node of a Cluster

Whether you are installing Warehouse Builder components onto a server or a client computer, you use the Oracle Universal Installer to install Warehouse Builder components.

For Oracle RAC, it is recommended that you install the Warehouse Builder components on each node of the cluster. The Control Center Service is required on to each node of the Oracle RAC cluster.You can achieve this in a single installation of the Warehouse Builder software if you install on a shared disk such as an OCFS or NTS shared disk.

Before launching the Universal Installer, review and complete the Checklist: Before You Start the Universal Installer.

Checklist: Before Using the Universal Installer in an Oracle RAC Environment

This section contains additional points to address before launching the Universal Installer:

  • If you have not already done so, review the latest version Oracle Warehouse Builder Release Notes at http://www.oracle.com/technetwork/index.html.

  • The installed location must be the same directory path if using separate OWB_ORACLE_HOME installed disks, that is, local physical disks on each server.

  • Close all other open applications.

For Windows Users Installing in an Oracle RAC Environment

To run the Oracle Universal Installer in an Oracle RAC environment:

  1. Ensure that you are logged on to your system as a member of the Administrators group.

  2. Insert the Oracle Warehouse Builder CD-ROM.

  3. If your computer supports the autorun feature, the autorun window will automatically start the Oracle Warehouse Builder installation.

    If your computer does not support the autorun feature, locate the executable setup.exe in the root directory of the CD-ROM or downloaded software. Start the installer by launching the setup.exe program.

  4. When prompted to specify the cluster node, you can select all hosts or the local node.

    If you select local node, then you must install Warehouse Builder separately for each system unless installing to a shared disk.

  5. When prompted, specify a home directory to be used only for the Warehouse Builder installation.

    For example, specify a path such as C:\oracle\owb11_1.

    For the sake of brevity, the path you specify in this step is referred to as OWB_ORACLE_HOME throughout this guide.

  6. Follow the on screen instructions.

When the software installation completes successfully, you can continue with the next step in "Steps for Installing Warehouse Builder in Oracle RAC Environments".

For UNIX Users Installing in an Oracle RAC Environment

To run the Oracle Universal Installer in an Oracle RAC environment:

You can run Oracle Universal Installer from the CD-ROM. Do not run the Installer while the CD-ROM directory is the current directory or you will be unable to unmount the next CD-ROM when prompted to do so.

  1. If you have not already done so, you must set the ORACLE_HOME environmental variable as described in "Setting Environmental Variables on a UNIX Server" and "Setting Environmental Variables on the Linux Client".

  2. To enable clusterware installation, ensure that you run an interactive secure shell such as /bin/ssh and have host user equivalency to all nodes.

  3. Log in as the operating system user of the Oracle Database.

    For example, log in as the oracle user.

    Be sure you are not logged in as the root user when you start the Oracle Universal Installer. If you are, then only the root user will have permissions to manage Oracle Warehouse Builder.

  4. Start the installer by entering the following at the prompt:

    cd mount_point

    ./runInstaller

  5. When prompted to specify the cluster node, you can select all hosts or the local node.

    If you select local node, then you must install Warehouse Builder separately for each system unless installing to a shared disk.

  6. As the installation proceeds, the Oracle Universal Installer prompts you to run several scripts. You must switch users and run the script as root.

When the software installation completes successfully, you can continue with the next step in General Steps for Installing Warehouse Builder.

Ensuring the Availability of Service Names for Oracle RAC Nodes

  1. List all of the instance or node names in the cluster. Issue the following command:

    srvctl config database -d dbname

    where dbname is the unique database name as specified by the init parameter db_name.

  2. For a given instance, instn, add a service with the following command:

    srvctl add service -d dbname -s instn -r instn

    The resulting service name is instn.clusterdomainname. For example, if the instance name is owbrac1,then the service name could be owbrac1.us.oracle.com.

  3. For a given instance, instn,start the service with the following command:

    srvctl start service -d dbname -s instn

  4. For a given instance, instn, verify the service is running with the following command:

    srvctl status service -d dbname -s instn

  5. Complete steps 2 through 4 for each node.