Database Initialization Parameters for Oracle E-Business Suite Release 12

In This Document

The most current version of this document can be obtained in My Oracle Support Document 396009.1.

There is a change log at the end of this document.

Key Points

The document consists of a common section, which provides a common set of database initialization parameters used for all releases of the Oracle Database, followed by several release-specific sections, which list parameters and settings required for a particular release of the Oracle Database.
Put together, the parameters from the common section and appropriate release-specific section formulate a complete set of database initialization parameters.
Parameters may appear on a "removal list" because they are obsolete; because the default value is required and no other value may be set; or to cater for certain special cases where a non-default value has to be set to meet specific needs (currently, there is only one such case, which is described in Section 7).
In the various parameter lists, check for comments giving any platform-specific exceptions. Such comments will apply only to the exact platform mentioned: for example, a reference to HP-UX (PA-RISC) will not apply to HP-UX (Itanium IA-64).
The "X" notation used in the release-specific section denotes all patchset releases within that major version. For example, "10.2.0.X" refers to all releases of 10.2.0, such as 10.2.0.2 and 10.2.0.3.
Oracle E-Business Suite Release 12 requires Oracle Database 10g Release 2 (10.2.0.2) Enterprise Edition as a minimum release level and edition. No earlier releases, or other editions of any release, may be used.
Oracle E-Business Suite Release 12.2 requires Oracle Database 11g Release 2 (11.2.0.3) Enterprise Edition as a minimum release level and edition. No earlier releases, or other editions of any release, may be used. Refer to Section 6: Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2.
Oracle E-Business Suite Release 12.1.3 customers with Oracle Database 12c Release 1 Version 12.1.0.2 implementing Oracle Database In-Memory should refer to Oracle Database Administrator's Guide 12c Release 1 (12.1) E41484 for the required set of database initialization parameters.

Section 1: Common Database Initialization Parameters For All Releases

This section lists the database initialization parameters that are common across releases of the Oracle Database. You should refer to it in conjunction with the relevant release-specific section.

Note: All parameters listed in this section should be set as stated, unless your particular release-specific section instructs otherwise.

The parameter values provided in this document reflect a small instance configuration (see Section 9). You should adjust the relevant parameters based on the number of active Oracle E-Business Suite users. In addition, you should investigate any parameters that are set but not mentioned in this document.

##############################################################################

#

# 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 

#

###############################################################################

Section 2: Release-Specific Database Initialization Parameters For Oracle 10g Release 2

This section discusses database initialization parameters and specific releases of Oracle Database 10g Release 2, first describing required parameters, then listing any parameters that should not be used.

2.1 Required Parameters

The following list describes database initialization parameters required for this specific release of the Oracle Database. These parameters should be added to the common database initialization parameters provided in Section 1, so that the final database initialization parameters file includes the common section plus the contents of this release-specific section.

#############################################################################

# 

# Oracle E-Business Suite Release 12

# Release-Specific Database Initialization Parameters for 10gR2 

# 

#############################################################################

#########

# 

# Compatibility parameter

# 

# Compatibility should be set to the current release.

# 

##########

compatible = 10.2.0 #MP

#########

#

# System-managed undo parameters 

#

# Oracle E-Business Suite Release 12 requires the use of system managed undo.

# This is much more efficient than rollback segments, and reduces the chances

# of snapshot too old errors. In addition, it is much easier to manage and

# administer system managed undo than manually managing rollback segments.

#

########

undo_management = AUTO #MP

undo_tablespace = APPS_UNDOTS1

#########

#

# PL/SQL parameters

#

# The following parameters are used to enable the PL/SQL global

# optimizer as well as native compilation.

# 

# PL/SQL native compilation is recommended for Oracle Database 10g-based

# Oracle E-Business Suite environments such as Release 12. Interpreted mode is supported,

# and can be used with Oracle E-Business Suite. However, native compilation is

# recommended in order to maximize runtime performance and scalability.

# Compiling PL/SQL units with native compilation takes longer than using

# interpreted mode, because of the need to generate and compile the native

# shared libraries.

# 

#########

plsql_optimize_level = 2 #MP

plsql_code_type = native

plsql_native_library_dir = /ebiz/prodr12/plsql_nativelib

plsql_native_library_subdir_count = 149

#########

#

# Other parameters

#

# _kks_use_mutex_pin

#

# 10gR2 facilitates the use of mutexes to lock resources in a lightweight

# fashion with higher granularity.

#

# On the HP-UX (PA-RISC) platform only, this parameter must be set to FALSE if using 10gR2.

#

#########

_kks_use_mutex_pin=FALSE # Set to FALSE on HP-UX (PA-RISC) only; otherwise, remove this parameter.

###############################################################################

#

# End of Release-Specific Database Initialization Parameters Section for 10gR2

#

###############################################################################

2.2 Parameter Removal List for Oracle Database 10g Release 2

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 10g Release 2.

Note: Parameters may appear on a "removal list" because they are obsolete; because the default value is required and no other value may be set; or to cater for certain special cases where a non-default value has to be set to meet specific needs (currently, there is only one such case, which is described in Section 7).

_always_anti_join

_always_semi_join

_complex_view_merging

_index_join_enabled

_kks_use_mutex_pin # Unless using HP-UX (PA-RISC) - see "Other parameters" section above.

_new_initial_join_orders

_optimizer_cost_based_transformation

_optimizer_cost_model

_optimizer_mode_force

_optimizer_undo_changes

_or_expand_nvl_predicate

_ordered_nested_loop

_push_join_predicate

_push_join_union_view

_shared_pool_reserved_min_alloc

_sortmerge_inequality_join_off

_table_scan_cost_plus_one

_unnest_subquery

_use_column_stats_for_function

always_anti_join

always_semi_join

db_block_buffers

db_file_multiblock_read_count

db_cache_size

enqueue_resources

event="10932 trace name context level 32768"

event="10933 trace name context level 512"

event="10943 trace name context forever, level 2"

event="10943 trace name context level 16384"

event="38004 trace name context forever, level 1"

hash_area_size

java_pool_size

job_queue_interval

large_pool_size

max_enabled_roles

optimizer_dynamic_sampling

optimizer_features_enable

optimizer_index_caching

optimizer_index_cost_adj

optimizer_max_permutations

optimizer_mode

optimizer_percent_parallel

plsql_compiler_flags

query_rewrite_enabled

row_locking

sort_area_size

undo_retention

undo_suppress_errors

Section 3: Release-Specific Database Initialization Parameters for Oracle 11g Release 1

3.1 Required Parameters

####################################################################

#

# Oracle E-Business Suite Release 12

# Release-Specific Database Initialization Parameters for 11gR1

#

####################################################################

#########

#

# Compatible

# 

# Set compatibility to the current release.

#

#########

compatible = 11.1.0

#######

#

# Diagnostic Parameters

#

# As of Oracle Database 11g Release 1, the diagnostics for each database

# instance are located in a dedicated directory that can be specified

# via the DIAGNOSTIC_DEST initialization parameter. The format of

# the directory specified by DIAGNOSTIC_DEST is as follows:

#

#

# Diagnostic files are located in their own subdirectories of the

# DIAGNOSTIC_DEST directory, according to type:

#

# Trace files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

# Alert logs - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/alert

# Core files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/cdumd

# Incident dump files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/incident/<incdir#> 

diagnostic_dest = ?/prod12

# System-Managed Undo Parameters 

#

# Oracle E-Business Suite requires the use of System Managed Undo (SMU).

# This is more efficient than manually managed rollback segments,

# and reduces the chances of "snapshot too old" errors. It is also

# easier to manage SMU than the rollback segments it replaces.

#

# ########

undo_management=AUTO #MP

undo_tablespace=APPS_UNDOTS1

#########

# PL/SQL parameters

#

# The following parameters are used to enable the PL/SQL global

# optimizer as well as native compilation.

#

# PL/SQL native compilation is recommended for Oracle Database 10g or 11g based

# Oracle E-Business Suite environments such as Release 12. Interpreted mode is supported,

# and can be used with Oracle E-Business Suite. However, native compilation is

# recommended in order to maximize runtime performance and scalability.

# Compiling PL/SQL units with native compilation takes longer than using

# interpreted mode, because of the need to generate and compile the native

# shared libraries.

#

# If native compilation is to be used, uncomment the plsql_code_type = NATIVE

# line below. Note that in 11g, the parameters plsql_native_library_dir and

# plsql_native_library_subdir_count have no effect and are not needed, as

# natively compiled code is now stored in the database, not a filesystem.

#

##########

#plsql_code_type = NATIVE #Uncomment if you want to use NATIVE compilation.

#########

#

# Optimizer Parameters

#

# Release 12 uses the Cost Based Optimizer (CBO). The following optimizer

# parameters must be set as shown, and should not be changed.

#

#########

_optimizer_autostats_job=FALSE #MP Turn off automatic statistics.

#########

# 

# Database Password Case Sensitivity

# 

# The default for Oracle E-Business Suite databases is FALSE i.e. passwords are case-insensitive

# 

# Oracle E-Business Suite now supports Oracle Database 11g case-sensitive database passwords

# This feature is available on E-Business Suite Rel 12.1.1 or higher

# To enable this feature apply patch 12964564 and set SEC_CASE_SENSITIVE_LOGON to TRUE

#

##########
sec_case_sensitive_logon = FALSE

###############################################################################

#

# End of Release-Specific Database Initialization Parameters Section for 11gR1

#

###############################################################################

3.2 Parameter Removal List for Oracle Database 11g Release 1

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 11g Release 1 (11.1.X).

_always_anti_join

_always_semi_join

_complex_view_merging

_index_join_enabled

_kks_use_mutex_pin

_new_initial_join_orders

_optimizer_cost_based_transformation

_optimizer_cost_model

_optimizer_mode_force

_optimizer_undo_changes

_or_expand_nvl_predicate

_ordered_nested_loop

_push_join_predicate

_push_join_union_view

_shared_pool_reserved_min_alloc

_sortmerge_inequality_join_off

_sqlexec_progression_cost

_table_scan_cost_plus_one

_unnest_subquery

_use_column_stats_for_function

always_anti_join

always_semi_join

background_dump_dest

core_dump_dest

db_block_buffers

db_cache_size

db_file_multiblock_read_count

enqueue_resources

event="10932 trace name context level 32768"

event="10933 trace name context level 512"

event="10943 trace name context forever, level 2"

event="10943 trace name context level 16384"

event="38004 trace name context forever, level 1"

hash_area_size

java_pool_size

job_queue_interval

large_pool_size

max_enabled_roles

nls_language

optimizer_dynamic_sampling

optimizer_features_enable

optimizer_index_caching

optimizer_index_cost_adj

optimizer_max_permutations

optimizer_mode

optimizer_percent_parallel

plsql_compiler_flags

plsql_native_library_dir

plsql_native_library_subdir_count

plsql_optimize_level

query_rewrite_enabled

rollback_segments

row_locking

sort_area_size

sql_trace

timed_statistics

undo_retention

undo_suppress_errors

user_dump_dest

Section 4: Release-Specific Database Initialization Parameters for Oracle 11g Release 2

4.1 Required Parameters

####################################################################

#

# Oracle E-Business Suite Release 12

# Release-Specific Database Initialization Parameters for 11gR2

#

####################################################################

#########

#

# Compatible

# 

# Compatibility should be set to the current release.

#

#########

compatible = 11.2.0 #MP

#######

#

# Diagnostic Parameters

#

# As of Oracle Database 11g Release 1, the diagnostics for each database

# instance are located in a dedicated directory that can be specified

# via the DIAGNOSTIC_DEST initialization parameter. The format of

# the directory specified by DIAGNOSTIC_DEST is as follows:

#

# <diagnostic_dest><diagnostic_dest>/diag/rdbms/<dbname>/<instname>

#

# Diagnostic files are located in their own subdirectories of the

# DIAGNOSTIC_DEST directory, according to type:

#

# Trace files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

# Alert logs - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/alert

# Core files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/cdumd

# Incident dump files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/incident/<incdir#>

diagnostic_dest = ?/prod12

#########

#

# System-Managed Undo Parameters

#

# Oracle E-Business Suite requires the use of System Managed Undo (SMU).

# This is more efficient than manually managed rollback segments,

# and reduces the chances of "snapshot too old" errors. It is also

# easier to manage SMU than the rollback segments it replaces.

# ########

undo_management=AUTO #MP

undo_tablespace=APPS_UNDOTS1

#########

# PL/SQL parameters

#

# The following parameters are used to enable the PL/SQL global

# optimizer as well as native compilation.

#

# PL/SQL native compilation is recommended for Oracle Database 10g or 11g based

# Oracle E-Business Suite environments such as Release 12. Interpreted mode is supported,

# and can be used with Oracle E-Business Suite. However, native compilation is

# recommended in order to maximize runtime performance and scalability.

# Compiling PL/SQL units with native compilation takes longer than using

# interpreted mode, because of the need to generate and compile the native

# shared libraries.

#

# If native compilation is to be used, uncomment the plsql_code_type = NATIVE

# line below. Note that in 11g, the parameters plsql_native_library_dir and

# plsql_native_library_subdir_count have no effect and are are not needed, as

# natively compiled code is now stored in the database, not a filesystem.

# 

#########

#plsql_code_type = NATIVE #Uncomment if you want to use NATIVE compilation.

#########

#

# Optimizer Parameters

#

# Release 12 uses cost based optimization. The following optimizer

# parameters must be set as shown, and should not be changed.

#

#########

_optimizer_autostats_job=FALSE #MP Turn off automatic statistics.

#########

# 

# Parallel Execution and Oracle RAC parameters

#

# It is recommended to set the parameters PARALLEL_FORCE_LOCAL

# on each instance, to ensure that parallel requests do not span instances.

# As of 11gR2, EBS customers must set the value of this parameter to TRUE

# and then are no longer required to set parallel_instance_groups and

# instance groups for the purpose of preventing inter-instance sql

# parallelism in RAC environments.

#

#########
parallel_force_local=TRUE #MP
#########

# 

# Database Password Case Sensitivity

# 

# The default for Oracle E-Business Suite databases is FALSE i.e. passwords are case-insensitive

# 

# Oracle E-Business Suite now supports Oracle Database 11g case-sensitive database passwords

# This feature is available on E-Business Suite Rel 12.1.1 or higher

# To enable this feature apply patch 12964564 and set SEC_CASE_SENSITIVE_LOGON to TRUE

#

##########
sec_case_sensitive_logon = FALSE

###############################################################################

#

# End of Release-Specific Database Initialization Parameters Section for 11gR2

#

###############################################################################

4.2 Parameter Removal List for Oracle Database 11g Release 2

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 11g Release 2 (11.2.X).

_always_anti_join

_always_semi_join

_complex_view_merging

_index_join_enabled

_kks_use_mutex_pin

_new_initial_join_orders

_optimizer_cost_based_transformation

_optimizer_cost_model

_optimizer_mode_force

_optimizer_undo_changes

_or_expand_nvl_predicate

_ordered_nested_loop

_push_join_predicate

_push_join_union_view

_shared_pool_reserved_min_alloc

_sortmerge_inequality_join_off

_sqlexec_progression_cost

_table_scan_cost_plus_one

_unnest_subquery

_use_column_stats_for_function

always_anti_join

always_semi_join

background_dump_dest

core_dump_dest

db_block_buffers

db_cache_size

db_file_multiblock_read_count

DRS_START

enqueue_resources

event="10932 trace name context level 32768"

event="10933 trace name context level 512"

event="10943 trace name context forever, level 2"

event="10943 trace name context level 16384"

event="38004 trace name context forever, level 1"

hash_area_size

java_pool_size

job_queue_interval

large_pool_size

max_enabled_roles

nls_language

optimizer_dynamic_sampling

optimizer_features_enable

optimizer_index_caching

optimizer_index_cost_adj

optimizer_max_permutations

optimizer_mode

optimizer_percent_parallel

parallel_instance_group

instance_groups

plsql_compiler_flags

plsql_native_library_dir

plsql_native_library_subdir_count

plsql_optimize_level

query_rewrite_enabled

rollback_segments

row_locking

sort_area_size

sql_trace

SQL_VERSION

timed_statistics

undo_retention

undo_suppress_errors

user_dump_dest

Section 5: Release-Specific Database Initialization Parameters for Oracle 12c Release 1

5.1 Required Parameters

####################################################################

#

# Oracle E-Business Suite Release 12

# Release-Specific Database Initialization Parameters for 12c Release 1

#

####################################################################

#########

#

# Compatible

# 

# Compatibility should be set to the current release.

#

#########

compatible = 12.1.0 #MP

#######

#

# Diagnostic Parameters

#

# As of Oracle Database 11g Release 1, the diagnostics for each database

# instance are located in a dedicated directory that can be specified

# via the DIAGNOSTIC_DEST initialization parameter. The format of

# the directory specified by DIAGNOSTIC_DEST is as follows:

#

# <diagnostic_dest><diagnostic_dest>/diag/rdbms/<dbname>/<instname>

#

# Diagnostic files are located in their own subdirectories of the

# DIAGNOSTIC_DEST directory, according to type:

#

# Trace files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/trace

# Alert logs - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/alert

# Core files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/cdumd

# Incident dump files - <diagnostic_dest>/diag/rdbms/<dbname>/<instname>/incident/<incdir#>

diagnostic_dest = ?/prod12

#########

#

# System-Managed Undo Parameters

#

# Oracle E-Business Suite requires the use of System Managed Undo (SMU).

# This is more efficient than manually managed rollback segments,

# and reduces the chances of "snapshot too old" errors. It is also

# easier to manage SMU than the rollback segments it replaces.

# ########

undo_management=AUTO #MP

undo_tablespace=APPS_UNDOTS1

#########

# PL/SQL parameters

#

# The following parameters are used to enable the PL/SQL global

# optimizer as well as native compilation.

#

# PL/SQL native compilation is recommended for Oracle Database 10g or 11g or 12c based

# Oracle E-Business Suite environments such as Release 12. Interpreted mode is supported,

# and can be used with Oracle E-Business Suite. However, native compilation is

# recommended in order to maximize runtime performance and scalability.

# Compiling PL/SQL units with native compilation takes longer than using

# interpreted mode, because of the need to generate and compile the native

# shared libraries.

#

# If native compilation is to be used, uncomment the plsql_code_type = NATIVE

# line below. Note that in 11g, the parameters plsql_native_library_dir and

# plsql_native_library_subdir_count have no effect and are are not needed, as

# natively compiled code is now stored in the database, not a filesystem.

# 

#########

#plsql_code_type = NATIVE #Uncomment if you want to use NATIVE compilation.

#########

#

# Optimizer Parameters

#

# Release 12 uses cost based optimization. The following optimizer

# parameters must be set as shown, and should not be changed.

#

#########

_optimizer_autostats_job=FALSE #MP Turn off automatic statistics.

#########

# 

# Parallel Execution and Oracle RAC parameters

#

# It is recommended to set the parameters PARALLEL_FORCE_LOCAL

# on each instance, to ensure that parallel requests do not span instances.

# As of 11gR2, EBS customers must set the value of this parameter to TRUE

# and then are no longer required to set parallel_instance_groups and

# instance groups for the purpose of preventing inter-instance sql

# parallelism in RAC environments.

#

#########
parallel_force_local=TRUE #MP
#########

# 

# PGA_AGGREGATE_LIMIT feature in 12c limits PGA memory usage

# 

# The default value of PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 

# 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. 

# It will not exceed 120% of the physical memory size minus the total SGA size.

# 

# PGA_AGGREGATE_LIMIT cannot be set below its default value. If a value of 0 is specified,

# it means there is no limit to the aggregate PGA memory consumed by the instance. 

# If total PGA memory usage is over PGA_AGGREGATE_LIMIT value. The sessions or processes 

# that are consuming the most untunable PGA memory will be terminated.

#

# Recommended value for PGA_AGGREGATE_LIMIT is 0. 

#

##########
pga_aggregate_limit = 0 #MP
#########

# 

# TEMP_UNDO_ENABLED is a new feature in 12c. it helps in reducing the amount of redo caused by DML

# on global temporary tables. If this parameter is set to TRUE it eliminates REDO on the permanent UNDO. 

# Recommended value for TEMP_UNDO_ENABLED is TRUE 

#

##########
temp_undo_enabled = true
#########

# 

# Database Password Case Sensitivity

# 

# The default value of this parameter is TRUE, i.e. passwords are case-insensitive at the database level. 

# The default for Oracle E-Business Suite databases is FALSE, i.e. passwords are case-insensitive.

# Even though its deprecated in 12c, but the parameter is needed to default it to false for Oracle E-Business Suite.

# 

# Oracle E-Business Suite now supports Oracle Database 11g case-sensitive

# database passwords. This feature is available with Oracle E-Business Suite Release 12.1.1 or higher. 

# Even though its deprecated in 12c, paramer value need this parameter to used for EBS.

# To enable case-sensitivity, set the parameter sec_case_sensitive_logon to

# TRUE, and if on a release prior to 12.2 also apply patch 12964564.

#

##########
sec_case_sensitive_logon = FALSE

###############################################################################

#

# End of Release-Specific Database Initialization Parameters Section for 12c

#

###############################################################################

5.2 Parameter Removal List for Oracle Database 12c Release 1

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 12c Release 1.

_always_anti_join

_always_semi_join

_complex_view_merging

_index_join_enabled

_kks_use_mutex_pin

_new_initial_join_orders

_optimizer_cost_based_transformation

_optimizer_cost_model

_optimizer_mode_force

_optimizer_undo_changes

_or_expand_nvl_predicate

_ordered_nested_loop

_push_join_predicate

_push_join_union_view

_shared_pool_reserved_min_alloc

_sortmerge_inequality_join_off

_sqlexec_progression_cost

_table_scan_cost_plus_one

_unnest_subquery

_use_column_stats_for_function

always_anti_join

always_semi_join

background_dump_dest

core_dump_dest

db_block_buffers

db_cache_size

db_file_multiblock_read_count

DRS_START

enqueue_resources

event="10932 trace name context level 32768"

event="10933 trace name context level 512"

event="10943 trace name context forever, level 2"

event="10943 trace name context level 16384"

event="38004 trace name context forever, level 1"

hash_area_size

java_pool_size

job_queue_interval

large_pool_size

max_enabled_roles

nls_language

optimizer_dynamic_sampling

optimizer_features_enable

optimizer_index_caching

optimizer_index_cost_adj

optimizer_max_permutations

optimizer_mode

optimizer_percent_parallel

parallel_instance_group

instance_groups

plsql_compiler_flags

plsql_native_library_dir

plsql_native_library_subdir_count

plsql_optimize_level

query_rewrite_enabled

rollback_segments

row_locking 

sort_area_size

sql_trace

SQL_VERSION

timed_statistics

undo_retention

undo_suppress_errors

user_dump_dest

Section 6: Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2

The parameters in this section only apply to Oracle E-Business Suite Release 12.2 on Oracle Database 11g Release 2 (11.2.0.3 and higher), and should be used in addition to the parameters in the other relevant sections of this document.

######### # # 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. # #########

service_names=%s_dbSid%,ebs_patch

local_listener=%s_dbSid%_LOCAL

Section 7: Using System Managed Undo (SMU)

As mentioned for the parameters related to system undo, the database releases certified for use with Oracle E-Business Suite Release 12 only support the use of system managed undo (SMU). SMU is more efficient than traditional rollback segments and reduces the possibility of snapshot too old (ORA-1555) errors.

Note the following points about the undo_retention parameter:

The values given for undo_retention are for guidance only. This parameter should be adjusted according to the elapse times of the concurrent jobs, and corresponding commit windows.
There is no need to specify a value for undo_retention on Oracle 10g or 11g or 12c based systems, because it is set automatically as part of automatic undo tuning.
Setting this parameter to a value higher than 900 (the default) is recommended if you experience "ORA-1555: Snapshot too old" errors.
Automatic undo is not supported for LOBS.

Section 8: Temporary Tablespace Setup

It is recommended that the temporary tablespace for Oracle E-Business Suite users be created using locally managed temp files with uniform extent sizes of 128K. The 128K extent size is recommended because numerous modules, such as Pricing and Planning, make extensive use of global temporary tables which also reside in the temporary tablespace. Since each user instantiates a temporary segment for these tables, large extent sizes may result in space allocation failures.

The following is an example of creating a locally managed temporary tablespace with temp files:

SQL>drop tablespace temp;

SQL>create temporary tablespace temp

tempfile '/d2/prodr12/dbf/temp01.dbf' size 2000M reuse

extent management local

uniform size 128K;

Section 9: Database Initialization Parameter Sizing

This section provides sizing recommendations based on the active Oracle E-Business Suite user counts. The following table should be used to size the relevant parameters:

Parameter Name	Development or Test Instance	11-100 Users	101-500 Users	501-1000 Users	1001-2000 Users
processes	200	200	800	1200	2500
sessions	400	400	1600	2400	5000
sga_target ^{Footnote 1}	1G	1G	2G	3G	14G
shared_pool_size (csp)	N/A	N/A	N/A	1800M	3000M
shared_pool_reserved_size (csp)	N/A	N/A	N/A	180M	300M
shared_pool_size (no csp)	400M	600M	800M	1000M	2000M
shared_pool_reserved_size (no csp)	40M	60M	80M	100M	100M
pga_aggregate_target	1G	2G	4G	10G	20G
Total Memory Required ^{Footnote 2}	~ 2 GB	~ 3 GB	~ 6 GB	~ 13 GB	~ 34 GB

Footnote 1

The parameter sga_target should be used for Oracle 10g or 11g or 12c based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle 9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g or 12c-based systems, since undo retention is set automatically as part of automatic undo tuning.

Enabling the 11g or 12c Automatic Memory Management (AMM) feature is supported in EBS, and has been found to be useful in scenarios where memory is limited, as it will dynamically adjust the SGA and PGA pool sizes. AMM is enabled by using the memory_target and memory_max_target initialization parameters. MEMORY_TARGET specifies the system-wide sharable memory for Oracle to use when dynamically controlling the SGA and PGA as workloads change. The memory_max_target parameter specifies the maximum size that memory_target may take. AMM has proven useful for small to mid-range systems as it simplifies both the configuration and management. However, many customers with large production systems have experienced better performance with manually sized pools (or large minimum values for the pools). On Linux, Hugepages has resulted in improved performance; however, this configuration is not compatible with AMM. For large mission-critical applications systems, it is advisable to set sga_target with a minimum fixed value for shared_pool_size and pga_aggregate_target.

Footnote 2

The total memory required refers to the amount of memory required for the database instance and associated memory, including the SGA and the PGA. You should ensure that your system has sufficient available memory in order to support the values provided above. The values provided above should be adjusted based on available memory so as to prevent paging and swapping.

General Notes on Table

"Development or Test instance" refers to a small instance used only for development or testing, with no more than 10 users.
The range of user counts provided above refers to active Oracle E-Business Suite users, not total or named users. For example, if you plan to support a maximum of 500 active Oracle E-Business Suite users, then you should use the sizing as per the range 101-500 users.
The parameter values provided in this document reflect a small instance configuration, and you should adjust the relevant parameters based on the Oracle E-Business Suite user counts as listed in the table above.
The "csp" and "no csp" options of the shared pool parameters refer to the use of cursor_space_for_time, which is documented in the common database initialization parameters section.

Note: Enabling cursor_space_for_time can result in significantly larger shared pool requirements.

posted on 2014-12-10 11:42 王小航阅读(258) 评论(0) 编辑收藏举报

刷新页面返回顶部

王小航