The init.ora file in 11gR2 for Siebel CRM and Oracle Applications Release 12

Almost 100 init.ora parameters affect the generation of SQL execution plans and thus the performance of the Oracle database.

There are so many books written about the Oracle database but not a single one dedicated to the init.ora file (or spfile if you prefer). The reason is simple: the names and the values vary from release to release and some special applications require special parameters.

Interesting enough, iTunes have already a free init.ora application, which DBAs can download! It says: “Oracle Database initialization parameters reference guide. This offline guide helps you in finding information about 2152 Oracle Database initialization parameters even without 3G or WIFI.”

The purpose of this blog post is also simple: how to set up a good init.ora file for 11gR2 in a “normal” database, in a Siebel database and in Oracle E-Business Suite Release 12.

1. Standard database:

You can get a sample init.ora file from the Oracle 11.2 Documentation. Here is one, I have already successfully used in dozens of critical production database.

Feel free to modify it according to your needs and use the new 11g parameters only if you understand what they are meant for. Modify the floating parameters according to your hardware capabilities and filesystem structure.

# Modified by Julian on 08.08.2011

# Archive
log_archive_dest= /opt/oracle11/SAMPLE/arch
log_archive_format=arc_SAMPLE_%t_%s_%r.arc
log_checkpoints_to_alert = true
log_checkpoint_interval = 9999999
log_checkpoint_timeout = 0
fast_start_parallel_rollback = high

# Flashback
# db_flashback_retention_target = 180
# db_recovery_file_dest = /opt/oracle11/SAMPLE/arch
# db_recovery_file_dest_size = 32G

# Cache, I/O, etc.

db_block_size = 8192
memory_target = 32G
recovery_parallelism = 64
result_cache_mode = force
result_cache_max_size = 128M

# Cursors and Library Cache
open_cursors = 1000
session_cached_cursors = 500

# Database Identification
db_domain=europe.xxx.com
db_name=SAMPLE
service_names=SAMPLE
smtp_out_server = ‘xxxx’

# Diagnostics and Statistics
optimizer_capture_sql_plan_baselines = true

# File Configuration
control_files=(“/opt/oracle11/data/SAMPLE/control_01.ctl”, “/opt/oracle11/index/SAMPLE/control_02.ctl”)

# Instance Identification
instance_name = SAMPLE

# Miscellaneous
compatible = 11.2.0
db_ultra_safe = data_and_index
ddl_lock_timeout = 10

# Materialized views
query_rewrite_enabled = TRUE

# Processes and Sessions
processes = 1000

# Security and Auditing
remote_login_passwordfile = exclusive
audit_trail = ‘db_extended’
audit_sys_operations = true

# System Managed Undo
undo_retention = 3600
undo_tablespace = UNDOTBS

2. Siebel 8.x parameters

Add all these Siebel recommended init.ora parameters:

_always_semi_join = OFF
_b_tree_bitmap_plans = FALSE
_partition_view_enabled = FALSE
_gc_defer_time = 0
_no_or_expansion = FALSE
_optimizer_max_permutations = 100
optimizer_index_caching = 0
optimizer_mode = all_rows
query_rewrite_integrity = enforced
star_transformation_enabled = false
optimizer_index_cost_adj = 1
optimizer_dynamic_sampling = 1
query_rewrite_enabled = false
statistics_level = typical
memory_target = 60% of total physical memory on DB server

Remove _add_col_optim_enabled if present!

3. Oracle E-Business Suite Release 12 parameters:

Add the following parameters:

_optimizer_autostats_job=false
_trace_files_public = TRUE
_sort_elimination_cost_ratio =5
_like_with_bind_as_equality = TRUE
_fast_full_scan_enabled = FALSE
_b_tree_bitmap_plans = FALSE
optimizer_secure_view_merging = FALSE
plsql_code_type = NATIVE
sec_case_sensitive_logon = FALSE
db_files = 512
dml_locks = 10000
cursor_sharing = EXACT
open_cursors = 600
session_cached_cursors = 500
log_checkpoint_timeout = 1200
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE
shared_pool_size = 600M
shared_pool_reserved_size = 60M
cursor_space_for_time = FALSE
aq_tm_processes = 1
job_queue_processes = 2
max_commit_propagation_delay = 0 (RAC only)
olap_page_pool_size = 4194304

If these parameters exist, you should remove them from your database initialization parameters file for Oracle Database 11gR2:

_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
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

These additional parameter recommendations by Oracle are based on the active Oracle E-Business Suite user counts:

For additional/detailed information check MOS ID 396009.1: Database Initialization Parameters for Oracle Applications Release 12

Although some parameters above have the default values, they are listed because of their importance.

DBA’s biggest enemy

A life spent in constant labor is a life wasted, save a man be such a fool as to regard a fulsome obituary notice as ample reward. ~George Jean Nathan

It is impossible to enjoy idling thoroughly unless one has plenty of work to do. ~Jerome K. Jerome

Slow down and everything you are chasing will come around and catch you. ~John De Paola

Back to reality: often problems occur when DBA managers are interested in the following three topics: costs savings, cost saving and cost savings. While rotating them in a round-robin fashion, some do consider in between which one to pick up next. It is not surprising for any DBA to have one of the following incentives for the year to come: automation, efficiency, do more with less, etc.

Very nice indeed but in several cases such extreme requests for perfection and efficiency are a call in the dark for the DBA’s biggest enemy. The Stress.

When Atlas Stumbles: Lessons for Preventing DBA Burn Out by Michael Corey is one of the most interesting articles on the topic one can find online. Let me quote him:

“Lesson 5: Stress is your biggest enemy. The world of the DBA is an incredibly stressful one and it is even worse in smaller organizations. If you have only one DBA, he or she knows that they alone have to keep the database up and running, or the business will stop running. This causes the Atlas Syndrome, named after the Greek myth of the Titan condemned by Zeus to hold up the heavens on his shoulders.”

In his blog, in an article entitled DBA Stress and retaining DBAs, Kirk Brocas says: “I couldn’t agree more with this bit though – Lesson 5”. Me too!

Why is that? How is the DBA profession different than any other? Here is how I see it:

1. Frustration and responsibility: DBAs get often blamed for problems that are beyond their control: “Why is the database slow”? Try to explain that those “additional” Siebel joins on 15 tables are not that easy to tune. Or than putting critical databases on RAC is not always the solution. And how important is to patch in order to avoid and fix the bugs but this somehow does not happen with zero downtime. You explain, explain.. but who believes you is rather unclear. Another thing: a small mistake/typo made by the DBA can bring systems down and cause huge financial losses. And how about arriving late at the party: always called upon after everything has gone wrong rather than before things were started so the DBA could recommend problem avoidance tactics!

Solution: listen very carefully to what the DBAs have to say.

2. Visibility, rewards and recognition: “The best DBA is the invisible one”. I used to hear that years ago. “No news is good news”. Yes but often no visibility and no news leads to no rewards and no recognition. Often DBAs are noticed and there names are in the headlines when downtime occurs or at least when systems are slow/hang.

Solution: give the DBAs the visibility they deserve.

3. On-call and overtime: Most DBAs work day and night. Or at least are available around the clock. I for one am not at all interested in smart phones, iphones, you name the phone. That is after 10.000+ hours of DBA on call. Connect the dots please. Do respect the DBA’s wish to quit on-call duty when enough is enough.

Solution: plan the DBA resources well in advance in order to avoid DBAs snowed with work around you.

4. Communication: all databases are down because of OS patching. Oh, the sysadmins forgot to mention that to the DBA team. Just one more example: have you ever tried to convince someone that having the downtime will probably be cheaper than investing in expensive systems created for infinite availability and zero downtime? Mission impossible!

Solution: as the database is part of every IT application, always involve/inform the DBAs for all incoming IT activities.

5. Fire fighting: no time left for proactive work. Because there are not enough DBAs for supporting all the databases. Or too many changes being constantly made? DBAs tune a system but new build comes to production and then you start tuning from scratch. I am far from pointing fingers to developers but someone must be behind those endless bugs in the database software!

Solution: the DBAs should be *always* involved when planning the new systems.

And finally: DBADD – Database attention deficit disorder. Give a dozen things superficial attention or one thing full & proper attention. Guess which one produces proper fixes and which one produces quick workarounds?

More on the topic:

How Do You Handle the Stress of Being a DBA by Brad M. McGehee

How To Prevent DBA Burnout, Suggestions Anyone by Michael Corey