１、使用中の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
２、SCOPEが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
３、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；
直接作成すると「使用中です」とのエラーとなる
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.
作成したファイルをデフォルトのファイルに改名する
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.
４、11gのこの新機能で作成したSPFILEはたくさんの隠れパラメータが含まれている。
下記はデフォルトの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/oradata/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'
５、付録：11g正式ドキュメントでSPFILE作成文の説明
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6016.htm#i2072626
Prerequisites
You must have theSYSDBAor theSYSOPERsystem privilege to execute this statement.
You can execute this statement before or after instance startup.
However, if you have already started an instance usingspfile_name,
you cannot specify the samespfile_namein this statement.
Syntax
create_spfile::=
Description of create_spfile.gif follows
CREATE SPFILE [= 'spfile_name' ]
FROM { PFILE [= 'pfile_name' ]
| MEMORY
} ;
