ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ASM实例使用CREATE PFILE FROM MEMORY的bug

ASM实例使用CREATE PFILE FROM MEMORY的bug

原创 Linux操作系统 作者:yangtingkun 时间:2009-06-14 20:38:17 0 删除 编辑

Oracle 11g增加了CREATE PFILE FROM MEMORY命令,这个命令可以方便的将当前实例设置的初始化参数写入初始化参数文件中,不过这个命令对于ASM实例而言,还存在bug

 

 

11g ASM环境的Oracle就可以通过下面了例子再现这个错误:

bash-3.00$ export ORACLE_SID=+ASM1
bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 6 14 20:20:04 2009

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set pages 100 lines 120
SQL> create pfile = '/export/home/oracle/init+ASM1.ora' from memory;

File created.

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup pfile=/export/home/oracle/init+ASM1.ora
ORA-15021: parameter "plsql_warnings" is not valid in asm instance

检查Oracle生成的PFILE文件:

SQL> host more /export/home/oracle/init+ASM1.ora
# Oracle init.ora parameter file generated by instance +ASM1 on 06/14/2009 20:21:43
*.__oracle_base='/data/oracle' # ORACLE_BASE set from environment
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._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_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=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_elimination=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=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=240
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_group_by_placement=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_join_elimination_enabled=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_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_transitivity_retain=TRUE
*._optimizer_undo_cost_change='11.1.0.6'
*._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_pwg_enabled=TRUE
*._px_ual_serial_input=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._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
*.asm_diskgroups='DATA'
*.cluster_database=TRUE
+ASM1.core_dump_dest='/data/oracle/diag/asm/+asm/+ASM1/cdump'
+ASM2.core_dump_dest='/data/oracle/diag/asm/+asm/+ASM2/cdump'
*.diagnostic_dest='/data/oracle'
+ASM1.instance_number=1
+ASM2.instance_number=2
*.instance_type='asm'
*.large_pool_size=12M
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
*.query_rewrite_enabled='TRUE'
*.result_cache_max_size=0
*.skip_unusable_indexes=TRUE

而真正启动ASM实例的PFILE文件仅仅包含了几个参数:

SQL> host more /data/oracle/product/11.1/database/dbs/init+ASM1.ora

 


##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cluster Database
###########################################
cluster_database=true
 
###########################################
# Miscellaneous
###########################################
diagnostic_dest=/data/oracle
instance_type=asm
 
###########################################
# Pools
###########################################
large_pool_size=12M
 
asm_diskgroups='DATA'
 
 
 
 
+ASM2.instance_number=2
+ASM1.instance_number=1

显然,OracleCREATE PFILE FROM MEMORY命令生成的初始化参数包含了一些ASM实例所不支持的参数。

OraclemetalinkBug No. 6742129文档中描述了这个bug,预计将在11.2版本中解决这个错误。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-606351/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10407254