Oracle11g資料庫引數檔案誤刪除恢復
本文測試了誤刪除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日誌中可以看到明確提示:
對於兩個廢棄引數資訊,可以檢視官方文件
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”
將兩個廢棄的引數註釋,然後生成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
找到最近幾次成功的日誌資訊,選取其中正確無誤的一條日誌資訊:
建立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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle恢復誤刪除的資料檔案Oracle
- oracle11g 資料檔案誤刪恢復(無備份)Oracle
- linux下恢復誤刪除的資料檔案Linux
- Oracle資料恢復 - Linux / Unix 誤刪除的檔案恢復(轉)Oracle資料恢復Linux
- 誤刪除資料檔案、控制檔案的非RMAN恢復方法
- 使用檔案描述符恢復誤刪除的資料檔案
- lsof恢復oracle誤刪除檔案Oracle
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- oracle恢復誤刪除資料Oracle
- RM 刪除資料檔案恢復操作
- 誤刪除儲存SqlServer資料庫資料恢復SQLServer資料庫資料恢復
- 使用lsof恢復誤刪除的檔案
- Sybase ASE資料庫恢復,Sybase資料恢復,資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- linux中誤刪除oracle資料檔案的恢復操作LinuxOracle
- 資料檔案誤刪--但有資料檔案的copy恢復
- Oracle資料庫意外刪除資料檔案的恢復(轉載)Oracle資料庫
- 【伺服器資料恢復】Zfs檔案系統下誤刪除怎麼恢復資料伺服器資料恢復
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫
- 恢復rm -f物理刪除資料檔案
- 恢復被rm意外刪除資料檔案
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- ZT:使用lsof恢復誤刪除的檔案
- Oracle閃回刪除恢復誤刪資料Oracle
- linux中誤刪除oracle資料檔案的恢復操作(轉)LinuxOracle
- SQL Server資料庫恢復,SQL Server資料恢復,SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- 【北亞資料恢復】zfs檔案系統的伺服器誤刪除的資料恢復資料恢復伺服器
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- 通過控制程式碼恢復Linux下誤刪除的資料庫資料檔案Linux資料庫
- Sybase SQL Anywhere(ASA)資料庫恢復,ASA資料恢復,資料誤刪除恢復工具ReadASADBSQL資料庫資料恢復
- OS 刪除oracle資料檔案恢復過程Oracle
- 恢復刪除的檔案
- 刪除檔案的恢復
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- Flashback Query恢復誤刪除資料(轉)
- 【伺服器資料恢復】linux ext3檔案系統下誤刪除mysql資料庫的資料恢復案例伺服器資料恢復LinuxMySql資料庫
- 【北亞資料恢復】分散式儲存hbase和hive資料庫底層檔案被誤刪除的資料恢復案例資料恢復分散式Hive資料庫