本文测试了误删除spfile,pfile,init.ora等文件后的恢复方法,考虑多种场景,在不同场景下进行参数文件恢复。
第一步:连上数据库,查看spfile文件所在路径
- [oracle@ora11g ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 2 11:37:08 2017
-
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
- Connected to an idle instance.
-
- SYS@cams>startup;
- ORACLE instance started.
-
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
- SYS@cams>show parameter pfile;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- spfile string /u01/app/oracle/product/11.2.0
- /db_1/dbs/spfilecams.ora
第二步:查看参数文件路径下文件信息
- [oracle@ora11g ~]$ cd $ORACLE_HOME/dbs
- [oracle@ora11g dbs]$ ls
- hc_cams.dat init.ora lkCAMS orapwcams spfilecams.ora
第三步:为了便于测试,这里创建一个pfile文件
- SYS@cams>create pfile from spfile;
-
- File created.
查看新创建的pfile文件
- [oracle@ora11g dbs]$ ls
- hc_cams.dat initcams.ora init.ora lkCAMS orapwcams spfilecams.ora
查看每个参数文件的内容
- [oracle@ora11g dbs]$ strings spfilecams.ora
- cams.__db_cache_size=348127232
- cams.__java_pool_size=4194304
- cams.__large_pool_size=12582912
- cams.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
- cams.__pga_aggregate_target=272629760
- cams.__sga_target=507510784
- cams.__shared_io_pool_size=0
- cams.__shared_pool_size=130023424
- cams.__streams_pool_size=0
- *.audit_file_dest='/u01/app/oracle/admin/cams/adump'
- *.audit_trail='db'
- *.compatible='11.2.0.4.0'
- *.control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/
- oracle/fast_recovery_area/cams/control02.ctl'
- *.db_block_size=8192
- *.db_domain=''
- *.db_name='cams'
- *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
- *.db_recovery_file_dest_size=4385144832
- *.diagnostic_dest='/u01/app/oracle'
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
- *.job_queue_processes=1000
- *.memory_target=780140544
- *.open_cursors=300
- *.processes=150
- *.remote_login_passwordfile='EXCLUSIVE'
- *.undo_tablespace='UNDOTBS1'
- [oracle@ora11g dbs]$ cat init.ora
- #
- # $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
- #
- # Copyright (c) 1991, 1997, 1998 by Oracle Corporation
- # NAME
- # init.ora
- # FUNCTION
- # NOTES
- # MODIFIED
- # ysarig 05/14/09 - Updating compatible to 11.2
- # ysarig 08/13/07 - Fixing the sample for 11g
- # atsukerm 08/06/98 - fix for 8.1.
- # hpiao 06/05/97 - fix for 803
- # glavash 05/12/97 - add oracle_trace_enable comment
- # hpiao 04/22/97 - remove ifile=, events=, etc.
- # alingelb 09/19/94 - remove vms-specific stuff
- # dpawson 07/07/93 - add more comments regarded archive start
- # maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
- # jloaiza 03/07/92 - change ALPHA to BETA
- # danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
- # ghallmar 02/03/92 - db_directory -> db_domain
- # maporter 01/12/92 - merge changes from branch 1.8.308.1
- # maporter 12/21/91 - bug 76493: Add control_files parameter
- # wbridge 12/03/91 - use of %c in archive format is discouraged
- # ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
- # thayes 11/27/91 - Change default for cache_clone
- # jloaiza 08/13/91 - merge changes from branch 1.7.100.1
- # jloaiza 07/31/91 - add debug stuff
- # rlim 04/29/91 - removal of char_is_varchar2
- # Bridge 03/12/91 - log_allocation no longer exists
- # Wijaya 02/05/91 - remove obsolete parameters
- #
- ##############################################################################
- # Example INIT.ORA file
- #
- # This file is provided by Oracle Corporation to help you start by providing
- # a starting point to customize your RDBMS installation for your site.
- #
- # NOTE: The values that are used in this file are only intended to be used
- # as a starting point. You may want to adjust/tune those values to your
- # specific hardware and needs. You may also consider using Database
- # Configuration Assistant tool (DBCA) to create INIT file and to size your
- # initial set of tablespaces based on the user input.
- ###############################################################################
-
- # Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
- # install time)
-
- db_name='ORCL'
- memory_target=1G
- processes = 150
- audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
- audit_trail ='db'
- db_block_size=8192
- db_domain=''
- db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
- db_recovery_file_dest_size=2G
- diagnostic_dest='<ORACLE_BASE>'
- dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
- open_cursors=300
- remote_login_passwordfile='EXCLUSIVE'
- undo_tablespace='UNDOTBS1'
- # You may want to ensure that control files are created on separate physical
- # devices
- control_files = (ora_control1, ora_control2)
- compatible ='11.2.0'
- [oracle@ora11g dbs]$ cat initcams.ora
- cams.__db_cache_size=348127232
- cams.__java_pool_size=4194304
- cams.__large_pool_size=12582912
- cams.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
- cams.__pga_aggregate_target=272629760
- cams.__sga_target=507510784
- cams.__shared_io_pool_size=0
- cams.__shared_pool_size=130023424
- cams.__streams_pool_size=0
- *.audit_file_dest='/u01/app/oracle/admin/cams/adump'
- *.audit_trail='db'
- *.compatible='11.2.0.4.0'
- *.control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
- *.db_block_size=8192
- *.db_domain=''
- *.db_name='cams'
- *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
- *.db_recovery_file_dest_size=4385144832
- *.diagnostic_dest='/u01/app/oracle'
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
- *.job_queue_processes=1000
- *.memory_target=780140544
- *.open_cursors=300
- *.processes=150
- *.remote_login_passwordfile='EXCLUSIVE'
- *.undo_tablespace='UNDOTBS1'
第四步:模拟参数文件被误删除
- [oracle@ora11g dbs]$ mkdir backup
- [oracle@ora11g dbs]$ mv initcams.ora init.ora spfilecams.ora backup/
- [oracle@ora11g dbs]$ ls
- backup hc_cams.dat lkCAMS orapwcams
- [oracle@ora11g dbs]$ ls backup/
- initcams.ora init.ora spfilecams.ora
第五步:检查数据库是否还能正常工作
- SYS@cams>select name,open_mode from v$database;
-
- NAME OPEN_MODE
- --------- --------------------
- CAMS READ WRITE
显然,现在数据库是可以正常工作的,因为数据库启动过程中已经将spfile参数文件的信息读到内存中。
第六步:这里模拟在数据库运行时,及时发现参数文件被误删除,进行恢复。
这里需要用到Oracle11gR2的新特性,对于Oracle官方文档的路径为:
Home / Database / Oracle Database Online Documentation 11g?Release 2 (11.2) / Database Administration/SQL Language Reference/What's New in the SQL Language Reference?
可以找到
CREATE PFILE has new syntax that lets you create a parameter file from current system-wide parameter settings.
CREATE SPFILE has new syntax that lets you create a system parameter file from current system-wide parameter settings.
点击create pfile或者create spfile进入链接页面,可以找到SQL命令:
CREATE PFILE [= 'pfile_name' ]
FROM { SPFILE [= 'spfile_name']
| MEMORY
} ;
CREATE SPFILE [= 'spfile_name' ]
FROM { PFILE [= 'pfile_name' ]
| MEMORY
} ;
这里执行恢复语句:
- SYS@cams>create spfile from memory;
- create spfile from memory
- *
- ERROR at line 1:
- ORA-32002: cannot create SPFILE already being used by the instance
-
-
- SYS@cams>create pfile from memory;
-
- File created.
-
- SYS@cams>create spfile='spfilecams1.ora' from memory;
-
- File created.
查看恢复后的spfile和pfile文件:
- [oracle@ora11g dbs]$ strings spfilecams1.ora
- *.__db_cache_size=320M
- *.__java_pool_size=4M
- *.__large_pool_size=12M
- *.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
- *.__pga_aggregate_target=260M
- *.__sga_target=484M
- *.__shared_io_pool_size=0
- *.__shared_pool_size=136M
- *.__streams_pool_size=0
- *._aggregation_optimization_settings=0
- *._always_anti_join='CHOOSE'
- *._always_semi_join='CHOOSE'
- *._and_pruning_enabled=TRUE
- *._b_tree_bitmap_plans=TRUE
- *._bloom_filter_enabled=TRUE
- *._bloom_folding_enabled=TRUE
- *._bloom_pru
- ning_enabled=TRUE
- *._complex_view_merging=TRUE
- *._compression_compatibility='11.2.0.4.0'
- *._connect_by_use_union_all='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_an
- ti_semi_join_allowed=TRUE
- *._improved_outerjoin_card=TRUE
- *._improved_row_length_enabled=TRUE
- *._index_join_enabled=TRUE
- *._ksb_restart_policy_times='0'
- *._ksb_restart_policy_times='60'
- *._ksb_restart_policy_times='120'
- *._ksb_restart_policy_times='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_estimat
- e=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_coalesce_subqueries=TRUE
- *._optimizer_complex_pred_selectivity=TRUE
- *._optimizer_compute_index_stats=TRUE
- *._optimizer_connect_by_combine_sw=TRUE
- *._optimizer_connect_by_cost_ba
- sed=TRUE
- *._optimizer_connect_by_elim_dups=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_agg_transform=TRUE
- *._optimizer_distinct_elimination=TRUE
- *._optimizer_distinct_placement=TRUE
- *._optimizer_eliminate_filtering_join=TRUE
- *._optimizer_enable_density_improvements=TRUE
- *._optimizer_enable_extended_stats=T
- *._optimizer_enable_table_lookup_by_nl=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=192
- *._optimizer_false_filter_pred_pullup=TRUE
- *._optimizer_fast_access_pred_analysis=TRUE
- *._optimizer_fast_pred_transitivity=TRUE
- *._optimizer_filter_pred_pullup=TRUE
- *._optimizer_fkr_index_cost_bias=10
- *._optimizer_full_ou
- ter_join_to_outer=TRUE
- *._optimizer_group_by_placement=TRUE
- *._optimizer_improve_selectivity=TRUE
- *._optimizer_interleave_jppd=TRUE
- *._optimizer_join_elimination_enabled=TRUE
- *._optimizer_join_factorization=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
- *._optimiz
- er_null_aware_antijoin=TRUE
- *._optimizer_or_expansion='DEPTH'
- *._optimizer_order_by_elimination_enabled=TRUE
- *._optimizer_outer_join_to_inner=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=TRU
- *._optimizer_system_stats_usage=TRUE
- *._optimizer_table_expansion=TRUE
- *._optimizer_transitivity_retain=TRUE
- *._optimizer_try_st_before_jppd=TRUE
- *._optimizer_undo_cost_change='11.2.0.4'
- *._optimizer_unnest_corr_set_subq=TRUE
- *._optimizer_unnest_disjunctive_subq=TRUE
- *._optimizer_use_cbqt_star_transformation=TRUE
- *._optimizer_use_feedback=TRUE
- *._or_expand_nvl_predicate=TRUE
- *._ordered_nested_loop=TRUE
- *._parallel_broadcast_enabled=TRUE
- *._partition_view_enabled=TRUE
- *._pivot_imple
- mentation_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_partition_scan_enabled=TRUE
- *._px_pwg_enabled=TRUE
- *._px_ual_serial_input=TRUE
- *._query_rewrite_setopgrw_enable=TRUE
- *._remove_aggr_subquery=TRUE
- *._replace_virtual_columns=TRUE
- *._right_outer_hash_enable=TRUE
- *._selfjoin_mv_duplicates=TRUE
- *._sql_model_unfold_forloops='RUN_TIME'
- *._sql
- tune_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='/u01/app/oracle/admin/cams/adump'
- *.audit_trail='DB'
- *.background_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
- *.compatible='11.2.0.4.0'
- *.control_files='/u01/app/oracle/oradata
- /cams/control01.ctl'
- *.control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
- *.core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'
- *.db_block_size=8192
- *.db_domain=''
- *.db_name='cams'
- *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
- *.db_recovery_file_dest_size=4182M
- *.diagnostic_dest='/u01/app/oracle'
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
- *.job_queue_processes=1000
- *.log_buffer=6520832 # log buffer update
- *.memory_target=744M
- *.open_cur
- sors=300
- *.optimizer_dynamic_sampling=2
- *.optimizer_mode='ALL_ROWS'
- *.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
- *.processes=150
- *.query_rewrite_enabled='TRUE'
- *.remote_login_passwordfile='EXCLUSIVE'
- *.result_cache_max_size=1920K
- *.skip_unusable_indexes=TRUE
- *.undo_tablespace='UNDOTBS1'
- *.user_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
-
- [oracle@ora11g dbs]$ cat initcams.ora
- # Oracle init.ora parameter file generated by instance cams on 08/02/2017 13:36:21
- __db_cache_size=320M
- __java_pool_size=4M
- __large_pool_size=12M
- __oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
- __pga_aggregate_target=260M
- __sga_target=484M
- __shared_io_pool_size=0
- __shared_pool_size=136M
- __streams_pool_size=0
- _aggregation_optimization_settings=0
- _always_anti_join='CHOOSE'
- _always_semi_join='CHOOSE'
- _and_pruning_enabled=TRUE
- _b_tree_bitmap_plans=TRUE
- _bloom_filter_enabled=TRUE
- _bloom_folding_enabled=TRUE
- _bloom_pruning_enabled=TRUE
- _complex_view_merging=TRUE
- _compression_compatibility='11.2.0.4.0'
- _connect_by_use_union_all='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'
- _ksb_restart_policy_times='60'
- _ksb_restart_policy_times='120'
- _ksb_restart_policy_times='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_coalesce_subqueries=TRUE
- _optimizer_complex_pred_selectivity=TRUE
- _optimizer_compute_index_stats=TRUE
- _optimizer_connect_by_combine_sw=TRUE
- _optimizer_connect_by_cost_based=TRUE
- _optimizer_connect_by_elim_dups=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_agg_transform=TRUE
- _optimizer_distinct_elimination=TRUE
- _optimizer_distinct_placement=TRUE
- _optimizer_eliminate_filtering_join=TRUE
- _optimizer_enable_density_improvements=TRUE
- _optimizer_enable_extended_stats=TRUE
- _optimizer_enable_table_lookup_by_nl=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=192
- _optimizer_false_filter_pred_pullup=TRUE
- _optimizer_fast_access_pred_analysis=TRUE
- _optimizer_fast_pred_transitivity=TRUE
- _optimizer_filter_pred_pullup=TRUE
- _optimizer_fkr_index_cost_bias=10
- _optimizer_full_outer_join_to_outer=TRUE
- _optimizer_group_by_placement=TRUE
- _optimizer_improve_selectivity=TRUE
- _optimizer_interleave_jppd=TRUE
- _optimizer_join_elimination_enabled=TRUE
- _optimizer_join_factorization=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_join_to_inner=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_table_expansion=TRUE
- _optimizer_transitivity_retain=TRUE
- _optimizer_try_st_before_jppd=TRUE
- _optimizer_undo_cost_change='11.2.0.4'
- _optimizer_unnest_corr_set_subq=TRUE
- _optimizer_unnest_disjunctive_subq=TRUE
- _optimizer_use_cbqt_star_transformation=TRUE
- _optimizer_use_feedback=TRUE
- _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_partition_scan_enabled=TRUE
- _px_pwg_enabled=TRUE
- _px_ual_serial_input=TRUE
- _query_rewrite_setopgrw_enable=TRUE
- _remove_aggr_subquery=TRUE
- _replace_virtual_columns=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='/u01/app/oracle/admin/cams/adump'
- audit_trail='DB'
- background_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
- compatible='11.2.0.4.0'
- control_files='/u01/app/oracle/oradata/cams/control01.ctl'
- control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
- core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'
- db_block_size=8192
- db_domain=''
- db_name='cams'
- db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
- db_recovery_file_dest_size=4182M
- diagnostic_dest='/u01/app/oracle'
- dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
- job_queue_processes=1000
- log_buffer=6520832 # log buffer update
- memory_target=744M
- open_cursors=300
- optimizer_dynamic_sampling=2
- optimizer_mode='ALL_ROWS'
- plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
- processes=150
- query_rewrite_enabled='TRUE'
- remote_login_passwordfile='EXCLUSIVE'
- result_cache_max_size=1920K
- skip_unusable_indexes=TRUE
- undo_tablespace='UNDOTBS1'
- user_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
- [oracle@ora11g dbs]$
第七步:重启数据库,检查恢复后的参数文件能否正常使用,并进行分析
- SYS@cams>shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SYS@cams>host mv /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams1.ora /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
-
- SYS@cams>startup;
- ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
- ORACLE instance started.
-
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 482348680 bytes
- Database Buffers 285212672 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
这里出现ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance的问题,是因为background_dump_dest和user_dump_dest两个参数在11gR1中废弃了,在alert日志中可以看到明确提示:
![](http://img.blog.itpub.net/blog/attachment/201708/2/31394774_1501657072keEy.png?x-oss-process=style/bb)
对于两个废弃参数信息,可以查看官方文档
Home / Database / Oracle Database Online Documentation 11g?Release 1 (11.1) / Database Administration/Reference/1?Initialization Parameters
在BACKGROUND_DUMP_DEST目录下看到:
Note:
This parameter is ignored by the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
在USER_DUMP_DEST目录下看到:
Note:
This parameter is ignored by the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, which places trace and core files in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
第八步:调整pfile参数文件,并创建spfile文件后启动
打开pfile文件,可以看到两个弃用的参数后面都有注释“#Deprecate parameter”
![](http://img.blog.itpub.net/blog/attachment/201708/2/31394774_1501657120lZPc.png?x-oss-process=style/bb)
将两个废弃的参数注释,然后生成spfile文件后启动
- SYS@cams>create spfile from pfile;
-
- File created.
-
- SYS@cams>startup;
- ORACLE instance started.
-
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 482348680 bytes
- Database Buffers 285212672 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
第九步:模拟在第六步的时候未能及时发现参数文件被误删除,然后数据库关闭了,启动的时候报错。
- [oracle@ora11g dbs]$ ls
- backup hc_cams.dat initcams.ora lkCAMS orapwcams spfilecams.ora
- [oracle@ora11g dbs]$ mkdir backup1
- [oracle@ora11g dbs]$ mv initcams.ora spfilecams.ora backup1/
- [oracle@ora11g dbs]$ ls backup1/
- initcams.ora spfilecams.ora
- [oracle@ora11g dbs]$ ls
- backup backup1 hc_cams.dat lkCAMS orapwcams
- SYS@cams>shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SYS@cams>startup;
- ORA-01078: failure in processing system parameters
- LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora'
第十步:找到alert日志,通过alert日志中的启动信息恢复pfile参数文件。
- [oracle@ora11g dbs]$ cd /u01/app/oracle/diag/rdbms/cams/cams/trace/
- [oracle@ora11g trace]$ ls | grep alert
- alert_cams.log
找到最近几次成功的日志信息,选取其中正确无误的一条日志信息:
![](http://img.blog.itpub.net/blog/attachment/201708/2/31394774_1501657220Zs7P.png?x-oss-process=style/bb)
创建pfile文件initcams.ora,将alert日志中的参数信息填入:
- [oracle@ora11g trace]$ cd $ORACLE_HOME/dbs
- [oracle@ora11g dbs]$ ls
- backup backup1 hc_cams.dat lkCAMS orapwcams
- [oracle@ora11g dbs]$ vi initcams.ora
- [oracle@ora11g dbs]$ cat initcams.ora
- processes = 150
- memory_target = 744M
- control_files = "/u01/app/oracle/oradata/cams/control01.ctl"
- control_files = "/u01/app/oracle/fast_recovery_area/cams/control02.ctl"
- db_block_size = 8192
- compatible = "11.2.0.4.0"
- db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
- db_recovery_file_dest_size= 4182M
- undo_tablespace = "UNDOTBS1"
- remote_login_passwordfile= "EXCLUSIVE"
- db_domain = ""
- dispatchers = "(PROTOCOL=TCP) (SERVICE=camsXDB)"
- job_queue_processes = 1000
- audit_file_dest = "/u01/app/oracle/admin/cams/adump"
- audit_trail = "DB"
- db_name = "cams"
- open_cursors = 300
- diagnostic_dest = "/u01/app/oracle"
直接使用pfile文件启动数据库:
- SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
- ORACLE instance started.
-
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 511708808 bytes
- Database Buffers 255852544 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
第十一步:这里假设第十步的alert日志中没找到参数信息,需要进行恢复,假设init.ora还能找到。
- [oracle@ora11g backup]$ ls
- initcams.ora init.ora spfilecams.ora
- [oracle@ora11g backup]$ cat init.ora | grep -v ^# | grep -v ^$ > initcams.ora
- [oracle@ora11g backup]$ cat initcams.ora
- db_name='ORCL'
- memory_target=1G
- processes = 150
- audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
- audit_trail ='db'
- db_block_size=8192
- db_domain=''
- db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
- db_recovery_file_dest_size=2G
- diagnostic_dest='<ORACLE_BASE>'
- dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
- open_cursors=300
- remote_login_passwordfile='EXCLUSIVE'
- undo_tablespace='UNDOTBS1'
- control_files = (ora_control1, ora_control2)
- compatible ='11.2.0'
然后根据实际环境情况修改initcams.ora,启动数据库,不过可能会出现部分参数的值与原数据库不一致,需要DBA进行调整。
第十二步:这里假设第十步的alert日志中没找到参数信息,需要进行恢复,假设init.ora不能找到。
- [oracle@ora11g dbs]$ vi initcams.ora
- [oracle@ora11g dbs]$ cat initcams.ora
- db_name='cams'
使用pfile启动数据库:
- SYS@cams>shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
- ORACLE instance started.
-
- Total System Global Area 263090176 bytes
- Fixed Size 2252256 bytes
- Variable Size 205521440 bytes
- Database Buffers 50331648 bytes
- Redo Buffers 4984832 bytes
- ORA-00205: error in identifying control file, check alert log for more info
检查alert日志
- [oracle@ora11g trace]$ tail -n 20 alert_cams.log
- CKPT started with pid=12, OS id=5722
- Wed Aug 02 14:38:15 2017
- SMON started with pid=13, OS id=5724
- Wed Aug 02 14:38:15 2017
- RECO started with pid=14, OS id=5726
- Wed Aug 02 14:38:15 2017
- MMON started with pid=15, OS id=5728
- Wed Aug 02 14:38:15 2017
- MMNL started with pid=16, OS id=5730
- ORACLE_BASE from environment = /u01/app/oracle
- Wed Aug 02 14:38:15 2017
- ALTER DATABASE MOUNT
- ORA-00210: cannot open the specified control file
- ORA-00202: control file: '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlcams.dbf'
- ORA-27037: unable to obtain file status
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
- ORA-205 signalled during: ALTER DATABASE MOUNT...
- Wed Aug 02 14:38:15 2017
- Checker run found 1 new persistent data failures
修改pfile文件,指定control_files参数(如果真的忘了,可以用linux命令查找)
- [oracle@ora11g dbs]$ find $ORACLE_BASE -name control*
- /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/images/database/storage/controlfile.gif
- /u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controlWindow.js
- /u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controls.html
- /u01/app/oracle/fast_recovery_area/cams/control02.ctl
- /u01/app/oracle/oradata/cams/control01.ctl
- [oracle@ora11g dbs]$ vi initcams.ora
- [oracle@ora11g dbs]$ cat initcams.ora
- db_name='cams'
- control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
再次使用pfile启动数据库
- SYS@cams>shutdown immediate;
- ORA-01507: database not mounted
-
-
- ORACLE instance shut down.
- SYS@cams>startup;
- ORACLE instance started.
-
- Total System Global Area 263090176 bytes
- Fixed Size 2252256 bytes
- Variable Size 205521440 bytes
- Database Buffers 50331648 bytes
- Redo Buffers 4984832 bytes
- ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version
- 11.2.0.0.0
- ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
查看错误信息
- [oracle@ora11g dbs]$ oerr ORA 00201
- 00201, 00000, "control file version %s incompatible with ORACLE version %s"
- // *Cause: The control file was created by incompatible software.
- // *Action: Either restart with a compatible software release or use
- // CREATE CONTROLFILE to create a new control file that is
- // compatible with this release.
这里需要在参数文件配置一个compatible参数
- [oracle@ora11g dbs]$ vi initcams.ora
- [oracle@ora11g dbs]$ cat initcams.ora
- db_name='cams'
- control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
- compatible="11.2.0.4.0"
再次使用pfile启动数据库
- SYS@cams>shutdown immediate;
- ORA-01507: database not mounted
-
-
- ORACLE instance shut down.
- SYS@cams>startup;
- ORACLE instance started.
-
- Total System Global Area 263090176 bytes
- Fixed Size 2252256 bytes
- Variable Size 205521440 bytes
- Database Buffers 50331648 bytes
- Redo Buffers 4984832 bytes
- Database mounted.
- Database opened.
数据库启动成功。同时,我们也从测试过程中知道,参数文件至少需要配置db_name,control_files和compatible等3个参数信息,可以让数据库成功启动。不过启动之后也需要DBA对数据库参数进行调整。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31394774/viewspace-2142977/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31394774/viewspace-2142977/