【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

Prerequisites

You must have the SYSDBA or the SYSOPER system privilege to execute this statement. You can execute this statement before or after instance startup. However, if you have already started an instance using spfile_name, you cannot specify the same spfile_name in this statement.

Syntax

create_spfile::=

CREATE SPFILE [= 'spfile_name' ]
  FROM { PFILE [= 'pfile_name' ]
       | MEMORY
       } ;

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1250934/，如需轉載，請註明出處，否則將追究法律責任。