Database Initialization Parameters for Oracle E-Business Suite Release 12

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

Mostly talking about 19c (multitenant) 

For Oracle Database 19c with a single tenant (1 CDB:1 PDB) configuration:

Update Database Initialization Parameter at PDB level

• By default, initialization parameters at the PDB level inherit the value from the initialization parameters at the CDB level.
• There are parameters that can be changed at the PDB level and override what is being inherited from the CDB.
• To identify which parameters can be modified for a PDB, the ISPDB_MODIFIABLE column in the V$SYSTEM_PARAMETER view must be TRUE.
•   If the ISPDB_MODIFIABLE is TRUE then the parameter inherits the value from the CDB until it is changed with an ALTER SYSTEM SET command at the PDB.
• The initialization parameter set at the PDB level aren't stored in the spfile. They are stored in a data dictionary table in the root of the CDB (SYS.PDB_SPFILE$).
• When a PDB is moved around (through an unplug/plug), the initialization parameters set at the PDB level are part of the metadata stored in the XML file used for the unplug/plug.
• To reset a database parameter at PDB level

source <EBS CDB>_<hostname>.env

export ORACLE_PDB_SID=<EBS PDB SID>

sqlplus "/as sysdba"

Example: alter system set "_disable_actualization_for_grant"=TRUE;

To check what all DB parameters can be set at PDB level, following query can be used:

SELECT NAME,ISPDB_MODIFIABLE FROM V$SYSTEM_PARAMETER WHERE UPPER(NAME) IN ('PROCESSES','SESSIONS','OPEN_CURSORS','_DISABLE_ACTUALIZATION_FOR_GRANT') ORDER BY 2,1;

 Check value of DB parameters at CDB and PDB level:

 

SELECT CON_ID,NAME,DISPLAY_VALUE FROM V$SYSTEM_PARAMETER WHERE UPPER(NAME) IN ('<db parameter name>') ORDER BY 1,2,3;

Required Parameters - validate and set below init parameters ( if any parameter is set then validate and check if missing from list)

compatible = 19.0.0 #MP
optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP
pga_aggregate_limit = 0 #MP <<<<<<<<<<<<<<<<<< change/update
temp_undo_enabled = FALSE <<<<<<<<<<<<<<<<<< change/update
_pdb_name_case_sensitive = TRUE #MP
event='10946 trace name context forever, level 8454144' #MP

Change/update parameter result_cache_max_size to 600M

4.2 Parameter Removal List for Oracle Database 19c
validate and remove from database
olap_page_pool_size

#MP - Must parameters

Release-Specific Database Initialization Parameters for Oracle 19c

4.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 19c
#
####################################################################

#########
#
# Compatibility parameter.
#
# Compatibility should be set to the current release.
#
#########

compatible = 19.0.0 #MP

#########
#
# Optimizer parameters.
#
# In Oracle Database 12.2 or higher, the Parameter optimizer_adaptive_features has been obsoleted,
# replaced and controlled by two new parameters, optimizer_adaptive_plans, which defaults to TRUE and
# optimizer_adaptive_statistics, which defaults to FALSE. For more details Refer MOS DOC ID 2031605.1.
#
##########

optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP

#########
#
# PGA Aggregate Limit parameter.
#
# PGA_AGGREGATE_LIMIT feature in 19c 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 parameter.
#
# TEMP_UNDO_ENABLED helps to reduce the amount of redo caused by DML on global temporary tables.
# Setting to TRUE may cause serious issues, such as ORA-55526, for distributed transactions (Ref. Bug 20712819).
# The recommended value for systems using distributed transactions is currently FALSE (Pending ER 24286334).
# If not using distributed transactions, TRUE will improve performance by eliminating REDO on permanent UNDO.
#
##########

temp_undo_enabled = FALSE

#########
#
# Multitenant Architecture parameter.
#
# This parameter is required if the non-cdb name in lower case or mixed case, then
# plugin database will be created with the same case. Otherwise default is Upper case.
# This parameter needs to be set at the CDB level before plugging in the PDB
#
##########

_pdb_name_case_sensitive = TRUE #MP

#########
#
# Event parameter.
#
# Ensure the following event is set for Oracle E-Business Suite to work with Database Release 19c.
# This parameter needs to be set at the CDB level. When this event is set,
# it will allow utl_file functions to accept a directory path as input and
# enable backward compatibility of user/all/dba_arguments behavior on 19c.
#
##########

event='10946 trace name context forever, level 8454144' #MP


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

4.2 Parameter Removal List for Oracle Database 19c

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

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 6).

_kks_use_mutex_pin
_shared_pool_reserved_min_alloc
_sqlexec_progression_cost
exafusion_enabled
exclude_seed_cdb_view
global_context_pool_size
max_enabled_roles
o7_dictionary_accessibility
olap_page_pool_size
optimizer_adaptive_features
parallel_automatic_tuning
parallel_degree_level
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count
plsql_optimize_level
standby_archive_dest
timed_statistics
use_indirect_data_buffers
utl_file_d

Section 5: 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.4 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 #MP

#########
# 
# Service Names and Local Listener parameters.
#
# To support online patching, Oracle E-Business Suite Release 12.2 introduces a
# new database service.
#
# 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.
#
# On codelevels lower than AD-TXK Delta 9, the service name is always 'ebs_patch'.
# From the AD-TXK Delta 9 codelevel, the service name is defined by the the value
# of the context variable 's_patch_service_name'.
# 
# The local_listener setting is part of the AutoConfig templates, and required
# for listener registration of any non-default (1521) ports.
# 
#########
# 
# For Oracle Database 19c with a single tenant (1 CDB:1 PDB) configuration:
#
# service_names: The value for this parameter is auto-populated when CDB is created.
# This parameter should not be modified manually.
#
# local_listener: Need to set this parameter value as per the instructions given in the
# Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
# (Ref. MOS Doc ID 2552181.1).
#
#########

service_names=%s_dbSid%, <s_patch_service_name or ebs_patch>  # Based on AD/TXK Code level

local_listener=%s_dbSid%_LOCAL

#########
#
# Result Cache parameter.
#
# For Oracle E-Business Suite 12.2.x, you should set the result_cache_max_size parameter to make optimal use of result cache memory.
# The value of result_cache_max_size is the maximum amount of memory taken from the shared pool, so it should be set in consideration
# with the setting of the shared_pool_size parameter.
#
# For higher concurrency environments that have more than 2000 active Oracle E-Business Suite user accounts, a setting of 600M is
# recommended.
#
#########

result_cache_max_size = 600Mir

Section 5: 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.4 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 #MP

#########
# 
# Service Names and Local Listener parameters.
#
# To support online patching, Oracle E-Business Suite Release 12.2 introduces a
# new database service.
#
# 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.
#
# On codelevels lower than AD-TXK Delta 9, the service name is always 'ebs_patch'.
# From the AD-TXK Delta 9 codelevel, the service name is defined by the the value
# of the context variable 's_patch_service_name'.
# 
# The local_listener setting is part of the AutoConfig templates, and required
# for listener registration of any non-default (1521) ports.
# 
#########
# 
# For Oracle Database 19c with a single tenant (1 CDB:1 PDB) configuration:
#
# service_names: The value for this parameter is auto-populated when CDB is created.
# This parameter should not be modified manually.
#
# local_listener: Need to set this parameter value as per the instructions given in the
# Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
# (Ref. MOS Doc ID 2552181.1).
#
#########

service_names=%s_dbSid%, <s_patch_service_name or ebs_patch>  # Based on AD/TXK Code level

local_listener=%s_dbSid%_LOCAL

#########
#
# Result Cache parameter.
#
# For Oracle E-Business Suite 12.2.x, you should set the result_cache_max_size parameter to make optimal use of result cache memory.
# The value of result_cache_max_size is the maximum amount of memory taken from the shared pool, so it should be set in consideration
# with the setting of the shared_pool_size parameter.
#
# For higher concurrency environments that have more than 2000 active Oracle E-Business Suite user accounts, a setting of 600M is
# recommended.
#
#########

result_cache_max_size = 600M