Oracle buenas practicas

Purpose: The purpose of this document is to provide guidelines and recommendations for setting up and managing a RedPrairie Oracle database.

Hardware: Note that storage hardware and configuration is generally going to be different for each system and many systems can be configured with a more complicated storage solution.

- Configure disks in a RAID 1+0 configuration. This willprovide generally good performance for every file type with fault tolerance.
- Configure a high number of smaller disks rather than a few number of large disks. This will ensure optimal performance for the RAID array.

File Layout: Note that with some storage solutions, these guidelines can become irrelevant.

- Create two redo log groups with 6 members to each groups (12 logstotal)
- Separate each redo log group onto it’s own filesystem
- Keep UNDO datafiles on separate filesystems than redo logs
- Keep index datafiles on separate filesystems than data datafiles

Datafile/Tablespace Setup:

- Use auto-extensible datafiles with a maximum size specified, such as 4000M
- Use an auto-extensible size so that it will not extend constantly,such as 100M
- Use locally managed extents
- Use automatic segment space management
- Use uniform extent sizes
- Configure redo log files so that they switch no more than two times per hour

Instance Setup:

- Use 3 copies of the database control file, multiplexed to eliminate single point of failure
- Configure a 2M log_buffer size
- Specifyprocesses parameter to be 400 at a minimum
- Configure job_queue_processes to be ½ # CPUs
- If uncommented, comment out disk_asynch_io and dbwr_io_slaves
- Configure db_writer_processes to be ½ # CPUs
- Configure db_file_multiblock_read_count to be 16
- Configure automatic memory management (values will be different for different sites)
o Setsga_max_size, i.e. 2000M
o Set sga_target, i.e. 1300M
o The following parameters now become minimum values
▪ Db_cache_size, i.e. 300M
▪ Shared_pool_size, i.e. 300M
- Configure large_pool_size to be 75M
- Configure remote_login_passwordfile to be EXCLUSIVE
- Configure the following optimizer parameters
oOptimizer_mode = ALL_ROWS
o Optimizer_index_caching = 80
o Optimizer_index_cost_adj = 20
- Set cursor_sharing to EXACT
- Set timed_statistics to TRUE
- Configure the instance for ARCHIVELOG mode

User Privileges: All RedPrairie database users should have the following privileges enabled.

- alter session
- create session
- execute anyprocedure
- select_catalog_role
- select any dictionary
- create table
- exp_full_database
- imp_full_database
- select on v_$version
- select on v_$parameter
- select on sys.obj$
- select on v_$lock
- select on v_$session
- select on v_$process
- select on dbms_lock_allocated
- select ondba_free_space_coalesced
- select on dba_data_files
- Unlimited quota on all application tablespaces

Statistics: Note that different sites will generally have a slightly different statistics gathering method. RedPrairie has seen issues with how Oracle is using histograms if the statistics are specified to gather them, so we recommend NOT creating histograms on statistics. This is done bymodifying the METHOD_OPT argument to be ‘FOR ALL COLUMNS SIZE 1’. The below script should be run through a cron script or some other scheduling tool. RedPrairie sometimes schedules this using the dba_job functionality, however this has not been 100% reliable as a scheduler so another facility should be used.

When setting up the RedPrairie recommended statistics gathering approach, make sure to run...