Monthly Archives: January 2013

Undocumented Parameters in Oracle 11g

In this post, I will give a list of all undocumented parameters in Oracle 11g. Here is a query to see all the parameters (documented and undocumented) which contain the string you enter when prompted:

– Enter name of the parameter when prompted

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT 
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

Enter value for 1: 
Here is the list of all undocumented parameters in Oracle 11g  :
Parameter Name Parameter Value Description
_4031_dump_bitvec 67194879 bitvec to specify dumps prior to 4031 error
_4031_dump_interval 300 Dump 4031 error once for each n-second interval
_4031_max_dumps 100 Maximum number of 4031 dumps for this process
_4031_sga_dump_interval 3600 Dump 4031 SGA heapdump error once for each n-second interval
_4031_sga_max_dumps 10 Maximum number of SGA heapdumps
_NUMA_instance_mapping Not specified Set of nodes that this instance should run on
_NUMA_pool_size Not specified aggregate size in bytes of NUMA pool
_PX_use_large_pool FALSE Use Large Pool as source of PX buffers
_UTL_FILE_FOLLOW_SYMLINKS FALSE UTL_FILE backout event which allows UTL_FILE to open symlinks
__db_cache_size 314572800 Actual size of DEFAULT buffer pool for standard block size buffers
__dg_broker_service_names pdb11_XPT service names for broker use
__java_pool_size 20971520 Actual size in bytes of java pool
__large_pool_size 4194304 Actual size in bytes of large pool
__oracle_base /u01/app/oracle ORACLE_BASE
__pga_aggregate_target 209715200 Current target size for the aggregate PGA memory consumed
__sga_target 629145600 Actual size of SGA
__shared_io_pool_size 0 Actual size of shared IO pool
__shared_pool_size 281018368 Actual size in bytes of shared pool
__streams_pool_size 0 Actual size in bytes of streams pool
_abort_recovery_on_join FALSE if TRUE, abort recovery on join reconfigurations
_accept_versions List of parameters for rolling operation
_active_standby_fast_reconfiguration TRUE if TRUE optimize dlm reconfiguration for active/standby OPS
_adaptive_direct_read TRUE Adaptive Direct Read
_adaptive_fetch_enabled TRUE enable/disable adaptive fetch in parallel group by
_add_col_optim_enabled TRUE Allows new add column optimization
_add_stale_mv_to_dependency_list TRUE add stale mv to dependency list
_addm_auto_enable TRUE governs whether ADDM gets run automatically after every AWR snapshot
_addm_skiprules comma-separated list of ADDM nodes to skip
_addm_version_check TRUE governs whether ADDM checks the input AWR snapshot version
_adjust_literal_replacement FALSE If TRUE, we will adjust the SQL/PLUS output
_affinity_on TRUE enable/disable affinity at run time
_aiowait_timeouts 100 Number of aiowait timeouts before error is reported
_alert_expiration 604800 seconds before an alert message is moved to exception queue
_alert_message_cleanup 1 Enable Alert Message Cleanup
_alert_message_purge 1 Enable Alert Message Purge
_alert_post_background 1 Enable Background Alert Posting
_all_shared_dblinks treat all dblinks as shared
_allocate_creation_order FALSE should files be examined in creation order during allocation
_allocation_update_interval 3 interval at which successful search in L1 should be updated
_allow_commutativity TRUE allow for commutativity of +, * when comparing expressions
_allow_drop_snapshot_standby_grsp FALSE Allow dropping snapshot standby guaranteed restore point
_allow_error_simulation FALSE Allow error simulation for testing
_allow_level_without_connect_by FALSE allow level without connect by
_allow_read_only_corruption FALSE allow read-only open even if database is corrupt
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
_allow_sage_attr TRUE Allow checking Disk Group SAGE Attr
_allow_terminal_recovery_corruption FALSE Finish terminal recovery even if it may cause corruption
_alternate_iot_leaf_block_split_points TRUE enable alternate index-organized table leaf-block split-points
_always_anti_join CHOOSE always use this method for anti-join when possible
_always_semi_join CHOOSE always use this method for semi-join when possible
_always_star_transformation FALSE always favor use of star transformation
_app_ctx_vers FALSE enable app ctx versioning
_aq_tm_scanlimit 0 scan limit for Time Managers to clean up IOT
_arch_corrupted_redo_log 0 Variable to simulate corruption errors during archivals
_arch_io_slaves 0 ARCH I/O slaves
_array_update_vector_read_enabled FALSE Enable array update vector read
_ash_disk_filter_ratio 10 Ratio of the number of in-memory samples to the number of samples actually written to disk
_ash_disk_write_enable TRUE To enable or disable Active Session History flushing
_ash_dummy_test_param 0 Oracle internal dummy ASH parameter used ONLY for testing!
_ash_eflush_trigger 66 The percentage above which if the in-memory ASH is full the emergency flusher will be triggered
_ash_enable TRUE To enable or disable Active Session sampling and flushing
_ash_min_mmnl_dump 90 Minimum Time interval passed to consider MMNL Dump
_ash_sample_all FALSE To enable or disable sampling every connected session including ones waiting for idle waits
_ash_sampling_interval 1000 Time interval between two successive Active Session samples in millisecs
_ash_size 1048618 To set the size of the in-memory Active Session History buffers
_asm_acd_chunks 1 initial ACD chunks created
_asm_allow_only_raw_disks TRUE Discovery only raw devices
_asm_allow_resilver_corruption FALSE Enable disk resilvering for external redundancy
_asm_ausize 1048576 allocation unit size
_asm_blksize 4096 metadata block size
_asm_compatibility 10.1 default ASM compatibility level
_asm_dbmsdg_nohdrchk FALSE dbms_diskgroup.checkfile does not check block headers
_asm_disk_repair_time 14400 seconds to wait before dropping a failing disk
_asm_emulmax 10000 max number of concurrent disks to emulate I/O errors
_asm_emultimeout 0 timeout before emulation begins (in 3s ticks)
_asm_kfdpevent 0 KFDP event
_asm_kfioevent 0 KFIO event
_asm_libraries ufs library search order for discovery
_asm_maxio 1048576 Maximum size of individual I/O request
_asm_repairquantum 60 quantum (in 3s) used to compute elapsed time for disk drop
_asm_runtime_capability_volume_support FALSE runtime capability for volume support returns supported
_asm_skip_resize_check FALSE skip the checking of the clients for s/w compatibility for resize
_asm_stripesize 131072 ASM file stripe size
_asm_stripewidth 8 ASM file stripe width
_asm_wait_time 18 Max/imum time to wait before asmb exits
_asmlib_test 0 Osmlib test event
_asmsid asm ASM instance id
_assm_default TRUE ASSM default
_async_recovery_claims TRUE if TRUE, issue recovery claims asynchronously
_async_recovery_reads TRUE if TRUE, issue recovery reads asynchronously
_async_ts_threshold 1 check tablespace thresholds asynchronously
_auto_assign_cg_for_sessions FALSE auto assign CGs for sessions
_automatic_maintenance_test 0 Enable AUTOTASK Test Mode
_automemory_broker_interval 3 memory broker statistics gathering interval for auto memory
_autotask_max_window 480 Maximum Logical Maintenance Window Length in minutes
_autotask_min_window 15 Minimum Maintenance Window Length in minutes
_autotune_gtx_idle_time 600 idle time to trigger auto-shutdown a gtx background process
_autotune_gtx_interval 5 interval to autotune global transaction background processes
_autotune_gtx_threshold 60 auto-tune threshold for degree of global transaction concurrency
_avoid_prepare TRUE if TRUE, do not prepare a buffer when the master is local
_aw_row_source_enabled TRUE Enables AW Row Source instead of collection iterator
_awr_corrupt_mode FALSE AWR Corrupt Mode
_awr_disabled_flush_tables Disable flushing of specified AWR tables
_awr_flush_threshold_metrics TRUE Enable/Disable Flushing AWR Threshold Metrics
_awr_flush_workload_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_awr_mmon_cpuusage TRUE Enable/disable AWR MMON CPU Usage Tracking
_awr_restrict_mode FALSE AWR Restrict Mode
_awr_sql_child_limit 200 Setting for AWR SQL Child Limit
_b_tree_bitmap_plans TRUE enable the use of bitmap plans for tables w. only B-tree indexes
_backup_disk_io_slaves 0 BACKUP Disk I/O slaves
_backup_encrypt_opt_mode 4294967294 specifies encryption block optimization mode
_backup_io_pool_size 1048576 memory to reserve from the large pool
_backup_kgc_bufsz 0 specifies buffer size to be used by BZIP2 compression
_backup_kgc_memlevel 8 specifies memory level for ZLIB compression
_backup_kgc_niters 0 specifies number of iterations done by BZIP2 compression
_backup_kgc_perflevel 1 specifies compression (performance) level for ZLIB compression
_backup_kgc_scheme ZLIB specifies compression scheme
_backup_kgc_type 0 specifies compression type used by kgc BZIP2 compression
_backup_kgc_windowbits 15 specifies window size for ZLIB compression
_backup_ksfq_bufcnt 0 number of the ksfq buffers used for backup/restore
_backup_ksfq_bufcnt_max 0 maximum number of the ksfq buffer used for backup/restore
_backup_ksfq_bufsz 0 size of the ksfq buffer used for backup/restore
_backup_max_gap_size 4294967294 largest gap in an incremental/optimized backup buffer, in bytes
_bct_bitmaps_per_file 8 number of bitmaps to store for each datafile
_bct_buffer_allocation_max 104857600 maximum size of all change tracking buffer allocations, in bytes
_bct_buffer_allocation_min_extents 1 mininum number of extents to allocate per buffer allocation
_bct_buffer_allocation_size 2097152 size of one change tracking buffer allocation, in bytes
_bct_chunk_size 0 change tracking datafile chunk size, in bytes
_bct_crash_reserve_size 262144 change tracking reserved crash recovery SGA space, in bytes
_bct_file_block_size 0 block size of change tracking file, in bytes
_bct_file_extent_size 0 extent size of change tracking file, in bytes
_bct_fixtab_file change tracking file for fixed tables
_bct_initial_private_dba_buffer_size 0 initial number of entries in the private change tracking dba buffers
_bct_public_dba_buffer_size 0 total size of all public change tracking dba buffers, in bytes
_bitmap_or_improvement_enabled TRUE controls extensions to partition pruning for general predicates
_block_change_tracking TRUE change tracking possible
_blocking_sess_graph_cache_size blocking session graph cache size in bytes
_blocks_per_cache_server 64 number of consecutive blocks per global cache server
_bloom_filter_debug 0 debug level for bloom filtering
_bloom_filter_enabled TRUE enables or disables bloom filter
_bloom_predicate_enabled FALSE enables or disables bloom filter predicate pushdown
_bloom_pruning_enabled TRUE Enable partition pruning using bloom filtering
_bloom_vector_elements 0 number of elements in a bloom filter vector
_bsln_adaptive_thresholds_enabled TRUE Adaptive Thresholds Enabled
_bt_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and base tables
_buffer_busy_wait_timeout 100 buffer busy wait time in centiseconds
_buffered_publisher_flow_control_threshold 0 Flow control threshold for buffered publishers except capture
_bufq_stop_flow_control FALSE Stop enforcing flow control for buffered queues
_build_deferred_mv_skipping_mvlog_update FALSE DEFERRED MV creation skipping MV log setup update
_bump_highwater_mark_count 0 how many blocks should we allocate per free list on advancing HWM
_bwr_for_flushed_pi TRUE if TRUE, generate a BWR for a flushed PI
_cache_stats_monitor FALSE if TRUE, enable cache stats monitoring
_capture_buffer_size 65536 To set the size of the PGA I/O recording buffers
_capture_publisher_flow_control_threshold 0 Flow control threshold for capture publishers
_case_sensitive_logon TRUE case sensitive logon enabled
_cgs_reconfig_timeout 1000 CGS reconfiguration timeout interval
_cgs_send_timeout 300 CGS send timeout value
_cgs_tickets 1000 CGS messaging tickets
_check_block_after_checksum TRUE perform block check after checksum if both are turned on
_check_block_new_invariant_for_flashback FALSE check block new invariant for flashback
_check_ts_threshold 0 check tablespace thresholds
_cleanup_rollback_entries 100 no. of undo entries to apply per transaction cleanup
_client_result_cache_bypass FALSE bypass the client result cache
_close_cached_open_cursors FALSE close cursors cached by PL/SQL at each commit
_cluster_library clss cluster library selection
_clusterwide_global_transactions TRUE enable/disable clusterwide global transactions
_collapse_wait_history FALSE collapse wait history
_collect_undo_stats TRUE Collect Statistics v$undostat
_column_compression_factor 0 Column compression ratio
_column_elimination_off FALSE turn off predicate-only column elimination
_column_tracking_level 1 column usage tracking
_compilation_call_heap_extent_size 16384 Size of the compilation call heaps extents
_complex_view_merging TRUE enable complex view merging
_compression_above_cache 0 number of recompression above cache for sanity check
_compression_advisor 0 Compression advisor
_compression_chain 50 percentage of chained rows allowed for Compression
_controlfile_block_size 0 control file block size in bytes
_controlfile_enqueue_dump FALSE dump the system states after controlfile enqueue timeout
_controlfile_enqueue_holding_time 780 control file enqueue max holding time in seconds
_controlfile_enqueue_timeout 900 control file enqueue timeout in seconds
_controlfile_section_init_size control file initial section size
_controlfile_section_max_expand control file max expansion rate
_controlfile_update_check OFF controlfile update sanity check
_convert_set_to_join FALSE enables conversion of set operator to join
_coord_message_buffer 0 parallel recovery coordinator side extra message buffer size
_corrupted_rollback_segments corrupted undo segment list
_cost_equality_semi_join TRUE enables costing of equality semi-join
_cp_num_hash_latches 1 connection pool number of hash latches
_cpu_to_io 0 divisor for converting CPU cost to I/O cost
_cr_grant_global_role TRUE if TRUE, grant lock for CR requests when block is in global role
_cr_grant_local_role AUTO turn 3-way CR grants off, make it automatic, or turn it on
_cr_server_log_flush TRUE if TRUE, flush redo log before serving a CR buffer
_cursor_bind_capture_area_size 400 maximum size of the cursor bind capture area
_cursor_bind_capture_interval 900 interval (in seconds) between two bind capture for a cursor
_cursor_db_buffers_pinned 178 additional number of buffers a cursor can pin at once
_cursor_features_enabled 2 Shared cursor features enabled bits.
_cursor_plan_enabled TRUE enable collection and display of cursor plans
_cursor_plan_hash_version 1 version of cursor plan hash value
_cursor_plan_unparse_enabled TRUE enables/disables using unparse to build projection/predicates
_cursor_stats_bucket 15 Hash table buckets for cursor stats (2^).
_cursor_stats_heap 4 Heaps/latches for cursor stats.
_cvw_enable_weak_checking TRUE enable weak view checking
_db_aging_cool_count 1 Touch count set when buffer cooled
_db_aging_freeze_cr FALSE Make CR buffers always be too cold to keep in cache
_db_aging_hot_criteria 2 Touch count which sends a buffer to head of replacement list
_db_aging_stay_count 0 Touch count set when buffer moved to head of replacement list
_db_aging_touch_time 3 Touch count which sends a buffer to head of replacement list
_db_always_check_system_ts TRUE Always perform block check and checksum for System tablespace
_db_block_adjcheck TRUE adjacent cache buffer checks – low blkchk overwrite parameter
_db_block_adjchk_level 0 adjacent cache buffer check level
_db_block_align_direct_read TRUE Align Direct Reads
_db_block_bad_write_check FALSE enable bad write checks
_db_block_buffers 27060 Number of database blocks cached in memory: hidden parameter
_db_block_cache_clone FALSE Always clone data blocks on get (for debugging)
_db_block_cache_history 0 buffer header tracing (non-zero only when debugging)
_db_block_cache_history_level 2 buffer header tracing level
_db_block_cache_num_umap 0 number of unmapped buffers (for tracking swap calls on blocks)
_db_block_cache_protect FALSE protect database blocks (true only when debugging)
_db_block_cache_protect_internal 0 protect database blocks (for strictly internal use only)
_db_block_check_for_debug FALSE Check more and dump block before image for debugging
_db_block_check_objtyp TRUE check objd and typ on cache disk read
_db_block_corruption_recovery_threshold 5 threshold number of block recovery attempts
_db_block_do_full_mbreads FALSE do full block read even if some blocks are in cache
_db_block_hash_buckets 65536 Number of database block hash buckets
_db_block_hash_latches 2048 Number of database block hash latches
_db_block_header_guard_level 0 number of extra buffer headers to use as guard pages
_db_block_hi_priority_batch_size 0 Fraction of writes for high priority reasons
_db_block_known_clean_pct 2 Initial Percentage of buffers to maintain known clean
_db_block_lru_latches 8 number of lru latches
_db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba
_db_block_max_scan_pct 40 Percentage of buffers to inspect when looking for free
_db_block_med_priority_batch_size 0 Fraction of writes for medium priority reasons
_db_block_numa 1 Number of NUMA nodes
_db_block_prefetch_fast_longjumps_enabled TRUE Batched IO enable fast longjumps
_db_block_prefetch_limit 0 Prefetch limit in blocks
_db_block_prefetch_override 0 Prefetch force override in blocks
_db_block_prefetch_private_cache_enabled TRUE Batched IO enable private cache
_db_block_prefetch_quota 10 Prefetch quota as a percent of cache size
_db_block_prefetch_skip_reading_enabled TRUE Batched IO enable skip reading buffers
_db_block_table_scan_buffer_size 4194304 Size of shared table scan read buffer
_db_block_temp_redo FALSE generate redo for temp blocks
_db_block_trace_protect FALSE trace buffer protect calls
_db_block_vlm_check FALSE check of rvlm mapping leaks (for debugging)
_db_block_vlm_leak_threshold 3 Threshold for allowable vlm leaks
_db_blocks_per_hash_latch Number of blocks per hash latch
_db_cache_advice_batch_size 128 cache advisory simulation batch size
_db_cache_advice_sample_factor 4 cache advisory sampling factor
_db_cache_crx_check FALSE check for costly crx examination functions
_db_cache_pre_warm TRUE Buffer Cache Pre-Warm Enabled : hidden parameter
_db_cache_process_cr_pin_max maximum number of cr pins a process may have
_db_change_notification_enable TRUE enable db change notification
_db_disable_temp_encryption FALSE Diable Temp Encryption for Spills
_db_fast_obj_check FALSE enable fast object drop sanity check
_db_fast_obj_ckpt TRUE enable fast object checkpoint
_db_fast_obj_truncate TRUE enable fast object truncate
_db_file_direct_io_count 1048576 Sequential I/O buf size
_db_file_exec_read_count 128 multiblock read count for regular clients
_db_file_format_io_buffers 4 Block formatting I/O buf count
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be prefetched
_db_file_optimizer_read_count 8 multiblock read count for regular clients
_db_handles 750 System-wide simultaneous buffer operations
_db_handles_cached 5 Buffer handles cached each process
_db_hot_block_tracking FALSE track hot blocks for hash latch contention
_db_index_block_checking TRUE index block checking override parameter
_db_large_dirty_queue 25 Number of buffers which force dirty queue to be written
_db_lost_write_checking FALSE Enable scn based lost write detection mechanism
_db_mttr_advice ON MTTR advisory
_db_mttr_partitions 0 number of partitions for MTTR advisory
_db_mttr_sample_factor 64 MTTR simulation sampling factor
_db_mttr_sim_target MTTR simulation targets
_db_mttr_sim_trace_size 256 MTTR simulation trace size
_db_mttr_trace_to_alert FALSE dump trace entries to alert file
_db_noarch_disble_optim FALSE Image redo logging (NOARCHIVEMODE)
_db_num_evict_waitevents 64 number of evict wait events
_db_obj_enable_ksr TRUE enable ksr in object checkpoint/reuse
_db_percent_hot_default 50 Percent of default buffer pool considered hot
_db_percent_hot_keep 0 Percent of keep buffer pool considered hot
_db_percent_hot_recycle 0 Percent of recycle buffer pool considered hot
_db_percpu_create_cachesize 2 size of cache created per cpu in deferred cache create
_db_recovery_temporal_file_dest default database recovery temporal file location
_db_required_percent_fairshare_usage 10 percent of fairshare a processor group should always use
_db_row_overlap_checking TRUE row overlap checking override parameter for data/index blocks
_db_todefer_cache_create TRUE buffer cache deferred create
_db_writer_chunk_writes 0 Number of writes DBWR should wait for
_db_writer_coalesce_area_size 2293760 Size of memory allocated to dbwriter for coalescing writes
_db_writer_coalesce_write_limit 131072 Limit on size of coalesced write
_db_writer_flush_imu TRUE If FALSE, DBWR will not downgrade IMU txns for AGING
_db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs
_db_writer_max_writes 0 Max number of outstanding DB Writer IOs
_db_writer_nomemcopy_coalesce FALSE Enable DBWR no-memcopy coalescing
_db_writer_verify_writes FALSE Enable lost write detection mechanism
_dbg_proc_startup FALSE debug process startup
_dbms_sql_security_level 1 Security level in DBMS_SQL
_dbwr_async_io TRUE Enable dbwriter asynchronous writes
_dbwr_scan_interval 300 dbwriter scan interval
_dbwr_tracing 0 Enable dbwriter tracing
_dde_flood_control_init TRUE Initialize Flood Control at database open
_dead_process_scan_interval 60 PMON dead process scan interval
_deadlock_diagnostic_level 2 automatic deadlock resolution diagnostics level
_deadlock_resolution_incidents_enabled TRUE create incidents during deadlock resolution
_deadlock_resolution_level 1 automatic deadlock resolution level
_deadlock_resolution_min_wait_timeout_secs 60 the minimum wait timeout required for deadlock resolution
_deadlock_resolution_signal_process_thresh_secs 60 the amount of time given to process a deadlock resolution signal
_dedicated_server_poll_count 10 dedicated server poll count
_dedicated_server_post_wait FALSE dedicated server post/wait
_dedicated_server_post_wait_call FALSE dedicated server post/wait call
_default_encrypt_alg 0 default encryption algorithm
_default_non_equality_sel_check TRUE sanity check on default selectivity for like/range predicate
_defer_log_boundary_ckpt TRUE defer media recovery checkpoint at log boundary
_defer_log_count 2 Number of log boundaries media recovery checkpoint lags behind
_delay_index_maintain TRUE delays index maintenance until after MV is refreshed
_desired_readmem_rate 70 The desired percentage of redo reading from memory
_diag_adr_auto_purge TRUE Enable/disable ADR MMON Auto Purging
_diag_adr_enabled TRUE Parameter to enable/disable Diag ADR
_diag_adr_test_param 0 Test parameter for Diagnosability
_diag_backward_compat TRUE Backward Compatibility for Diagnosability
_diag_cc_enabled TRUE Parameter to enable/disable Diag Call Context
_diag_conf_cap_enabled TRUE Parameter to enable/disable Diag Configuration Capture
_diag_daemon TRUE start DIAG daemon
_diag_dde_async_age_limit 300 diag dde async actions: message age limit (in seconds)
_diag_dde_async_cputime_limit 300 diag dde async actions: action cputime limit (in seconds)
_diag_dde_async_mode 1 diag dde async actions: dispatch mode
_diag_dde_async_msg_capacity 1024 diag dde async actions: message buffer capacity
_diag_dde_async_msgs 50 diag dde async actions: number of preallocated message buffers
_diag_dde_async_process_rate 5 diag dde async actions: message processing rate – per loop
_diag_dde_async_runtime_limit 900 diag dde async actions: action runtime limit (in seconds)
_diag_dde_async_slaves 5 diag dde async actions: max number of concurrent slave processes
_diag_dde_enabled TRUE enable DDE handling of critical errors
_diag_dde_fc_enabled TRUE Parameter to enable/disable Diag Flood Control
_diag_dde_fc_implicit_time 0 Override Implicit Error Flood Control time parameter
_diag_dde_fc_macro_time 0 Override Macro Error Flood Control time parameter
_diag_dde_inc_proc_delay 1 The minimum delay between two MMON incident sweeps (minutes)
_diag_diagnostics TRUE Turn off diag diagnostics
_diag_dump_timeout 30 timeout parameter for SYNC dump
_diag_hm_rc_enabled TRUE Parameter to enable/disable Diag HM Reactive Checks
_diag_hm_tc_enabled FALSE Parameter to enable/disable Diag HM Test(dummy) Checks
_diag_uts_control 0 UTS control parameter
_dimension_skip_null TRUE control dimension skip when null feature
_direct_path_insert_features 0 disable direct path insert features
_disable_12751 FALSE disable policy timeout error (ORA-12751)
_disable_adaptive_shrunk_aggregation FALSE adaptive shrunk aggregation
_disable_autotune_gtx FALSE disable autotune global transaction background processes
_disable_datalayer_sampling FALSE disable datalayer sampling
_disable_duplex_link TRUE Turn off connection duplexing
_disable_fast_aggregation FALSE fast aggregation
_disable_fast_validate FALSE disable PL/SQL fast validation
_disable_fastopen FALSE Do Not Use Fastopen
_disable_fba_qrw 0 disable flashback archiver query rewrite
_disable_fba_wpr 0 disable flashback archiver wait for prepared transactions
_disable_file_locks FALSE disable file locks for control, data, redo log files
_disable_flashback_archiver 0 disable flashback archiver
_disable_flashback_wait_callback FALSE Disable flashback wait callback
_disable_function_based_index FALSE disable function-based index matching
_disable_health_check FALSE Disable Health Check
_disable_highres_ticks FALSE disable high-res tick counter
_disable_image_check FALSE Disable Oracle executable image checking
_disable_incremental_checkpoints FALSE Disable incremental checkpoints for thread recovery
_disable_incremental_recovery_ckpt FALSE Disable incremental recovery checkpoint mechanism
_disable_index_block_prefetching FALSE disable index block prefetching
_disable_instance_params_check FALSE disable instance type check for ksp
_disable_interface_checking FALSE disable interface checking at startup
_disable_kcb_flashback_blocknew_opt FALSE Disable KCB flashback block new optimization
_disable_kcbhxor_osd FALSE disable kcbh(c)xor OSD functionality
_disable_kcbl_flashback_blocknew_opt FALSE Disable KCBL flashback block new optimization
_disable_latch_free_SCN_writes_via_32cas FALSE disable latch-free SCN writes using 32-bit compare & swap
_disable_latch_free_SCN_writes_via_64cas FALSE disable latch-free SCN writes using 64-bit compare & swap
_disable_logging FALSE Disable logging
_disable_metrics_group 0 Disable Metrics Group (or all Metrics Groups)
_disable_multiple_block_sizes FALSE disable multiple block size support (for debugging)
_disable_odm FALSE disable odm feature
_disable_parallel_conventional_load FALSE Disable parallel conventional loads
_disable_read_only_open_dict_check FALSE Disable read-only open dictionary check
_disable_rebalance_space_check FALSE disable space usage checks for storage reconfiguration
_disable_recoverable_recovery FALSE Disable the new recoverable recovery mechanism
_disable_recovery_read_skip FALSE Disable the read optimization during media recovery
_disable_sample_io_optim FALSE disable row sampling IO optimization
_disable_savepoint_reset FALSE disable the fix for bug 1402161
_disable_sec_ticks FALSE disable low-res (sec) counter
_disable_selftune_checkpointing FALSE Disable self-tune checkpointing
_disable_streams_pool_auto_tuning FALSE disable streams pool auto tuning
_disable_sun_rsm TRUE Disable IPC OSD support for Sun RSMAPI
_disable_system_state 4294967294 disable system state dump
_disable_thread_internal_disable FALSE Disable thread internal disable feature
_disable_txn_alert 0 disable txn layer alert
_disable_vktm FALSE disable vktm process
_disable_wait_stack FALSE Disable wait stack
_discrete_transactions_enabled FALSE enable OLTP mode
_diskmon_pipe_name DiSKMon skgznp pipe name
_dispatcher_rate_scale scale to display rate statistic (100ths of a second)
_dispatcher_rate_ttl time-to-live for rate statistic (100ths of a second)
_distinct_view_unnesting FALSE enables unnesting of in subquery into distinct view
_distributed_recovery_connection_hold_time 200 number of seconds RECO holds outbound connections open
_dlmtrace Trace string of global enqueue type(s)
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use for a mining model
_dml_monitoring_enabled TRUE enable modification monitoring
_domain_index_batch_size 2000 maximum number of rows from one call to domain index fetch routine
_domain_index_dml_batch_size 200 maximum number of rows for one call to domain index dml routines
_dra_bmr_number_threshold 1000 Maximum number of BMRs that can be done to a file
_dra_bmr_percent_threshold 10 Maximum percentage of blocks in a file that can be BMR-ed
_dra_enable_offline_dictionary TRUE Enable the periodic creation of the offline dictionary for DRA
_drop_table_granule 256 drop_table_granule
_drop_table_optimization_enabled TRUE reduce SGA memory use during drop of a partitioned table
_ds_iocount_iosize 6553664 Dynamic Sampling Service defaults: #IOs and IO Size
_dss_cache_flush FALSE enable full cache flush for parallel execution
_dtree_area_size 131072 size of Decision Tree Classification work area
_dtree_binning_enabled TRUE Decision Tree Binning Enabled
_dtree_bintest_id 0 Decision Tree Binning Test ID
_dtree_compressbmp_enabled TRUE Decision Tree Using Compressed Bitmaps Enabled
_dtree_max_surrogates 1 maximum number of surrogates
_dtree_pruning_enabled TRUE Decision Tree Pruning Enabled
_dummy_instance FALSE dummy instance started by RMAN
_dump_common_subexpressions FALSE dump common subexpressions
_dump_connect_by_loop_data FALSE dump connect by loop error message into trc file
_dump_cursor_heap_sizes FALSE dump comp/exec heap sizes to tryace file
_dump_interval_limit 120 trace dump time interval limit (in seconds)
_dump_max_limit 5 max number of dump within dump interval
_dump_qbc_tree 0 dump top level query parse tree to trace
_dump_rcvr_ipc TRUE if TRUE enables IPC dump at instance eviction time
_dump_system_state_scope local scope of sysstate dump during instance termination
_dump_trace_scope global scope of trace dump during a process crash
_dynamic_rls_policies TRUE rls policies are dynamic
_dynamic_stats_threshold 30 delay threshold (in seconds) between sending statistics messages
_edition_based_redefinition FALSE enable edition based redefinition
_eliminate_common_subexpr TRUE enables elimination of common sub-expressions
_emon_regular_ntfn_slaves 4 number of EMON slaves doing regular database notifications
_enable_NUMA_optimization TRUE Enable NUMA specific optimizations
_enable_automatic_maintenance 1 if 1, Automated Maintenance Is Enabled
_enable_automatic_sqltune TRUE Automatic SQL Tuning Advisory enabled parameter
_enable_block_level_transaction_recovery TRUE enable block level recovery
_enable_cscn_caching FALSE enable commit SCN caching for all transactions
_enable_ddl_wait_lock TRUE use this to turn off ddls with wait semantics
_enable_default_affinity 0 to enable default implementation of affinity osds
_enable_dml_lock_escalation TRUE enable dml lock escalation against partitioned tables if TRUE
_enable_exchange_validation_using_check TRUE use check constraints on the table for validation
_enable_fast_ref_after_mv_tbs FALSE enable fast refresh after move tablespace
_enable_hash_overflow FALSE TRUE – enable hash cluster overflow based on SIZE
_enable_hwm_sync TRUE enable HWM synchronization
_enable_list_io FALSE Enable List I/O
_enable_midtier_affinity TRUE enable midtier affinity metrics processing
_enable_nativenet_tcpip FALSE Enable skgxp driver usage for native net
_enable_obj_queues TRUE enable object queues
_enable_online_index_without_s_locking TRUE Allow online index creation algorithm without S DML lock
_enable_query_rewrite_on_remote_objs TRUE mv rewrite on remote table/view
_enable_refresh_schedule TRUE enable or disable MV refresh scheduling (revert to 9.2 behavior)
_enable_reliable_latch_waits TRUE Enable reliable latch waits
_enable_rlb TRUE enable RLB metrics processing
_enable_row_shipping TRUE use the row shipping optimization for wide table selects
_enable_scn_wait_interface TRUE use this to turn off scn wait interface in kta
_enable_separable_transactions FALSE enable/disable separable transactions
_enable_shared_pool_durations TRUE temporary to disable/enable kgh policy
_enable_shared_server_vector_io FALSE Enable shared server vector I/O
_enable_space_preallocation 3 enable space pre-allocation
_enable_spacebg TRUE enable space management background task
_enable_tablespace_alerts TRUE enable tablespace alerts
_enable_type_dep_selectivity TRUE enable type dependent selectivity estimates
_endprot_chunk_comment chk 10235 dflt chunk comment for selective overrun protection
_endprot_heap_comment hp 10235 dflt heap comment for selective overrun protection
_endprot_subheaps TRUE selective overrun protection for subeheaps
_enqueue_deadlock_time_sec 5 requests with timeout <= this will not have deadlock detection
_enqueue_debug_multi_instance FALSE debug enqueue multi instance
_enqueue_hash 375 enqueue hash table length
_enqueue_hash_chain_latches 1 enqueue hash chain latches
_enqueue_locks 2260 locks for managed enqueues
_enqueue_resources 968 resources for enqueues
_evolve_plan_baseline_report_level TYPICAL Level of detail to show in plan verification/evolution report
_evt_system_event_propagation TRUE disable system event propagation
_expand_aggregates TRUE expand aggregates
_explain_rewrite_mode FALSE allow additional messages to be generated during explain rewrite
_extended_pruning_enabled TRUE do runtime pruning in iterator if set to TRUE
_fair_remote_cvt FALSE if TRUE enables fair remote convert
_fairness_threshold 4 number of times to CR serve before downgrading lock
_fast_dual_enabled TRUE enable/disable fast dual
_fast_full_scan_enabled TRUE enable/disable index fast full scan
_fastpin_enable 1 enable reference count based fast pins
_fbda_busy_percentage 0 flashback archiver busy percentage
_fbda_inline2_percentage 0 flashback archiver full inline percentage
_fbda_inline_percentage 0 flashback archiver inline percentage
_fg_iorm_slaves 1 ForeGround I/O slaves for IORM
_fg_log_checksum TRUE Checksum redo in foreground process
_fic_algorithm_set automatic Set Frequent Itemset Counting Algorithm
_fic_area_size 131072 size of Frequent Itemset Counting work area
_fic_max_length 20 Frequent Itemset Counting Maximum Itemset Length
_fic_min_bmsize 1024 Frequent Itemset Counting Minimum BITMAP Size
_fic_outofmem_candidates FALSE Frequent Itemset Counting Out Of Memory Candidates Generation
_fifth_spare_parameter fifth spare parameter – string
_filemap_dir FILEMAP directory
_first_k_rows_dynamic_proration TRUE enable the use of dynamic proration of join cardinalities
_first_spare_parameter first spare parameter – integer
_fix_control bug fix control parameter
_flashback_allow_noarchivelog FALSE Allow enabling flashback on noarchivelog database
_flashback_archiver_partition_size 0 flashback archiver table partition size
_flashback_barrier_interval 1800 Flashback barrier interval in seconds
_flashback_copy_latches 10 Number of flashback copy latches
_flashback_format_chunk_mb 4 Chunk mega-bytes for formatting flashback logs using sync write
_flashback_format_chunk_mb_dwrite 16 Chunk mega-bytes for formatting flashback logs using delayed write
_flashback_fuzzy_barrier TRUE Use flashback fuzzy barrier
_flashback_generation_buffer_size 4194304 flashback generation buffer size
_flashback_hint_barrier_percent 20 Flashback hint barrier percent
_flashback_log_io_error_behavior 0 Specify Flashback log I/O error behavior
_flashback_log_min_size 100 Minimum flashback log size
_flashback_log_size 1000 Flashback log size
_flashback_logfile_enqueue_timeout 600 flashback logfile enqueue timeout for opens
_flashback_marker_cache_enabled TRUE Enable flashback database marker cache
_flashback_marker_cache_size 328 Size of flashback database marker cache
_flashback_max_log_size 0 Maximum flashback log size in bytes (OS limit)
_flashback_max_n_log_per_thread 2048 Maximum number of flashback logs per flashback thread
_flashback_max_standby_sync_span 900 Maximum time span between standby recovery sync for flashback
_flashback_n_log_per_thread 128 Desired number of flashback logs per flashback thread
_flashback_standby_barrier_interval 1800 Flashback standby barrier interval in seconds
_flashback_verbose_info FALSE Print verbose information about flashback database
_flashback_write_max_loop_limit 10 Flashback writer loop limit before it returns
_force_datefold_trunc FALSE force use of trunc for datefolding rewrite
_force_hsc_compress FALSE compress all new created tables
_force_oltp_compress FALSE OLTP Compress all new created tables
_force_rewrite_enable FALSE control new query rewrite features
_force_slave_mapping_intra_part_loads FALSE Force slave mapping for intra partition loads
_force_temptables_for_gsets FALSE executes concatenation of rollups using temp tables
_force_tmp_segment_loads FALSE Force tmp segment loads
_forwarded_2pc_threshold 10 auto-tune threshold for two-phase commit rate across RAC instances
_fourth_spare_parameter fourth spare parameter – string
_full_pwise_join_enabled TRUE enable full partition-wise join when TRUE
_gby_hash_aggregation_enabled TRUE enable group-by and aggregation using hash scheme
_gby_onekey_enabled TRUE enable use of one comparison of all group by keys
_gc_affinity_locking TRUE if TRUE, enable object affinity
_gc_affinity_locks TRUE if TRUE, get affinity locks
_gc_affinity_ratio 50 dynamic object affinity ratio
_gc_async_memcpy FALSE if TRUE, use async memcpy
_gc_bypass_readers TRUE if TRUE, modifications bypass readers
_gc_check_bscn TRUE if TRUE, check for stale blocks
_gc_coalesce_recovery_reads TRUE if TRUE, coalesce recovery reads
_gc_defer_time 3 how long to defer down converts for hot buffers
_gc_dissolve_undo_affinity FALSE if TRUE, dissolve undo affinity after an offline
_gc_element_percent 110 global cache element percent
_gc_global_checkpoint_scn TRUE if TRUE, enable global checkpoint scn
_gc_global_lru AUTO turn global lru off, make it automatic, or turn it on
_gc_initiate_undo_affinity TRUE if TRUE, initiate undo affinity after an online
_gc_integrity_checks 1 set the integrity check level
_gc_keep_recovery_buffers TRUE if TRUE, make recovery buffers current
_gc_latches 8 number of latches per LMS process
_gc_long_query_threshold 0 threshold for long running query
_gc_maximum_bids 0 maximum number of bids which can be prepared
_gc_policy_minimum 1500 dynamic object policy minimum activity per minute
_gc_policy_time 10 how often to make object policy decisions in minutes
_gc_read_mostly_locking TRUE if TRUE, enable read-mostly locking
_gc_statistics TRUE if TRUE, kcl statistics are maintained
_gc_transfer_ratio 2 dynamic object read-mostly transfer ratio
_gc_undo_affinity TRUE if TRUE, enable dynamic undo affinity
_gc_undo_block_disk_reads TRUE if TRUE, enable undo block disk reads
_gc_use_cr TRUE if TRUE, allow CR pins on PI and WRITING buffers
_gc_vector_read TRUE if TRUE, vector read current buffers
_gc_voluntary_down_convert_obj 0 object number for voluntary down-convert
_gcs_fast_reconfig TRUE if TRUE, enable fast reconfiguration for gcs locks
_gcs_latches 0 number of gcs resource hash latches to be allocated per LMS process
_gcs_process_in_recovery TRUE if TRUE, process gcs requests during instance recovery
_gcs_res_per_bucket 16 number of gcs resource per hash bucket
_gcs_resources number of gcs resources to be allocated
_gcs_shadow_locks number of pcm shadow locks to be allocated
_gcs_testing 0 GCS testing parameter
_generalized_pruning_enabled TRUE controls extensions to partition pruning for general predicates
_ges_dd_debug 1 if 1 or higher enables GES deadlock detection debug diagnostics
_ges_diagnostics TRUE if TRUE enables GES diagnostics
_ges_health_check 1 if greater than 0 enables GES system health check
_global_hang_analysis_interval_secs 10 the interval at which global hang analysis is run
_globalindex_pnum_filter_enabled TRUE enables filter for global index with partition extended syntax
_groupby_nopushdown_cut_ratio 3 groupby nopushdown cut ratio
_groupby_orderby_combine 5000 groupby/orderby don’t combine threshold
_gs_anti_semi_join_allowed TRUE enable anti/semi join for the GS query
_hang_analysis_num_call_stacks 3 hang analysis num call stacks
_hang_detection 60 Hang Management detection interval
_hang_resolution FALSE Hang Management hang resolution
_hard_protection FALSE if TRUE enable H.A.R.D specific format changes
_hash_join_enabled TRUE enable/disable hash join
_hash_multiblock_io_count 0 number of blocks hash join will read/write at once
_heur_deadlock_resolution_secs 0 the heuristic wait time per node for deadlock resolution
_high_priority_processes LMS*|VKTM High Priority Process Name Mask
_high_server_threshold 0 high server thresholds
_hj_bit_filter_threshold 50 hash-join bit filtering threshold (0 always enabled)
_hm_analysis_oradebug_node_dump_level 0 the oradebug node dump level for hang manager hang analysis
_hm_analysis_oradebug_sys_dump_level 0 the oradebug system state level for hang manager hang analysis
_hm_analysis_output_disk FALSE if TRUE the hang manager outputs hang analysis results to disk
_hm_log_incidents FALSE Hang Manager incident logging
_hm_verification_interval 60 the hang manager verification interval
_hwm_sync_threshold 10 HWM synchronization threshold in percentage
_idl_conventional_index_maintenance TRUE enable conventional index maintenance for insert direct load
_idle_session_kill_enabled TRUE enables or disables resource manager session idle limit checks
_idxrb_rowincr 100000000 proportionality constant for dop vs. rows in index rebuild
_ignore_desc_in_index FALSE ignore DESC in indexes, sort those columns ascending anyhow
_ignore_fg_deps ignore fine-grain dependencies during invalidation
_immediate_commit_propagation TRUE if TRUE, propagate commit SCN immediately
_improved_outerjoin_card TRUE improved outer-join cardinality calculation
_improved_row_length_enabled TRUE enable the improvements for computing the average row length
_imr_active TRUE Activate Instance Membership Recovery feature
_imr_avoid_double_voting TRUE Avoid device voting for CSS reconfig during IMR
_imr_device_type controlfile Type of device to be used by IMR
_imr_disk_voting_interval 3 Maximum wait for IMR disk voting (seconds)
_imr_evicted_member_kill TRUE IMR issue evicted member kill after a wait
_imr_evicted_member_kill_wait 20 IMR evicted member kill wait time in seconds
_imr_highload_threshold IMR system highload threshold
_imr_max_reconfig_delay 300 Maximum Reconfiguration delay (seconds)
_imr_splitbrain_res_wait 600 Maximum wait for split-brain resolution (seconds)
_imr_systemload_check TRUE Perform the system load check during IMR
_imu_pools 3 in memory undo pools
_in_memory_undo TRUE Make in memory undo for top level transactions
_incremental_recovery_ckpt_min_batch 30 Minimum number of writes for incremental recovery ckpt every 3 sec
_index_join_enabled TRUE enable the use of index joins
_index_prefetch_factor 100 index prefetching factor
_init_granule_interval 10 number of granules to process for deferred cache
_init_sql_file ?/rdbms/admin/sql.bsq File containing SQL statements to execute upon database creation
_inject_startup_fault 0 inject fault in the startup code
_inline_sql_in_plsql FALSE inline SQL in PL/SQL
_insert_enable_hwm_brokered TRUE during parallel inserts high water marks are brokered
_inst_locking_period 5 period an instance can retain a newly acquired level1 bitmap
_interconnect_checksum TRUE if TRUE, checksum interconnect blocks
_intrapart_pdml_enabled TRUE Enable intra-partition updates/deletes
_intrapart_pdml_randomlocal_enabled TRUE Enable intra-partition updates/deletes with random local dist
_io_resource_manager_always_on FALSE io resource manager always on
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_io_slaves_disabled FALSE Do not use I/O slaves
_io_statistics TRUE if TRUE, ksfd I/O statistics are collected
_ioq_fanin_multiplier 2 IOQ miss count before a miss exception
_ior_serialize_fault 0 inject fault in the ior serialize code
_iorm_tout 1000 IORM scheduler timeout value in msec
_ioslave_batch_count 1 Per attempt IOs picked
_ioslave_issue_count 500 IOs issued before completion check
_ipc_fail_network 0 Simulate cluster network failer
_ipc_test_failover 0 Test transparent cluster network failover
_ipc_test_mult_nets 0 simulate multiple cluster networks
_job_queue_interval 5 Wakeup interval in seconds for job queue co-ordinator
_k2q_latches 0 number of k2q latches
_kcfis_fault_control 0 trace level setting for KCFIS module
_kcfis_max_appliances 40 Max. appliances in a KCFIS session
_kcfis_oss_io_size 0 KCFIS OSS I/O size
_kcfis_read_buffer_limit 0 KCFIS Read Buffer (per session) memory limit in bytes
_kcfis_trace_level 0 trace level setting for KCFIS module
_kcfis_work_set_appliances 6 Working Set of appliances in a KCFIS session
_kcl_commit TRUE if TRUE, call kjbcommit
_kcl_conservative_log_flush FALSE if TRUE, conservatively log flush before CR serving
_kcl_debug TRUE if TRUE, record le history
_kcl_index_split TRUE if TRUE, reject pings on blocks in middle of a split
_kdbl_enable_post_allocation FALSE allocate dbas after populating data buffers
_kdi_avoid_block_checking FALSE avoid index block checking on sensitive opcodes
_kdli_STOP_bsz 0 undocumented parameter for internal use only
_kdli_STOP_dba 0 undocumented parameter for internal use only
_kdli_STOP_fsz 0 undocumented parameter for internal use only
_kdli_STOP_nio 0 undocumented parameter for internal use only
_kdli_STOP_tsn 0 undocumented parameter for internal use only
_kdli_allow_corrupt TRUE allow corrupt filesystem_logging data blocks during read/write
_kdli_cache_inode TRUE cache inode state across calls
_kdli_cache_read_threshold 0 minimum lob size for cache->nocache read (0 disables heuristic)
_kdli_cache_size 8 maximum #entries in inode cache
_kdli_cache_verify FALSE verify cached inode via deserialization
_kdli_cache_write_threshold 0 minimum lob size for cache->nocache write (0 disables heuristic)
_kdli_cacheable_length 0 minimum lob length for inode cacheability
_kdli_force_cr TRUE force CR when reading data blocks of direct-write lobs
_kdli_force_cr_meta TRUE force CR when reading metadata blocks of direct-write lobs
_kdli_force_storage none force storage settings for all lobs
_kdli_full_readahead_threshold 0 maximum lob size for full readahead
_kdli_inject_assert 0 inject asserts into the inode
_kdli_inject_crash 0 inject crashes into the inode
_kdli_inline_xfm TRUE allow inline transformed lobs
_kdli_inode_preference data inline inode evolution preference (data, headless, lhb)
_kdli_itree_entries 0 #entries in lhb/itree blocks (for testing only)
_kdli_memory_protect FALSE trace accesses to inode memory outside kdli API functions
_kdli_rci_lobmap_entries 255 #entries in RCI lobmap before migration to lhb
_kdli_readahead_limit 0 shared/cached IO readahead limit
_kdli_readahead_strategy contig shared/cached IO readahead strategy
_kdli_recent_scn FALSE use recent (not dependent) scns for block format/allocation
_kdli_reshape FALSE reshape an inode to inline or headless on length truncation
_kdli_safe_callbacks TRUE invoke inode read/write callbacks safely
_kdli_sio_async TRUE asynchronous shared IO
_kdli_sio_backoff FALSE use exponential backoff when attempting SIOP allocations
_kdli_sio_bps 0 maximum blocks per IO slot
_kdli_sio_dop 2 degree-of-parallelism in the SIO keep pool
_kdli_sio_fgio TRUE reap asynchronous IO in the foreground
_kdli_sio_fileopen nodsync shared IO fileopen mode: datasync vs nodatasync vs async
_kdli_sio_flush FALSE enable shared IO pool operations
_kdli_sio_free TRUE free IO buffers when not in active use
_kdli_sio_min_read 0 shared IO pool read threshold
_kdli_sio_min_write 0 shared IO pool write threshold
_kdli_sio_nbufs 8 maximum #IO buffers to allocate per session
_kdli_sio_niods 8 maximum #IO descriptors to allocate per session
_kdli_sio_on TRUE enable shared IO pool operations
_kdli_sio_pga FALSE use PGA allocations for direct IO
_kdli_sio_pga_top FALSE PGA allocations come from toplevel PGA heap
_kdli_sio_strategy auto shared IO strategy: block vs. extent
_kdli_small_cache_limit 32 size limit of small inode cache
_kdli_sort_dbas FALSE sort dbas during chunkification
_kdli_space_cache_limit 2048 maximum size of the space cache in #blocks
_kdli_squeeze TRUE compact lobmap extents with contiguous dbas
_kdli_timer_dmp FALSE dump inode timers on session termination
_kdli_timer_trc FALSE trace inode timers to uts/tracefile
_kdli_trace 0 inode trace level
_kdlu_max_bucket_size 4194304 UTS kdlu bucket size
_kdlu_max_bucket_size_mts 131072 UTS kdlu bucket size for mts
_kdlu_trace_layer 0 UTS kdlu per-layer trace level
_kdlu_trace_session 0 UTS session dump
_kdlu_trace_system 0 UTS system dump
_kdlw_enable_ksi_locking FALSE enable ksi locking for lobs
_kdlw_enable_write_gathering TRUE enable lob write gathering for sql txns
_kdlwp_flush_threshold 4194304 WGC flush threshold in bytes
_kdlxp_cmp_subunit_size 262144 size of compression sub-unit in bytes
_kdlxp_dedup_flush_threshold 8388608 deduplication flush threshold in bytes
_kdlxp_dedup_hash_algo SHA1 secure hash algorithm for deduplication – only on SecureFiles
_kdlxp_dedup_prefix_threshold 1048576 deduplication prefix hash threshold in bytes
_kdlxp_lobcompress FALSE enable lob compression – only on SecureFiles
_kdlxp_lobdeduplicate FALSE enable lob deduplication – only on SecureFiles
_kdlxp_lobdedupvalidate TRUE enable deduplicate validate – only on SecureFiles
_kdlxp_lobencrypt FALSE enable lob encryption – only on SecureFiles
_kdlxp_min_xfm_size 32768 minimum transformation size in bytes
_kdlxp_mincmp 20 minimum comp ratio in pct – only on SecureFiles
_kdlxp_spare1 0 deduplication spare 1
_kdlxp_xfmcache TRUE enable xfm cache – only on SecureFiles
_kdt_buffering TRUE control kdt buffering for conventional inserts
_kebm_nstrikes 3 kebm # strikes to auto suspend an action
_kebm_suspension_time 82800 kebm auto suspension time in seconds
_keep_remote_column_size FALSE remote column size does not get modified
_kernel_message_network_driver FALSE kernel message network driver
_kffmap_hash_size 1024 size of kffmap_hash table
_kffmop_hash_size 2048 size of kffmop_hash table
_kfm_disable_set_fence FALSE disable set fence calls and revert to default (process fence)
_kghdsidx_count 1 max kghdsidx count
_kgl_bucket_count 9 index to the bucket count array
_kgl_debug Library cache debugging
_kgl_features_enabled 2 Library cache features enabled bits
_kgl_fixed_extents TRUE fixed extent size for library cache memory allocations
_kgl_hash_collision FALSE whether KGL hash collision is possible
_kgl_heap_size 1024 extent size for library cache heap 0
_kgl_kqr_cap_so_stacks FALSE capture stacks for library and row cache state objects
_kgl_large_heap_warning_threshold 52428800 maximum heap size before KGL writes warnings to the alert log
_kgl_latch_count 0 number of library cache latches
_kgl_message_locks 64 RAC message lock count
_kgl_min_cached_so_count 1 Minimum cached SO count. If > 1 can help find SO corruptions
_kgl_multi_instance_invalidation TRUE whether KGL to support multi-instance invalidations
_kgl_multi_instance_lock TRUE whether KGL to support multi-instance locks
_kgl_multi_instance_pin TRUE whether KGL to support multi-instance pins
_kgl_so_stack_depth 16 The depth of the stack capture for SO get or free
_kgl_time_to_wait_for_locks 15 time to wait for locks and pins before timing out
_kglsim_maxmem_percent 5 max percentage of shared pool size to be used for KGL advice
_kgsb_threshold_size 16777216 threshold size for base allocator
_kgx_latches 1024 # of mutex latches if CAS is not supported.
_kgx_spin_count 255 third spare parameter – integer
_kill_controlfile_enqueue_blocker TRUE enable killing controlfile enqueue blocker on timeout
_kill_diagnostics_timeout 60 timeout delay in seconds before killing enqueue blocker
_kill_enqueue_blocker 2 if greater than 0 enables killing enqueue blocker
_kill_java_threads_on_eoc FALSE Kill Java threads and do sessionspace migration at end of call
_kill_session_dump TRUE Process dump on kill session immediate
_kkdlgon_max_iter 20000 kkdlgon maximum number of iterations
_kkfi_trace FALSE trace expression substitution
_kks_free_cursor_stat_pct 10 percentage of cursor stats buckets to scan on each load, in 1/10th of a percent
_kks_use_mutex_pin TRUE Turning on this will make KKS use mutex for cursor pins.
_kokli_cache_size 32 Size limit of Materialized View and Object Table Lookup Cache
_kolfuseslf FALSE allow kolf to use slffopen
_kql_subheap_trace 0 tracing level for library cache subheap level pins
_ksb_disable_diagpid FALSE disable the call to ksb_diagpid
_ksb_restart_clean_time 30000 process uptime for restarts
_ksb_restart_policy_times 0, 60, 120, 240 process restart policy times in seconds
_ksd_test_param 999 KSD test parmeter
_ksdxdocmd_default_timeout_ms 30000 default timeout for internal oradebug commands
_ksdxdocmd_enabled TRUE if TRUE ksdxdocmd* invocations are enabled
_ksdxw_cini_flg 0 ksdxw context initialization flag
_ksdxw_nbufs 1000 ksdxw number of buffers in buffered mode
_ksdxw_num_pgw 10 number of watchpoints on a per-process basis
_ksdxw_num_sgw 10 number of watchpoints to be shared by all processes
_ksdxw_stack_depth 4 number of PCs to collect in the stack when watchpoint is hit
_kse_die_timeout 60000 amount of time a dying process is spared by PMON (in centi-secs)
_kse_pc_table_size 256 kse pc table cache size
_kse_signature_entries 0 number of entries in the kse stack signature cache
_kse_signature_limit 7 number of stack frames to cache per kse signature
_kse_snap_ring_size 0 ring buffer to debug internal error 17090
_ksfd_verify_write FALSE verify asynchronous writes issued through ksfd
_ksi_trace KSI trace string of lock type(s)
_ksi_trace_bucket PRIVATE memory tracing: use ksi-private or rdbms-shared bucket
_ksi_trace_bucket_size 8192 size of the KSI trace bucket
_ksmb_debug 0 ksmb debug flags
_ksmd_protect_mode off KSMD protect mode for catching stale access
_ksmg_granule_locking_status 1 granule locking status
_ksmg_granule_size 4194304 granule size in bytes
_ksmg_lock_check_interval timeout action interval in minutes
_ksmg_lock_reacquire_count 5 repeat count for acquisition of locks
_kspol_tac_timeout 5 timeouts for TAC registerd by kspol
_ksr_unit_test_processes 0 number of ksr unit test processes
_kss_quiet FALSE if TRUE access violations during kss dumps are not recorded
_ksu_diag_kill_time 5 number of seconds ksuitm waits before killing diag
_ksuitm_addon_trccmd command to execute when dead processes don’t go away
_ksuitm_dont_kill_dumper FALSE delay inst. termination to allow processes to dump
_ksv_max_spawn_fail_limit 5 bg slave spawn failure limit
_ksv_pool_hang_kill_to 0 bg slave pool terminate timeout
_ksv_pool_wait_timeout 600 bg slave pool wait limit
_ksv_spawn_control_all FALSE control all spawning of background slaves
_ksvppktmode 0 ksv internal pkt test
_ksxp_diagmode OFF set to OFF to disable automatic slowsend diagnostics
_ksxp_disable_dynamic_loading FALSE disable dynamic loadin of lib skgxp
_ksxp_disable_ipc_stats FALSE disable ipc statistics
_ksxp_init_stats_bkts 0 initial number arrays for ipc statistics
_ksxp_max_stats_bkts 0 max. arrays for ipc statistics
_ksxp_ping_enable TRUE disable dynamic loadin of lib skgxp
_ksxp_ping_polling_time 0 max. arrays for ipc statistics
_ksxp_reaping 20 tune ksxp layer reaping limit
_ksxp_reporting_process LMD0 reporting process for KSXP
_ksxp_send_timeout 300 set timeout for sends queued with the inter-instance IPC
_ksxp_skgxp_compat_library_path over-ride default location of lib skgxp compat
_ksxp_skgxp_library_path over-ride default location of lib skgxp
_ksxp_stats_mem_lmt 0 limit ipc statistics memory. this parameter is a percentage value
_ksxp_testing 0 KSXP test parameter
_ksxp_unit_test_byte_transformation FALSE enable byte transformation unit test
_ktc_debug 0 for ktc debug
_ktc_latches 0 number of ktc latches
_ktslj_segext_warning 10 segment pre-extension warning threshold in percentage
_ktspsrch_maxsc 32 maximum segments supported by space search cache
_kttext_warning 5 tablespace pre-extension warning threshold in percentage
_ktu_latches 0 number of KTU latches
_ku_trace none datapump trace parameter
_large_pool_min_alloc 16000 minimum allocation size in bytes for the large allocation pool
_last_allocation_period 5 period over which an instance can retain an active level1 bitmap
_latch_class_0 latch class 0
_latch_class_1 latch class 1
_latch_class_2 latch class 2
_latch_class_3 latch class 3
_latch_class_4 latch class 4
_latch_class_5 latch class 5
_latch_class_6 latch class 6
_latch_class_7 latch class 7
_latch_classes latch classes override
_latch_miss_stat_sid 0 Sid of process for which to collect latch stats
_latch_recovery_alignment 65534 align latch recovery structures
_ldr_io_size 262144 size of write IOs used during a load operation
_left_nested_loops_random TRUE enable random distribution method for left of nestedloops
_lgwr_async_broadcasts TRUE LGWR Asynchronous Broadcasts enabling boolean flag
_lgwr_delay_write FALSE LGWR write delay for debugging
_lgwr_io_slaves 0 LGWR I/O slaves
_lgwr_max_ns_wt 1 Maximum wait time for lgwr to allow NetServer to progress
_lgwr_ns_nl_max 1000 Variable to simulate network latency or buffer threshold
_lgwr_ns_nl_min 500 Variable to simulate network latency or buffer threshold
_lgwr_ns_sim_err 0 Variable to simulate errors lgwrns
_lgwr_ta_sim_err 0 Variable to simulate errors lgwr true async
_library_cache_advice TRUE whether KGL advice should be turned on
_lightweight_hdrs TRUE Lightweight headers for redo
_like_with_bind_as_equality FALSE treat LIKE predicate with bind as an equality predicate
_lm_activate_lms_threshold 100 threshold value to activate an additional lms
_lm_asm_enq_hashing TRUE if TRUE makes ASM use enqueue master hashing for fusion locks
_lm_better_ddvictim TRUE GES better deadlock victim
_lm_cache_lvl0_cleanup 0 how often to cleanup level 0 cache res (in sec)
_lm_cache_res_cleanup 25 percentage of cached resources should be cleanup
_lm_cache_res_type TMHWDI cache resource: string of lock types(s)
_lm_checksum_batch_msg 0 GES checksum batch messages
_lm_contiguous_res_count 128 number of contiguous blocks that will hash to the same HV bucket
_lm_dd_interval 10 dd time interval in seconds
_lm_dd_max_search_time 180 max dd search time per token
_lm_dd_scan_interval 5 dd scan interval in seconds
_lm_dd_search_cnt 3 number of dd search per token get
_lm_drm_max_requests 100 dynamic remastering maximum affinity requests processed together
_lm_drm_window 0 dynamic remastering bucket window size
_lm_drm_xlatch 0 dynamic remastering forced exclusive latches
_lm_dynamic_lms FALSE dynamic lms invocation
_lm_dynamic_load TRUE dynamic load adjustment
_lm_enq_lock_freelist Number of ges enqueue element freelist
_lm_enq_rcfg TRUE if TRUE enables enqueue reconfiguration
_lm_enqueue_freelist 3 Number of enqueue freelist
_lm_file_affinity mapping between file id and master instance number
_lm_global_posts TRUE if TRUE deliver global posts to remote nodes
_lm_idle_connection_check TRUE GES idle connection check
_lm_idle_connection_check_interval 10 GES idle connection check interval time
_lm_idle_connection_kill FALSE GES idle connection kill
_lm_lmd_waittime 8 default wait time for lmd in centiseconds
_lm_lmon_nowait_latch TRUE if TRUE makes lmon get nowait latches with timeout loop
_lm_lms 0 number of background gcs server processes to start
_lm_lms_waittime 8 default wait time for lms in centiseconds
_lm_locks 12000 number of enqueues configured for cluster database
_lm_master_weight 1 master resource weight for this instance
_lm_max_lms 0 max. number of background global cache server processes
_lm_msg_batch_size 0 GES batch message size
_lm_msg_cleanup_interval 3000 GES message buffer cleanup interval time
_lm_node_join_opt FALSE cluster database node join optimization in reconfig
_lm_non_fault_tolerant FALSE disable cluster database fault-tolerance mode
_lm_num_pt_buckets 8192 number of buckets in the object affinity hash table
_lm_num_pt_latches 128 number of latches in the object affinity hash table
_lm_postevent_buffer_size 256 postevent buffer size
_lm_proc_freeze_timeout 300 reconfiguration: process freeze timeout
_lm_process_batching TRUE GES implicit process batching for IPC messages
_lm_procs 320 number of client processes configured for cluster database
_lm_psrcfg FALSE enable pseudo reconfiguration
_lm_rcfg_timeout 1260 Reconfiguration timeout
_lm_rcvr_hang_allow_time 200 receiver hang allow time in seconds
_lm_rcvr_hang_check_frequency 60 receiver hang check frequency in seconds
_lm_rcvr_hang_kill FALSE to kill receiver hang
_lm_res_hash_bucket 0 number of resource hash buckets
_lm_res_part 128 number of resource partition configured for gcs
_lm_ress 6000 number of resources configured for cluster database
_lm_send_mode auto GES send mode
_lm_send_queue_batching TRUE GES send queue message batching
_lm_send_queue_length 5000 GES send queue maximum length
_lm_sendproxy_reserve 25 GES percentage of send proxy reserve of send tickets
_lm_share_lock_opt FALSE if TRUE enables share lock optimization
_lm_sq_batch_factor 2 GES send queue minimum batching factor
_lm_sq_batch_type auto GES send queue batching mechanism
_lm_sq_batch_waittick 3 GES send queue batching waittime in tick
_lm_sync_timeout 420 Synchronization timeout for DLM reconfiguration steps
_lm_ticket_active_sendback Flow control ticket active sendback threshold
_lm_tickets 1000 GES messaging tickets
_lm_tx_delta 16 TX lock localization delta
_lm_validate_resource_type FALSE if TRUE enables resource name validation
_lm_xids 352 number of transaction IDs configured for cluster database
_lmn_compression FALSE suppl logging for compression enabled
_load_without_compile NONE Load PL/SQL or Database objects without compilation
_local_communication_costing_enabled TRUE enable local communication costing when TRUE
_local_communication_ratio 50 set the ratio between global and local communication (0..100)
_local_hang_analysis_interval_secs 3 the interval at which local hang analysis is run
_lock_sga_areas 0 Lock specified areas of the SGA in physical memory
_log_archive_avoid_memcpy TRUE log archive avoid memcpy
_log_archive_buffers 10 Number of buffers to allocate for archiving
_log_archive_callout archival callout
_log_archive_compress_enable FALSE Bypass database rules for enabling archivelog compression
_log_archive_network_redo_size 10 Log archive network redo buffer size used by ARCH
_log_archive_prot_auto_demote FALSE log archive protection auto demotion
_log_archive_security_enabled TRUE log archive security enabled
_log_archive_strong_auth TRUE log archive security strong auth
_log_blocks_during_backup TRUE log block images when changed during backup
_log_buffers_corrupt FALSE corrupt redo buffers before write
_log_buffers_debug FALSE debug redo buffers (slows things down)
_log_checkpoint_recovery_check 0 # redo blocks to verify after checkpoint
_log_committime_block_cleanout FALSE Log commit-time block cleanout
_log_deletion_policy mandatory archivelog deletion policy for mandatory/all destination
_log_event_queues 0 number of the log writer event queues
_log_io_size 0 automatically initiate log write if this many redo blocks in buffer
_log_parallelism_dynamic TRUE Enable dynamic strands
_log_parallelism_max 1 Maximum number of log buffer strands
_log_private_mul 5 Private strand multiplier for log space preallocation
_log_private_parallelism FALSE Number of private log buffer strands for zero-copy redo
_log_private_parallelism_mul 10 Active sessions multiplier to deduce number of private strands
_log_read_buffers 4 Number of log read buffers for media recovery
_log_simultaneous_copies 2 number of simultaneous copies into redo buffer(# of copy latches)
_log_space_errors TRUE should we report space errors to alert log
_log_switch_timeout 0 Maximum number of seconds redos in the current log could span
_logout_storm_rate 0 number of processes that can logout in a second
_logout_storm_retrycnt 600 maximum retry count for logouts
_logout_storm_timeout 5 timeout in centi-seconds for time to wait between retries
_longops_enabled TRUE longops stats enabled
_low_server_threshold 0 low server thresholds
_ltc_trace 0 tracing level for load table conventional
_master_direct_sends 31 direct sends for messages from master (DFS)
_mav_refresh_consistent_read TRUE refresh materialized views using consistent read snapshot
_mav_refresh_double_count_prevented FALSE materialized view MAV refreshes avoid double counting
_mav_refresh_opt 0 optimizations during refresh of materialized views
_mav_refresh_unionall_tables 3 # tables for union all expansion during materialized view refresh
_max_arch_lns_wait 120 Maximum wait by ARCH to allow NetServer to complete archival
_max_exponential_sleep 0 max sleep during exponential backoff
_max_io_size 1048576 Maximum I/O size in bytes for sequential file accesses
_max_large_io 0 IORM:max number of large I/O’s to issue
_max_lns_shutdown_archival_time 30 Maximum time spent by LNS to archive last log during shutdown
_max_protocol_support 10000 Max occurrence protocols supported in a process
_max_shrink_obj_stats 0 number of segments for which shrink stats will be maintained
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_max_small_io 0 IORM:max number of small I/O’s to issue
_max_spacebg_slaves 10 maximum space management background slaves
_max_spacebg_tasks 1000 maximum space management background tasks
_max_temp_overhead 0 max tempspc overhead
_media_recovery_read_batch 128 media recovery block read batch
_mem_annotation_pr_lev 0 private memory annotation collection level
_mem_annotation_scale 1 memory annotation pre-allocation scaling
_mem_annotation_sh_lev 0 shared memory annotation collection level
_mem_annotation_store FALSE memory annotation in-memory store
_mem_std_extent_size 4096 standard extent size for fixed-size-extent heaps
_memory_broker_log_stat_entries 5 memory broker num stat entries
_memory_broker_marginal_utility_bc 12 Marginal Utility threshold pct for bc
_memory_broker_marginal_utility_sp 7 Marginal Utility threshold pct for sp
_memory_broker_shrink_heaps 15 memory broker allow policy to shrink shared pool
_memory_broker_shrink_java_heaps 900 memory broker allow policy to shrink java pool
_memory_broker_shrink_streams_pool 900 memory broker allow policy to shrink streams pool
_memory_broker_shrink_timeout 60000000 memory broker policy to timeout shrink shared/java pool
_memory_broker_stat_interval 30 memory broker statistics gathering interval for auto sga
_memory_management_tracing 0 trace memory management activity
_memory_nocancel_defsgareq FALSE do not cancel deferred sga reqs with auto-memory
_memory_sanity_check 0 partial granule sanity check
_messages 300 message queue resources – dependent on # processes & # buffers
_midtier_affinity_clusterwait_threshold 100 cluster wait threshold to enter affinity
_midtier_affinity_goodness_threshold 2000 goodness gradient threshold to dissolve affinity
_midtier_affinity_timeout 30000 default timeout for midtier affinity processing
_minfree_plus 0 max percentage of block space + minfree before we mark block full
_minimal_stats_aggregation TRUE prohibit stats aggregation at compile/partition maintenance time
_minimum_blocks_to_shrink 0 minimum number freeable blocks for shrink to be present
_minimum_db_flashback_retention 60 Minimum flashback retention
_minimum_extents_to_shrink 1 minimum number freeable extents for shrink to be present
_minimum_giga_scn 0 Minimum SCN to start with in 2^30 units
_mirror_redo_buffers FALSE Save buffers for debugging redo corruptions
_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and/or base tables
_multi_instance_pmr TRUE force multi instance or single instance parallel recovery
_multi_join_key_table_lookup TRUE TRUE iff multi-join-key table lookup prefetch is enabled
_multiple_instance_recovery FALSE use multiple instances for media recovery
_mv_refresh_ana 0 what percent to analyze after complete/PCT refresh
_mv_refresh_costing rule refresh decision based on cost or on rules
_mv_refresh_delta_fraction 10 delta mv as fractional percentage of size of mv
_mv_refresh_eut TRUE refresh materialized views using EUT(partition)-based algorithm
_mv_refresh_force_parallel_query 0 force materialized view refreshes to use parallel query
_mv_refresh_new_setup_disabled FALSE materialized view MV refresh new setup disabling
_mv_refresh_rebuild_percentage 10 minimum percentage change required in MV to force an indexrebuild
_mv_refresh_selections TRUE create materialized views with selections and fast refresh
_mv_refresh_use_stats FALSE pass cardinality hints to refresh queries
_mv_refsched_timeincr 300000 proportionality constant for dop vs. time in MV refresh
_mv_rolling_inv FALSE create/alter mv uses rolling cursor invalidation instead of immediate
_mwin_schedule TRUE Enable/disable Maintenance Window Schedules
_nchar_imp_cnv TRUE NLS allow Implicit Conversion between CHAR and NCHAR
_nchar_imp_conv TRUE should implicit conversion bewteen clob and nclob be allowed
_ncmb_readahead_enabled 0 enable multi-block readahead for an index scan
_ncmb_readahead_tracing 0 turn on multi-block readahead tracing
_ncomp_shared_objects_dir native compilation shared objects dir
_nested_loop_fudge 100 nested loop fudge
_nested_mav_fast_oncommit_enabled TRUE nested MAV refresh fast on commit allowed
_new_initial_join_orders TRUE enable initial join orders based on new ordering heuristics
_new_sort_cost_estimate TRUE enables the use of new cost estimate for sort
_newsort_enabled TRUE controls whether new sorts can be used as system sort
_newsort_ordered_pct 63 controls when new sort avoids sorting ordered input
_newsort_type 0 specifies options for the new sort algorithm
_nlj_batching_enabled 1 enable batching of the RHS IO in NLJ
_nlj_batching_misses_enabled 1 enable exceptions for buffer cache misses
_no_objects FALSE no object features are used
_no_or_expansion FALSE OR expansion during optimization disabled
_no_recovery_through_resetlogs FALSE no recovery through this resetlogs operation
_notify_crs FALSE notify cluster ready services of startup and shutdown
_ns_max_flush_wt 1 Flush wait time for NetServer to flush oustanding writes
_ns_max_send_delay 15 Data Loss Time Bound for NetServer
_num_longop_child_latches 1 number of child latches for long op array
_number_cached_attributes 10 maximum number of cached attributes per instance
_object_reuse_bast 2 if 1 or higher, handle object reuse
_object_statistics TRUE enable the object level statistics collection
_object_stats_max_entries 3072 Maximum number of entries to be tracked per stat
_offline_rollback_segments offline undo segment list
_ogms_home GMS home directory
_olap_aggregate_buffer_size 1048576 OLAP Aggregate max buffer size
_olap_aggregate_flags 0 OLAP Aggregate debug flags
_olap_aggregate_function_cache_enabled TRUE OLAP Aggregate function cache enabler
_olap_aggregate_function_merge_threshold 529 OLAP Aggregate function merge threshold
_olap_aggregate_max_thread_tuples 5000 OLAP Aggregate max thread tuples creation
_olap_aggregate_min_buffer_size 1024 OLAP Aggregate min buffer size
_olap_aggregate_min_thread_status 64 OLAP Aggregate minimum cardinality of dimensions for thread
_olap_aggregate_multipath_hier FALSE OLAP Aggregate Multi-path Hierarhies enabled
_olap_aggregate_statlen_thresh 1024 OLAP Aggregate status array usage threshold
_olap_aggregate_store_probability 100 OLAP Aggregate function storeback probability
_olap_aggregate_work_per_thread 1024 OLAP Aggregate max work parents
_olap_aggregate_worklist_max 5000 OLAP Aggregate max worklists generated at once
_olap_allocate_errorlog_format %8p %8y %8z %e (%n) OLAP Allocate Errorlog Format
_olap_allocate_errorlog_header Dim Source Basis %-8d %-8s %-8b Description ——– ——– ——– ———– OLAP Allocate Errorlog Header format
_olap_analyze_max 10000 OLAP DML ANALYZE command max cells to analyze
_olap_continuous_trace_file FALSE Specify TRUE to enable continuous OLAP tracing – otherwise only exceptional events will be logged
_olap_dbgoutfile_echo_to_eventlog FALSE OLAP DbgOutfile copy output to event log (tracefile)
_olap_dimension_corehash_class 1 OLAP Dimension In-Core Hash Table Class
_olap_dimension_corehash_large 50000 OLAP Dimension In-Core Hash Table Large Threshold
_olap_dimension_corehash_pressure 90 OLAP Dimension In-Core Hash Table Pressure Threshold
_olap_dimension_corehash_size 30 OLAP Dimension In-Core Hash Table Maximum Memory Use
_olap_dimension_corehash_small 1000 OLAP Dimension In-Core Hash Table Small Threshold
_olap_eif_export_lob_size 2147483647 OLAP EIF Export BLOB size
_olap_lmgen_dim_size 100 Limitmap generator dimension column size
_olap_lmgen_meas_size 1000 Limitmap generator measure column size
_olap_object_hash_class 2 OLAP Object Hash Table Class
_olap_page_pool_expand_rate 20 OLAP Page Pool Expand Rate
_olap_page_pool_hi 50 OLAP Page Pool High Watermark
_olap_page_pool_hit_target 100 OLAP Page Pool Hit Target
_olap_page_pool_low 262144 OLAP Page Pool Low Watermark
_olap_page_pool_pressure 90 OLAP Page Pool Pressure Threshold
_olap_page_pool_shrink_rate 50 OLAP Page Pool Shrink Rate
_olap_parallel_update_small_group 400 OLAP parallel update pagespace by group
_olap_parallel_update_small_threshold 1000 OLAP parallel update threshold for number of small pagespaces
_olap_parallel_update_threshold 1000 OLAP parallel update threshold in pages
_olap_poutlog_echo_to_eventlog FALSE OLAP POutLog copy output to event log (tracefile)
_olap_sesscache_enabled TRUE OLAP Session Cache knob
_olap_sort_buffer_size 262144 OLAP Sort Buffer Size
_olap_statbool_corebits 20000000 OLAP Status Boolean max incore bits
_olap_statbool_threshold 8100 OLAP Status Boolean CBM threshold
_olap_table_function_statistics FALSE Specify TRUE to output OLAP table function timed statistics trace
_olap_wrap_errors FALSE Wrap error messages to OLAP outfile
_olapi_history_retention FALSE enable olapi history retention
_olapi_iface_object_history 1000 enable olapi interface object history collection
_olapi_iface_object_history_retention FALSE enable olapi interface object history retention
_olapi_iface_operation_history_retention FALSE enable olapi interface operation history retention
_olapi_interface_operation_history 1000 enable olapi interface operation history collection
_olapi_memory_operation_history 1000 enable olapi memory alloc/free history collection
_olapi_memory_operation_history_pause_at_seqno 0 enable olapi memory alloc/free history collection pausing
_olapi_memory_operation_history_retention FALSE enable olapi memory operation history retention
_olapi_session_history 300 enable olapi session history collection
_olapi_session_history_retention FALSE enable olapi session history retention
_old_connect_by_enabled FALSE enable/disable old connect by
_ols_cleanup_task TRUE Clean up unnecessary entries in OLS sessinfo table
_oltp_compression TRUE oltp compression enabled
_oltp_compression_gain 10 oltp compression gain
_omf enabled enable/disable OMF
_oneside_colstat_for_equijoins TRUE sanity check on default selectivity for like/range predicate
_online_patch_disable_stack_check FALSE disable check for function on stack for online patches
_optim_adjust_for_part_skews TRUE adjust stats for skews across partitions
_optim_dict_stats_at_db_cr_upg TRUE enable/disable dictionary stats gathering at db create/upgrade
_optim_enhance_nnull_detection TRUE TRUE to enable index [fast] full scan more often
_optim_new_default_join_sel TRUE improves the way default equijoin selectivity are computed
_optim_peek_user_binds TRUE enable peeking of user binds
_optimizer_adaptive_cursor_sharing TRUE optimizer adaptive cursor sharing
_optimizer_adjust_for_nulls TRUE adjust selectivity for null values
_optimizer_autostats_job TRUE enable/disable auto stats collection job
_optimizer_aw_join_push_enabled TRUE Enables AW Join Push optimization
_optimizer_aw_stats_enabled TRUE Enables statistcs on AW olap_table table function
_optimizer_better_inlist_costing ALL enable improved costing of index access using in-list(s)
_optimizer_block_size 8192 standard block size used by optimizer
_optimizer_cache_stats FALSE cost with cache statistics
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled
_optimizer_cbqt_factor 50 cost factor for cost-based query transformation
_optimizer_cbqt_no_size_restriction TRUE disable cost based transformation query size restriction
_optimizer_ceil_cost TRUE CEIL cost in CBO
_optimizer_choose_permutation 0 force the optimizer to use the specified permutation
_optimizer_complex_pred_selectivity TRUE enable selectivity estimation for builtin functions
_optimizer_compute_index_stats TRUE force index stats collection on index creation/rebuild
_optimizer_connect_by_combine_sw TRUE combine no filtering connect by and start with
_optimizer_connect_by_cost_based TRUE use cost-based transformation for connect by
_optimizer_correct_sq_selectivity TRUE force correct computation of subquery selectivity
_optimizer_cost_based_transformation LINEAR enables cost-based query transformation
_optimizer_cost_filter_pred FALSE enables costing of filter predicates in IO cost model
_optimizer_cost_hjsmj_multimatch TRUE add cost of generating result set when #rows per key > 1
_optimizer_cost_model CHOOSE optimizer cost model
_optimizer_degree 0 force the optimizer to use the same degree of parallelism
_optimizer_dim_subq_join_sel TRUE use join selectivity in choosing star transformation dimensions
_optimizer_disable_strans_sanity_checks 0 disable star transformation sanity checks
_optimizer_distinct_elimination TRUE Eliminates redundant SELECT DISTNCT’s
_optimizer_dyn_smp_blks 32 number of blocks for optimizer dynamic sampling
_optimizer_enable_density_improvements TRUE use improved density computation for selectivity estimation
_optimizer_enable_extended_stats TRUE use extended statistics for selectivity estimation
_optimizer_enhanced_filter_push TRUE push filters before trying cost-based query transformation
_optimizer_extend_jppd_view_types TRUE join pred pushdown on group-by, distinct, semi-/anti-joined view
_optimizer_extended_cursor_sharing UDO optimizer extended cursor sharing
_optimizer_extended_cursor_sharing_rel SIMPLE optimizer extended cursor sharing for relational operators
_optimizer_extended_stats_usage_control 240 controls the optimizer usage of extended stats
_optimizer_filter_pred_pullup TRUE use cost-based flter predicate pull up transformation
_optimizer_fkr_index_cost_bias 10 Optimizer index bias over FTS/IFFS under first K rows mode
_optimizer_free_transformation_heap TRUE free transformation subheap after each transformation
_optimizer_group_by_placement TRUE consider group-by placement optimization
_optimizer_ignore_hints FALSE enables the embedded hints to be ignored
_optimizer_improve_selectivity TRUE improve table and partial overlap join selectivity computation
_optimizer_invalidation_period 18000 time window for invalidation of cursors of analyzed objects
_optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
_optimizer_join_order_control 3 controls the optimizer join order search algorithm
_optimizer_join_sel_sanity_check TRUE enable/disable sanity check for multi-column join selectivity
_optimizer_max_permutations 2000 optimizer maximum join permutations per query block
_optimizer_min_cache_blocks 10 set minimum cached blocks
_optimizer_mjc_enabled TRUE enable merge join cartesian
_optimizer_mode_force TRUE force setting of optimizer mode for user recursive SQL also
_optimizer_multi_level_push_pred TRUE consider join-predicate pushdown that requires multi-level pushdown to base table
_optimizer_multiple_cenv generate and run plans using several compilation environments
_optimizer_native_full_outer_join FORCE execute full outer join using native implementaion
_optimizer_nested_rollup_for_gset 100 number of groups above which we use nested rollup exec for gset
_optimizer_new_join_card_computation TRUE compute join cardinality using non-rounded input values
_optimizer_null_aware_antijoin TRUE null-aware antijoin parameter
_optimizer_or_expansion DEPTH control or expansion approach used
_optimizer_or_expansion_subheap TRUE Use subheap for optimizer or-expansion
_optimizer_order_by_elimination_enabled TRUE Eliminates order bys from views before query transformation
_optimizer_outer_to_anti_enabled TRUE Enable transformation of outer-join to anti-join if possible
_optimizer_percent_parallel 101 optimizer percent parallel
_optimizer_push_down_distinct 0 push down distinct from query block to table
_optimizer_push_pred_cost_based TRUE use cost-based query transformation for push pred optimization
_optimizer_random_plan 0 optimizer seed value for random plans
_optimizer_reuse_cost_annotations TRUE reuse cost annotations during cost-based query transformation
_optimizer_rownum_bind_default 10 Default value to use for rownum bind
_optimizer_rownum_pred_based_fkr TRUE enable the use of first K rows due to rownum predicate
_optimizer_save_stats TRUE enable/disable saving old versions of optimizer stats
_optimizer_search_limit 5 optimizer search limit
_optimizer_self_induced_cache_cost FALSE account for self-induced caching
_optimizer_skip_scan_enabled TRUE enable/disable index skip scan
_optimizer_skip_scan_guess FALSE consider index skip scan for predicates with guessed selectivity
_optimizer_sortmerge_join_enabled TRUE enable/disable sort-merge join method
_optimizer_sortmerge_join_inequality TRUE enable/disable sort-merge join using inequality predicates
_optimizer_squ_bottomup TRUE enables unnesting of subquery in a bottom-up manner
_optimizer_star_tran_in_with_clause TRUE enable/disable star transformation in with clause queries
_optimizer_star_trans_min_cost 0 optimizer star transformation minimum cost
_optimizer_star_trans_min_ratio 0 optimizer star transformation minimum ratio
_optimizer_starplan_enabled TRUE optimizer star plan enabled
_optimizer_system_stats_usage TRUE system statistics usage
_optimizer_trace none optimizer trace parameter
_optimizer_transitivity_retain TRUE retain equi-join pred upon transitive equality pred generation
_optimizer_undo_changes FALSE undo changes to query optimizer
_optimizer_undo_cost_change 11.1.0.6 optimizer undo cost change
_optimizer_use_subheap TRUE Enables physical optimizer subheap
_or_expand_nvl_predicate TRUE enable OR expanded plan for NVL/DECODE predicate
_oradbg_pathname path of oradbg script
_oradebug_force FALSE force target processes to execute oradebug commands?
_ordered_nested_loop TRUE enable ordered nested loop costing
_ordered_semijoin TRUE enable ordered semi-join subquery
_orph_cln_interval 1200 qmon periodic interval for removed subscriber messages cleanup
_os_sched_high_priority 1 OS high priority level
_other_wait_event_exclusion exclude event names from _other_wait_threshold calculations
_other_wait_threshold 0 threshold wait percentage for event wait class Other
_outline_bitmap_tree TRUE BITMAP_TREE hint enabled in outline
_parallel_adaptive_max_users 2 maximum number of users running with default DOP
_parallel_broadcast_enabled TRUE enable broadcasting of small inputs to hash and sort merge joins
_parallel_default_max_instances 1 default maximum number of instances for parallel query
_parallel_execution_message_align FALSE Alignment of PX buffers to OS page boundary
_parallel_fake_class_pct 0 fake db-scheduler percent used for testing
_parallel_fixwrite_bucket 1000 Number of buckets for each round of fix write
_parallel_load_bal_unit 0 number of threads to allocate per instance
_parallel_load_balancing TRUE parallel execution load balanced slave allocation
_parallel_min_message_pool 206592 minimum size of shared pool memory to reserve for pq servers
_parallel_policy MANUAL policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO)
_parallel_recovery_stopat 32767 stop at -position- to step through SMON
_parallel_replay_msg_limit 4000 Number of messages for each round of parallel replay
_parallel_server_idle_time 30000 idle time before parallel query server dies (in 1/100 sec)
_parallel_server_sleep_time 10 sleep time between dequeue timeouts (in 1/100ths)
_parallel_slave_acquisition_wait 1 time(in seconds) to wait before retrying slave acquisition
_parallel_time_threshold 10 threshold above which a plan is a candidate for parallelization (in seconds)
_parallel_time_unit 10 unit of work used to derive the degree of parallelism (in seconds)
_parallel_txn_global FALSE enable parallel_txn hint with updates and deletes
_parallelism_cost_fudge_factor 350 set the parallelism cost fudge factor
_parameter_table_block_size 2048 parameter table block size
_partial_pwise_join_enabled TRUE enable partial partition-wise join when TRUE
_partition_view_enabled TRUE enable/disable partitioned views
_passwordfile_enqueue_timeout 900 password file enqueue timeout in seconds
_pct_refresh_double_count_prevented TRUE materialized view PCT refreshes avoid double counting
_pdml_gim_sampling 5000 control separation of global index maintenance for PDML
_pdml_gim_staggered FALSE slaves start on different index when doing index maint
_pdml_slaves_diff_part TRUE slaves start on different partition when doing index maint
_percent_flashback_buf_partial_full 50 Percent of flashback buffer filled to be considered partial full
_pga_large_extent_size 1048576 PGA large extent size
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_ping_level 3 fusion ping level
_pivot_implementation_method CHOOSE pivot implementation method
_pkt_enable FALSE enable progressive kill test
_pkt_pmon_interval 50 PMON process clean-up interval (cs)
_pkt_start FALSE start progressive kill test instrumention
_plan_outline_data TRUE explain plan outline data enabled
_plan_verify_improvement_margin 150 Performance improvement criterion for evolving plan baselines
_plan_verify_local_time_limit 0 Local time limit to use for an individual plan verification
_plsql_anon_block_code_type INTERPRETED PL/SQL anonymous block code-type
_plsql_cache_enable TRUE PL/SQL Function Cache Enabled
_plsql_dump_buffer_events conditions upon which the PL/SQL circular buffer is dumped
_plsql_minimum_cache_hit_percent 20 plsql minimum cache hit percentage required to keep caching active
_plsql_nvl_optimize FALSE PL/SQL NVL optimize
_pmon_load_constants 300,192,64,3,10,10,0,0 server load balancing constants (S,P,D,I,L,C,M)
_pre_rewrite_push_pred TRUE push predicates into views before rewrite
_precompute_gid_values TRUE precompute gid values and copy them before returning a row
_pred_move_around TRUE enables predicate move-around
_predicate_elimination_enabled TRUE allow predicate elimination if set to TRUE
_prescomm FALSE presume commit of IMU transactions
_print_refresh_schedule false enable dbms_output of materialized view refresh schedule
_private_memory_address Start address of large extent memory segment
_project_view_columns TRUE enable projecting out unreferenced columns of a view
_projection_pushdown TRUE projection pushdown
_projection_pushdown_debug 0 level for projection pushdown debugging
_prop_old_enabled FALSE Shift to pre 11g propagation behaviour
_protect_frame_heaps FALSE Protect cursor frame heaps
_push_join_predicate TRUE enable pushing join predicate inside a view
_push_join_union_view TRUE enable pushing join predicate inside a union all view
_push_join_union_view2 TRUE enable pushing join predicate inside a union view
_px_async_getgranule FALSE asynchronous get granule in the slave
_px_bind_peek_sharing TRUE enables sharing of px cursors that were built using bind peeking
_px_broadcast_fudge_factor 100 set the tq broadcasting fudge factor percentage
_px_buffer_ttl 30 ttl for px mesg buffers in seconds
_px_compilation_debug 0 debug level for parallel compilation
_px_compilation_trace 0 tracing level for parallel compilation
_px_dump_12805_source TRUE enables or disables tracing of 12805 signal source
_px_dynamic_opt TRUE turn off/on restartable qerpx dynamic optimization
_px_dynamic_sample_size 50 num of samples for restartable qerpx dynamic optimization
_px_execution_services_enabled TRUE enable service-based constraint of px slave allocation
_px_granule_size 100000 default size of a rowid range granule (in KB)
_px_index_sampling 200 parallel query sampling for index create (100000 = 100%)
_px_io_process_bandwidth 200 IO process bandwidth in MB/sec for computing DOP
_px_io_system_bandwidth 0 total IO system bandwidth in MB/sec for computing DOP
_px_kxib_tracing 0 turn on kxib tracing
_px_load_publish_interval 200 interval at which LMON will check whether to publish PX load
_px_loc_msg_cost 1000 CPU cost to send a PX message via shared memory
_px_max_granules_per_slave 100 maximum number of rowid range granules to generate per slave
_px_min_granules_per_slave 13 minimum number of rowid range granules to generate per slave
_px_minus_intersect TRUE enables pq for minus/interect operators
_px_net_msg_cost 10000 CPU cost to send a PX message over the internconnect
_px_no_granule_sort FALSE prevent parallel partition granules to be sorted on size
_px_no_stealing FALSE prevent parallel granule stealing in shared nothing environment
_px_nss_planb TRUE enables or disables NSS Plan B reparse with outline
_px_proc_constrain TRUE reduce parallel_max_servers if greater than (processes – fudge)
_px_pwg_enabled TRUE parallel partition wise group by enabled
_px_rownum_pd TRUE turn off/on parallel rownum pushdown optimization
_px_send_timeout 300 IPC message send timeout value in seconds
_px_slaves_share_cursors 0 slaves share cursors with QC
_px_trace none px trace parameter
_px_ual_serial_input TRUE enables new pq for UNION operators
_px_xtgranule_size 10000 default size of a external table granule (in KB)
_qa_control 0 Oracle internal parameter to control QA
_qa_lrg_type 0 Oracle internal parameter to specify QA lrg type
_query_cost_rewrite TRUE perform the cost based rewrite with materialized views
_query_execution_cache_max_size 65536 max size of query execution cache
_query_mmvrewrite_maxcmaps 20 query mmv rewrite maximum number of cmaps per dmap in query disjunct
_query_mmvrewrite_maxdmaps 10 query mmv rewrite maximum number of dmaps per query disjunct
_query_mmvrewrite_maxinlists 5 query mmv rewrite maximum number of in-lists per disjunct
_query_mmvrewrite_maxintervals 5 query mmv rewrite maximum number of intervals per disjunct
_query_mmvrewrite_maxmergedcmaps 50 query mmv rewrite maximum number of merged cmaps
_query_mmvrewrite_maxpreds 10 query mmv rewrite maximum number of predicates per disjunct
_query_mmvrewrite_maxqryinlistvals 500 query mmv rewrite maximum number of query in-list values
_query_mmvrewrite_maxregperm 512 query mmv rewrite maximum number of region permutations
_query_rewrite_1 TRUE perform query rewrite before&after or only before view merging
_query_rewrite_2 TRUE perform query rewrite before&after or only after view merging
_query_rewrite_drj TRUE mv rewrite and drop redundant joins
_query_rewrite_expression TRUE rewrite with cannonical form for expressions
_query_rewrite_fpc TRUE mv rewrite fresh partition containment
_query_rewrite_fudge 90 cost based query rewrite with MVs fudge factor
_query_rewrite_jgmigrate TRUE mv rewrite with jg migration
_query_rewrite_maxdisjunct 257 query rewrite max disjuncts
_query_rewrite_or_error FALSE allow query rewrite, if referenced tables are not dataless
_query_rewrite_setopgrw_enable TRUE perform general rewrite using set operator summaries
_query_rewrite_vop_cleanup TRUE prune frocol chain before rewrite after view-merging
_rcfg_parallel_fixwrite TRUE if TRUE enables parallel fixwrite at reconfiguration
_rcfg_parallel_replay TRUE if TRUE enables parallel replay and cleanup at reconfiguration
_rcfg_parallel_verify TRUE if TRUE enables parallel verify at reconfiguration
_rdbms_compatibility 10.1 default RDBMS compatibility level
_rdbms_internal_fplib_enabled FALSE enable SAGE FPLIB filtering within rdbms
_rdbms_internal_fplib_raise_errors FALSE enable reraising of any exceptions in SAGE FPLIB
_readable_standby_mode SLFLUSH readable standby mode
_readable_standby_sync_timeout 10 readable standby query scn sync timeout
_real_time_apply_sim 0 Simulation value with real time apply
_realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
_realfree_heap_mode 0 mode flags for real-free heap
_realfree_heap_pagesize_hint 65536 hint for real-free page size in bytes
_recoverable_recovery_batch_percent 50 Recoverable recovery batch size (percentage of buffer cache)
_recovery_asserts FALSE if TRUE, enable expensive integrity checks
_recovery_percentage 50 recovery buffer cache percentage
_recovery_verify_writes FALSE enable thread recovery write verify
_recursive_imu_transactions FALSE recursive transactions may be IMU
_redo_compatibility_check FALSE general and redo/undo compatibility sanity check
_redo_read_from_memory TRUE Enable reading redo from memory
_redo_transport_compress_all FALSE Is ASYNC LNS compression allowed?
_release_insert_threshold 5 maximum number of unusable blocks to unlink from freelist
_reliable_block_sends FALSE if TRUE, block sends across interconnect are reliable
_remove_aggr_subquery TRUE enables removal of subsumed aggregated subquery
_replace_virtual_columns TRUE replace expressions with virtual columns
_resource_manager_always_off FALSE disable the resource manager always
_resource_manager_always_on TRUE enable the resource manager always
_restore_spfile restore spfile to this location
_result_cache_auto_execution_threshold 1 result cache auto execution threshold
_result_cache_auto_size_threshold 100 result cache auto max size allowed
_result_cache_auto_time_distance 300 result cache auto time distance
_result_cache_auto_time_threshold 1000 result cache auto time threshold
_result_cache_block_size 1024 result cache block size
_result_cache_timeout 60 maximum time (sec) a session waits for a result
_reuse_index_loop 5 number of blocks being examine for index block reuse
_right_outer_hash_enable TRUE Right Outer/Semi/Anti Hash Enabled
_rman_io_priority 3 priority at which rman backup i/o’s are done
_rman_restore_through_link FALSE RMAN restore through link
_rollback_segment_count 0 number of undo segments
_rollback_segment_initial 1 starting undo segment number
_rollback_stopat 0 stop at -position to step rollback
_row_cache_cursors 20 number of cached cursors for row cache management
_row_cr TRUE enable row cr for all sql
_row_locking always row-locking
_row_shipping_explain FALSE enable row shipping explain plan support
_row_shipping_threshold 80 row shipping column selection threshold
_rowsource_execution_statistics FALSE if TRUE, Oracle will collect rowsource statistics
_rowsource_profiling_statistics TRUE if TRUE, Oracle will capture active row sources in v$active_session_history
_rowsource_statistics_sampfreq 128 frequency of rowsource statistic sampling (must be a power of 2)
_rowsrc_trace_level 0 Row source tree tracing level
_rtc_infeasible_threshold 10 Redo Transport Compression infeasible threshold
_sage_enabled 1 enable real SAGE functionality
_sage_fcre TRUE Allow optimized file creation path for SAGE
_sample_rows_per_block 4 number of rows per block used for sampling IO optimization
_scatter_gcs_resources FALSE if TRUE, gcs resources are scattered uniformly across sub pools
_scatter_gcs_shadows FALSE if TRUE, gcs shadows are scattered uniformly across sub pools
_scn_wait_interface_max_backoff_time_secs 600 max exponential backoff time for scn wait interface in kta
_scn_wait_interface_max_timeout_secs 2147483647 max timeout for scn wait interface in kta
_sdiag_crash NONE sql diag crash
_sec_enable_test_rpcs FALSE Whether to enable the test RPCs
_second_spare_parameter second spare parameter – integer
_securefile_timers FALSE collect kdlu timers and accumulate per layers
_selectivity_for_srf_enabled FALSE enable/disable selectivity for storage reduction factor
_selfjoin_mv_duplicates TRUE control rewrite self-join algorithm
_selftune_checkpoint_write_pct 3 Percentage of total physical i/os for self-tune ckpt
_selftune_checkpointing_lag 300 Self-tune checkpointing lag the tail of the redo log
_send_ast_to_foreground TRUE if TRUE, send ast message to foreground
_send_close_with_block TRUE if TRUE, send close with block even with direct sends
_send_requests_to_pi TRUE if TRUE, try to send CR requests to PI buffers
_serial_direct_read FALSE enable direct read in serial
_serial_recovery FALSE force serial recovery or parallel recovery
_serializable FALSE serializable
_session_cached_instantiations 60 Number of pl/sql instantiations to cache in a session.
_session_context_size 10000 session app context size
_session_idle_bit_latches 0 one latch per session or a latch per group of sessions
_session_idle_check_interval 60 Resource Manager session idle limit check interval in seconds
_session_kept_cursor_pins 0 Number of cursors pins to keep in a session
_session_wait_history 10 enable session wait history collection
_seventh_spare_parameter seventh spare parameter – string list
_sga_clear_dump FALSE Allow dumping encrypted blocks in clear for debugging
_sga_early_trace 0 sga early trace event
_sga_locking none sga granule locking state
_shared_io_pool_buf_size 1048576 Shared IO pool buffer size
_shared_io_pool_debug_trc 0 trace kcbi debug info to tracefile
_shared_io_pool_size 0 Size of shared IO pool
_shared_io_set_value FALSE shared io pool size set internal value – overwrite zero user size
_shared_iop_max_size 536870912 maximum shared io pool size
_shared_pool_max_size 0 shared pool maximum size when auto SGA enabled
_shared_pool_minsize_on FALSE shared pool minimum size when auto SGA enabled
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area of shared pool
_shared_pool_reserved_pct 5 percentage memory of the shared pool allocated for the reserved area
_shared_server_load_balance 0 shared server load balance
_shared_server_num_queues 1 number of shared server common queues
_short_stack_timeout_ms 30000 short stack timeout in ms
_shrunk_aggs_disable_threshold 60 percentage of exceptions at which to switch to full length aggs
_shrunk_aggs_enabled TRUE enable use of variable sized buffers for non-distinct aggregates
_side_channel_batch_size 200 number of messages to batch in a side channel message (DFS)
_side_channel_batch_timeout 6 timeout before shipping out the batched side channelmessages in seconds
_side_channel_batch_timeout_ms 500 timeout before shipping out the batched side channelmessages in milliseconds
_simple_view_merging TRUE control simple view merging performed by the optimizer
_simulate_mem_transfer FALSE simulate auto memory sga/pga transfers
_simulator_bucket_mindelta 8192 LRU bucket minimum delta
_simulator_internal_bound 10 simulator internal bound percent
_simulator_lru_rebalance_sizthr 5 LRU list rebalance threshold (size)
_simulator_lru_rebalance_thresh 10240 LRU list rebalance threshold (count)
_simulator_lru_scan_count 8 LRU scan count
_simulator_pin_inval_maxcnt 16 maximum count of invalid chunks on pin list
_simulator_reserved_heap_count 4096 simulator reserved heap count
_simulator_reserved_obj_count 1024 simulator reserved object count
_simulator_sampling_factor 2 sampling factor for the simulator
_simulator_upper_bound_multiple 2 upper bound multiple of pool size
_single_process FALSE run without detached processes
_siop_flashback_scandepth 20 Shared IO pool flashback io completion scan depth
_sixth_spare_parameter sixth spare parameter – string list
_skgxp_reaping 1000 tune skgxp OSD reaping limit
_skgxp_udp_ach_reaping_time 120 time in minutes before idle ach’s are reaped
_skgxp_udp_ack_delay 0 Enables delayed acks
_skgxp_udp_enable_dynamic_credit_mgmt 0 Enables dynamic credit management
_skgxp_udp_hiwat_warn 1000 ach hiwat mark warning interval
_skgxp_udp_interface_detection_time_secs 60 time in seconds between interface detection checks
_skgxp_udp_keep_alive_ping_timer_secs 300 connection idle time in seconds before keep alive is initiated. min: 30 sec max: 1800 sec default: 300 sec
_skgxp_udp_lmp_mtusize 0 MTU size for UDP LMP testing
_skgxp_udp_lmp_on FALSE enable UDP long message protection
_skgxp_udp_timed_wait_buffering 1024 diagnostic log buffering space (in bytes) for timed wait (0 means unbufferd
_skgxp_udp_timed_wait_seconds 5 time in seconds before timed wait is invoked
_skgxp_udp_use_tcb TRUE disable use of high speek timer
_skip_assume_msg TRUE if TRUE, skip assume message for consigns at the master
_slave_mapping_enabled TRUE enable slave mapping when TRUE
_slave_mapping_group_size 0 force the number of slave group in a slave mapper
_small_table_threshold 541 threshold level of table size for direct reads
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_bound 0 overwrites memory manager automatically computed bound
_smm_control 0 provides controls on the memory manager
_smm_freeable_retain 5120 value in KB of the instance freeable PGA memory to retain
_smm_isort_cap 102400 maximum work area for insertion sort(v1)
_smm_max_size 40960 maximum work area size in auto mode (serial)
_smm_min_size 204 minimum work area size in auto mode
_smm_px_max_size 102400 maximum work area size in auto mode (global)
_smm_retain_size 0 work area retain size in SGA for shared server sessions (0 for AUTO)
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smon_internal_errlimit 100 limit of SMON internal errors
_smon_undo_seg_rescan_limit 10 limit of SMON continous undo segments re-scan
_smu_debug_mode 0 - set debug event for testing SMU operations
_smu_error_simulation_site 0 site ID of error simulation in KTU code
_smu_error_simulation_type 0 error type for error simulation in KTU code
_smu_timeouts comma-separated *AND double-quoted* list of AUM timeouts: mql, tur, sess_exprn, qry_exprn, slot_intvl
_sort_elimination_cost_ratio 0 cost ratio for sort eimination under first_rows mode
_sort_multiblock_read_count 2 multi-block read count for sort
_space_align_size 1048576 space align size
_spin_count 1 Amount to spin waiting for a latch
_spr_max_rules 10000 maximum number of rules in sql spreadsheet
_spr_push_pred_refspr TRUE push predicates through reference spreadsheet
_spr_use_AW_AS TRUE enable AW for hash table in spreadsheet
_spr_use_hash_table FALSE use hash table for spreadsheet
_sql_analyze_parse_model 2 SQL Analyze Parse Model control parameter
_sql_connect_capability_override 0 SQL Connect Capability Table Override
_sql_connect_capability_table SQL Connect Capability Table (testing only)
_sql_hash_debug 0 Hash value of the SQL statement to debug
_sql_model_unfold_forloops RUN_TIME specifies compile-time unfolding of sql model forloops
_sql_plan_baseline_capture_on_1_exec FALSE With auto-capture on, create new SQL plan baseline on first exec
_sqlexec_progression_cost 1000 sql execution progression monitoring cost threshold
_sqlmon_max_plan 20 Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines 300 Number of plan lines beyond which a plan cannot be monitored
_sqlmon_threshold 5 CPU/IO time threshold before a statement is monitored. 0 is disabled
_sqltune_category_parsed DEFAULT Parsed category qualifier for applying hintsets
_srvntfn_job_deq_timeout 60 srvntfn job deq timeout
_srvntfn_jobsubmit_interval 3 srvntfn job submit interval
_srvntfn_max_concurrent_jobs 20 srvntfn max concurrent jobs
_srvntfn_q_msgcount 50 srvntfn q msg count for job exit
_srvntfn_q_msgcount_inc 100 srvntfn q msg count increase for job submit
_sscr_dir Session State Capture and Restore DIRectory object
_sscr_osdir Session State Capture and Restore OS DIRectory
_sta_control 0 SQL Tuning Advisory control parameter
_stack_guard_level 0 stack guard level
_static_backgrounds static backgrounds
_statistics_based_srf_enabled TRUE enable/disable the use of statistics for storage reduction factor
_step_down_limit_in_pct 1 step down limit in percentage
_stn_trace 0 SQL tracing parameter
_storage_predicate_access_predicate 0 put storage predicate into access predicate in explain plan
_streams_pool_max_size 0 streams pool maximum size when auto SGA enabled
_subquery_pruning_cost_factor 20 subquery pruning cost factor
_subquery_pruning_enabled TRUE enable the use of subquery predicates to perform pruning
_subquery_pruning_mv_enabled FALSE enable the use of subquery predicates with MVs to perform pruning
_subquery_pruning_reduction 50 subquery pruning reduction factor
_swrf_metric_frequent_mode FALSE Enable/disable SWRF Metric Frequent Mode Collection
_swrf_mmon_dbfus TRUE Enable/disable SWRF MMON DB Feature Usage
_swrf_mmon_flush TRUE Enable/disable SWRF MMON FLushing
_swrf_mmon_metrics TRUE Enable/disable SWRF MMON Metrics Collection
_swrf_on_disk_enabled TRUE Parameter to enable/disable SWRF
_swrf_test_action 0 test action parameter for SWRF
_swrf_test_dbfus FALSE Enable/disable DB Feature Usage Testing
_synonym_repoint_tracing FALSE whether to trace metadata comparisons for synonym repointing
_sysaux_test_param 1 test parameter for SYSAUX
_system_index_caching 0 optimizer percent system index caching
_system_trig_enabled TRUE are system triggers enabled
_ta_lns_wait_for_arch_log 20 LNS Wait time for arhcived version of online log
_table_lookup_prefetch_size 40 table lookup prefetch vector size
_table_lookup_prefetch_thresh 2 table lookup prefetch threshold
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one
_target_rba_max_lag_percentage 90 target rba max log lag percentage
_tdb_debug_mode 16 set debug mode for testing transportable database
_temp_tran_block_threshold 100 number of blocks for a dimension before we temp transform
_temp_tran_cache TRUE determines if temp table is created with cache option
_test_ksusigskip 5 test the function ksusigskip
_test_param_1 25 test parmeter 1 – integer
_test_param_2 test parameter 2 – string
_test_param_3 test parameter 3 – string
_test_param_4 test parameter 4 – string list
_test_param_5 25 test parmeter 5 – deprecated integer
_test_param_6 0 test parmeter 6 – size (ub8)
_test_spacebg 0 test space management background task
_third_spare_parameter third spare parameter – integer
_threshold_alerts_enable 1 if 1, issue threshold-based alerts
_timemodel_collection TRUE enable timemodel collection
_timeout_actions_enabled TRUE enables or disables KSU timeout actions
_timer_precision 20 VKTM sleep time in milli-sec
_total_large_extent_memory 0 Total memory for allocating large extents
_tq_dump_period 0 time period for duping of TQ statistics (s)
_trace_buffer_wait_timeouts 0 trace buffer busy wait timeouts
_trace_buffers ALL:256 trace buffer sizes per process
_trace_dump_all_procs FALSE if TRUE on error buckets of all processes will be dumped to the current trace file
_trace_dump_client_buckets TRUE if TRUE dump client (ie. non-kst) buckets
_trace_dump_cur_proc_only FALSE if TRUE on error just dump our process bucket
_trace_dump_static_only FALSE if TRUE filter trace dumps to always loaded dlls
_trace_events trace events enabled at startup
_trace_files_public FALSE Create publicly accessible trace files
_trace_kqlidp FALSE trace kqlidp0 operation
_trace_navigation_scope global enabling trace navigation linking
_trace_pin_time 0 trace how long a current pin is held
_trace_pool_size trace pool size in bytes
_trace_processes ALL enable KST tracing in process
_trace_virtual_columns FALSE trace virtual columns exprs
_transaction_auditing TRUE transaction auditing records generated in the redo log
_transaction_recovery_servers 0 max number of parallel recovery slaves that may be used
_truncate_optimization_enabled TRUE do truncate optimization if set to TRUE
_tsenc_tracing 0 Enable TS encryption tracing
_tsm_connect_string TSM test connect string
_tsm_disable_auto_cleanup 1 Disable TSM auto cleanup actions
_tts_allow_charset_mismatch FALSE allow plugging in a tablespace with an incompatible character set
_two_pass TRUE enable two-pass thread recovery
_two_pass_reverse_polish_enabled TRUE uses two-pass reverse polish alg. to generate canonical forms
_uga_cga_large_extent_size 262144 UGA/CGA large extent size
_ultrafast_latch_statistics TRUE maintain fast-path statistics for ultrafast latches
_undo_autotune TRUE enable auto tuning of undo_retention
_undo_block_compression TRUE enable undo block compression
_undo_debug_mode 0 debug flag for undo related operations
_undo_debug_usage 0 invoke undo usage functions for testing
_union_rewrite_for_gs YES_GSET_MVS expand queries with GSets into UNIONs for rewrite
_unnest_subquery TRUE enables unnesting of correlated subqueries
_unused_block_compression TRUE enable unused block compression
_update_datafile_headers_with_space_information FALSE user requested update of datafile headers of locally managed datafiles with space information
_use_best_fit FALSE use best fit to allocate space
_use_column_stats_for_function TRUE enable the use of column statistics for DDP functions
_use_ism TRUE Enable Shared Page Tables – ISM
_use_ism_for_pga TRUE Use ISM for allocating large extents
_use_nosegment_indexes FALSE use nosegment indexes in explain plan
_use_realfree_heap TRUE use real-free based allocator for PGA memory
_use_seq_process_cache TRUE whether to use process local seq cache
_use_vector_post TRUE use vector post
_use_zero_copy_io TRUE Should network vector IO interface be used for data transfer
_validate_flashback_database FALSE Scan database to validate result of flashback database
_vendor_lib_loc Vendor library search root directory
_verify_flashback_redo TRUE Verify that the redo logs needed for flashback are available
_verify_undo_quota FALSE TRUE – verify consistency of undo quota statistics
_very_large_partitioned_table 1024 very_large_partitioned_table
_virtual_column_overload_allowed TRUE overload virtual columns expression
_wait_breakup_threshold_csecs 600 Wait breakup threshold (in centiseconds)
_wait_breakup_time_csecs 300 Wait breakup time (in centiseconds)
_wait_for_sync TRUE wait for sync on commit MUST BE ALWAYS TRUE
_walk_insert_threshold 0 maximum number of unusable blocks to walk across freelist
_watchpoint_on FALSE is the watchpointing feature turned on?
_wcr_control 0 Oracle internal test WCR parameter used ONLY for testing!
_windowfunc_optimization_settings 0 settings for window function optimizations
_with_subquery OPTIMIZER WITH subquery transformation
_write_clones 3 write clones flag
_xpl_peeked_binds_log_size 8192 maximum bytes for logging peeked bind values for V$SQL_PLAN (0 = OFF)
_xpl_trace 0 Explain Plan tracing parameter
_xsolapi_auto_materialization_bound 20 OLAP API lower bound for auto materialization.
_xsolapi_auto_materialization_type PRED_AND_RC OLAP API behavior for auto materialization
_xsolapi_build_trace FALSE OLAP API output build info to trace file
_xsolapi_debug_output SUPPRESS OLAP API debug output disposition
_xsolapi_densify_cubes TABULAR OLAP API cube densification
_xsolapi_dimension_group_creation OVERFETCH OLAP API symmetric overfetch
_xsolapi_dml_trace FALSE OLAP API output dml commands and expressions to trace file
_xsolapi_fetch_type PARTIAL OLAP API fetch type
_xsolapi_fix_vptrs FALSE OLAP API Enable vptr fixing logic in shared server mode
_xsolapi_generate_with_clause FALSE OLAP API generates WITH clause?
_xsolapi_hierarchy_value_type unique OLAP API hierarchy value type
_xsolapi_load_at_process_start NEVER When to load OLAP API library at server process start
_xsolapi_materialization_rowcache_min_rows_for_use 1 OLAP API min number of rows required to use rowcache in query materialization
_xsolapi_materialize_sources TRUE OLAP API Enable source materialization
_xsolapi_metadata_reader_mode 11_ONLY OLAP API metadata reader mode
_xsolapi_odbo_mode FALSE OLAP API uses ODBO mode?
_xsolapi_opt_aw_position TRUE OLAP API enables AW position and count optimization?
_xsolapi_optimize_suppression TRUE OLAP API optimizes suppressions?
_xsolapi_precompute_subquery TRUE OLAP API precomputes subqueries?
_xsolapi_remove_columns_for_materialization TRUE OLAP API removes columns for materialization?
_xsolapi_set_nls TRUE OLAP API sets NLS?
_xsolapi_share_executors TRUE OLAP API share executors?
_xsolapi_source_trace FALSE OLAP API output Source definitions to trace file
_xsolapi_sql_all_multi_join_non_base_hints OLAP API multi-join non-base hints
_xsolapi_sql_all_non_base_hints OLAP API non-base hints
_xsolapi_sql_auto_dimension_hints FALSE OLAP API enable automatic dimension hints
_xsolapi_sql_auto_measure_hints TRUE OLAP API enable automatic measure hints
_xsolapi_sql_dimension_hints OLAP API dimension hints
_xsolapi_sql_enable_aw_join TRUE OLAP API enables AW join?
_xsolapi_sql_enable_aw_qdr_merge TRUE OLAP API enables AW QDR merge?
_xsolapi_sql_hints OLAP API generic hints
_xsolapi_sql_measure_hints OLAP API measure hints
_xsolapi_sql_minus_threshold 1000 OLAP API SQL MINUS threshold
_xsolapi_sql_optimize TRUE OLAP API enable optimization
_xsolapi_sql_prepare_stmt_cache_size 16 OLAP API prepare statement cache size
_xsolapi_sql_remove_columns TRUE OLAP API enable remove unused columns optimizations
_xsolapi_sql_result_set_cache_size 32 OLAP API result set cache size
_xsolapi_sql_symmetric_predicate TRUE OLAP API enable symmetric predicate for dimension groups
_xsolapi_sql_top_dimension_hints OLAP API top dimension hints
_xsolapi_sql_top_measure_hints OLAP API top measure hints
_xsolapi_sql_use_bind_variables TRUE OLAP API enable bind variables optimization
_xsolapi_stringify_order_levels FALSE OLAP API stringifies order levels?
_xsolapi_support_mtm FALSE OLAP API MTM mapping classes supported?
_xsolapi_suppression_aw_mask_threshold 1000 OLAP API suppression AW mask threshold
_xsolapi_suppression_chunk_size 4000 OLAP API suppression chunk size
_xsolapi_use_models TRUE OLAP API uses models?
_xsolapi_use_olap_dml TRUE OLAP API uses OLAP DML?
_xsolapi_use_olap_dml_for_rank TRUE OLAP API uses OLAP DML for rank?
_xt_coverage none external tables code coverage parameter
_xt_trace none external tables trace parameter
_xtbuffer_size 0 buffer size in KB needed for populate/query operation
_xtts_allow_pre10 FALSE allow cross platform for pre10 compatible tablespace
_xtts_set_platform_info FALSE set cross platform info during file header read
_yield_check_interval 100000 interval to check whether actses should yield

——————————————————————————————–

Related links:

HOME

ORA-12528: TNS:listener: all appropriate instances are blocking new connection

I  got this error when I was configuring 11g Dataguard using active dataguard.
 On my standby machine, I had configured listener on port 1521 in grid home and had registered the service for standby database (dg02) with this listener statically. In my primary tnsnames.ora , I had made an entry for dg02 on standby machine on port 1521.
While creating standby database,duplicate database command was being issued from database home on primary and it expected listener to be running in database home on standby machine and hence giving above error.
I created another listener on port 1522 in database home on standby machine and registered the service for standby database (dg02) with this listener statically. In my primary tnsnames.ora ,  I modified the entry for dg02 on standby machine on port 1522.
This resolved my problem.Hence, it can be concluded   that if we have two machines say node1 and node2.
On node1, I have a database db1  which I want to access from node2.
I have a listener on port 1521 in grid home.On node2 I create an entry in tnsnames.ora for db1 available on node1  on port 1521.Now If I try to connect to db1 from node2, I will get above error. Since tnsnames.ora entry is created in
database home,  while connecting to node1, it is expected that listener is also available in database home
on node1.Hence, if I move the listener on node1  from grid home  to database home, the problem will be resolved
or I can
–  create another listener on node1 in database home on port 1522(say) ,
– Make service for db1 available at port 1522
– Register this service statically with listener on port 1522
– create an entry in tnsnames.ora on node2 for db1 available on node1 at port 1522

References:
————————————————————————————————-

11g Dataguard Index

Warning: ORA-16792: configurable property value is inconsistent with database setting

I got this error on DGMGRL command line when I was configuring 11g Dataguard and I issued

DGMGRL> show configuration;

 

This error was coming as my standby database was started using pfile,

I shutdown the standby database and restarted it using spfile and the problem was resolved.

Another time I got this error was when I changed the IP address of the machine after dataguard had been configured. I deleted the tnsnames.ora and listener.ora entries and recreated them. Then it started working.

———————————————————————————————————-

Related links:
                                                                ————

RECOVER VOTING DISK – SCENARIO-II

In this post, I will demonstrate how to recover voting disk in case we lose 2 out of 3 copies of voting disk.
In this case, voting disk will be recovered using surviving copy of voting disk.

Current scenario:
3 copies of voting disk are present in test diskgroup  on disks ASMDIsk010, ASMDIsk011, ASMDIsk012.
We will corrupt two disks ASMDIsk010, ASMDIsk011 so that ASMDISK012 still has a copy of the voting disk. We will restore voting disk to another diskgroup using the only valid copy we have.

Let’s start ...
– Currently, we have 3 voting disks. AT least 2 should be accessible for the clusterware to work. Let us corrupt one of the voting disks and check if clusterware still continues

– FIND OUT LOCATION OF VOTEDISK
[grid@host01 cssd]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
–  —–    —————–                ——— ———
 1. ONLINE   00ce3c95c6534f44bfffa645a3430bc3 (ORCL:ASMDISK012) [TEST]
 2. ONLINE   a3751063aec14f8ebfe8fb89fccf45ff (ORCL:ASMDISK010) [TEST]
 3. ONLINE   0fce89ac35834f99bff7b04ccaaa8006 (ORCL:ASMDISK011) [TEST]
Located 3 voting disk(s).

– FIND OUT THE NO. OF DISKS IN test DG (CONTAINING VOTEDISK)
ASMCMD> lsdsk -G test
Path
ORCL:ASMDISK010
ORCL:ASMDISK011
ORCL:ASMDISK012

-- Let us corrupt ASMDISK010
– bs = blocksize = 4096
– count = # of blocks overwritten = 1000000 (~1M)
– total no. of bytes corrupted = 4096 * 1000000
                                 (~4096M = size of one partition)
#dd if=/dev/zero of=/dev/soracleasm/disks/ASMDISK010 bs=4096 count=1000000
CHECK THAT C/W KEEPS RUNNING AS 2 VOTING DISKS (MORE THAN HALF OF
  VOTING DISKS) STILL AVAILABLE
#crsctl stat res -t
-- Now let us corrupt ASMDISK011
– bs = blocksize = 4096
– count = # of blocks overwritten = 1000000 (~1M)
– total no. of bytes corrupted = 4096 * 1000000
                                 (~4096M = size of one partition)
#dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK011 bs=4096 count=1000000
Here, I was expecting clusterware to stop as only 1 voting disk ( < half of total(3)) were available but surprisingly clusterware kept running. I event waited for quite some time but to no avail.  I would be glad if someone can give more input  on this.
Finally, I stopped clusterware and tried to restart it. It was not able to restart.

- CHECK THAT C/W IS NOT RUNNING
#crsctl stat res -t
– Now we have one copy of the voting disk on one of the disks in test diskgroup we can use that copy to get voting disk back. Since voting disk can’t be restored back to test diskgroup as disks in test have been corrupted, we will restore voting disk to data diskgroup .

-- RECOVER VOTING DISK –
– To move voting disk to data diskgroup, ASM instance should be up and for ASM instance to be up, CRS should be up. Hence we will
     – stop crs on all the nodes
     – start crs in exclusive mode on one of the nodes (host01)
     – start asm instance on host01 using pfile (since spfile of ASM instance is on ASM)
     – move voting disk to data diskgroup.
     – drop test diskgroup (it will allow as it does not have voting disk any more)
     – stop crs on host01(was running in exclusive mode)
     – restart crs on host01
     – start crs on rest of the nodes
     – start cluster on all the nodes

-- IMPLEMENTATION –
    - stop crs on all the nodes(if it does not stop, kill ohasd process and retry)
root@hostn# crsctl stop crs -f
     – start crs in exclusive mode on one of the nodes (host01)
root@host01# crsctl start crs -excl
     - start asm instance on host01 using pfile (since spfile is on ASM)
grid@host01$ echo INSTANCE_TYPE=ASM >> /u01/app/oracle/init+ASM1.ora 
             chown grid:oinstall /u01/app/oracle/init+ASM1.ora 
SQL>startup pfile='/u01/app/oracle/init+ASM1.ora';
- Check that data diskgroup is mounted on host01. if not, mount it.
ASMCMD>lsdg 
        mount data
 - move voting disk to data diskgroup.  voting disk will be automatically recovered using surviving copy of voting disk.
root@host01#crsctl replace votedisk +data
     - drop test diskgroup (it will allow as it does not have voting disk)
SQL>drop diskgroup test force including contents;
     – stop crs on host01(was running in exclusive mode)
root@host01#crsctl stop crs
     – restart crs on host01
root@host01#crsctl start crs
     - start crs on rest of the nodes (if it does not start, kill ohasd process and retry)
root@host02#crsctl start crs 
root@host03#crsctl start crs 
     – start cluster on all the nodes and check that it is running
root@host01#crsctl start cluster -all                                        crsctl stat res -t
————————————————————————————————
Related links:

                                                           ———————

 

RECOVER VOTING DISK – SCENARIO-I

In this post, I will demonstrate how to recover voting disk in case we lose the only copy of voting disk.Voting disk will be automatically recovered using latest available backup of OCR.

 

Current scenario:
The only copy of the voting disk is  present in test diskgroup   on disk ASMDIsk010
We will corrupt ASMDIsk011 so that we lose the only copy of the voting disk.
We will restore voting disk to another diskgroup using the OCR.

 

Let’s start …
– Currently, we have 1 voting disk. Let us corrupt it and check if clusterware still continues
– FIND OUT LOCATION OF VOTEDISK
[grid@host01 cssd]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
–  —–    —————–                ——— ———
 1. ONLINE   00ce3c95c6534f44bfffa645a3430bc3 (ORCL:ASMDISK010) [TEST]

 

– FIND OUT THE NO. OF DISKS IN test DG (CONTAINING VOTEDISK)
ASMCMD> lsdsk -G test
Path
ORCL:ASMDISK010

 

– Let us corrupt ASMDISK010

— bs = blocksize = 4096

— count = # of blocks overwritten = 1000000 (~1M)

– total no. of bytes corrupted = 4096 * 1000000
                                 (~4096M = size of one partition)
#dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK010 bs=4096 count=1000000
Here, I was expecting clusterware to stop as the  only  voting disk was not  available but surprisingly clusterware kept running. I even waited for quite some time but to no avail.  I would be glad if someone can give more input  on this.
Finally, I stopped clusterware and tried to restart it. It was not able to restart.

– Reboot all the nodes and note that cluster ware does not start as voting disk is not accessible.

#crsctl stat res -t
– Now since voting disk can’t be restored back to test diskgroup as disk in test has been corrupted,
   we will create another diskgroup votedg where we will restore voting disk.

 

RECOVER VOTING DISK
– To move voting disk to votedg diskgroup, ASM instance should be up and for ASM
   instance to be up, CRS should be up. Hence we will
     – stop crs on all the nodes
     – start crs in exclusive mode on one of the nodes (host01)
     – start asm instance on host01 using pfile (since spfile of ASM instance is on ASM)
     – create a new diskgroup votedg
     – move voting disk to votedg  diskgroup
     – stop crs on host01(was running in exclusive mode)
     – restart crs on host01
     – start crs on rest of the nodes
     – start cluster on all the nodes

 

– IMPLEMENTATION –
    - stop crs on all the nodes(if it does not stop, kill ohasd process and retry)
root@hostn# crsctl stop crs -f
     – start crs in exclusive mode on one of the nodes (host01)
root@host01# crsctl start crs -excl
     – start asm instance on host01 using pfile 
grid@host01$ echo INSTANCE_TYPE=ASM >> /u01/app/oracle/init+ASM1.ora 
             chown grid:oinstall /u01/app/oracle/init+ASM1.ora 
 SQL>startup pfile='/u01/app/oracle/init+ASM1.ora';
- create a new diskgroup votedg

– move voting disk to data diskgroup – voting disk is automaticaly recovered using latest available backup of OCR.
root@host01#crsctl replace votedisk +votedg
     – stop crs on host01(was running in exclusive mode)
root@host01#crsctl stop crs
     – restart crs on host01
root@host01#crsctl start crs
     – start crs on rest of the nodes (if it does not start, kill ohasd process and retry)
root@host02#crsctl start crs 
root@host03#crsctl start crs
     – start cluster on all the nodes and check that it is running
root@host01#crsctl start cluster -all 
            crsctl stat res -t

I hope this post was useful.
Regards

————————————————————————————————

                                                 ——————–

NEED FOR VIP IN RAC

In this post, I will demonstrate why do we need VIPs in RAC for failover.If the client request for connection goes to Physical IP of a node and the node is down, client will get notified about after network time out only (which could be as long as 10 minutes). After the client gets this information, he tries to connect to the alternative address. Alternatively, if the client request goes to Virtual IP of the node (VIP) and the node is down, VIP will automatically failover to one of the surviving nodes without waitinig for the network timeout. The listener on the new node listens only to the native VIP on that node and not to the relocated VIP. Any connection request to the relocated VIP receives ORA-12541 No listener error. On receiving the error, the client will try the next address to connect to the database. Hence, VIPs are needed in a RAC for quick failover of client connection requests.
Let’s demonstrate :
Current Scenario:
Two node setup
Nodes : host01, host02
RAC database : orcl

Overview :
— Find out VIPs of both the nodes
— Add entries in tnsnames.ora for two aliases : SIP and SVIP
SIP sends request to physical IP’s of the nodes
SVIP sends request to virtual IP’s (VIP’s) of the nodes
— Shutdown host02

— Try to connect using SIP – waits for network timeout before connecting to host01

– Try to connect using SVIP –  connects to host01 immediately without waiting  for network timeout

Implementation:

- Find out VIPs of both the nodes
[grid@host01 ~]$ srvctl config vip -n host01

VIP exists.:host01
VIP exists.: /192.9.201.254/192.9.201.254/255.255.255.0/eth0

[grid@host01 ~]$ srvctl config vip -n host02

VIP exists.:host02
VIP exists.: /192.9.201.250/192.9.201.250/255.255.255.0/eth0

– Add entries in tnsnames.ora for

– SVIP : An alias for orcl service using which, the client will try to
.  connect to  host02-vip (192.9.201.250 ) first.
. If host02 is down, host02-vip will failover to the other host (host01)
and on receiving No listener error, client will get try connecting to host01

– SIP : An alias for orcl service using which, the client will try to
.  connect to  host02 (Physical IP) first.
. If host02 is down, client gets notified after network time out
. Client tries and gets connected to host01 (Physical IP)

[grid@host01 admin]$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

SVIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.201.250 )(PORT = 1521)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.201.254 )(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

SIP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

– Shutdown node host02

[root@host02]#init 0

- Try to connect to the database using service sip
— Note that as host02 is down, client waits for network timeout before trying  connection to host01 and time taken to connect to host01 is 25 seconds (23:42:57  – 23:42:32 )

SQL> set time on
 23:42:32 SQL>  conn system/sys@sip
 Connected.
 23:42:57 SQL>

– Try to connect to the database using service svip
— Note that as host02 is down, host02 VIP immediately fails over to host01 and time taken to connect to host01 is of the order of milliseconds (23:43:38  – 23:43:38 )

23:43:38 SQL> conn system/sys@svip
 Connected.
 23:43:38 SQL>

I hope this demonstration was useful.
Your comments and suggestions are always welcome !!!

References:

Oracle RAC VIP and ARP Primer

—————————————————————————————

 

11g R2 RAC : SERVICES

 

In this post, I will explain the concept of services and their benefits in 11g R2 RAC.

What is a service?

A service is a logical grouping of sessions performing similar kind of work. They  enable you to group database workloads and route work to the optimal instances that are assigned to offer the service.It is recommended that all the users who connect using a service have the same service-level requirements. We can use services to manage workloads or a group of applications. For example , all on-line users can use one service whereas batch users can use another service and reporting can use yet another service.

Benefits of services:

– Services make the work more manageable, measurable, tunable and recoverable.

– Services provide an abstraction layer and permit clients and middle tiers to access required data from the database independent of where the instances reside.

– Dynamic: As the load increases/reduces, the number of instances supporting a service can be increased/decreased accordingly.

- High availability : In case an instance crashes, the services supported by it failover to another surviving instance.

- Tuning and Monitoring Aid

. Tracing can be enabled for all the sessions who connect using a service: This can be used to identify the culprit SQL statements issued by the users connected via a service.  . Performance statistics are collected and aggregated by service: This can be used to find out the performance of all the sessions belonging to a service.

. Two Performance thresholds can be specified for a service: SERVICE_ELAPSED_TIME and SERVICE_CPU_TIME: The AWR monitors the service time and CPU time and publishes alerts when the performance esceeds the thresholds. In response to these alerts, you can change the priority of a job, stop overloaded process , or relocate, expand, shrink , start or stop a service.

- Resource Management Aid : We can bind resource consumer groups to services. The users who connect using a service will  get the resources as per the allocation specified for the respective consumer group in the active resource plan. For example, if OLTP and BATCH services are defined to run on the same instance and our requirement is as follows:

. During day time OLTP users should get more resources whereas BATCH users should get less resources

  . During night, BATCH users should get more resources whereas OLTP users should get less resources

We can have two consumer groups : OLTP_GRP mapped to OLTP service and

                                                                          BATCH_GRP mapped to BATCH service
so that all users who connect using OLTP service are assigned OLTP_GRP and all users who connect using BATCH service are assigned BATCH_GRP .

We can have two resource plans :

  . DAY_PLAN in which OLTP_GRP is given more resources and BATCH_GRP is given less resources.
  . NIGHT_PLAN in which BATCH_GRP is given more resources and OLTP_GRP is given less resources.
 
- Job Control Aid : We can associate jobs with a job class and job class can be mapped to service.

– If service is mapped to a consumer group, the jobs will get the resources as per the allocation specified for the respective consumer group in the active resource plan. If  jobs of a job class are taking more time than desired, we can change the consumer group mapping of the respective service to one with more resources.

– If the instance running the jobs of a job class crashes, the services on the crashed instance fail over to another instance and all the jobs of the job class mapped to thst service also failover along with it.

i.e.
       Job1  —–+
       Job2  ——|——– Jobclass1 —— Service1
       Job3  ——+                                  |
                                                  Consumer group
                                                          |
                                                 Resource Plan

– Using Transparent Application Failover:  When a node crashes, all the services running on the instance hosted by that node fail over to another instance. From now onwards, the users who try to connect using failed over services will connect to the instances supporting that service. But what happens to the sessions who were already connected using that service at the time of crash? That is decided by the TAF (Transparent application Failover) policy which can be defined for the service. We can have 3 types of TAF policies : Basic, Preconnect and None.

 
BASIC failover. In this approach, the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created.

PRECONNECT failover. In this approach, the application simultaneously connects to both a primary and a backup node. This offers faster failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.

  We can also specify FAILOVER_TYPE for a service i.e. what kind of activity going on in the connected sessions will be resumed after the failover .  It can be Select or Session.

SELECT failover. With SELECT failover, Oracle Net keeps track of all SELECT statements issued during the transaction, tracking how many rows have been fetched back to the client for each cursor associated with a SELECT statement. If the connection to the instance is lost, Oracle Net establishes a connection to another Oracle RAC node and re-executes the SELECT statements, repositioning the cursors so the client can continue fetching rows as if nothing has happened. The SELECT failover approach is best for data warehouse systems that perform complex and time-consuming transactions.

 SESSION failover. When the connection to an instance is lost, SESSION failover results only in the establishment of a new connection to another Oracle RAC node; any work in progress is lost. SESSION failover is ideal for online transaction processing (OLTP) systems, where transactions are small.
 
The following types of transactions do not automatically fail over and must be restarted by TAF:
  • Transactional statements. Transactions involving INSERT, UPDATE, or DELETE statements are not supported by TAF.
  •  ALTER SESSION statements. ALTER SESSION and SQL*Plus SET statements do not fail over.
  • Transactions using temporary segments in the TEMP tablespace and global temporary tables do not fail over.
  • PL/SQL package states. PL/SQL package states are lost during failover.
– Connection Load balancing : If we have multiple instances supporting a service, We can spread user connections across all those instances. For each service, you can define the method that you want the listener to use for load balancing by setting the connection load balancing goal, CLB_GOAL. It can have two values : CLB_GOAL_LONG(default) and CLB_GOAL_SHORT.
 
Where’s the configuration of services stored?
Data Dictionary (Bulk of service configuration)
DBA_SERVICES
DBA_RSRC_GROUP_MAPPINGS
DBA_THRESHOLDS
DBA_SCHEDULER_JOB_CLASSES
TNSNAMES.ORA
Server-side
Entries for LOCAL_LISTENER and REMOTE_LISTENER
Client-side (in tnsnames.ora)
CONNECT_DATA
Special entries for PRECONNECT services
Resource profile in Cluster Registry (OCR)
Resource
Start/stop script
Dependencies
Restart policy
Stringpairs
Instance list
Preferred/available instances
Enabled/disabled
TAF policy
Initialization Parameters
LOCAL_LISTENER
REMOTE_LISTENERS
DB_DOMAIN
DISPATCHERS
STATISTICS_LEVEL
References:
—————————————————————————————————–
Related links:

Home
11G R2 RAC Index
Instance Recovery In RAC
Job Failover In RAC
Service Created Using EM Does Not Update DD or OCR