Oracle手工不完全恢復（二）：使用備份控制檔案

迷倪小魏發表於2017-08-04
Oracle


相關連線：
Oracle手工完全恢復案例 
Oracle手工不完全恢復（一）：使用當前控制檔案 

示例一：冷備份所有資料檔案--->新建表空間--->備份控制檔案（日誌檔案完好）

 

實驗環境：

當前的控制檔案損壞，新建立的表空間損壞，冷備的資料檔案中沒有該資料檔案的備份，但是控制檔案和聯機日誌中有相關的記錄；由於控制檔案損壞，只能使用備份的控制檔案來做恢復。

  1. --檢視資料庫中已有的表空間
  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. --檢視當前日誌的序列號為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. --新建立一個表空間test
  28. SYS@seiang11g>create tablespace test datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf' size 5M;
  29. Tablespace created.


  32. --表空間建立完成之後，備份控制檔案
  33. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak';
  34. Database altered.

  36. --檢視資料庫中控制檔案的多元化路徑
  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使用者下建立一張表test4,隸屬於test表空間
  50. SYS@seiang11g>create table seiang.test4(ID number,name varchar2(30)) tablespace test;
  51. Table created.


  54. --在test4表中插入兩條資料，並提交
  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. --執行日誌切換，剛插入的表中的記錄資訊已歸檔
  65. SYS@seiang11g>alter system switch logfile;
  66. System altered.

  68. --檢視當前的日誌序列號為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表中插入兩條資料，但後兩條插入的資料記錄在當前日誌檔案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表中資料的內容
  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表空間中資料檔案損壞或丟失，以及控制檔案損壞
  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. --資料庫已經當機，無法訪問
  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. --還原所有的資料檔案和控制檔案，準備做不完全恢復

    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. --檢視控制檔案和資料檔案頭所記錄的SCN，發現test01.dbf資料檔案頭沒有記錄
  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,而與之對應的資料檔案10是不存在的，
  37. ②    備份的資料備份的SCN比控制檔案SCN還老。


  40. --檢視需要恢復的資料檔案
  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. --嘗試做完全恢復，提示使用備份的控制檔案來恢復
  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. --使用備份的控制檔案來做恢復，出現報錯
  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. 此錯是因為老備份裡沒有abcd表空間，但只要控制檔案裡記錄了abcd就好辦，方法是建一個datafile的空檔案，而其中內容可由日誌檔案recover(前滾)時填補出來。


  73. --新建一個資料檔案
  74. SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';
  75. Database altered.


  78. --再次檢視控制檔案和資料檔案頭中做記錄的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. --再次使用備份的控制檔案來做恢復
  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 (因為需要的日誌已經歸檔，所以選擇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. 出現此錯誤，因為當前的當前的日誌檔案尚未歸檔，所以出現錯誤，所以接下來使用當前的日誌檔案來做恢復

  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 （當前日誌檔案）
  188. Log applied.
  189. Media recovery complete.
  190. SYS@seiang11g>


  193. --恢復完成，使用resetlogs開啟資料庫
  194. SYS@seiang11g>alter database open resetlogs;
  195. Database altered.

  197. --檢視控制檔案和資料檔案頭記錄的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. --確認test4表中的資料全部恢復成功
  229. SYS@seiang11g>select * from seiang.test4;

  231.         ID NAME
  232. ---------- --------------------------------------------------
  233.       1001 wjq
  234.       1002 seiang
  235.       1003 wjqgood
  236.       1004 wjqbest


示例二：冷備份所有資料檔案--->備份控制檔案--->新建表空間（日誌檔案完好）

 

實驗環境：

當前的控制檔案損壞，新建立的表空間損壞，冷備的資料檔案中沒有該資料檔案的備份，控制檔案中也沒有該表空間的記錄，但是聯機日誌中有相關的記錄；由於控制檔案損壞，只能使用備份的控制檔案來做恢復。


  1. --檢視資料庫中已存在的表空間
  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. -建立表空間comsys該表空間記錄在當前的日誌redo01.log中
  24. SYS@seiang11g>create tablespace comsys datafile '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' size 5M;
  25. Tablespace created.


  28. --在seiang使用者下建立一張表test4,隸屬於comsys表空間
  29. SYS@seiang11g>create table seiang.test4(age number,address varchar2(10)) tablespace comsys;
  30. Table created.

  32. --在test4表中插入兩條資料，並提交
  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. --檢視當前日誌的序列號為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資料檔案丟失或損壞，控制檔案損壞
  61. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/comsys01.dbf

  63. SYS@seiang11g>shutdown abort
  64. ORACLE instance shut down.

  66. --從備份的檔案中還原控制檔案和資料檔案
  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. --檢視控制檔案和資料檔案頭，發現並沒有comsys表空間的相關記錄
  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. --嘗試完全恢復，提示使用備份的控制檔案做恢復
  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. --使用備份的控制檔案做恢復
  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. 出現此錯誤，因為當前的日誌檔案尚未歸檔，所以出現錯誤，所以接下來使用當前的日誌檔案來做恢復


  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 (當前的日誌檔案)
  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. (從當前的日誌檔案中，我們發現了關於comsys表空間的相關記錄)

  137. ORA-01112: media recovery not started


  140. 當再次使用備份的控制檔案做恢復時，出現如下的錯誤提示
  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. --檢視控制檔案和資料檔案頭，有了關於comsys表空間的相關記錄
  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. --建立資料檔案，並對控制檔案中記錄未知的資料檔案重新命名
  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. (當前的日誌檔案)
  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. --再次檢視控制檔案和資料檔案頭
  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. --再次使用備份的控制檔案和當前日誌做恢復
  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. --恢復完成後，使用resetlogs開啟資料庫
  235. SYS@seiang11g>alter database open resetlogs;
  236. Database altered.


  239. --檢視控制檔案和資料檔案頭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. --檢視已恢復test4表中的資料記錄
  273. SYS@seiang11g>select * from seiang.test4;

  275.        AGE ADDRESS
  276. ---------- ----------
  277.         23 beijing
  278.         25 shanghai


作者：SEian.G（苦練七十二變，笑對八十一難）

ITPUB：http://blog.itpub.net/31015730/

51CTO：http://seiang.blog.51cto.com/


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2143113/，如需轉載，請註明出處，否則將追究法律責任。

相關文章