A Preparing an Oracle Database

Before installing Warehouse Builder, you need to prepare your Oracle Database by setting the following configuration parameters:

• Parameters for the Design Repository Database Instance

• Parameters for the Runtime Repository Database Instance

• Parameters for the Oracle Application Server Database (Optional)

Parameters for the Design Repository Database Instance

The Warehouse Builder Repository database runs on standard Oracle Database 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
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 in the next row.
O7_DICTIONARY_ACCESSIBILITY	TRUE	This setting is an alternative to use if you cannot set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE, as recommended in the previous row. If the REMOTE_LOGIN_PASSWORDFILE parameter is set to NONE, then set the O7_DICTIONARY_ACCESSIBILITY parameter as an alternative. It enables 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	10.0	If this parameter is not in the initialization file, add it to the end of the file.
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	This setting is required to ensure that the SYSDBA privilege is granted to the SYS user.
LOCK_SGA	TRUE	Oracle recommends locking the design SGA in physical memory.

Parameters for the Runtime Repository Database Instance

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

Note:

If you install Warehouse Builder on Oracle8i, you must configure the Warehouse Builder Runtime instance to have at least one non-system rollback segment for the seeded runtime packages to compile. Warehouse Builder suggests the use of server-managed rollback segments.

Table A-2 lists the database configuration parameters.

Table A-2 Initialization Parameters for the Runtime Instance

Initialization Parameter	Set to Value	Comments
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.
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.
LOG_BUFFER	See comments	Set the value to larger than 512K and must be 128K times the number of CPUs.
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, because they will be automatically adjusted by the server: 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.
DB_CACHE_SIZE	314632800	This setting amounts to 300 MB. Set this value as high as the system permits. Do not set any value for the DB_CACHE_SIZE parameter if you set a value for the SGA_TARGET parameter.
SHARED_POOL_SIZE	52428800	The recommended minimum value is 50 MB. Oracle recommends raising this parameter to 100MB if memory is available. In the unlikely case that you require more than 100MB, ensure that this parameter is tuned by a knowledgeable DBA. Do not set any value for the SHARED_POOL_SIZE parameter if you set a value for the SGA_TARGET parameter.
LARGE_POOL_SIZE	0	This setting enables the server to set the LARGE_POOL_SIZE automatically. Prerequisite: PARALLEL_AUTOMATIC_TUNING must be set to TRUE. Do not set any value for the LARGE_POOL_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.
DISK_ASYNCH_IO	TRUE	If the platform does not support asynchronous I/O, set DBWR_IO_SLAVES to a positive number, such as 4, to simulate asynchronous I/O.
DB_WRITER_PROCESSES	n	n is the number of CPUs. Increase this parameter value by 1 for every 8 CPUs. If you have fewer than 8 CPUs, set DB_WRITER_PROCESSES to 1.
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.
PGA_AGGREGATE_TARGET	314572800	This is 300 MB. If you perform frequent sorting and aggregation, you can increase this value. However, PGA_AGGREGATE_TARGET must be smaller than the available physical memory size.
PARALLEL_ADAPTIVE_MULTI_USER	TRUE	Set PARALLEL_AUTOMATIC_TUNING to TRUE as a prerequisite for this parameter.
PLSQL_OPTIMIZE_LEVEL	2	The PL/SQL compiler in Oracle Database 10g Release 1 (10.1) can perform more elaborate optimization on PL/SQL code.
LOG_CHECKPOINT_TIMEOUT	3000	This setting increases the timeout for performing checkpoints from the default 3 minutes to 5 minutes.
STATISTICS_LEVEL	TYPICAL
UNDO_MANAGEMENT	AUTO	With this setting, you do not have to create rollback segments.
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.
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.
QUERY_REWRITE_ENABLED	TRUE	Set this parameter to TRUE if you plan to generate materialized views with the QUERY REWRITE option.
USE_INDIRECT_DATA_BUFFERS	See note.	Do not set this parameter to TRUE as that would conflict with other recommendations.
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.
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. These grants are made by the SYSDBA account when you create the repository. This setting ensures that the SYSDBA privilege is granted to SYS.
COMPATIBLE	10.0	If this parameter is not in the initialization file, add it to the end of the file.
AQ_TM_PROCESSES	1	This parameter is required for the Warehouse Builder and Oracle Workflow Advanced Queueing System.
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, repeat this parameter on contiguous lines for each directory. If you use flat file targets in Warehouse Builder, 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 AppendixA, "Configuring the Target Data File Path for Flat File Targets" for details).
WORKAREA_SIZE_POLICY	AUTO
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.
JOB_QUEUE_PROCESSES	>10	Optimal setting is 10. If JOB_QUEUE_PROCESSES is set to 0, the Runtime Platform Service does not run, and the Runtime Assistant produces error messages.

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 = * 

Parameters for the Oracle Application Server Database (Optional)

If you plan to integrate your Warehouse Builder Design Browser and Runtime Audit Browser with Oracle Application Server, set the parameters listed in Table A-3.

Table A-3 Initialization Parameters for Oracle Application Server

Initialization Parameter	Set to Value
GLOBAL_NAMES	FALSE
OPEN_CURSORS	300
OPEN_LINK	100