Shulou(Shulou.com)06/01 Report--

In this paper, we test the recovery method after mistakenly deleting spfile,pfile,init.ora and other files, consider a variety of scenarios, and restore parameter files in different scenarios.

Step 1: connect to the database and view the path where the spfile file is located

[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 step 2: view the file information under the parameter file path

[oracle@ora11g ~] $cd $ORACLE_HOME/dbs

[oracle@ora11g dbs] $ls

Step 3 of hc_cams.dat init.ora lkCAMS orapwcams spfilecams.ora: for ease of testing, create a pfile file here

SYS@cams > create pfile from spfile

File created. View the newly created pfile file

[oracle@ora11g dbs] $ls

Hc_cams.dat initcams.ora init.ora lkCAMS orapwcams spfilecams.ora views the contents of each parameter file

[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 05Compact 14Compact 09-Updating compatible to 11.2

# ysarig 08Compact 13Compact 07-Fixing the sample for 11g

# atsukerm 08Compact 06Compact 98-fix for 8.1.

# hpiao 06max 05Compact 97-fix for 803

# glavash 05Compact 12Compact 97-add oracle_trace_enable comment

# hpiao 04Universe 22 Universe 97-remove ifile=, events=, etc.

# alingelb 09Compact 19Compact 94-remove vms-specific stuff

# dpawson 07Compact 07Compact 93-add more comments regarded archive start

# maporter 10-29-92-Add vms_sga_use_gblpagfile=TRUE

# jloaiza 03max 07Compact 92-change ALPHA to BETA

# danderso 02Universe 26Universe 92-change db_block_cache_protect to _ db_block_cache_p

# ghallmar 02max 03Compact 92-db_directory-> db_domain

# maporter 01 merge changes from branch 12 Compact 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 08Compact 13Compact 91-merge changes from branch 1.7.100.1

# jloaiza 07Compact 31Compact 91-add debug stuff

# rlim 04Universe 29Universe 91-removal of char_is_varchar2

# Bridge 03After 12Universe 91-log_allocation no longer exists

# Wijaya 02Universe 05Compact 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'

Step 4 of undo_tablespace='UNDOTBS1': the simulation parameter file is deleted by mistake

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

Step 5 of initcams.ora init.ora spfilecams.ora: check whether the database is still working properly

SYS@cams > select name,open_mode from v$database

NAME OPEN_MODE

--

CAMS READ WRITE obviously, the database is working properly now, because the information of the spfile parameter file has been read into memory during the database startup.

Step 6: when the database is running, it is simulated that the parameter file has been mistakenly deleted and restored.

The new features of Oracle11gR2 need to be used here. For Oracle official documents, the path is:

Home / Database / Oracle Database Online Documentation 11g?Release 2 / Database Administration/SQL Language Reference/What's New in the SQL Language Reference?

Can be found.

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.

Click create pfile or create spfile to go to the link page, you can find the SQL command:

CREATE PFILE [= 'pfile_name']

FROM {SPFILE [= 'spfile_name']

| | MEMORY |

}

CREATE SPFILE [= 'spfile_name']

FROM {PFILE [= 'pfile_name']

| | MEMORY |

}

The restore statement is executed here:

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. View the restored spfile and pfile files:

[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] $step 7: restart the database, check whether the restored parameter files can be used properly, and analyze

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. The problem with ORA-32004: obsolete or deprecated parameter (s) specified for RDBMS instance occurs because the parameters background_dump_dest and user_dump_dest are discarded in 11gR1, and you can see a clear prompt in the alert log:

For two obsolete parameter information, you can check the official documentation

Home / Database / Oracle Database Online Documentation 11g?Release 111.1 / Database Administration/Reference/1?Initialization Parameters

See the following in the BACKGROUND_DUMP_DEST directory:

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.

See the following in the USER_DUMP_DEST directory:

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.

Step 8: start after adjusting the pfile parameter file and creating the spfile file

Open the pfile file and you can see that the two deprecated parameters are followed by the comment "# Deprecate parameter"

Annotate two obsolete parameters, and then start after generating a spfile file

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. Step 9: the simulation failed to find that the parameter file was mistakenly deleted in step 6, then the database was closed and an error was reported at startup.

[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'/ u01ActionAccording to an oracle.product 11.2.0According to dbcams.ora'

Step 10: find the alert log and restore the pfile parameter file from the startup information in the alert log.

[oracle@ora11g dbs] $cd / u01/app/oracle/diag/rdbms/cams/cams/trace/

[oracle@ora11g trace] $ls | grep alert

Alert_cams.log finds the most recent successful log information and selects the correct one:

Create the pfile file initcams.ora, and fill in the parameter information in the alert log:

[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" starts the database directly using the pfile file:

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.

Step 11: this assumes that no parameter information is found in the alert log in step 10 and needs to be restored, and that init.ora can still find it.

[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' and then modify the initcams.ora according to the actual environment to start the database, but there may be some parameter values that are inconsistent with the original database, which need to be adjusted by DBA.

Step 12: assume that no parameter information is found in the alert log in step 10 and need to be restored, and that init.ora cannot find it.

[oracle@ora11g dbs] $vi initcams.ora

[oracle@ora11g dbs] $cat initcams.ora

Db_name='cams' uses pfile to start the database:

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 check alert log

[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 apprenticespact oracleUniplicationproductUniplic11.2.0Ondbtrlcams.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 modifies the pfile file to specify the control_files parameter (if you really forget, you can use the linux command to find it)

[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' uses pfile to start the database again

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:'/ u01OnOnOnActionoradataUniqqcamsUniverse control 01.ctl' View error message

[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. Here you need to configure a compatible parameter in the parameter file

[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" uses pfile to start the database again

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.

The database started successfully. At the same time, we also know from the testing process that the parameter file needs to configure at least three parameter information such as db_name,control_files and compatible, so that the database can be started successfully. However, after startup, DBA is also required to adjust the database parameters.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.