A
Preparing an 8.1.7 or 9i Database

Before installing Warehouse Builder, you need to prepare your Oracle 8.1.7 or 9i database by setting the following configuration parameters.

Parameters for the Repository Database Instance

The Warehouse Builder Repository database runs on standard Oracle8i/9i OLTP configuration parameters. To specify these parameters, modify the init.ora file in the directory [Drive:]\Oracle\admin\owbrepos\pfile\.

Table A-1 lists the configuration parameters to use as an initial guide for improving performance.

Table A-1 Initialization Parameters for Warehouse Builder Repository Instance  

Initialization Parameter	Set to Value	Comments
GLOBAL_NAMES	FALSE	Allows you to create database link names without the restrictions that Oracle Names imposes. If you set this parameter to FALSE on the Oracle Warehouse Builder databases, you may not be able to access other databases that have Oracle Names enabled.
OPEN_CURSORS	200	You may specify a higher value.
DB_BLOCK_SIZE	8192	This parameter is set when the database is created and cannot be changed. The recommended value is 8192 or greater depending on your business requirements.
DB_BLOCK_BUFFERS	4098	Consult your DBA for the correct settings for your installation.
REMOTE_LOGIN_PARAMETERFILE	EXCLUSIVE	Required to ensure that the SYSDBA privilege is granted to the SYS user.

Parameters for the Runtime Database Instance

To support the Warehouse Builder runtime component, you need to modify the Oracle8i/9i Enterprise Edition instance on the system where your data warehouse will reside. The Warehouse Builder Runtime Assistant creates a runtime schema with sufficient privileges to create materialized views and perform query rewrite.

In order for the materialized views to perform properly in the runtime, you must set the following initialization parameters for the Runtime Repository instance in its init.ora file, which is found in the directory [Drive:]\Oracle\admin\owbdw\pfile\.

Note: Configure the Warehouse Builder Runtime instance to have at least one non-system rollback segment. Otherwise the seeded runtime packages will not compile.

Table A-2 lists the initialization parameters.

Table A-2 Initialization Parameters for Warehouse Builder Runtime Instance

Initialization Parameter	Set to Value	Comments
GLOBAL_NAMES	FALSE	Allows you to create database link names without the restrictions that Oracle Names imposes. Note that if you set this parameter to FALSE on the Oracle Warehouse Builder databases, you may not be able to access other databases that have Oracle Names enabled.
OPEN_CURSORS	200	You may specify a higher value.
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, use the largest size available. If your computer has less than 512 MB of RAM, a value of 9600 is recommended.
DB_BLOCK_BUFFERS	20000	This change is optional. Depending upon your requirements, set the buffer size to 80000 or even 200000. Use a large enough number to hold most of the largest dimension table, but not so large that it causes paging and swapping. If your computer has less than 512 MB of RAM, use the default value for this parameter.
COMPATIBLE	8.1.0	If this parameter is not in the initialization file, add it to the end of the file.
OPTIMIZER_MODE	CHOOSE	For other possible optimizer modes, see Oracle8i Designing and Tuning for Performance, Oracle9i Database Performance Tuning Guide and Reference, and Oracle8i/9i Data Warehousing Guide.
QUERY_REWRITE_ENABLED	TRUE	If you plan to generate materialized views with the QUERY REWRITE option.
DB_FILE_MULTIPLE_BLOCK_READ_COUNT	16	A value of 16 is recommended, 32 is preferred. If you use disk striping or RAID5, set this parameter according to the stripe size: stripe_size = 2 * DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT.
DBWR_IO_SLAVES	n	n= number of CPUs (minimum) or n = 2* the number of CPUs (recommended)
LOG_BUFFER	n	n = 20 * DB_BLOCK_SIZE (minimum) or 40 * DB_BLOCK_SIZE (recommended)
HASH_AREA_SIZE	1 MB (1000000)
SORT_AREA_SIZE	1 MB (1000000)
UTL_FILE_DIR	*	Specifies the directories that PL/SQL can use for file I/O. UTL_FILE_DIR = * specifies that all directories can be used for file I/O. If you want to specify individual directories, repeat this parameter on contiguous lines for each directory. If you are creating flat file targets in Warehouse Builder, this parameter needs to be set to the directory where you want to create the flat file target so that your database engine has access to it. (Refer to note below for details).
AQ_TM_PROCESSES	1	OWB/OWF Advanced Queueing System: Workflow Engine only.
ENQUEUE_RESOURCES	3000
Parameters for Parallel Query: Depending on how familiar you are with administering parallel processing in Oracle8i or 9i, we suggest the following: For Novice Users:
PARALLEL_AUTOMATIC_TUNING	TRUE
For Experienced DBAs:
PARALLEL_MIN_PERCENT	50
PARALLEL_MIN_SERVERS	2
PARALLEL_MAX_SERVERS	n	Where n > 4 * the number of CPUs on the servers. Increase PARALLEL_MAX_SERVERS if you run several mappings at the same time.

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 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:

UTL_FILE_DIR = *

Warehouse Builder Validation returns a warning that this parameter must be set in the init.ora file.