ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【11g】【10g】【实验】spfile文件的恢复（from memory;）

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

Oracle 作者：ysh033481 时间：2012-01-17 13:48:09 0 删除 编辑

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


<!-- 正文结束 -->

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23209265/viewspace-1115010/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： 没有了~
请登录后发表评论 登录
全部评论
ysh033481

注册时间：2010-01-19

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593