Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, August 12, 2008

Deprecated and recommended initialization parameters after 9206 to 11g upgrade

While upgrading the 11.5.10.2 Vision instance which comes with RDBMS 9.2.0.6, the following initialization parameters came up as deprecated:

user_dump_dest
background_dump_dest
core_dump_dest
enqueue_resources
row_locking
undo_suppress_errors
optimizer_max_permutations
max_enabled_roles
sql_trace

The 11g manual upgrade metalink note 429825.1 mentions some more:

Initialization parameters deprecated in Oracle Database 11g release 1 (11.1)

BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)


Appendix B:

Initialization Parameters Obsolete in Oracle Database 11g release 1 (11.1)

DDL_WAIT_FOR_LOCKS
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS

Metalink Note 216205.1 mentions the Release-Specific Database Initialization Parameters for 11gR1 (11.1.X):

####################################################################
#
# Oracle Applications Release 11i - database initialization parameters
#
# This file contains the release-specific database initialization parameters
# for 11gR1. Oracle Applications Release 11i requires a minimum of 11.1.0.6.

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

compatible = 11.1.0

########
#
# Cache Sizes
#
# For 11g, the automatic SGA tuning option (sga_target) is required.
# This avoids the need for individual tuning of the different
# SGA 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 11g Database Administrator's
# Guide for information on how to create and maintain an SPFILE.
#
#
########

sga_target = 1G

########
#
# Shared Pool
#
# The shared pool should be tuned so as to minimize contention for SQL
# and PL/SQL objects. A value of 400M is a reasonable starting point for
#
Release 11i, and automatic SGA tuning will adjust the caches as per
# the workload.
The values below for the shared pool related caches are
# simply minimum (starting) values.
#
########

shared_pool_size = 400M
shared_pool_reserved_size = 40M

#########
#
# NLS and Character Sets
#
#########

nls_length_semantics = BYTE #MP

########
#
# Rollback Segments
#
# From 9iR2, Oracle Applications requires the use of System Managed Undo.
# This is straightforward to manage and administer, much more efficient
# than manually managed rollback segments, and reduces the chances of
# "snapshot too old" errors. To use System Managed Undo, you must create
# an UNDO tablespace.
#
########

undo_management=AUTO #MP
undo_tablespace=apps_undots1 #MP

########
#
# Private Memory Areas
#
# The Automatic Memory Manager is used to manage PGA memory. This avoids
# the need to tune sort_area_size and hash_area_size manually.
#
# 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

########
#
# Cursor-Related Settings
#
# Prior to 10g, PL/SQL (server-side) used the setting of the open_cursors
# parameter as the upper limit for caching PL/SQL (server-side) cursors.
# In 10g, the upper limit was controlled by the parameter session_cached_cursors.
#
# 11g changes the default behavior for the server side PL/SQL cursor cache.
# For 11g environments, the parameters open_cursors and session_cached_cursors
# should be set as follows, in accordance with this change in behavior.
#
########

open_cursors = 600
session_cached_cursors = 500

########
#
# Events
#
# Events should not be set unless specifically requested by Oracle Support,
# or in Applications documentation.
#
########

#########
#
# PL/SQL Parameters
#
# The following parameters are used to enable the PL/SQL global optimizer
# and specify the compilation type.
#
# Release 11i environments that use Oracle Database 11g can employ either
# interpreted or compiled (native) PL/SQL code. The default is interpreted.
# While native PL/SQL compilation can improve runtime performance, this may
# be at the expense of increased downtime, 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 11i uses the Cost Based Optimizer (CBO). The following optimizer
# parameters must be set as shown, and should not be changed.
#
#########

_b_tree_bitmap_plans = FALSE #MP
optimizer_secure_view_merging = FALSE #MP
_optimizer_autostats_job=false # Turn off automatic statistics

#########
#
# Database Password Case Sensitivity (new with Oracle Database 11g)
#
# Database password case sensitivity is a new feature available with 11g.
# Oracle E-Business Suite does not currently integrate with this feature,
# so the parameter must be set to FALSE.
#
#########

sec_case_sensitive_logon = FALSE #MP

6 comments:

Pavan Kumar said...

G'day Vikram,

Not sure which section to post a question on 11.5.10.2 Upgrade:

we are currently on 11.5.9/9.2.0.5.
We upgraded it to 11.5.10.2 database. Is 9.2.0.5 db version supported with 11.5.10.2?
As the rapid install comes along with 9.2.0.6 db so just had a concern?

Thanks for your help.

-Pavan

Vikram Das said...

Hi Pavan,

You can always post your questions to the google group Oracle Apps Technology @ http://groups.google.com/group/oracle-apps-technology.

9.2.0.5 DB version was supported with 11.5.10.2 when 9.2.0.5 was the terminal version of Oracle 9i. However all versions of 9i are de-supported now. So it is best to upgrade to 10.2.0.4 now. In theory, you may not have issues, but from a support perspective, you must upgrade to 10.2.0.4, if you want Oracle to answer your questions in a Service Request in Metalink.

- Vikram

Infinite Dimensions said...

G'day Vikram,

Thank you will join the group. I had this question as 3480000 patch / latest family packs on 9.2.0.5 had lots of latch issues.In 3480000 the patch hung while executing different odf's against a 9.2.0.5 database the patch hung. Not much of hits on this in metalink ..So had to follow few unconventional standards to bypass the issue.

Never had this issue in 9.2.0.6/higher versions.

-Pavan

Vikram Das said...

Hi Pavan,

Back in 2005, when I had done my first upgrade of 11.5.10.2, we were on Oracle 9.2.0.5. I distinctly recall having the same situation. The patch was 3140000 at that time. 3480000 was released later. However it hung up and I was waiting for 12 hours. I checked for locks and realized that it would wait forever. So I stopped all workers. Shutdown the database, restarted it and resumed the patch. It worked.

- Vikram

Anonymous said...

I am reading this article second time today, you have to be more careful with content leakers. If I will fount it again I will send you a link

Anonymous said...

It is useful to try everything in practise anyway and I like that here it's always possible to find something new. :)