A
Preparing an Oracle 8.1.7 or 9i Database

Before installing Warehouse Builder, you need to prepare your Oracle9i database by setting the following configuration parameters.

This appendix includes the following topics:

A.1 Parameters for the Design Repository Database Instance

The Warehouse Builder Repository database runs on standard Oracle9i configuration parameters.

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

Table A-1 Initialization Parameters for the Design Repository Instance

Initialization Parameter	Set to Value	Comments
GLOBAL_NAMES	FALSE	To use the Warehouse Builder Runtime Audit Browser client, set this parameter on your Warehouse Builder Runtime Repository instance.
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	This setting is required for the following connect statement to work: `connect sys/<<sys_password>>@``TNS_NAME_OF_DB` `as sysdba;` If, however, this parameter is set to `NONE` instead of `EXCLUSIVE`, set the `07_DICTIONARY_ACCESSIBILITY` parameter as described below.
O7_DICTIONARY_ACCESSIBILITY	TRUE	If the `REMOTE_LOGIN_PASSWORDFILE` is set to `NONE` instead of `EXCLUSIVE`, as recommended above, this parameter setting is an alternative that allows the following statement to work when connecting to a `SYS` user: `connect sys/<<sys_password>>@``TNS_NAME_OF_DB`
OPEN_CURSORS	300	You may specify a higher value.
DB_BLOCK_SIZE	8192	This parameter is set when the database is created and cannot be changed. Warehouse Builder does not recommend a value higher than 8192 for the Design Repository.
DB_CACHE_SIZE	104877600	This is 100 MB.
COMPATIBLE	9.0	If this parameter is not in the initialization file, add it to the end of the file.
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	Required to ensure that the SYSDBA privilege is granted to the SYS user.

A.2 Parameters for the Runtime Repository Database Instance

To support the Warehouse Builder runtime component, you need to modify the Oracle9i Enterprise Edition instance on the system where your data warehouse will reside.

Table A-2 lists the initialization parameters.

Table A-2 Initialization Parameters for the Runtime Instance

Initialization Parameter	Set to Value	Comments
GLOBAL_NAMES	FALSE	To use the Warehouse Builder Runtime Audit Browser client, set this parameter on your Warehouse Builder Runtime Repository instance.
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	You must use the sys account with SYSDBA privileges in order to access or create the Warehouse Builder Runtime Repository schema. The Warehouse Builder Repository user requires access to certain `v_$` tables and these grants are made by the SYSDBA account when you create the repository. This setting ensures that the SYSDBA privilege is granted to SYS.
JAVA_POOL_SIZE	20 MB	Minimum recommendation.
OPEN_CURSORS	500	You may specify a higher value if you launch multiple sessions or if you run multiple or complicated mappings in one session.
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.
COMPATIBLE	9.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_CACHE_SIZE	314632800	This is 300 MB. Set this value as high as the system permits.
DB_FILE_MULTIPLE_BLOCK_READ_COUNT	16	A value of 16 is recommended, 32 is preferred.
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)
PGA_AGGREGATE_TARGET	314572800	This is 300 MB. If you perform frequent sorting and aggregation, you can increase this value. However, DB_CACHE_SIZE and PGA_AGGREGATE_TARGET must be smaller than the available physical memory size.
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 or higher if you are importing large MDL files.	A minimum setting of '1' is required for the install to complete without error.
PARALLEL_AUTOMATIC_TUNING	TRUE	This is a parameter for parallel query that depends on how familiar you are with administering parallel processing in Oracle8i or 9i. If you are a novice user, Oracle suggests this setting. This setting delegates the task of tuning parallel processing to the server.
LARGE_POOL_SIZE	0	This is a parameter for parallel query that depends on how familiar you are with administering parallel processing in Oracle8i or 9i. If you are an experienced DBA, Oracle suggests this setting. This setting allows the server to set the LARGE_POOL_SIZE automatically.

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.

A.3 Parameters for the Oracle9iAS Database (Optional)

If you are integrating your Oracle9i Warehouse Builder Design Browser and Runtime Audit Browser with Oracle9iAS, set the parameters listed in Table A-3.

Table A-3 Initialization Parameters for Oracle9iDS

Initialization Parameter	Set to Value
GLOBAL_NAMES	FALSE
OPEN_CURSORS	300
OPEN_LINK	100

A Preparing an Oracle 8.1.7 or 9i Database