欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oraclek 数据库的pfile 和spfile故障解决

程序员文章站 2022-05-28 21:57:19
...

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 _dimension_skip_null=TRUE _eliminate_common_subexpr=TRUE _enable_type_dep_selectivity=TRUE _fast_full_scan_enabled=TRUE _first_k_rows_dynamic_proration=TRUE _gby_hash_aggregation_enabl

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

_dimension_skip_null=TRUE

_eliminate_common_subexpr=TRUE

_enable_type_dep_selectivity=TRUE

_fast_full_scan_enabled=TRUE

_first_k_rows_dynamic_proration=TRUE

_gby_hash_aggregation_enabled=TRUE

_generalized_pruning_enabled=TRUE

_globalindex_pnum_filter_enabled=TRUE

_gs_anti_semi_join_allowed=TRUE

_improved_outerjoin_card=TRUE

_improved_row_length_enabled=TRUE

_index_join_enabled=TRUE

_ksb_restart_policy_times='0'

_ksb_restart_policy_times='60'

_ksb_restart_policy_times='120'

_ksb_restart_policy_times='240'# internal update to set default

_left_nested_loops_random=TRUE

_local_communication_costing_enabled=TRUE

_minimal_stats_aggregation=TRUE

_mmv_query_rewrite_enabled=TRUE

_new_initial_join_orders=TRUE

_new_sort_cost_estimate=TRUE

_nlj_batching_enabled=1

_optim_adjust_for_part_skews=TRUE

_optim_enhance_nnull_detection=TRUE

_optim_new_default_join_sel=TRUE

_optim_peek_user_binds=TRUE

_optimizer_adaptive_cursor_sharing=TRUE

_optimizer_better_inlist_costing='ALL'

_optimizer_cbqt_no_size_restriction=TRUE

_optimizer_coalesce_subqueries=TRUE

_optimizer_complex_pred_selectivity=TRUE

_optimizer_compute_index_stats=TRUE

_optimizer_connect_by_combine_sw=TRUE

_optimizer_connect_by_cost_based=TRUE

_optimizer_connect_by_elim_dups=TRUE

_optimizer_correct_sq_selectivity=TRUE

_optimizer_cost_based_transformation='LINEAR'

_optimizer_cost_hjsmj_multimatch=TRUE

_optimizer_cost_model='CHOOSE'

_optimizer_dim_subq_join_sel=TRUE

_optimizer_distinct_agg_transform=TRUE

_optimizer_distinct_elimination=TRUE

_optimizer_distinct_placement=TRUE

_optimizer_eliminate_filtering_join=TRUE

_optimizer_enable_density_improvements=TRUE

_optimizer_enable_extended_stats=TRUE

_optimizer_enable_table_lookup_by_nl=TRUE

_optimizer_enhanced_filter_push=TRUE

_optimizer_extend_jppd_view_types=TRUE

_optimizer_extended_cursor_sharing='UDO'

_optimizer_extended_cursor_sharing_rel='SIMPLE'

_optimizer_extended_stats_usage_control=192

_optimizer_false_filter_pred_pullup=TRUE

_optimizer_fast_access_pred_analysis=TRUE

_optimizer_fast_pred_transitivity=TRUE

_optimizer_filter_pred_pullup=TRUE

_optimizer_fkr_index_cost_bias=10

_optimizer_full_outer_join_to_outer=TRUE

_optimizer_group_by_placement=TRUE

_optimizer_improve_selectivity=TRUE

_optimizer_interleave_jppd=TRUE

_optimizer_join_elimination_enabled=TRUE

_optimizer_join_factorization=TRUE

_optimizer_join_order_control=3

_optimizer_join_sel_sanity_check=TRUE

_optimizer_max_permutations=2000

_optimizer_mode_force=TRUE

_optimizer_multi_level_push_pred=TRUE

_optimizer_native_full_outer_join='FORCE'

_optimizer_new_join_card_computation=TRUE

_optimizer_null_aware_antijoin=TRUE

_optimizer_or_expansion='DEPTH'

_optimizer_order_by_elimination_enabled=TRUE

_optimizer_outer_join_to_inner=TRUE

_optimizer_outer_to_anti_enabled=TRUE

_optimizer_push_down_distinct=0

_optimizer_push_pred_cost_based=TRUE

_optimizer_rownum_bind_default=10

_optimizer_rownum_pred_based_fkr=TRUE

_optimizer_skip_scan_enabled=TRUE

_optimizer_sortmerge_join_inequality=TRUE

_optimizer_squ_bottomup=TRUE

_optimizer_star_tran_in_with_clause=TRUE

_optimizer_system_stats_usage=TRUE

_optimizer_table_expansion=TRUE

_optimizer_transitivity_retain=TRUE

_optimizer_try_st_before_jppd=TRUE

_optimizer_undo_cost_change='11.2.0.3'

_optimizer_unnest_corr_set_subq=TRUE

_optimizer_unnest_disjunctive_subq=TRUE

_optimizer_use_cbqt_star_transformation=TRUE

_optimizer_use_feedback=TRUE

_or_expand_nvl_predicate=TRUE

_ordered_nested_loop=TRUE

_parallel_broadcast_enabled=TRUE

_partition_view_enabled=TRUE

_pivot_implementation_method='CHOOSE'

_pre_rewrite_push_pred=TRUE

_pred_move_around=TRUE

_push_join_predicate=TRUE

_push_join_union_view=TRUE

_push_join_union_view2=TRUE

_px_minus_intersect=TRUE

_px_partition_scan_enabled=TRUE

_px_pwg_enabled=TRUE

_px_ual_serial_input=TRUE

_query_rewrite_setopgrw_enable=TRUE

_remove_aggr_subquery=TRUE

_replace_virtual_columns=TRUE

_right_outer_hash_enable=TRUE

_selfjoin_mv_duplicates=TRUE

_sql_model_unfold_forloops='RUN_TIME'

_sqltune_category_parsed='DEFAULT'# parsed sqltune_category

_subquery_pruning_enabled=TRUE

_subquery_pruning_mv_enabled=FALSE

_table_scan_cost_plus_one=TRUE

_union_rewrite_for_gs='YES_GSET_MVS'

_unnest_subquery=TRUE

_use_column_stats_for_function=TRUE

audit_file_dest='/u01/app/oracle/admin/dave/adump'

audit_trail='DB'

background_dump_dest='/u01/app/oracle/diag/rdbms/dave_st/dave/trace'#Deprecate parameter

compatible='11.2.0.0.0'

control_files='/u01/app/oracle/oradata/dave/control01.ctl'

control_files='/u01/app/oracle/fast_recovery_area/dave/control02.ctl'# Restore Controlfile

core_dump_dest='/u01/app/oracle/diag/rdbms/dave_st/dave/cdump'

db_block_size=8192

db_create_file_dest='/u01/app/oracle/oradata/dave'

db_domain=''

db_file_multiblock_read_count=16

db_file_name_convert='+data/dave/datafile'

db_file_name_convert='/u01/app/oracle/oradata/dave'

db_file_name_convert='+data/dave/tempfile'

db_file_name_convert='/u01/app/oracle/oradata/dave'

db_name='dave'

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=1018937856

db_unique_name='dave_st'

fal_client='dave_st'

fal_server='dave_pd1'

fal_server='dave_pd2'

log_archive_config='dg_config=(dave,dave_st)'

log_archive_dest_1='location=/u01/archvalid_for=(all_logfiles,all_roles) db_unique_name=dave_st'

log_archive_dest_2='service=davevalid_for=(online_logfiles,primary_role) db_unique_name=dave'

log_archive_dest_state_1='enable'

log_archive_dest_state_2='enable'

log_buffer=2736128 # logbuffer update

log_file_name_convert='+fra/dave/onlinelog'

log_file_name_convert='/u01/app/oracle/oradata/dave'

memory_target=688M

open_cursors=300

optimizer_dynamic_sampling=2

optimizer_mode='ALL_ROWS'

plsql_warnings='DISABLE:ALL' #PL/SQL warnings at init.ora

processes=150

query_rewrite_enabled='TRUE'

remote_login_passwordfile='EXCLUSIVE'

resource_manager_plan='SCHEDULER[0x318F]:DEFAULT_MAINTENANCE_PLAN'

result_cache_max_size=1792K

skip_unusable_indexes=TRUE

standby_file_management='auto'

undo_tablespace='UNDOTBS1'

user_dump_dest='/u01/app/oracle/diag/rdbms/dave_st/dave/trace'#Deprecate parameter

从上面的结果,也知道是什么原因了,从内存来创建PFILE,会把一些隐含参数也写进来。

我这里的默认值是:

_compression_compatibility='11.2.0.0.0'

而朋友这里的默认值是:

_compression_compatibility='11.2.0.1.0'

这个参数就是朋友数据库启动报:ORA-00600: internal errorcode, arguments: [kck_rls_check must use (11,0,0,0,0) or lower] 错误的原因。

将这个参数改成:_compression_compatibility="11.2.0",在启动数据库,即可正常。

实际上,这个问题是Oracle 11g 中遗留的一个小问题。 在11.2.0.1 和11.0.2.0.2 中都会有这种问题。

在Oracle 11.2.0.3的版本中,这个_compression_compatibility参数的默认值就是11.2.0.0.0了,那么在从内存创建pfile,就不会在报ORA-600的错误了。

这也是我创建启动没报错,朋友启动创建启动报错的原因,因为我的数据库版本是11.2.0.3的。

还有一点,就是我们使用内存创建数据库参数的时候,可以把前面的隐含参数全部删除掉,哪些参数太繁琐了,使用默认值就好。

[1] [2]

Oraclek 数据库的pfile 和spfile故障解决