溫馨提示×

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

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

Oracle如何使用備份控制文件

發(fā)布時(shí)間：2021-11-10 09:49:44 來源：億速云 閱讀：157 作者：小新 欄目：關(guān)系型數(shù)據(jù)庫

這篇文章主要為大家展示了“Oracle如何使用備份控制文件”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“Oracle如何使用備份控制文件”這篇文章吧。




示例一：冷備份所有數(shù)據(jù)文件--->新建表空間--->備份控制文件（日志文件完好）

實(shí)驗(yàn)環(huán)境：

當(dāng)前的控制文件損壞，新創(chuàng)建的表空間損壞，冷備的數(shù)據(jù)文件中沒有該數(shù)據(jù)文件的備份，但是控制文件和聯(lián)機(jī)日志中有相關(guān)的記錄；由于控制文件損壞，只能使用備份的控制文件來做恢復(fù)。

  1. --查看數(shù)據(jù)庫中已有的表空間

  2. SYS@seiang11g>select * from v$tablespace;


  4.        TS# NAME                                               INC BIG FLA ENC

  5. ---------- -------------------------------------------------- --- --- --- ---

  6.          0 SYSTEM                                             YES NO  YES

  7.          1 SYSAUX                                             YES NO  YES

  8.          2 UNDOTBS1                                           YES NO  YES

  9.          4 USERS                                              YES NO  YES

  10.          3 TEMP                                               NO  NO  YES

  11.          6 EXAMPLE                                            YES NO  YES

  12.          7 RMAN_CATALOG                                       YES NO  YES

  13.          8 SEIANG                                             YES NO  YES

  14.          9 WJQ                                                YES NO  YES

  15.         10 WJQBEST                                            YES NO  YES


  17. --查看當(dāng)前日志的序列號(hào)為3

  18. SYS@seiang11g>select group#,sequence#,status from v$log;


  20.     GROUP# SEQUENCE# STATUS

  21. ---------- ---------- ----------------

  22.          1          1 INACTIVE

  23.          2          2 INACTIVE

  24.          3          3 CURRENT



  27. --新創(chuàng)建一個(gè)表空間test

  28. SYS@seiang11g>create tablespace test datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf' size 5M;

  29. Tablespace created.



  32. --表空間創(chuàng)建完成之后，備份控制文件

  33. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak';

  34. Database altered.


  36. --查看數(shù)據(jù)庫中控制文件的多元化路徑

  37. SYS@seiang11g>show parameter control


  39. NAME TYPE VALUE

  40. ------------------------------------ ----------- ------------------------------

  41. control_file_record_keep_time integer 14

  42. control_files                        string /u01/app/oracle/oradata/OraDB1

  43.                                                  1g/control01.ctl, /u01/app/ora

  44.                                                  cle/fast_recovery_area/OraDB11

  45.                                                  g/control02.ctl

  46. control_management_pack_access       string      DIAGNOSTIC+TUNING



  49. --4在seiang用戶下創(chuàng)建一張表test4,隸屬于test表空間

  50. SYS@seiang11g>create table seiang.test4(ID number,name varchar2(30)) tablespace test;

  51. Table created.



  54. --在test4表中插入兩條數(shù)據(jù)，并提交

  55. SYS@seiang11g>insert into seiang.test4 values(1001,'wjq');

  56. 1 row created.


  58. SYS@seiang11g>insert into seiang.test4 values(1002,'seiang');

  59. 1 row created.


  61. SYS@seiang11g>commit;

  62. Commit complete.


  64. --執(zhí)行日志切換，剛插入的表中的記錄信息已歸檔

  65. SYS@seiang11g>alter system switch logfile;

  66. System altered.


  68. --查看當(dāng)前的日志序列號(hào)為4

  69. SYS@seiang11g>select group#,sequence#,status from v$log;


  71.     GROUP# SEQUENCE# STATUS

  72. ---------- ---------- ----------------

  73.          1          4 CURRENT

  74.          2          2 INACTIVE

  75.          3          3 ACTIVE


  77. --再在test4表中插入兩條數(shù)據(jù)，但后兩條插入的數(shù)據(jù)記錄在當(dāng)前日志文件1中

  78. SYS@seiang11g>insert into seiang.test4 values(1003,'wjqgood');

  79. 1 row created.


  81. SYS@seiang11g>insert into seiang.test4 values(1004,'wjqbest');

  82. 1 row created.


  84. SYS@seiang11g>commit;

  85. Commit complete.


  87. --查看test4表中數(shù)據(jù)的內(nèi)容

  88. SYS@seiang11g>select * from seiang.test4;


  90.         ID NAME

  91. ---------- --------------------------------------------------

  92.       1001 wjq

  93.       1002 seiang

  94.       1003 wjqgood

  95.       1004 wjqbest



  98. --模擬test表空間中數(shù)據(jù)文件損壞或丟失，以及控制文件損壞

  99. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/test01.dbf

  100. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/control01.ctl


  102. SYS@seiang11g>host rm /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl



  105. --數(shù)據(jù)庫已經(jīng)宕機(jī)，無法訪問

  106. SYS@seiang11g>select * from seiang.test4;

  107. select * from seiang.test4

  108. *

  109. ERROR at line 1:

  110. ORA-03135: connection lost contact

  111. Process ID: 17679

  112. Session ID: 34 Serial number: 531


  1. --還原所有的數(shù)據(jù)文件和控制文件，準(zhǔn)備做不完全恢復(fù)

    SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g/


    SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/oradata/OraDB11g/control01.ctl


    SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl


  2. --查看控制文件和數(shù)據(jù)文件頭所記錄的SCN，發(fā)現(xiàn)test01.dbf數(shù)據(jù)文件頭沒有記錄

  3. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  5.      FILE# CHECKPOINT_CHANGE# NAME

  6. ---------- ------------------ --------------------------------------------------

  7.          1            1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  8.          2            1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  9.          3            1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  10.          4            1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  11.          5            1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  12.          6            1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  13.          7            1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  14.          8            1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  15.          9            1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  16.         10            1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf


  18. SYS@seiang11g>

  19. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  21.      FILE# CHECKPOINT_CHANGE# NAME

  22. ---------- ------------------ --------------------------------------------------

  23.          1            1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  24.          2            1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  25.          3            1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  26.          4            1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  27.          5            1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  28.          6            1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  29.          7            1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  30.          8            1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  31.          9            1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  32.         10                  0



  35. 可以看出：

  36. ①    file10在控制文件里記錄是test01.dbf,而與之對(duì)應(yīng)的數(shù)據(jù)文件10是不存在的，

  37. ②    備份的數(shù)據(jù)備份的SCN比控制文件SCN還老。



  40. --查看需要恢復(fù)的數(shù)據(jù)文件

  41. SYS@seiang11g>select * from v$recover_file;


  43.      FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

  44. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------

  45.          1 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  46.          2 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  47.          3 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  48.          4 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  49.          5 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  50.          6 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  51.          7 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  52.          8 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  53.          9 ONLINE  ONLINE  UNKNOWN ERROR                                                        1913765 02-AUG-17

  54.         10 ONLINE  ONLINE  FILE NOT FOUND                                                             0


  56.     

  57. --嘗試做完全恢復(fù)，提示使用備份的控制文件來恢復(fù)

  58. SYS@seiang11g>recover database;

  59. ORA-00283: recovery session canceled due to errors

  60. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done



  63. --使用備份的控制文件來做恢復(fù)，出現(xiàn)報(bào)錯(cuò)

  64. SYS@seiang11g>recover database using backup controlfile;

  65. ORA-00283: recovery session canceled due to errors

  66. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'

  67. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

  68. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'


  70. 此錯(cuò)是因?yàn)槔蟼浞堇餂]有abcd表空間，但只要控制文件里記錄了abcd就好辦，方法是建一個(gè)datafile的空文件，而其中內(nèi)容可由日志文件recover(前滾)時(shí)填補(bǔ)出來。



  73. --新建一個(gè)數(shù)據(jù)文件

  74. SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';

  75. Database altered.



  78. --再次查看控制文件和數(shù)據(jù)文件頭中做記錄的SCN

  79. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  81.      FILE# CHECKPOINT_CHANGE# NAME

  82. ---------- ------------------ --------------------------------------------------

  83.          1            1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  84.          2            1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  85.          3            1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  86.          4            1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  87.          5            1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  88.          6            1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  89.          7            1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  90.          8            1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  91.          9            1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  92.         10            1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf


  94. 10 rows selected.


  96. SYS@seiang11g>

  97. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  99.      FILE# CHECKPOINT_CHANGE# NAME

  100. ---------- ------------------ --------------------------------------------------

  101.          1            1913766 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  102.          2            1913766 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  103.          3            1913766 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  104.          4            1913766 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  105.          5            1913766 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  106.          6            1913766 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  107.          7            1913766 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  108.          8            1913766 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  109.          9            1913766 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  110.         10            1985999 /u01/app/oracle/oradata/OraDB11g/test01.dbf



  113. --再次使用備份的控制文件來做恢復(fù)

  114. SYS@seiang11g>recover database using backup controlfile;

  115. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1

  116. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log

  117. ORA-00280: change 1913766 for thread 1 is in sequence #1


  119. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  120. auto (因?yàn)樾枰娜罩疽呀?jīng)歸檔，所以選擇auto)

  121. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1

  122. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log

  123. ORA-00280: change 1914386 for thread 1 is in sequence #2

  124. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery



  127. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1

  128. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log

  129. ORA-00280: change 1914402 for thread 1 is in sequence #1



  132. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1

  133. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log

  134. ORA-00280: change 1936446 for thread 1 is in sequence #2

  135. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery



  138. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1

  139. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log

  140. ORA-00280: change 1937042 for thread 1 is in sequence #3

  141. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery



  144. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1

  145. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log

  146. ORA-00280: change 1937100 for thread 1 is in sequence #4

  147. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery



  150. ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1

  151. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log

  152. ORA-00280: change 1937111 for thread 1 is in sequence #1



  155. ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1

  156. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log

  157. ORA-00280: change 1955524 for thread 1 is in sequence #2

  158. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery



  161. ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1

  162. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log

  163. ORA-00280: change 1981768 for thread 1 is in sequence #3

  164. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery



  167. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1

  168. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log

  169. ORA-00280: change 1986580 for thread 1 is in sequence #4

  170. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery



  173. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950977433_4.log'

  174. ORA-27037: unable to obtain file status

  175. Linux-x86_64 Error: 2: No such file or directory

  176. Additional information: 3


  178. 出現(xiàn)此錯(cuò)誤，因?yàn)楫?dāng)前的當(dāng)前的日志文件尚未歸檔，所以出現(xiàn)錯(cuò)誤，所以接下來使用當(dāng)前的日志文件來做恢復(fù)


  180. SYS@seiang11g>recover database using backup controlfile;

  181. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1

  182. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log

  183. ORA-00280: change 1986580 for thread 1 is in sequence #4



  186. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  187. /u01/app/oracle/oradata/OraDB11g/redo01.log （當(dāng)前日志文件）

  188. Log applied.

  189. Media recovery complete.

  190. SYS@seiang11g>



  193. --恢復(fù)完成，使用resetlogs打開數(shù)據(jù)庫

  194. SYS@seiang11g>alter database open resetlogs;

  195. Database altered.


  197. --查看控制文件和數(shù)據(jù)文件頭記錄的SCN一致

  198. SYS@seiang11g>select file#,checkpoint_change# from v$datafile;


  200.      FILE# CHECKPOINT_CHANGE#

  201. ---------- ------------------

  202.          1            1986883

  203.          2            1986883

  204.          3            1986883

  205.          4            1986883

  206.          5            1986883

  207.          6            1986883

  208.          7            1986883

  209.          8            1986883

  210.          9            1986883

  211.         10            1986883


  213. SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;


  215.      FILE# CHECKPOINT_CHANGE#

  216. ---------- ------------------

  217.          1            1986883

  218.          2            1986883

  219.          3            1986883

  220.          4            1986883

  221.          5            1986883

  222.          6            1986883

  223.          7            1986883

  224.          8            1986883

  225.          9            1986883

  226.         10            1986883


  228. --確認(rèn)test4表中的數(shù)據(jù)全部恢復(fù)成功

  229. SYS@seiang11g>select * from seiang.test4;


  231.         ID NAME

  232. ---------- --------------------------------------------------

  233.       1001 wjq

  234.       1002 seiang

  235.       1003 wjqgood

  236.       1004 wjqbest



示例二：冷備份所有數(shù)據(jù)文件--->備份控制文件--->新建表空間（日志文件完好）

 

實(shí)驗(yàn)環(huán)境：

當(dāng)前的控制文件損壞，新創(chuàng)建的表空間損壞，冷備的數(shù)據(jù)文件中沒有該數(shù)據(jù)文件的備份，控制文件中也沒有該表空間的記錄，但是聯(lián)機(jī)日志中有相關(guān)的記錄；由于控制文件損壞，只能使用備份的控制文件來做恢復(fù)。


  1. --查看數(shù)據(jù)庫中已存在的表空間

  2. SYS@seiang11g>select * from v$tablespace;


  4.        TS# NAME                                               INC BIG FLA ENC

  5. ---------- -------------------------------------------------- --- --- --- ---

  6.          0 SYSTEM                                             YES NO  YES

  7.          1 SYSAUX                                             YES NO  YES

  8.          2 UNDOTBS1                                           YES NO  YES

  9.          4 USERS                                              YES NO  YES

  10.          3 TEMP                                               NO  NO  YES

  11.          6 EXAMPLE                                            YES NO  YES

  12.          7 RMAN_CATALOG                                       YES NO  YES

  13.          8 SEIANG                                             YES NO  YES

  14.          9 WJQ                                                YES NO  YES

  15.         10 WJQBEST                                            YES NO  YES



  18. --備份控制文件

  19. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak1';

  20. Database altered.



  23. -創(chuàng)建表空間comsys該表空間記錄在當(dāng)前的日志redo01.log中

  24. SYS@seiang11g>create tablespace comsys datafile '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' size 5M;

  25. Tablespace created.



  28. --在seiang用戶下創(chuàng)建一張表test4,隸屬于comsys表空間

  29. SYS@seiang11g>create table seiang.test4(age number,address varchar2(10)) tablespace comsys;

  30. Table created.


  32. --在test4表中插入兩條數(shù)據(jù)，并提交

  33. SYS@seiang11g>insert into seiang.test4 values(23,'beijing');

  34. 1 row created.


  36. SYS@seiang11g>insert into seiang.test4 values(25,'shanghai');

  37. 1 row created.


  39. SYS@seiang11g>commit;

  40. Commit complete.


  42. SYS@seiang11g>select * from seiang.test4;


  44.        AGE ADDRESS

  45. ---------- ----------

  46.         23 beijing

  47.         25 shanghai



  50. --查看當(dāng)前日志的序列號(hào)為1

  51. SYS@seiang11g>select group#,sequence#,status from v$log;


  53.     GROUP# SEQUENCE# STATUS

  54. ---------- ---------- ----------------

  55.          1          1 CURRENT

  56.          2          0 UNUSED

  57.          3          0 UNUSED



  60. --模擬comsys01.dbf數(shù)據(jù)文件丟失或損壞，控制文件損壞

  61. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


  63. SYS@seiang11g>shutdown abort

  64. ORACLE instance shut down.


  66. --從備份的文件中還原控制文件和數(shù)據(jù)文件

  67. SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak1 /u01/app/oracle/oradata/OraDB11g/control01.ctl


  69. SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak1 /u01/app/oracle/fast_recovery_area/OraDB11g/control02.ctl


  71. SYS@seiang11g>host cp /u01/app/oracle/UMAN_Backup/*.dbf /u01/app/oracle/oradata/OraDB11g

  1. SYS@seiang11g>startup

  2. ORACLE instance started.


  4. Total System Global Area 1252663296 bytes

  5. Fixed Size 2252824 bytes

  6. Variable Size 788533224 bytes

  7. Database Buffers          452984832 bytes

  8. Redo Buffers                8892416 bytes

  9. Database mounted.

  10. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



  13. --查看控制文件和數(shù)據(jù)文件頭，發(fā)現(xiàn)并沒有comsys表空間的相關(guān)記錄

  14. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  16.      FILE# CHECKPOINT_CHANGE# NAME

  17. ---------- ------------------ --------------------------------------------------

  18.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  19.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  20.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  21.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  22.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  23.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  24.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  25.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  26.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf



  29. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  31.      FILE# CHECKPOINT_CHANGE# NAME

  32. ---------- ------------------ --------------------------------------------------

  33.          1            1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  34.          2            1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  35.          3            1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  36.          4            1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  37.          5            1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  38.          6            1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  39.          7            1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  40.          8            1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  41.          9            1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf



  44. --嘗試完全恢復(fù)，提示使用備份的控制文件做恢復(fù)

  45. SYS@seiang11g>recover database;

  46. ORA-00283: recovery session canceled due to errors

  47. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done



  50. --使用備份的控制文件做恢復(fù)

  51. SYS@seiang11g>recover database using backup controlfile;

  52. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1

  53. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log

  54. ORA-00280: change 1913766 for thread 1 is in sequence #1



  57. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  58. auto    （該日志已歸檔，所以選擇auto）

  59. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1

  60. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log

  61. ORA-00280: change 1914386 for thread 1 is in sequence #2

  62. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery



  65. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1

  66. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log

  67. ORA-00280: change 1914402 for thread 1 is in sequence #1



  70. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1

  71. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log

  72. ORA-00280: change 1936446 for thread 1 is in sequence #2

  73. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery



  76. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1

  77. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log

  78. ORA-00280: change 1937042 for thread 1 is in sequence #3

  79. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery



  82. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1

  83. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log

  84. ORA-00280: change 1937100 for thread 1 is in sequence #4

  85. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery



  88. ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1

  89. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log

  90. ORA-00280: change 1937111 for thread 1 is in sequence #1



  93. ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1

  94. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log

  95. ORA-00280: change 1955524 for thread 1 is in sequence #2

  96. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery



  99. ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1

  100. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log

  101. ORA-00280: change 1981768 for thread 1 is in sequence #3

  102. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery



  105. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1

  106. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log

  107. ORA-00280: change 1986580 for thread 1 is in sequence #4

  108. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery



  111. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1

  112. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  113. ORA-00280: change 1986880 for thread 1 is in sequence #1



  116. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_951042827_1.log'

  117. ORA-27037: unable to obtain file status

  118. Linux-x86_64 Error: 2: No such file or directory

  119. Additional information: 3


  121. 出現(xiàn)此錯(cuò)誤，因?yàn)楫?dāng)前的日志文件尚未歸檔，所以出現(xiàn)錯(cuò)誤，所以接下來使用當(dāng)前的日志文件來做恢復(fù)



  124. SYS@seiang11g>recover database using backup controlfile;

  125. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1

  126. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  127. ORA-00280: change 1986880 for thread 1 is in sequence #1



  130. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  131. /u01/app/oracle/oradata/OraDB11g/redo01.log (當(dāng)前的日志文件)

  132. ORA-00283: recovery session canceled due to errors

  133. ORA-01244: unnamed datafile(s) added to control file by media recovery

  134. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf'

  135. (從當(dāng)前的日志文件中，我們發(fā)現(xiàn)了關(guān)于comsys表空間的相關(guān)記錄)


  137. ORA-01112: media recovery not started



  140. 當(dāng)再次使用備份的控制文件做恢復(fù)時(shí)，出現(xiàn)如下的錯(cuò)誤提示

  141. SYS@seiang11g>recover database using backup controlfile;

  142. ORA-00283: recovery session canceled due to errors

  143. ORA-01111: name for data file 10 is unknown - rename to correct file

  144. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'

  145. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

  146. ORA-01111: name for data file 10 is unknown - rename to correct file

  147. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'


  149. --查看控制文件和數(shù)據(jù)文件頭，有了關(guān)于comsys表空間的相關(guān)記錄

  150. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  152.      FILE# CHECKPOINT_CHANGE# NAME

  153. ---------- ------------------ --------------------------------------------------

  154.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  155.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  156.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  157.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  158.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  159.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  160.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  161.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  162.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  163.         10            1988334 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAME

  164.                               D00010



  167. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  169.      FILE# CHECKPOINT_CHANGE# NAME

  170. ---------- ------------------ --------------------------------------------------

  171.          1            1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  172.          2            1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  173.          3            1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  174.          4            1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  175.          5            1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  176.          6            1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  177.          7            1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  178.          8            1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  179.          9            1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  180.         10                  0


  182. --創(chuàng)建數(shù)據(jù)文件，并對(duì)控制文件中記錄未知的數(shù)據(jù)文件重命名

  183. SYS@seiang11g>alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'

  184.   2 as '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf';

  185. Database altered.


  187. (當(dāng)前的日志文件)

  188. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  190.      FILE# CHECKPOINT_CHANGE# NAME

  191. ---------- ------------------ --------------------------------------------------

  192.          1            1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  193.          2            1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  194.          3            1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  195.          4            1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  196.          5            1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  197.          6            1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  198.          7            1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  199.          8            1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  200.          9            1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  201.         10            1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  204. --再次查看控制文件和數(shù)據(jù)文件頭

  205. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  207.      FILE# CHECKPOINT_CHANGE# NAME

  208. ---------- ------------------ --------------------------------------------------

  209.          1            1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  210.          2            1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  211.          3            1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  212.          4            1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  213.          5            1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  214.          6            1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  215.          7            1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  216.          8            1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  217.          9            1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  218.         10            1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  221. --再次使用備份的控制文件和當(dāng)前日志做恢復(fù)

  222. SYS@seiang11g>recover database using backup controlfile;

  223. ORA-00279: change 1988334 generated at 08/03/2017 10:53:39 needed for thread 1

  224. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log

  225. ORA-00280: change 1988334 for thread 1 is in sequence #1



  228. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  229. /u01/app/oracle/oradata/OraDB11g/redo01.log

  230. Log applied.

  231. Media recovery complete.



  234. --恢復(fù)完成后，使用resetlogs打開數(shù)據(jù)庫

  235. SYS@seiang11g>alter database open resetlogs;

  236. Database altered.



  239. --查看控制文件和數(shù)據(jù)文件頭SCN一致

  240. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;


  242.      FILE# CHECKPOINT_CHANGE# NAME

  243. ---------- ------------------ --------------------------------------------------

  244.          1            1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  245.          2            1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  246.          3            1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  247.          4            1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  248.          5            1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  249.          6            1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  250.          7            1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  251.          8            1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  252.          9            1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  253.         10            1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  256. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;


  258.      FILE# CHECKPOINT_CHANGE# NAME

  259. ---------- ------------------ --------------------------------------------------

  260.          1            1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf

  261.          2            1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf

  262.          3            1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

  263.          4            1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf

  264.          5            1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf

  265.          6            1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf

  266.          7            1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

  267.          8            1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf

  268.          9            1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf

  269.         10            1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf



  272. --查看已恢復(fù)test4表中的數(shù)據(jù)記錄

  273. SYS@seiang11g>select * from seiang.test4;


  275.        AGE ADDRESS

  276. ---------- ----------

  277.         23 beijing

  278.         25 shanghai

以上是“Oracle如何使用備份控制文件”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！

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

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

AI
助
手