oracle重建spfile,【11g】【10g】【实验】spfile文件的恢复（from memory;）

1.模拟删除数据库正在使用的spfile

10g中：

ora10g@linux5 /oracle/u01/app/oracle/product/10.2.0/db_1/dbs$ mv

spfileora10g.ora spfileora10g.ora_move

11g中：

ora11g@RHEL53 /oracle/u01/app/oracle/product/1101/db/dbs$ mv

spfileora11g.ora spfileora11g.ora_move

2.修改数据库参数，操作范围spfile，这里会看到10g和11g数据库人性化的提示不能修改，提示找不到系统使用到的spfile

10g中：

sys@ora10g> alter system set

sga_max_size=200m scope=spfile;

alter system set sga_max_size=200m

scope=spfile

*

ERROR at line 1:

ORA-01565: error in identifying file

'/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/spfileora10g.ora'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or

directory

Additional information: 3

11g中：

sys@ora11g> alter system set

sga_max_size=200m scope=spfile;

alter system set sga_max_size=200m

scope=spfile

*

ERROR at line 1:

ORA-01565: error in identifying file

'/oracle/u01/app/oracle/product/1101/db/dbs/spfileora11g.ora'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

3.使用11g新特性恢复spfile

10g中使用时提示不存在这个命令，说明10g中不支持这样的操作：

sys@ora10g> create spfile from

memory;

create spfile from memory

*

ERROR at line 1:

ORA-00922: missing or invalid option

11g中：

直接创建spfile会报错

sys@ora11g> create spfile from

memory;

create spfile from memory

*

ERROR at line 1:

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

instance

采用迂回的方式创建

在spfile相同的目录下创建spfile_temp.ora这个名字的文件

sys@ora11g>create

spfile =

'/oracle/u01/app/oracle/product/1101/db/dbs/spfile_temp.ora' from

memory;

File created.

将创建的文件修改为系统缺省的spfile文件

ora11g@RHEL53

/oracle/u01/app/oracle/product/1101/db/dbs$mv

spfile_temp.ora spfileora11g.ora

尝试修改数据库参数，成功！

sys@ora11g>alter

system set sga_max_size=200m

scope=spfile;

System altered.

4.使用11g的这个新特性创建出来的spfile包含的内容包含大量的隐含参数的内容，以下是11g中默认spfile和通过内存创建出来的spfile的比较

1).默认情况下的spfile内容如下：

ora11g@RHEL53

/oracle/u01/app/oracle/product/1101/db/dbs$strings

spfileora11g.ora_move

ora11g.__db_cache_size=20971520

ora11g.__java_pool_size=4194304

ora11g.__large_pool_size=4194304

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

environment

ora11g.__pga_aggregate_target=146800640

ora11g.__sga_target=167772160

ora11g.__shared_io_pool_size=0

ora11g.__shared_pool_size=125829120

ora11g.__streams_pool_size=8388608

*.audit_file_dest='/oracle/u01/app/oracle/admin/ora11g/adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='/oracle/u02/orad

ata/ora11g/control01.ctl','/oracle/u02/oradata/ora11g/control02.ctl','/oracle/u02/oradata/ora11g/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ora11g'

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

*.db_recovery_file_dest_size=4294967296

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

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=314572800

*.open_cursors=300

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

2).通过内存创建出来的spfile内容如下：

ora11g@RHEL53

/oracle/u01/app/oracle/product/1101/db/dbs$strings

spfileora11g.ora

*.__db_cache_size=20M

*.__java_pool_size=4M

*.__large_pool_size=4M

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

environment

*.__pga_aggregate_target=140M

*.__sga_target=160M

*.__shared_io_pool_size=0

*.__shared_pool_size=120M

*.__streams_pool_size=8M

*._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','60','120','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'

*._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='/oracle/u01/app/oracle/admin/ora11g/adump'

*.audit_trail='DB'

*.compatible='11.1.0.0.0'

*.control_files='/oracle/u02/oradata/ora11g/control01.ctl','/oracle/u02/oradata/ora11g/control02.ctl','/oracle/u02/oradata/ora11g/control03.ctl'

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

*.db_block_size=8192

*.db_domain=''

*.db_name='ora11g'

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

*.db_recovery_file_dest_size=4G

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

*.log_archive_format='%t_%s_%r.dbf'

*.log_buffer=6174208# log buffer update

*.memory_target=300M

*.open_cursors=300

*.optimizer_dynamic_sampling=2

*.optimizer_mode='ALL_ROWS'

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

init.ora

*.processes=500

*.query_rewrite_enabled='TRUE'

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=768K

*.sga_max_size=314572800

*.skip_unusable_indexes=TRUE

*.undo_tablespace='UNDOTBS1'

5.附录：11g官方文档中关于spfile创建语句的描述

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6016.htm#i2072626