Pfile and spfile troubleshooting for Oraclek Databases

Welcome to the Oracle community forum and interact with 2 million technical staff.

Welcome to the Oracle community forum and interact with 2 million technical staff> go to _ dimension_skip_null = TRUE _ blank = TRUE _ enable_type_dep_selecti.pdf = TRUE _ fast_full_scan_enabled = TRUE _ blank = TRUE _ gby_hash_aggregation_en

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

_ Dimension_skip_null = TRUE

_ Eliminate_common_subexpr = TRUE

_ Enable_type_dep_selecti.pdf = 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 = '000000'

_ Ksb_restart_policy_times = '000000' # 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_selecti.pdf = 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_selecti.pdf = 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_transiti.pdf = 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_selecti.pdf = 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 = 'dest'

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

_ Effect_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/track' # 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 = 688 M

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 = 'clusive'

Resource_manager_plan = 'scheduler [0x318F]: DEFAULT_MAINTENANCE_PLAN'

Result_cache_max_size = 1792 K

Skip_unusable_indexes = TRUE

Standby_file_management = 'auto'

Undo_tablespace = 'undotbs1'

User_dump_dest = '/u01/app/oracle/diag/rdbms/dave_st/dave/track' # Deprecate parameter

From the above results, I also know why. To create a PFILE from the memory, some implicit parameters will also be written in.

The default value here is:

_ Compression_compatibility = '11. 2.0.0.0'

The default value here is:

_ Compression_compatibility = '11. 2.0.1.0'

This parameter is a friend database boot Report: ORA-00600: internal errorcode, arguments: [kck_rls_check must use (11,0, 0) or lower] Error cause.

Change this parameter to _ compression_compatibility = "11.2.0". The database can be started normally.

In fact, this problem is a small problem left over from Oracle 11g. This problem occurs in 11.2.0.1 and 11.0.2.0.2.

In Oracle 11.2.0.3, the default value of this _ compression_compatibility parameter is 11.2.0.0.0, so when you create a pfile from memory, there will be no ORA-600 error reported.

This is also the reason why I did not report an error when creating and starting a database. My database version is 11.2.0.3.

Another point is that when we use the memory to create database parameters, we can delete all the preceding implicit parameters. Which parameters are too cumbersome and the default values are good.

[1] [2]