溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

Oracle11g數(shù)據(jù)庫(kù)參數(shù)文件誤刪除恢復(fù)

發(fā)布時(shí)間：2020-08-10 11:44:47 來(lái)源：ITPUB博客 閱讀：174 作者：az65381973 欄目：關(guān)系型數(shù)據(jù)庫(kù)

本文測(cè)試了誤刪除spfile，pfile，init.ora等文件后的恢復(fù)方法，考慮多種場(chǎng)景，在不同場(chǎng)景下進(jìn)行參數(shù)文件恢復(fù)。

 

第一步：連上數(shù)據(jù)庫(kù)，查看spfile文件所在路徑


  1. [oracle@ora11g ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 2 11:37:08 2017

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  7. Connected to an idle instance.

  9. SYS@cams>startup;
  10. ORACLE instance started.

  12. Total System Global Area 776646656 bytes
  13. Fixed Size         2257272 bytes
  14. Variable Size         478154376 bytes
  15. Database Buffers     289406976 bytes
  16. Redo Buffers         6828032 bytes
  17. Database mounted.
  18. Database opened.
  19. SYS@cams>show parameter pfile;

  21. NAME                 TYPE     VALUE
  22. ------------------------------------ ----------- ------------------------------
  23. spfile                 string     /u01/app/oracle/product/11.2.0
  24.                          /db_1/dbs/spfilecams.ora

第二步：查看參數(shù)文件路徑下文件信息


  1. [oracle@ora11g ~]$ cd $ORACLE_HOME/dbs
  2. [oracle@ora11g dbs]$ ls
  3. hc_cams.dat init.ora lkCAMS orapwcams spfilecams.ora

第三步：為了便于測(cè)試，這里創(chuàng)建一個(gè)pfile文件


  1. SYS@cams>create pfile from spfile;

  3. File created.

查看新創(chuàng)建的pfile文件

  1. [oracle@ora11g dbs]$ ls
  2. hc_cams.dat initcams.ora init.ora lkCAMS orapwcams spfilecams.ora

查看每個(gè)參數(shù)文件的內(nèi)容

  1. [oracle@ora11g dbs]$ strings spfilecams.ora
  2. cams.__db_cache_size=348127232
  3. cams.__java_pool_size=4194304
  4. cams.__large_pool_size=12582912
  5. cams.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  6. cams.__pga_aggregate_target=272629760
  7. cams.__sga_target=507510784
  8. cams.__shared_io_pool_size=0
  9. cams.__shared_pool_size=130023424
  10. cams.__streams_pool_size=0
  11. *.audit_file_dest='/u01/app/oracle/admin/cams/adump'
  12. *.audit_trail='db'
  13. *.compatible='11.2.0.4.0'
  14. *.control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/
  15. oracle/fast_recovery_area/cams/control02.ctl'
  16. *.db_block_size=8192
  17. *.db_domain=''
  18. *.db_name='cams'
  19. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  20. *.db_recovery_file_dest_size=4385144832
  21. *.diagnostic_dest='/u01/app/oracle'
  22. *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
  23. *.job_queue_processes=1000
  24. *.memory_target=780140544
  25. *.open_cursors=300
  26. *.processes=150
  27. *.remote_login_passwordfile='EXCLUSIVE'
  28. *.undo_tablespace='UNDOTBS1'
  1. [oracle@ora11g dbs]$ cat init.ora
  2. #
  3. # $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
  4. #
  5. # Copyright (c) 1991, 1997, 1998 by Oracle Corporation
  6. # NAME
  7. # init.ora
  8. # FUNCTION
  9. # NOTES
  10. # MODIFIED
  11. # ysarig 05/14/09 - Updating compatible to 11.2
  12. # ysarig 08/13/07 - Fixing the sample for 11g
  13. # atsukerm 08/06/98 - fix for 8.1.
  14. # hpiao 06/05/97 - fix for 803
  15. # glavash 05/12/97 - add oracle_trace_enable comment
  16. # hpiao 04/22/97 - remove ifile=, events=, etc.
  17. # alingelb 09/19/94 - remove vms-specific stuff
  18. # dpawson 07/07/93 - add more comments regarded archive start
  19. # maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
  20. # jloaiza 03/07/92 - change ALPHA to BETA
  21. # danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
  22. # ghallmar 02/03/92 - db_directory -> db_domain
  23. # maporter 01/12/92 - merge changes from branch 1.8.308.1
  24. # maporter 12/21/91 - bug 76493: Add control_files parameter
  25. # wbridge 12/03/91 - use of %c in archive format is discouraged
  26. # ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
  27. # thayes 11/27/91 - Change default for cache_clone
  28. # jloaiza 08/13/91 - merge changes from branch 1.7.100.1
  29. # jloaiza 07/31/91 - add debug stuff
  30. # rlim 04/29/91 - removal of char_is_varchar2
  31. # Bridge 03/12/91 - log_allocation no longer exists
  32. # Wijaya 02/05/91 - remove obsolete parameters
  33. #
  34. ##############################################################################
  35. # Example INIT.ORA file
  36. #
  37. # This file is provided by Oracle Corporation to help you start by providing
  38. # a starting point to customize your RDBMS installation for your site.
  39. #
  40. # NOTE: The values that are used in this file are only intended to be used
  41. # as a starting point. You may want to adjust/tune those values to your
  42. # specific hardware and needs. You may also consider using Database
  43. # Configuration Assistant tool (DBCA) to create INIT file and to size your
  44. # initial set of tablespaces based on the user input.
  45. ###############################################################################

  47. # Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
  48. # install time)

  50. db_name='ORCL'
  51. memory_target=1G
  52. processes = 150
  53. audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
  54. audit_trail ='db'
  55. db_block_size=8192
  56. db_domain=''
  57. db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
  58. db_recovery_file_dest_size=2G
  59. diagnostic_dest='<ORACLE_BASE>'
  60. dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
  61. open_cursors=300
  62. remote_login_passwordfile='EXCLUSIVE'
  63. undo_tablespace='UNDOTBS1'
  64. # You may want to ensure that control files are created on separate physical
  65. # devices
  66. control_files = (ora_control1, ora_control2)
  67. compatible ='11.2.0'

  1. [oracle@ora11g dbs]$ cat initcams.ora
  2. cams.__db_cache_size=348127232
  3. cams.__java_pool_size=4194304
  4. cams.__large_pool_size=12582912
  5. cams.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  6. cams.__pga_aggregate_target=272629760
  7. cams.__sga_target=507510784
  8. cams.__shared_io_pool_size=0
  9. cams.__shared_pool_size=130023424
  10. cams.__streams_pool_size=0
  11. *.audit_file_dest='/u01/app/oracle/admin/cams/adump'
  12. *.audit_trail='db'
  13. *.compatible='11.2.0.4.0'
  14. *.control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
  15. *.db_block_size=8192
  16. *.db_domain=''
  17. *.db_name='cams'
  18. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  19. *.db_recovery_file_dest_size=4385144832
  20. *.diagnostic_dest='/u01/app/oracle'
  21. *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
  22. *.job_queue_processes=1000
  23. *.memory_target=780140544
  24. *.open_cursors=300
  25. *.processes=150
  26. *.remote_login_passwordfile='EXCLUSIVE'
  27. *.undo_tablespace='UNDOTBS1'

第四步：模擬參數(shù)文件被誤刪除


  1. [oracle@ora11g dbs]$ mkdir backup
  2. [oracle@ora11g dbs]$ mv initcams.ora init.ora spfilecams.ora backup/
  3. [oracle@ora11g dbs]$ ls
  4. backup hc_cams.dat lkCAMS orapwcams
  5. [oracle@ora11g dbs]$ ls backup/
  6. initcams.ora init.ora spfilecams.ora

第五步：檢查數(shù)據(jù)庫(kù)是否還能正常工作


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

  3. NAME     OPEN_MODE
  4. --------- --------------------
  5. CAMS     READ WRITE

顯然，現(xiàn)在數(shù)據(jù)庫(kù)是可以正常工作的，因?yàn)閿?shù)據(jù)庫(kù)啟動(dòng)過(guò)程中已經(jīng)將spfile參數(shù)文件的信息讀到內(nèi)存中。

第六步：這里模擬在數(shù)據(jù)庫(kù)運(yùn)行時(shí)，及時(shí)發(fā)現(xiàn)參數(shù)文件被誤刪除，進(jìn)行恢復(fù)。


這里需要用到Oracle11gR2的新特性，對(duì)于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.

點(diǎn)擊create pfile或者create spfile進(jìn)入鏈接頁(yè)面，可以找到SQL命令：

CREATE PFILE [= 'pfile_name' ]

   FROM { SPFILE [= 'spfile_name']

        | MEMORY

        } ;

 

CREATE SPFILE [= 'spfile_name' ]

  FROM { PFILE [= 'pfile_name' ]

       | MEMORY

       } ;


這里執(zhí)行恢復(fù)語(yǔ)句：


  1. SYS@cams>create spfile from memory;
  2. create spfile from memory
  3. *
  4. ERROR at line 1:
  5. ORA-32002: cannot create SPFILE already being used by the instance


  8. SYS@cams>create pfile from memory;

  10. File created.

  12. SYS@cams>create spfile='spfilecams1.ora' from memory;

  14. File created.

查看恢復(fù)后的spfile和pfile文件：


  1. [oracle@ora11g dbs]$ strings spfilecams1.ora
  2. *.__db_cache_size=320M
  3. *.__java_pool_size=4M
  4. *.__large_pool_size=12M
  5. *.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
  6. *.__pga_aggregate_target=260M
  7. *.__sga_target=484M
  8. *.__shared_io_pool_size=0
  9. *.__shared_pool_size=136M
  10. *.__streams_pool_size=0
  11. *._aggregation_optimization_settings=0
  12. *._always_anti_join='CHOOSE'
  13. *._always_semi_join='CHOOSE'
  14. *._and_pruning_enabled=TRUE
  15. *._b_tree_bitmap_plans=TRUE
  16. *._bloom_filter_enabled=TRUE
  17. *._bloom_folding_enabled=TRUE
  18. *._bloom_pru
  19. ning_enabled=TRUE
  20. *._complex_view_merging=TRUE
  21. *._compression_compatibility='11.2.0.4.0'
  22. *._connect_by_use_union_all='TRUE'
  23. *._convert_set_to_join=FALSE
  24. *._cost_equality_semi_join=TRUE
  25. *._cpu_to_io=0
  26. *._dimension_skip_null=TRUE
  27. *._eliminate_common_subexpr=TRUE
  28. *._enable_type_dep_selectivity=TRUE
  29. *._fast_full_scan_enabled=TRUE
  30. *._first_k_rows_dynamic_proration=TRUE
  31. *._gby_hash_aggregation_enabled=TRUE
  32. *._generalized_pruning_enabled=TRUE
  33. *._globalindex_pnum_filter_enabled=TRUE
  34. *._gs_an
  35. ti_semi_join_allowed=TRUE
  36. *._improved_outerjoin_card=TRUE
  37. *._improved_row_length_enabled=TRUE
  38. *._index_join_enabled=TRUE
  39. *._ksb_restart_policy_times='0'
  40. *._ksb_restart_policy_times='60'
  41. *._ksb_restart_policy_times='120'
  42. *._ksb_restart_policy_times='240' # internal update to set default
  43. *._left_nested_loops_random=TRUE
  44. *._local_communication_costing_enabled=TRUE
  45. *._minimal_stats_aggregation=TRUE
  46. *._mmv_query_rewrite_enabled=TRUE
  47. *._new_initial_join_orders=TRUE
  48. *._new_sort_cost_estimat
  49. e=TRUE
  50. *._nlj_batching_enabled=1
  51. *._optim_adjust_for_part_skews=TRUE
  52. *._optim_enhance_nnull_detection=TRUE
  53. *._optim_new_default_join_sel=TRUE
  54. *._optim_peek_user_binds=TRUE
  55. *._optimizer_adaptive_cursor_sharing=TRUE
  56. *._optimizer_better_inlist_costing='ALL'
  57. *._optimizer_cbqt_no_size_restriction=TRUE
  58. *._optimizer_coalesce_subqueries=TRUE
  59. *._optimizer_complex_pred_selectivity=TRUE
  60. *._optimizer_compute_index_stats=TRUE
  61. *._optimizer_connect_by_combine_sw=TRUE
  62. *._optimizer_connect_by_cost_ba
  63. sed=TRUE
  64. *._optimizer_connect_by_elim_dups=TRUE
  65. *._optimizer_correct_sq_selectivity=TRUE
  66. *._optimizer_cost_based_transformation='LINEAR'
  67. *._optimizer_cost_hjsmj_multimatch=TRUE
  68. *._optimizer_cost_model='CHOOSE'
  69. *._optimizer_dim_subq_join_sel=TRUE
  70. *._optimizer_distinct_agg_transform=TRUE
  71. *._optimizer_distinct_elimination=TRUE
  72. *._optimizer_distinct_placement=TRUE
  73. *._optimizer_eliminate_filtering_join=TRUE
  74. *._optimizer_enable_density_improvements=TRUE
  75. *._optimizer_enable_extended_stats=T
  76. *._optimizer_enable_table_lookup_by_nl=TRUE
  77. *._optimizer_enhanced_filter_push=TRUE
  78. *._optimizer_extend_jppd_view_types=TRUE
  79. *._optimizer_extended_cursor_sharing='UDO'
  80. *._optimizer_extended_cursor_sharing_rel='SIMPLE'
  81. *._optimizer_extended_stats_usage_control=192
  82. *._optimizer_false_filter_pred_pullup=TRUE
  83. *._optimizer_fast_access_pred_analysis=TRUE
  84. *._optimizer_fast_pred_transitivity=TRUE
  85. *._optimizer_filter_pred_pullup=TRUE
  86. *._optimizer_fkr_index_cost_bias=10
  87. *._optimizer_full_ou
  88. ter_join_to_outer=TRUE
  89. *._optimizer_group_by_placement=TRUE
  90. *._optimizer_improve_selectivity=TRUE
  91. *._optimizer_interleave_jppd=TRUE
  92. *._optimizer_join_elimination_enabled=TRUE
  93. *._optimizer_join_factorization=TRUE
  94. *._optimizer_join_order_control=3
  95. *._optimizer_join_sel_sanity_check=TRUE
  96. *._optimizer_max_permutations=2000
  97. *._optimizer_mode_force=TRUE
  98. *._optimizer_multi_level_push_pred=TRUE
  99. *._optimizer_native_full_outer_join='FORCE'
  100. *._optimizer_new_join_card_computation=TRUE
  101. *._optimiz
  102. er_null_aware_antijoin=TRUE
  103. *._optimizer_or_expansion='DEPTH'
  104. *._optimizer_order_by_elimination_enabled=TRUE
  105. *._optimizer_outer_join_to_inner=TRUE
  106. *._optimizer_outer_to_anti_enabled=TRUE
  107. *._optimizer_push_down_distinct=0
  108. *._optimizer_push_pred_cost_based=TRUE
  109. *._optimizer_rownum_bind_default=10
  110. *._optimizer_rownum_pred_based_fkr=TRUE
  111. *._optimizer_skip_scan_enabled=TRUE
  112. *._optimizer_sortmerge_join_inequality=TRUE
  113. *._optimizer_squ_bottomup=TRUE
  114. *._optimizer_star_tran_in_with_clause=TRU
  115. *._optimizer_system_stats_usage=TRUE
  116. *._optimizer_table_expansion=TRUE
  117. *._optimizer_transitivity_retain=TRUE
  118. *._optimizer_try_st_before_jppd=TRUE
  119. *._optimizer_undo_cost_change='11.2.0.4'
  120. *._optimizer_unnest_corr_set_subq=TRUE
  121. *._optimizer_unnest_disjunctive_subq=TRUE
  122. *._optimizer_use_cbqt_star_transformation=TRUE
  123. *._optimizer_use_feedback=TRUE
  124. *._or_expand_nvl_predicate=TRUE
  125. *._ordered_nested_loop=TRUE
  126. *._parallel_broadcast_enabled=TRUE
  127. *._partition_view_enabled=TRUE
  128. *._pivot_imple
  129. mentation_method='CHOOSE'
  130. *._pre_rewrite_push_pred=TRUE
  131. *._pred_move_around=TRUE
  132. *._push_join_predicate=TRUE
  133. *._push_join_union_view=TRUE
  134. *._push_join_union_view2=TRUE
  135. *._px_minus_intersect=TRUE
  136. *._px_partition_scan_enabled=TRUE
  137. *._px_pwg_enabled=TRUE
  138. *._px_ual_serial_input=TRUE
  139. *._query_rewrite_setopgrw_enable=TRUE
  140. *._remove_aggr_subquery=TRUE
  141. *._replace_virtual_columns=TRUE
  142. *._right_outer_hash_enable=TRUE
  143. *._selfjoin_mv_duplicates=TRUE
  144. *._sql_model_unfold_forloops='RUN_TIME'
  145. *._sql
  146. tune_category_parsed='DEFAULT' # parsed sqltune_category
  147. *._subquery_pruning_enabled=TRUE
  148. *._subquery_pruning_mv_enabled=FALSE
  149. *._table_scan_cost_plus_one=TRUE
  150. *._union_rewrite_for_gs='YES_GSET_MVS'
  151. *._unnest_subquery=TRUE
  152. *._use_column_stats_for_function=TRUE
  153. *.audit_file_dest='/u01/app/oracle/admin/cams/adump'
  154. *.audit_trail='DB'
  155. *.background_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
  156. *.compatible='11.2.0.4.0'
  157. *.control_files='/u01/app/oracle/oradata
  158. /cams/control01.ctl'
  159. *.control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
  160. *.core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'
  161. *.db_block_size=8192
  162. *.db_domain=''
  163. *.db_name='cams'
  164. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  165. *.db_recovery_file_dest_size=4182M
  166. *.diagnostic_dest='/u01/app/oracle'
  167. *.dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
  168. *.job_queue_processes=1000
  169. *.log_buffer=6520832 # log buffer update
  170. *.memory_target=744M
  171. *.open_cur
  172. sors=300
  173. *.optimizer_dynamic_sampling=2
  174. *.optimizer_mode='ALL_ROWS'
  175. *.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
  176. *.processes=150
  177. *.query_rewrite_enabled='TRUE'
  178. *.remote_login_passwordfile='EXCLUSIVE'
  179. *.result_cache_max_size=1920K
  180. *.skip_unusable_indexes=TRUE
  181. *.undo_tablespace='UNDOTBS1'
  182. *.user_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter

  183.
  184. [oracle@ora11g dbs]$ cat initcams.ora
  185. # Oracle init.ora parameter file generated by instance cams on 08/02/2017 13:36:21
  186. __db_cache_size=320M
  187. __java_pool_size=4M
  188. __large_pool_size=12M
  189. __oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
  190. __pga_aggregate_target=260M
  191. __sga_target=484M
  192. __shared_io_pool_size=0
  193. __shared_pool_size=136M
  194. __streams_pool_size=0
  195. _aggregation_optimization_settings=0
  196. _always_anti_join='CHOOSE'
  197. _always_semi_join='CHOOSE'
  198. _and_pruning_enabled=TRUE
  199. _b_tree_bitmap_plans=TRUE
  200. _bloom_filter_enabled=TRUE
  201. _bloom_folding_enabled=TRUE
  202. _bloom_pruning_enabled=TRUE
  203. _complex_view_merging=TRUE
  204. _compression_compatibility='11.2.0.4.0'
  205. _connect_by_use_union_all='TRUE'
  206. _convert_set_to_join=FALSE
  207. _cost_equality_semi_join=TRUE
  208. _cpu_to_io=0
  209. _dimension_skip_null=TRUE
  210. _eliminate_common_subexpr=TRUE
  211. _enable_type_dep_selectivity=TRUE
  212. _fast_full_scan_enabled=TRUE
  213. _first_k_rows_dynamic_proration=TRUE
  214. _gby_hash_aggregation_enabled=TRUE
  215. _generalized_pruning_enabled=TRUE
  216. _globalindex_pnum_filter_enabled=TRUE
  217. _gs_anti_semi_join_allowed=TRUE
  218. _improved_outerjoin_card=TRUE
  219. _improved_row_length_enabled=TRUE
  220. _index_join_enabled=TRUE
  221. _ksb_restart_policy_times='0'
  222. _ksb_restart_policy_times='60'
  223. _ksb_restart_policy_times='120'
  224. _ksb_restart_policy_times='240' # internal update to set default
  225. _left_nested_loops_random=TRUE
  226. _local_communication_costing_enabled=TRUE
  227. _minimal_stats_aggregation=TRUE
  228. _mmv_query_rewrite_enabled=TRUE
  229. _new_initial_join_orders=TRUE
  230. _new_sort_cost_estimate=TRUE
  231. _nlj_batching_enabled=1
  232. _optim_adjust_for_part_skews=TRUE
  233. _optim_enhance_nnull_detection=TRUE
  234. _optim_new_default_join_sel=TRUE
  235. _optim_peek_user_binds=TRUE
  236. _optimizer_adaptive_cursor_sharing=TRUE
  237. _optimizer_better_inlist_costing='ALL'
  238. _optimizer_cbqt_no_size_restriction=TRUE
  239. _optimizer_coalesce_subqueries=TRUE
  240. _optimizer_complex_pred_selectivity=TRUE
  241. _optimizer_compute_index_stats=TRUE
  242. _optimizer_connect_by_combine_sw=TRUE
  243. _optimizer_connect_by_cost_based=TRUE
  244. _optimizer_connect_by_elim_dups=TRUE
  245. _optimizer_correct_sq_selectivity=TRUE
  246. _optimizer_cost_based_transformation='LINEAR'
  247. _optimizer_cost_hjsmj_multimatch=TRUE
  248. _optimizer_cost_model='CHOOSE'
  249. _optimizer_dim_subq_join_sel=TRUE
  250. _optimizer_distinct_agg_transform=TRUE
  251. _optimizer_distinct_elimination=TRUE
  252. _optimizer_distinct_placement=TRUE
  253. _optimizer_eliminate_filtering_join=TRUE
  254. _optimizer_enable_density_improvements=TRUE
  255. _optimizer_enable_extended_stats=TRUE
  256. _optimizer_enable_table_lookup_by_nl=TRUE
  257. _optimizer_enhanced_filter_push=TRUE
  258. _optimizer_extend_jppd_view_types=TRUE
  259. _optimizer_extended_cursor_sharing='UDO'
  260. _optimizer_extended_cursor_sharing_rel='SIMPLE'
  261. _optimizer_extended_stats_usage_control=192
  262. _optimizer_false_filter_pred_pullup=TRUE
  263. _optimizer_fast_access_pred_analysis=TRUE
  264. _optimizer_fast_pred_transitivity=TRUE
  265. _optimizer_filter_pred_pullup=TRUE
  266. _optimizer_fkr_index_cost_bias=10
  267. _optimizer_full_outer_join_to_outer=TRUE
  268. _optimizer_group_by_placement=TRUE
  269. _optimizer_improve_selectivity=TRUE
  270. _optimizer_interleave_jppd=TRUE
  271. _optimizer_join_elimination_enabled=TRUE
  272. _optimizer_join_factorization=TRUE
  273. _optimizer_join_order_control=3
  274. _optimizer_join_sel_sanity_check=TRUE
  275. _optimizer_max_permutations=2000
  276. _optimizer_mode_force=TRUE
  277. _optimizer_multi_level_push_pred=TRUE
  278. _optimizer_native_full_outer_join='FORCE'
  279. _optimizer_new_join_card_computation=TRUE
  280. _optimizer_null_aware_antijoin=TRUE
  281. _optimizer_or_expansion='DEPTH'
  282. _optimizer_order_by_elimination_enabled=TRUE
  283. _optimizer_outer_join_to_inner=TRUE
  284. _optimizer_outer_to_anti_enabled=TRUE
  285. _optimizer_push_down_distinct=0
  286. _optimizer_push_pred_cost_based=TRUE
  287. _optimizer_rownum_bind_default=10
  288. _optimizer_rownum_pred_based_fkr=TRUE
  289. _optimizer_skip_scan_enabled=TRUE
  290. _optimizer_sortmerge_join_inequality=TRUE
  291. _optimizer_squ_bottomup=TRUE
  292. _optimizer_star_tran_in_with_clause=TRUE
  293. _optimizer_system_stats_usage=TRUE
  294. _optimizer_table_expansion=TRUE
  295. _optimizer_transitivity_retain=TRUE
  296. _optimizer_try_st_before_jppd=TRUE
  297. _optimizer_undo_cost_change='11.2.0.4'
  298. _optimizer_unnest_corr_set_subq=TRUE
  299. _optimizer_unnest_disjunctive_subq=TRUE
  300. _optimizer_use_cbqt_star_transformation=TRUE
  301. _optimizer_use_feedback=TRUE
  302. _or_expand_nvl_predicate=TRUE
  303. _ordered_nested_loop=TRUE
  304. _parallel_broadcast_enabled=TRUE
  305. _partition_view_enabled=TRUE
  306. _pivot_implementation_method='CHOOSE'
  307. _pre_rewrite_push_pred=TRUE
  308. _pred_move_around=TRUE
  309. _push_join_predicate=TRUE
  310. _push_join_union_view=TRUE
  311. _push_join_union_view2=TRUE
  312. _px_minus_intersect=TRUE
  313. _px_partition_scan_enabled=TRUE
  314. _px_pwg_enabled=TRUE
  315. _px_ual_serial_input=TRUE
  316. _query_rewrite_setopgrw_enable=TRUE
  317. _remove_aggr_subquery=TRUE
  318. _replace_virtual_columns=TRUE
  319. _right_outer_hash_enable=TRUE
  320. _selfjoin_mv_duplicates=TRUE
  321. _sql_model_unfold_forloops='RUN_TIME'
  322. _sqltune_category_parsed='DEFAULT' # parsed sqltune_category
  323. _subquery_pruning_enabled=TRUE
  324. _subquery_pruning_mv_enabled=FALSE
  325. _table_scan_cost_plus_one=TRUE
  326. _union_rewrite_for_gs='YES_GSET_MVS'
  327. _unnest_subquery=TRUE
  328. _use_column_stats_for_function=TRUE
  329. audit_file_dest='/u01/app/oracle/admin/cams/adump'
  330. audit_trail='DB'
  331. background_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
  332. compatible='11.2.0.4.0'
  333. control_files='/u01/app/oracle/oradata/cams/control01.ctl'
  334. control_files='/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
  335. core_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/cdump'
  336. db_block_size=8192
  337. db_domain=''
  338. db_name='cams'
  339. db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  340. db_recovery_file_dest_size=4182M
  341. diagnostic_dest='/u01/app/oracle'
  342. dispatchers='(PROTOCOL=TCP) (SERVICE=camsXDB)'
  343. job_queue_processes=1000
  344. log_buffer=6520832 # log buffer update
  345. memory_target=744M
  346. open_cursors=300
  347. optimizer_dynamic_sampling=2
  348. optimizer_mode='ALL_ROWS'
  349. plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
  350. processes=150
  351. query_rewrite_enabled='TRUE'
  352. remote_login_passwordfile='EXCLUSIVE'
  353. result_cache_max_size=1920K
  354. skip_unusable_indexes=TRUE
  355. undo_tablespace='UNDOTBS1'
  356. user_dump_dest='/u01/app/oracle/diag/rdbms/cams/cams/trace' #Deprecate parameter
  357. [oracle@ora11g dbs]$

第七步：重啟數(shù)據(jù)庫(kù)，檢查恢復(fù)后的參數(shù)文件能否正常使用，并進(jìn)行分析


  1. SYS@cams>shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. 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

  7. SYS@cams>startup;
  8. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  9. ORACLE instance started.

  11. Total System Global Area 776646656 bytes
  12. Fixed Size         2257272 bytes
  13. Variable Size         482348680 bytes
  14. Database Buffers     285212672 bytes
  15. Redo Buffers         6828032 bytes
  16. Database mounted.
  17. Database opened.

這里出現(xiàn)ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance的問(wèn)題，是因?yàn)?/span>background_dump_dest和user_dump_dest兩個(gè)參數(shù)在11gR1中廢棄了，在alert日志中可以看到明確提示：

Oracle11g數(shù)據(jù)庫(kù)參數(shù)文件誤刪除恢復(fù)

對(duì)于兩個(gè)廢棄參數(shù)信息，可以查看官方文檔

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.


第八步：調(diào)整pfile參數(shù)文件，并創(chuàng)建spfile文件后啟動(dòng)

打開(kāi)pfile文件，可以看到兩個(gè)棄用的參數(shù)后面都有注釋“#Deprecate parameter”

Oracle11g數(shù)據(jù)庫(kù)參數(shù)文件誤刪除恢復(fù)

將兩個(gè)廢棄的參數(shù)注釋?zhuān)缓笊蓅pfile文件后啟動(dòng)


  1. SYS@cams>create spfile from pfile;

  3. File created.

  5. SYS@cams>startup;
  6. ORACLE instance started.

  8. Total System Global Area 776646656 bytes
  9. Fixed Size         2257272 bytes
  10. Variable Size         482348680 bytes
  11. Database Buffers     285212672 bytes
  12. Redo Buffers         6828032 bytes
  13. Database mounted.
  14. Database opened.

第九步：模擬在第六步的時(shí)候未能及時(shí)發(fā)現(xiàn)參數(shù)文件被誤刪除，然后數(shù)據(jù)庫(kù)關(guān)閉了，啟動(dòng)的時(shí)候報(bào)錯(cuò)。


  1. [oracle@ora11g dbs]$ ls
  2. backup hc_cams.dat initcams.ora lkCAMS orapwcams spfilecams.ora
  3. [oracle@ora11g dbs]$ mkdir backup1
  4. [oracle@ora11g dbs]$ mv initcams.ora spfilecams.ora backup1/
  5. [oracle@ora11g dbs]$ ls backup1/
  6. initcams.ora spfilecams.ora
  7. [oracle@ora11g dbs]$ ls
  8. backup backup1 hc_cams.dat lkCAMS orapwcams

  1. SYS@cams>shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SYS@cams>startup;
  6. ORA-01078: failure in processing system parameters
  7. LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora'

第十步：找到alert日志，通過(guò)alert日志中的啟動(dòng)信息恢復(fù)pfile參數(shù)文件。


  1. [oracle@ora11g dbs]$ cd /u01/app/oracle/diag/rdbms/cams/cams/trace/
  2. [oracle@ora11g trace]$ ls | grep alert
  3. alert_cams.log

找到最近幾次成功的日志信息，選取其中正確無(wú)誤的一條日志信息：

Oracle11g數(shù)據(jù)庫(kù)參數(shù)文件誤刪除恢復(fù)

創(chuàng)建pfile文件initcams.ora，將alert日志中的參數(shù)信息填入：


  1. [oracle@ora11g trace]$ cd $ORACLE_HOME/dbs
  2. [oracle@ora11g dbs]$ ls
  3. backup backup1 hc_cams.dat lkCAMS orapwcams
  4. [oracle@ora11g dbs]$ vi initcams.ora
  5. [oracle@ora11g dbs]$ cat initcams.ora
  6.   processes = 150
  7.   memory_target = 744M
  8.   control_files = "/u01/app/oracle/oradata/cams/control01.ctl"
  9.   control_files = "/u01/app/oracle/fast_recovery_area/cams/control02.ctl"
  10.   db_block_size = 8192
  11.   compatible = "11.2.0.4.0"
  12.   db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
  13.   db_recovery_file_dest_size= 4182M
  14.   undo_tablespace = "UNDOTBS1"
  15.   remote_login_passwordfile= "EXCLUSIVE"
  16.   db_domain = ""
  17.   dispatchers = "(PROTOCOL=TCP) (SERVICE=camsXDB)"
  18.   job_queue_processes = 1000
  19.   audit_file_dest = "/u01/app/oracle/admin/cams/adump"
  20.   audit_trail = "DB"
  21.   db_name = "cams"
  22.   open_cursors = 300
  23.   diagnostic_dest = "/u01/app/oracle"

直接使用pfile文件啟動(dòng)數(shù)據(jù)庫(kù)：

  1. SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
  2. ORACLE instance started.

  4. Total System Global Area 776646656 bytes
  5. Fixed Size         2257272 bytes
  6. Variable Size         511708808 bytes
  7. Database Buffers     255852544 bytes
  8. Redo Buffers         6828032 bytes
  9. Database mounted.
  10. Database opened.


第十一步：這里假設(shè)第十步的alert日志中沒(méi)找到參數(shù)信息，需要進(jìn)行恢復(fù)，假設(shè)init.ora還能找到。


  1. [oracle@ora11g backup]$ ls
  2. initcams.ora init.ora spfilecams.ora
  3. [oracle@ora11g backup]$ cat init.ora | grep -v ^# | grep -v ^$ > initcams.ora
  4. [oracle@ora11g backup]$ cat initcams.ora
  5. db_name='ORCL'
  6. memory_target=1G
  7. processes = 150
  8. audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
  9. audit_trail ='db'
  10. db_block_size=8192
  11. db_domain=''
  12. db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
  13. db_recovery_file_dest_size=2G
  14. diagnostic_dest='<ORACLE_BASE>'
  15. dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
  16. open_cursors=300
  17. remote_login_passwordfile='EXCLUSIVE'
  18. undo_tablespace='UNDOTBS1'
  19. control_files = (ora_control1, ora_control2)
  20. compatible ='11.2.0'

然后根據(jù)實(shí)際環(huán)境情況修改initcams.ora，啟動(dòng)數(shù)據(jù)庫(kù)，不過(guò)可能會(huì)出現(xiàn)部分參數(shù)的值與原數(shù)據(jù)庫(kù)不一致，需要DBA進(jìn)行調(diào)整。

 

第十二步：這里假設(shè)第十步的alert日志中沒(méi)找到參數(shù)信息，需要進(jìn)行恢復(fù)，假設(shè)init.ora不能找到。


  1. [oracle@ora11g dbs]$ vi initcams.ora
  2. [oracle@ora11g dbs]$ cat initcams.ora
  3. db_name='cams'

使用pfile啟動(dòng)數(shù)據(jù)庫(kù)：


  1. SYS@cams>shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SYS@cams>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcams.ora';
  6. ORACLE instance started.

  8. Total System Global Area 263090176 bytes
  9. Fixed Size         2252256 bytes
  10. Variable Size         205521440 bytes
  11. Database Buffers     50331648 bytes
  12. Redo Buffers         4984832 bytes
  13. ORA-00205: error in identifying control file, check alert log for more info

檢查alert日志


  1. [oracle@ora11g trace]$ tail -n 20 alert_cams.log
  2. CKPT started with pid=12, OS id=5722
  3. Wed Aug 02 14:38:15 2017
  4. SMON started with pid=13, OS id=5724
  5. Wed Aug 02 14:38:15 2017
  6. RECO started with pid=14, OS id=5726
  7. Wed Aug 02 14:38:15 2017
  8. MMON started with pid=15, OS id=5728
  9. Wed Aug 02 14:38:15 2017
  10. MMNL started with pid=16, OS id=5730
  11. ORACLE_BASE from environment = /u01/app/oracle
  12. Wed Aug 02 14:38:15 2017
  13. ALTER DATABASE MOUNT
  14. ORA-00210: cannot open the specified control file
  15. ORA-00202: control file: '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlcams.dbf'
  16. ORA-27037: unable to obtain file status
  17. Linux-x86_64 Error: 2: No such file or directory
  18. Additional information: 3
  19. ORA-205 signalled during: ALTER DATABASE MOUNT...
  20. Wed Aug 02 14:38:15 2017
  21. Checker run found 1 new persistent data failures

修改pfile文件，指定control_files參數(shù)（如果真的忘了，可以用linux命令查找）


  1. [oracle@ora11g dbs]$ find $ORACLE_BASE -name control*
  2. /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/images/database/storage/controlfile.gif
  3. /u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controlWindow.js
  4. /u01/app/oracle/product/11.2.0/db_1/apex/images/fck/editor/dialog/fck_spellerpages/spellerpages/controls.html
  5. /u01/app/oracle/fast_recovery_area/cams/control02.ctl
  6. /u01/app/oracle/oradata/cams/control01.ctl

  1. [oracle@ora11g dbs]$ vi initcams.ora
  2. [oracle@ora11g dbs]$ cat initcams.ora
  3. db_name='cams'
  4. control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'

再次使用pfile啟動(dòng)數(shù)據(jù)庫(kù)


  1. SYS@cams>shutdown immediate;
  2. ORA-01507: database not mounted


  5. ORACLE instance shut down.
  6. SYS@cams>startup;
  7. ORACLE instance started.

  9. Total System Global Area 263090176 bytes
  10. Fixed Size         2252256 bytes
  11. Variable Size         205521440 bytes
  12. Database Buffers     50331648 bytes
  13. Redo Buffers         4984832 bytes
  14. ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version
  15. 11.2.0.0.0
  16. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'

查看錯(cuò)誤信息


  1. [oracle@ora11g dbs]$ oerr ORA 00201
  2. 00201, 00000, "control file version %s incompatible with ORACLE version %s"
  3. // *Cause: The control file was created by incompatible software.
  4. // *Action: Either restart with a compatible software release or use
  5. // CREATE CONTROLFILE to create a new control file that is
  6. // compatible with this release.

這里需要在參數(shù)文件配置一個(gè)compatible參數(shù)


  1. [oracle@ora11g dbs]$ vi initcams.ora
  2. [oracle@ora11g dbs]$ cat initcams.ora
  3. db_name='cams'
  4. control_files='/u01/app/oracle/oradata/cams/control01.ctl','/u01/app/oracle/fast_recovery_area/cams/control02.ctl'
  5. compatible="11.2.0.4.0"

再次使用pfile啟動(dòng)數(shù)據(jù)庫(kù)


  1. SYS@cams>shutdown immediate;
  2. ORA-01507: database not mounted


  5. ORACLE instance shut down.
  6. SYS@cams>startup;
  7. ORACLE instance started.

  9. Total System Global Area 263090176 bytes
  10. Fixed Size         2252256 bytes
  11. Variable Size         205521440 bytes
  12. Database Buffers     50331648 bytes
  13. Redo Buffers         4984832 bytes
  14. Database mounted.
  15. Database opened.

數(shù)據(jù)庫(kù)啟動(dòng)成功。同時(shí)，我們也從測(cè)試過(guò)程中知道，參數(shù)文件至少需要配置db_name，control_files和compatible等3個(gè)參數(shù)信息，可以讓數(shù)據(jù)庫(kù)成功啟動(dòng)。不過(guò)啟動(dòng)之后也需要DBA對(duì)數(shù)據(jù)庫(kù)參數(shù)進(jìn)行調(diào)整。




向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手