Oracle 11g数据库参数文件误删除恢复(2)

Home / Database / Oracle Database Online Documentation 11g?Release 2 (11.2) / Database Administration/SQL Language Reference/What's New in the SQL Language Reference?

可以找到

CREATE PFILE has new syntax that lets you create a parameter file from current system-wide parameter settings.

CREATE SPFILE has new syntax that lets you create a system parameter file from current system-wide parameter settings.

点击create pfile或者create spfile进入链接页面，可以找到SQL命令：

CREATE PFILE [= 'pfile_name' ]

FROM { SPFILE [= 'spfile_name']

| MEMORY

} ;

CREATE SPFILE [= 'spfile_name' ]

FROM { PFILE [= 'pfile_name' ]

| MEMORY

} ;

这里执行恢复语句：

SYS@cams>create spfile from memory;

create spfile from memory

*

ERROR at line 1:

ORA-32002: cannot create SPFILE already being used by the instance

SYS@cams>create pfile from memory;

File created.

SYS@cams>create spfile='spfilecams1.ora' from memory;

File created.

查看恢复后的spfile和pfile文件：

[oracle@ora11g dbs]$ strings spfilecams1.ora

*.__db_cache_size=320M

*.__java_pool_size=4M

*.__large_pool_size=12M

*.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment

*.__pga_aggregate_target=260M

*.__sga_target=484M

*.__shared_io_pool_size=0

*.__shared_pool_size=136M

*.__streams_pool_size=0

*._aggregation_optimization_settings=0

*._always_anti_join='CHOOSE'

*._always_semi_join='CHOOSE'

*._and_pruning_enabled=TRUE

*._b_tree_bitmap_plans=TRUE

*._bloom_filter_enabled=TRUE

*._bloom_folding_enabled=TRUE

*._bloom_pru

ning_enabled=TRUE

*._complex_view_merging=TRUE

*._compression_compatibility='11.2.0.4.0'

*._connect_by_use_union_all='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_an

ti_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_estimat

e=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_ba

sed=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=T

*._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_ou

ter_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

*._optimiz

er_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=TRU

*._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.4'

*._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_imple

mentation_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'

*._sql

tune_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/cams/adump'

*.audit_trail='DB'

*.background_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata

/cams/control01.ctl'

*.control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'

*.core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_name='cams'

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

*.db_recovery_file_dest_size=4182M

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'

*.job_queue_processes=1000

*.log_buffer=6520832 # log buffer update

*.memory_target=744M

*.open_cur

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

*.result_cache_max_size=1920K

*.skip_unusable_indexes=TRUE

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter

[oracle@ora11g dbs]$ cat initcams.ora

# Oracle init.ora parameter file generated by instance cams on 08/02/2017 13:36:21

__db_cache_size=320M

__java_pool_size=4M

__large_pool_size=12M

__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment

__pga_aggregate_target=260M

__sga_target=484M

__shared_io_pool_size=0

__shared_pool_size=136M

__streams_pool_size=0

_aggregation_optimization_settings=0

_always_anti_join='CHOOSE'

_always_semi_join='CHOOSE'

_and_pruning_enabled=TRUE

_b_tree_bitmap_plans=TRUE

_bloom_filter_enabled=TRUE

_bloom_folding_enabled=TRUE

_bloom_pruning_enabled=TRUE

_complex_view_merging=TRUE

_compression_compatibility='11.2.0.4.0'

_connect_by_use_union_all='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_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.4'

_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/cams/adump'

audit_trail='DB'

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

compatible='11.2.0.4.0'

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

control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'

core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'

db_block_size=8192

db_domain=''

db_name='cams'

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

db_recovery_file_dest_size=4182M

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'

job_queue_processes=1000

log_buffer=6520832 # log buffer update

memory_target=744M

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'

result_cache_max_size=1920K

skip_unusable_indexes=TRUE

undo_tablespace='UNDOTBS1'

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

[oracle@ora11g dbs]$

第七步：重启数据库，检查恢复后的参数文件能否正常使用，并进行分析

SYS@cams>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@cams>host mv /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams1.ora /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora

SYS@cams>startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 776646656 bytes

Fixed Size        2257272 bytes

Variable Size        482348680 bytes

Database Buffers    285212672 bytes

Redo Buffers        6828032 bytes

Database mounted.

Database opened.