oracle怎么恢复删除数据库数据库文件,Oracle 11g数据库参数文件误删除恢复

最新推荐文章于 2023-06-01 12:28:59 发布

weixin_39820244

最新推荐文章于 2023-06-01 12:28:59 发布

阅读量388

点赞数

文章标签： oracle怎么恢复删除数据库数据库文件

本文测试了误删除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

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 $

# 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 '' to point to the oracle base (the one you specify at

# install time)

db_name='ORCL'

memory_target=1G

processes = 150

audit_file_dest='/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest=''

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:

第八步：调整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='/admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/flash_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest=''

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对数据库参数进行调整。

weixin_39820244

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle怎么恢复删除数据库数据库文件,Oracle 11g数据库参数文件误删除恢复

本文测试了误删除spfile，pfile，init.ora等文件后的恢复方法，考虑多种场景，在不同场景下进行参数文件恢复。第一步：连上数据库，查看spfile文件所在路径[Oracle@ora11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 2 11:37:08 2017Copyright (...
复制链接

扫一扫