ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle手工不完全恢复（二）：使用备份控制文件

Oracle手工不完全恢复（二）：使用备份控制文件

原创 Oracle 作者：迷倪小魏 时间：2017-08-04 21:52:10 0 删除 编辑


相关连接：
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博客 ” ，链接：https://blog.itpub.net/31015730/viewspace-2143113/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 通过sqlplus连接数据库服务器很慢的问题
下一篇： SYS,SYSTEM,DBA,SYSDBA,SYSOPER的区别与联系
请登录后发表评论 登录
全部评论
迷倪小魏

注册时间：2016-03-12

  • 博文量
    101
  • 访问量
    453410

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593