Oracle Database Parameters
db_name = "<SID>"
instance_name = <SID>
service_names = <SID>
#
#Place mutiple control files onto different mount points
#
control_files =
(
"/<mount-point-a>/oracle/oradata/<SID>/control01a.ctl"
,"/<mount-point-a>/oracle/oradata/<SID>/control02a.ctl"
,"/<mount-point-b>/oracle/oradata/<SID>/control01b.ctl"
,"/<mount-point-b>/oracle/oradata/<SID>/control02b.ctl"
)
db_block_size = 8192 #OLTP databases in Oracle8i databases are typicaly 8K block
#size. Increase the block size to 16 or even 32K block size
#for data warehouse/read-only type databases.
shared_pool_size = 1610612736 #This setting depends on the amount of RAM and swap space
#or virtual memory available. This value is set to 1.5G -
#change to 1G if using MTS and increase the large pool to
#atleast 500M. This will force user connections into the
#large pool.
shared_pool_reserved_size = 161061274 #Defaulted at 5% but recommended at 10%. Prevents memory
#fragmentation during large operations. Forces retention
#of large allocations in the shared pool such that they
#are not aged out such as large pinned packages and stored
#procedures.
large_pool_size = 209715200 #This is set to 200M for backups and should be increased
#for MTS.
java_pool_size = 0 #This parameter is only applicable to JServer. Set it to
#zero. Pre-8.1.6 Standard Edition on NT and Unix will not
#start the Oracle instance with this parameter set to zero;
#set it to a minimum value such as 10M (10485760).
db_block_buffers = 64000 #500M = 64000 blocks, 200M = 25600 blocks, 64M = 8192 blocks
#- determines how much of the data in the database is retained
#in RAM and Swap space or virtual memory.
db_file_multiblock_read_count = 8 #This must remain small for OLTP databases since it can cause
#full table scans. Higher values for this parameter are more
#applicable to read-only and data warehouse type databases.
#Multiple physically sequential blocks are read at once.
log_buffer = 131072 #131072 (128K-16 O/S blocks), 65536 (64K-8 O/S blocks). A
#small increase in log buffer size can increase performance
#drastically.
db_block_lru_latches = 3 #cpu_count*2*3 (can be 6 but 3 since there are 3 buffer and
#only 1 DBWn).
buffer_pool_keep = (buffers:6400,lru_latches:1) #The keep buffer is recommended at 5% of DB Cache. The keep
#buffer should contain static data only.
buffer_pool_recycle = (buffers:16000,lru_latches:1) #The recycle buffer is recommended at 25% of DB Cache. This
#depends on database activity. Dependant on transaction size
#and database type, ie. OLTP, DSS, Batch or Data Warehouse.
sort_area_size = 10240 #100 connections would claim 10G of memory. These buffers can
#be forced into the large pool when using MTS. Each user has
#a sort_area_size buffer allocated for the duration of the
#connection when not using MTS. DO NOT make this value too
#because it will use a lot of memory if there are many
#connections.
sort_area_retained_size = 1024 #Recommended at 10% to avoid large transactions pushing
#small transactions out of cache. 10% of sort area size takes
#1G RAM for 100 connections and is optionally placed into the
#large pool in MTS.
sort_multiblock_read_count = 4 #This parameter has a similar function to that of the
#db_file_multiblock_read_count parameter. This must remain
#small for OLTP databases since it can cause full table scans.
#Higher values for this parameter are more applicable to
#read-only and data warehouse type databases. Multiple
#physically sequential blocks are read at once.
open_cursors = 100 #The maximum open cursors allowed per session. This is a
#limitiation and is not reserved for each session.
processes = 500 #At a minimum Oracle requires 55 processes. 200 processes
#minimum are required for an Oracle database running anything
# to do with OEM (Oracle Enterprise Manager). The maximum
processes for a non-MTS setup is 500-650, Oracle recommends
#150 but MTS is awkward to set up and 150 is probably a little
#low.
#There is 1 process per connection. For 100 concurrent
#connections @ 300K/connection 50M of shared pool size would
#be required. For 500 concurrent connections @ 300K/connection
#300M of shared pool space would be required.
max_enabled_roles = 30
cursor_sharing = force #This is a temporary solution for use of literals and not of
#bind variables in SQL filtering. Basically allows for non-bind
#variable statements to NOT have to be reparsed. Oracle9i has
#this type of functionality automatically included.
log_checkpoint_interval = 0 #Setting to 0 means checkpointing is ignored. When checkpointing
#is ignored checkpointing is controled by the size of the redo
#log buffer, ie. a checkpoint is automatically executed when the
#redo log buffer is 2/3 full.
log_checkpoint_timeout = 300 #Checkpointing is executed every 5 minutes.
#fast_start_io_target = 25600 #A limit on the number of dirty buffers allowed and defaulted
#to db_block_size. When dirty buffers reach the limit of
#fast_start_io_target a checkpoint is executed.
max_dump_file_size = 10240 #The maximum trace file size excluding the alert.log. a new trace
#file is created when this limit is reached.
#timed_statistics = true #Resource statistics gathering.
#timed_os_statistics = 0 #O/S level resource statistics gathering.
#sql_trace = true #SYSTEM-wide SQL tracing.
#audit_trail = DB #Will not do DML statement tracing - goes into SYS.AUD$ table.
#
#General database archiving parameters. Required for recoverability.
#
log_archive_start = true
log_archive_dest_1 = "location=/<mount-point-a>/oracle/oradata/<SID&;gt;/archive"
log_archive_dest_state_1 = ENABLE
log_archive_format = arch_%S.arc
#
#Primary database with standby database connection - standby databases automated through TNS with Enterprise Edition.
#The setting of log_archive_dest_2 = "service=STBY MANDATORY REOPEN=30" requires that the archive log transferred to
#the standby database must be transferred. If not the primary database will halt operations when all log files are
#not archived to the standby database.
#
#log_archive_start = true
#log_archive_dest_1 = "location=/<mount-point-a>/oracle/oradata/PROD/archive"
#log_archive_dest_state_1 = ENABLE
#log_archive_dest_2 = "service=STBY MANDATORY REOPEN=30"
#log_archive_dest_state_2 = ENABLE
#log_archive_format = arch_%S.arc
#
#Standby database archive parameters
#
#log_archive_start = true
#log_archive_dest_1 = "location=/<mount-point-a>/oracle/oradata/STBY/archive"
#log_archive_dest_state_1 = ENABLE
#log_archive_format = arch_%S.arc
#standby_archive_dest = "/<mount-point-a>/oracle/oradata/STBY/archive"
#db_file_name_convert = ('/<mount-point-a>/oracle/oradata/PROD','/<mount-point-a>/oracle/oradata/STBY')
#log_file_name_convert = ('/<mount-point-a>/oracle/oradata/PROD','/<mount-point-a>/oracle/oradata/STBY')
#lock_name_space = STBY
#
#The background_dump_dest parameter determines the location of the alert log file.
#
background_dump_dest = /<mount-point-b>/oracle/admin/<SID>/bdump
core_dump_dest = /<mount-point-b>/oracle/admin/<SID>/cdump
user_dump_dest = /<mount-point-b>/oracle/admin/<SID>/udump
#
#OLTP databases require many small rollback segments. Separated large rollback segments, usually placed offline,
#can be placed online and utilised for large transactions. Many small rollback transactions for an OLTP database
#will increase database performance drastically for an OLTP database because ALL of those small rollback segments
#will be permanently placed in cache. It may even be intelligent to make many rollback segments as small as 16K
#initial and next plus and optimal of 32K.
#
max_rollback_segments 50
rollback_segments =
(
RB00,RB01,RB02,RB03,RB04,RB05,RB06,RB07
,RB08,RB09,RB10,RB11,RB12,RB13,RB14,RB15
,RB16,RB17,RB18,RB19,RB20,RB21,RB22,RB23
,RB24,RB25,RB26,RB27,RB28,RB29,RB30,RB31
)
compatible = "8.1.7.0.0"
remote_login_passwordfile = exclusive
os_authent_prefix = ""
distributed_transactions = 10 #The RECO process will not be started at instance startup
#if this parameter is set to 0. The RECO process is
#essential when recovering from redo logs and/or archives.
#
query_rewrite_integrity = trusted #Enables function-based indexes. Not available in Standard Edition.
query_rewrite_enabled = true #Required for function-based indexes and materialised views.
resource_manager_plan='OLTP' #See Tuning Resources