An example of 1TB OLTP Unix database

An example of 1TB OLTP Unix database

To understand better and have a solid example of a system, imagine this middle range server.

16 Cores PARISC or Itanium 2 (8 CPUs dual core)
Operating System : HP-UX 11i v2
48 GB of RAM
EMC SAN storage attached
The brand name (HP) is not import, can be something equivalent from Sun or IBM.

IF this machine is going to host a 1TByte OLTP database with about 2500 users (dedicated connections) then you can setup for example like this:

Memmory parameters
db_block_size=8192
db_cache_size=4GB
shared_pool_size=4GB
large_pool_size=128MB
shared_pool_reserved_size=80MB
java_pool_size=128MB
log_buffer=10MB
sga_max_size=16GB
pga_aggregate_target=4GB

Tip:From Oracle9i and afterwards you will need 20GB for the oracle processes (Every process takes at an average 8MB at unix).

At Oracle8i you will need 5GB for the oracle processes (Every process takes at an average 2MB at unix).

Parallel processing

You must take advantage of the 16Cpus, so parallel processing must be setup. A good setup is for minimum 16/4=4 and for

maximum 16*4=64.
parallel_min_servers=4
parallel_max_servers=64
parallel_min_percent=0
recovery_parallelism=0
fast_start_parallel_rollback=LOW
parallel_automatic_tuning=FALSE
parallel_execution_message_size=16384

Other init parameters

db_writer_processes=4 (A good setup is 16/4=4)
db_file_multiblock_read_count=8
timed_statistics=TRUE
open_cursors=10000
log_checkpoint_interval=0
log_checkpoint_timeout=1800
log_archive_max_processes=3

Filesystems

Oracle software: 1 filesystem(30GB is enough)
For the datafiles: 10 filesystems (100GB each) is good enough. Don't use for instance less than 5, it won't be good for Disk I/O performance
For redologs: 5 filesystems, very small (10GB each) is good enough
For controlfiles: 3 filesystems, very small (use the same for redologs)
For archives: 1 filesystem (100GB) setting Rman backup every 6 hours with delete input.
For trace files, exports, scripts, etc: 1 filesystem (200GB)

Tip: Create TEMP tablepace and UNDO tablespace with 10 datafiles each, one in every filesystem.