La semana anterior fue actualizada la nota de soporte de My Oracle Support #396009.1 con los valores recomendados para ejecutar Oracle E-Business Suite Release 12 en la base de datos Oracle.
Oracle E-Business Suite 12, requiere como mínimo el nivel de release 11.2.0.3 y la base de datos debe ser la Edición Corporativa ( Enterprise Edition ).
Dimensionamiento de parámetros de la base de datos para distintos ambientes.
Valores para parámetros de la base de datos.
##############################################################################
#
# Oracle E-Business Suite Release 12
# Common Database Initialization Parameters
#
# The following represents the common database initialization
# parameters file for Oracle E-Business Suite Release 12.
# Release-specific parameters are included in the respective release
# section. The release-specific parameters should be appended to the
# common database initialization parameter file.
#
# There are numerous mandatory database initialization parameters.
# Their settings must not be altered. The use of values other than
# those provided in this document will not be supported unless Oracle
# Support has specifically instructed you to alter these parameters
# from their mandatory settings.
#
# Mandatory parameters are denoted with the #MP symbol as a
# comment. This includes parameters such as NLS and optimizer
# related parameters.
#
# The remaining (non-mandatory) parameters relate to either sizing or
# configuration requirements that are specific to customer environments
# or system capacity. A sizing table provides recommendations and
# guidelines based on the number of deployed and active Oracle
# E-Business Suite users. Customers can adjust these parameters as per
# their environment and system resource capacity.
#
##############################################################################
##########
#
# Database identification parameters
#
# The database identification parameters define the name of the
# database and the names of the database control files.
#
# The database name is established when the database is built, and
# for most customers it matches the instance name. It should not
# normally be necessary to change the database name, except for
# the purposes of database cloning.
#
# There should be at least two control files, preferably three,
# located on different volumes in case one of the volumes fails.
# Control files can expand, hence you should allow at least 20M
# per file for growth.
#
#########
db_name = prodr12
control_files = ('/disk1/prodr12_DB/cntrlprodr12_1.dbf',
'/disk2/prodr12_DB/cntrlprodr12_2.dbf',
'/disk3/prodr12_DB/cntrlprodr12_3.dbf')
#########
#
# Database block size parameter
#
# The required block size for Oracle E-Business Suite is 8K. No other value may be used.
#
#########
db_block_size = 8192 #MP
#########
#
# Compatibility parameter
#
# See the appropriate release-specific section of this document for details of setting compatibility.
#
#########
#########
#
# _system_trig_enabled
#
# The _system_trig_enabled parameter must be set to TRUE.
# If _system_trig_enabled parameter is set to FALSE it will
# prevent system triggers from being executed.
#
#########
_system_trig_enabled = TRUE #MP
#########
#
# o7_dictionary_accessibility parameter
#
# This parameter must be set to FALSE for Oracle E-Business Suite Release 12.
#
########
o7_dictionary_accessibility = FALSE #MP
#########
#
# NLS and character set parameters
#
# Some NLS parameter values are marked as being mandatory settings.
# These are the only supported settings for these parameters for
# Oracle E-Business Suite Release 12. They must not be changed to other values.
# Other NLS parameters have been given default values, which can
# be changed as required.
#
#########
nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR #MP
nls_numeric_characters = ".,"
nls_sort = binary #MP
nls_comp = binary #MP
nls_length_semantics = BYTE #MP
#########
#
# Multi-Threaded Server (MTS) parameters
#
# Most Oracle E-Business Suite customers do not need to use MTS,
# and the default configuration disables MTS.
#
# If MTS is used, it can have a dramatic effect on the SGA, as
# session memory, including sort and cursor areas, resides in the
# SGA.
#
#########
#########
#
# Auditing parameter
#
# There is a performance overhead for enabling the audit_trail
# parameter. In addition, the database administrator will need
# to implement a purge policy for the SYS.AUD$ table.
#
# Statement-level auditing should not be used.
#
#########
# audit_trail = TRUE # Uncomment if you want to enable audit_trail.
########
#
# Dump parameters
#
# The main dump parameters specify the location of the trace and core
# files, and will normally point to the appropriate trace directories.
# The max_dump_file_size parameter can be used to specify the maximum
# size of a dump file, to prevent a trace file using an excessive
# amount of disk space. (This can also be changed at session level.)
#
########
user_dump_dest = /ebiz/prodr12/udump
background_dump_dest = /ebiz/prodr12/bdump
core_dump_dest = /ebiz/prodr12/cdump
max_dump_file_size = 20480 #Limit default trace file size to 10 MB.
########
#
# Timed statistics
#
# On most platforms, enabling timed statistics has minimal effect
# on performance. It can be enabled or disabled dynamically at
# both system and session level.
#
# Timed statistics is required for use of SQL Trace and Statspack.
#
########
#
timed_statistics = TRUE
########
# Trace file accessibility parameter
#
# As the database machine should be in a secure environment,
# setting this parameter to TRUE is recommended in order to
# facilitate trace file analysis.
# Warning:
# Consider the security implications of setting this to TRUE
# as trace files may include secure data in BIND variables.
#
########
_trace_files_public = TRUE
#########
#
# Processes and sessions parameters
#
# A database process can be associated with one or more database
# sessions. For all technology stack components other than Oracle
# Forms, there is a one-to-one mapping between sessions and processes.
#
# For Forms processes, there will be one database session per
# open form, with a minimum of two sessions per Forms user (one
# for the navigator form, and one for the active form).
#
# The sessions parameter should be set to twice the value of the
# processes parameter.
#
#########
processes = 200 # Max. no. of users.
sessions = 400 # 2 x no. of processes.
db_files = 512 # Max. no. of database files.
dml_locks = 10000 # Database locks.
########
#
# Cursor-related parameters
#
########
cursor_sharing = EXACT #MP
open_cursors = 600
session_cached_cursors = 500
########
#
# Cache parameters
#
# For Oracle 10g and 11g, the automatic SGA tuning option (sga_target)
# is required. This avoids the need for individual tuning of the different
# caches, such as the buffer cache, shared pool, and large pool. Use
# of the automatic SGA tuning option also improves manageability and
# overall performance.
#
# sga_target refers to the total size of the SGA. This includes
# all the sub-caches, such as the buffer cache, log buffer,
# shared pool, and large pool. The sizing table in the
# section Database Initialization Parameter Sizing contains
# sizing recommendations for sga_target.
#
# # When the automatic SGA tuning option is used to dynamically size
# the individual caches, it is recommended to use a Server Parameter
# file (SPFILE) to store the initialization parameter values.
# Using an SPFILE allows the dynamically-adjusted values to persist
# across restarts. Refer to the Oracle Database Administrator's
# Guide for information on how to create and maintain an SPFILE.
sga_target = 2G #MP
db_block_checking = FALSE
db_block_checksum = TRUE
########
#
# Log Writer parameters
#
# The log writer parameters control the size of the log buffer
# within the SGA, and how frequently the redo logs are checkpointed
# (when all dirty buffers written to disk and a new recovery point
# is created).
#
# A size of 10MB for the log buffer is a reasonable value for
# Oracle E-Business Suite. This represents a balance between
# concurrent programs and online users. The value of log_buffer
# must be a multiple of redo block size (normally 512 bytes).
#
# The checkpoint interval and timeout control the frequency of
# checkpoints.
#
########
log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE
#########
#
# Shared pool parameters
#
# The shared pool should be tuned to minimize contention for SQL
# and PL/SQL objects. For Release 12, a reasonable starting point
# is a size of 600M and a 60M reserved area (10%).
#
########
shared_pool_size = 600M
shared_pool_reserved_size = 60M
_shared_pool_reserved_min_alloc = 4100
########
#
# cursor_space_for_time parameter
#
# Cursor space for time is an optimization strategy that
# results in holding pins on cursors and their associated
# frames/buffers for longer periods of time. The pins are held
# until the cursor is closed, instead of at the end-of-fetch
# (normal behavior). This reduces library cache pin traffic,
# which reduces library cache latch gets. Setting cursor space
# for time to TRUE can be useful for large Oracle E-Business
# Suite environments, where library cache latch contention# (specifically due to pin gets) can be a performance issue.
#
# Cursor space for time requires at least a 50% increase in the
# size of the shared pool because of the frames/buffers. If AWR
# or Statspack reports show that the waits for library cache latch
# gets are significant, and the latch gets are due to pin
# requests, then cursor space for time can be used to improve
# performance.
#
# It is important to note that library cache latch contention can
# be caused by numerous different factors, including the use of
# non-sharable SQL (i.e. literals), lack of space, frequent
# loads/unloads, invalidation, patching, gathering statistics
# frequently and during peak periods, and pin requests.
# Cursor space for time is designed to optimize the pin requests
# only, and will not reduce latch contention for other issues.
#
########
# cursor_space_for_time = FALSE # Disabled by default.
#########
#
# PL/SQL parameters
#
# The utl_file_dir parameter must be set as:
# utl_file_dir = < directory> ...
#
########
utl_file_dir = /ebiz/prodr12/utl_file_dir
########
#
# Advanced Queuing (AQ) and Job Queue parameters
#
# AQ requires the TM process to handle delayed messages. A number
# of Application modules use AQ, including Workflow. Job Queues
# enable advanced queue to submit background jobs.
#
# Starting from 10gR1, aq_tm_processes is auto-tuned.
# Omitting this parameter has not, however, been tested with
# Oracle E-Business Suite. The minimum required value is 1 and
# should be increased to resolve lengthy queues.
#
# Usually, job_queue_processes should typically be be set to a value of 2
# for optimal performance. However, the value can be tuned to meet the
# specific requirements of the Workflow module and customer needs,
# based on the number of job queue processes needed to handle AQ
# event messages and for Workflow notification mailers.
#
########
aq_tm_processes = 1
job_queue_processes = 2
########
#
# Archiving parameters
#
# Archiving parameters, including destination (optionally,
# multiple destinations in 10gR2 or 11g) may be specified.
#
########
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc'
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'
########
#
# Parallel execution parameters
#
# Parallel execution is used by some Oracle E-Business Suite concurrent programs,
# including DBI programs and Gathering Statistics. AD will also use parallel
# execution when creating large indexes.
#
########
parallel_max_servers = 8 # Max. value should be 2 x no. of CPUs.
parallel_min_servers = 0
########
#
# Events parameters
#
# Events should only be set when explicitly requested by Oracle Support or Development.
# Refer to the appropriate release-specific section for events that may be set.
#
########
#########
#
# Optimizer parameters
#
# The following optimizer parameters must be set as below, and may
# not be changed. No other values are supported.
#
# Refer also to the release-specific section for any additional
# optimizer parameters which must be set.
#
#########
_sort_elimination_cost_ratio =5 #MP
_like_with_bind_as_equality = TRUE #MP
_fast_full_scan_enabled = FALSE #MP
_b_tree_bitmap_plans = FALSE #MP
optimizer_secure_view_merging = FALSE #MP
_sqlexec_progression_cost = 2147483647 #MP
#########
#
# Oracle Real Application Clusters (Oracle RAC) parameters
#
# The following Oracle RAC related parameters should be set when running
# E-Business Suite in an Oracle RAC environment.
# Set cluster_database = FALSE if not using RAC
#########
cluster_database = TRUE #MP
#########
#
# Parallel Execution and Oracle RAC parameters
#
# It is recommended to set the parameters instance_groups and
# parallel_instance_group on each instance, to ensure that parallel
# requests do not span instances.
# For example, on instance1, set instance_groups=apps1 and
# parallel_instance_group=apps1. On instance2, set
# instance_groups=apps2 and parallel_instance_group=apps2, and so on.
#
#########
#########
#
# Private memory area parameters
#
# The automatic memory manager is used to manage the PGA memory. This
# avoids the need to manually tune the settings of sort_area_size and
# hash_area_size.
# The automatic memory manager also improves performance and scalability,
# as memory is released back to the operating system.
#
#########
pga_aggregate_target = 1G
workarea_size_policy = AUTO #MP
olap_page_pool_size = 4194304
###############################################################################
#
# End of Common Database Initialization Parameters Section
#
###############################################################################
#########
#
# recyclebin parameter
#
# The database recyclebin must be turned off to allow the cleanup phase of the
# online patching cycle to be performed without having to connect as SYS.
#
# This feature may still be used at other times.
#
#########recyclebin=off #########
#
# service_names, local_listener parameter
#
# To support online patching, Oracle E-Business Suite Release 12.2 introduces a
# new database service called ebs_patch.
#
# The service_names parameter specifies one or more names by which clients can
# connect to an instance. The instance registers its service names with the
# listener. When a client requests a service, the listener determines which
# instances offer the requested service and then routes the client to the most
# appropriate instance.
#
# local_listener setting is part of Auto-config templates and is required for
# listener registration of any non default (1521) ports.
#
#########
