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

ITAA-OCM

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 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::=

                               
登录/注册后可看大图

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

-- The End --

xiaobudian

支持

hnjxklj

hnjxklj

∑≈⊙♂⊙

珊珊爱雨

look

lthaha168

恩 说的很正确 我准了