ITPub博客

首页 > 数据库 > Oracle > Oracle手工不完全恢复(二):使用备份控制文件

Oracle手工不完全恢复(二):使用备份控制文件

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


相关连接:
Oracle手工完全恢复案例 
Oracle手工不完全恢复(一):使用当前控制文件 

示例一:冷备份所有数据文件--->新建表空间--->备份控制文件(日志文件完好)

 

实验环境:

当前的控制文件损坏,新创建的表空间损坏,冷备的数据文件中没有该数据文件的备份,但是控制文件和联机日志中有相关的记录;由于控制文件损坏,只能使用备份的控制文件来做恢复。

  1. --查看数据库中已有的表空间
  2. SYS@seiang11g>select * from v$tablespace;

  3.        TS# NAME INC BIG FLA ENC
  4. ---------- -------------------------------------------------- --- --- --- ---
  5.          0 SYSTEM YES NO YES
  6.          1 SYSAUX YES NO YES
  7.          2 UNDOTBS1 YES NO YES
  8.          4 USERS YES NO YES
  9.          3 TEMP NO NO YES
  10.          6 EXAMPLE YES NO YES
  11.          7 RMAN_CATALOG YES NO YES
  12.          8 SEIANG YES NO YES
  13.          9 WJQ YES NO YES
  14.         10 WJQBEST YES NO YES

  15. --查看当前日志的序列号为3
  16. SYS@seiang11g>select group#,sequence#,status from v$log;

  17.     GROUP# SEQUENCE# STATUS
  18. ---------- ---------- ----------------
  19.          1 1 INACTIVE
  20.          2 2 INACTIVE
  21.          3 3 CURRENT


  22. --新创建一个表空间test
  23. SYS@seiang11g>create tablespace test datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf' size 5M;
  24. Tablespace created.


  25. --表空间创建完成之后,备份控制文件
  26. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak';
  27. Database altered.

  28. --查看数据库中控制文件的多元化路径
  29. SYS@seiang11g>show parameter control

  30. NAME TYPE VALUE
  31. ------------------------------------ ----------- ------------------------------
  32. control_file_record_keep_time integer 14
  33. control_files string /u01/app/oracle/oradata/OraDB1
  34.                                                  1g/control01.ctl, /u01/app/ora
  35.                                                  cle/fast_recovery_area/OraDB11
  36.                                                  g/control02.ctl
  37. control_management_pack_access string DIAGNOSTIC+TUNING


  38. --4在seiang用户下创建一张表test4,隶属于test表空间
  39. SYS@seiang11g>create table seiang.test4(ID number,name varchar2(30)) tablespace test;
  40. Table created.


  41. --在test4表中插入两条数据,并提交
  42. SYS@seiang11g>insert into seiang.test4 values(1001,'wjq');
  43. 1 row created.

  44. SYS@seiang11g>insert into seiang.test4 values(1002,'seiang');
  45. 1 row created.

  46. SYS@seiang11g>commit;
  47. Commit complete.

  48. --执行日志切换,刚插入的表中的记录信息已归档
  49. SYS@seiang11g>alter system switch logfile;
  50. System altered.

  51. --查看当前的日志序列号为4
  52. SYS@seiang11g>select group#,sequence#,status from v$log;

  53.     GROUP# SEQUENCE# STATUS
  54. ---------- ---------- ----------------
  55.          1 4 CURRENT
  56.          2 2 INACTIVE
  57.          3 3 ACTIVE

  58. --再在test4表中插入两条数据,但后两条插入的数据记录在当前日志文件1中
  59. SYS@seiang11g>insert into seiang.test4 values(1003,'wjqgood');
  60. 1 row created.

  61. SYS@seiang11g>insert into seiang.test4 values(1004,'wjqbest');
  62. 1 row created.

  63. SYS@seiang11g>commit;
  64. Commit complete.

  65. --查看test4表中数据的内容
  66. SYS@seiang11g>select * from seiang.test4;

  67.         ID NAME
  68. ---------- --------------------------------------------------
  69.       1001 wjq
  70.       1002 seiang
  71.       1003 wjqgood
  72.       1004 wjqbest


  73. --模拟test表空间中数据文件损坏或丢失,以及控制文件损坏
  74. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/test01.dbf
  75. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/control01.ctl

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


  77. --数据库已经宕机,无法访问
  78. SYS@seiang11g>select * from seiang.test4;
  79. select * from seiang.test4
  80. *
  81. ERROR at line 1:
  82. ORA-03135: connection lost contact
  83. Process ID: 17679
  84. 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;

  4.      FILE# CHECKPOINT_CHANGE# NAME
  5. ---------- ------------------ --------------------------------------------------
  6.          1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  7.          2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  8.          3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  9.          4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  10.          5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  11.          6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  12.          7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  13.          8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  14.          9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  15.         10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf

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

  18.      FILE# CHECKPOINT_CHANGE# NAME
  19. ---------- ------------------ --------------------------------------------------
  20.          1 1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  21.          2 1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  22.          3 1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  23.          4 1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  24.          5 1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  25.          6 1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  26.          7 1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  27.          8 1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  28.          9 1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  29.         10 0


  30. 可以看出:
  31. ①    file10在控制文件里记录是test01.dbf,而与之对应的数据文件10是不存在的,
  32. ②    备份的数据备份的SCN比控制文件SCN还老。


  33. --查看需要恢复的数据文件
  34. SYS@seiang11g>select * from v$recover_file;

  35.      FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
  36. ---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
  37.          1 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  38.          2 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  39.          3 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  40.          4 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  41.          5 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  42.          6 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  43.          7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  44.          8 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  45.          9 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17
  46.         10 ONLINE ONLINE FILE NOT FOUND 0

  47.     
  48. --尝试做完全恢复,提示使用备份的控制文件来恢复
  49. SYS@seiang11g>recover database;
  50. ORA-00283: recovery session canceled due to errors
  51. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


  52. --使用备份的控制文件来做恢复,出现报错
  53. SYS@seiang11g>recover database using backup controlfile;
  54. ORA-00283: recovery session canceled due to errors
  55. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'
  56. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
  57. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/test01.dbf'

  58. 此错是因为老备份里没有abcd表空间,但只要控制文件里记录了abcd就好办,方法是建一个datafile的空文件,而其中内容可由日志文件recover(前滚)时填补出来。


  59. --新建一个数据文件
  60. SYS@seiang11g>alter database create datafile '/u01/app/oracle/oradata/OraDB11g/test01.dbf';
  61. Database altered.


  62. --再次查看控制文件和数据文件头中做记录的SCN
  63. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

  64.      FILE# CHECKPOINT_CHANGE# NAME
  65. ---------- ------------------ --------------------------------------------------
  66.          1 1981768 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  67.          2 1981768 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  68.          3 1981768 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  69.          4 1981768 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  70.          5 1981768 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  71.          6 1981768 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  72.          7 1981768 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  73.          8 1981768 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  74.          9 1981768 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  75.         10 1986000 /u01/app/oracle/oradata/OraDB11g/test01.dbf

  76. 10 rows selected.

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

  79.      FILE# CHECKPOINT_CHANGE# NAME
  80. ---------- ------------------ --------------------------------------------------
  81.          1 1913766 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  82.          2 1913766 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  83.          3 1913766 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  84.          4 1913766 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  85.          5 1913766 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  86.          6 1913766 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  87.          7 1913766 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  88.          8 1913766 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  89.          9 1913766 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  90.         10 1985999 /u01/app/oracle/oradata/OraDB11g/test01.dbf


  91. --再次使用备份的控制文件来做恢复
  92. SYS@seiang11g>recover database using backup controlfile;
  93. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  94. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  95. ORA-00280: change 1913766 for thread 1 is in sequence #1

  96. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  97. auto (因为需要的日志已经归档,所以选择auto)
  98. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
  99. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
  100. ORA-00280: change 1914386 for thread 1 is in sequence #2
  101. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery


  102. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
  103. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
  104. ORA-00280: change 1914402 for thread 1 is in sequence #1


  105. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
  106. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
  107. ORA-00280: change 1936446 for thread 1 is in sequence #2
  108. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery


  109. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
  110. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
  111. ORA-00280: change 1937042 for thread 1 is in sequence #3
  112. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery


  113. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
  114. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
  115. ORA-00280: change 1937100 for thread 1 is in sequence #4
  116. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery


  117. ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
  118. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
  119. ORA-00280: change 1937111 for thread 1 is in sequence #1


  120. ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
  121. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
  122. ORA-00280: change 1955524 for thread 1 is in sequence #2
  123. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery


  124. ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
  125. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
  126. ORA-00280: change 1981768 for thread 1 is in sequence #3
  127. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery


  128. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
  129. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
  130. ORA-00280: change 1986580 for thread 1 is in sequence #4
  131. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery


  132. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_950977433_4.log'
  133. ORA-27037: unable to obtain file status
  134. Linux-x86_64 Error: 2: No such file or directory
  135. Additional information: 3

  136. 出现此错误,因为当前的当前的日志文件尚未归档,所以出现错误,所以接下来使用当前的日志文件来做恢复

  137. SYS@seiang11g>recover database using backup controlfile;
  138. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
  139. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
  140. ORA-00280: change 1986580 for thread 1 is in sequence #4


  141. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  142. /u01/app/oracle/oradata/OraDB11g/redo01.log (当前日志文件)
  143. Log applied.
  144. Media recovery complete.
  145. SYS@seiang11g>


  146. --恢复完成,使用resetlogs打开数据库
  147. SYS@seiang11g>alter database open resetlogs;
  148. Database altered.

  149. --查看控制文件和数据文件头记录的SCN一致
  150. SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

  151.      FILE# CHECKPOINT_CHANGE#
  152. ---------- ------------------
  153.          1 1986883
  154.          2 1986883
  155.          3 1986883
  156.          4 1986883
  157.          5 1986883
  158.          6 1986883
  159.          7 1986883
  160.          8 1986883
  161.          9 1986883
  162.         10 1986883

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

  164.      FILE# CHECKPOINT_CHANGE#
  165. ---------- ------------------
  166.          1 1986883
  167.          2 1986883
  168.          3 1986883
  169.          4 1986883
  170.          5 1986883
  171.          6 1986883
  172.          7 1986883
  173.          8 1986883
  174.          9 1986883
  175.         10 1986883

  176. --确认test4表中的数据全部恢复成功
  177. SYS@seiang11g>select * from seiang.test4;

  178.         ID NAME
  179. ---------- --------------------------------------------------
  180.       1001 wjq
  181.       1002 seiang
  182.       1003 wjqgood
  183.       1004 wjqbest


示例二:冷备份所有数据文件--->备份控制文件--->新建表空间(日志文件完好)

 

实验环境:

当前的控制文件损坏,新创建的表空间损坏,冷备的数据文件中没有该数据文件的备份,控制文件中也没有该表空间的记录,但是联机日志中有相关的记录;由于控制文件损坏,只能使用备份的控制文件来做恢复。


  1. --查看数据库中已存在的表空间
  2. SYS@seiang11g>select * from v$tablespace;

  3.        TS# NAME INC BIG FLA ENC
  4. ---------- -------------------------------------------------- --- --- --- ---
  5.          0 SYSTEM YES NO YES
  6.          1 SYSAUX YES NO YES
  7.          2 UNDOTBS1 YES NO YES
  8.          4 USERS YES NO YES
  9.          3 TEMP NO NO YES
  10.          6 EXAMPLE YES NO YES
  11.          7 RMAN_CATALOG YES NO YES
  12.          8 SEIANG YES NO YES
  13.          9 WJQ YES NO YES
  14.         10 WJQBEST YES NO YES


  15. --备份控制文件
  16. SYS@seiang11g>alter database backup controlfile to '/u01/app/oracle/oradata/OraDB11g/control.bak1';
  17. Database altered.


  18. -创建表空间comsys该表空间记录在当前的日志redo01.log中
  19. SYS@seiang11g>create tablespace comsys datafile '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf' size 5M;
  20. Tablespace created.


  21. --在seiang用户下创建一张表test4,隶属于comsys表空间
  22. SYS@seiang11g>create table seiang.test4(age number,address varchar2(10)) tablespace comsys;
  23. Table created.

  24. --在test4表中插入两条数据,并提交
  25. SYS@seiang11g>insert into seiang.test4 values(23,'beijing');
  26. 1 row created.

  27. SYS@seiang11g>insert into seiang.test4 values(25,'shanghai');
  28. 1 row created.

  29. SYS@seiang11g>commit;
  30. Commit complete.

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

  32.        AGE ADDRESS
  33. ---------- ----------
  34.         23 beijing
  35.         25 shanghai


  36. --查看当前日志的序列号为1
  37. SYS@seiang11g>select group#,sequence#,status from v$log;

  38.     GROUP# SEQUENCE# STATUS
  39. ---------- ---------- ----------------
  40.          1 1 CURRENT
  41.          2 0 UNUSED
  42.          3 0 UNUSED


  43. --模拟comsys01.dbf数据文件丢失或损坏,控制文件损坏
  44. SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/comsys01.dbf

  45. SYS@seiang11g>shutdown abort
  46. ORACLE instance shut down.

  47. --从备份的文件中还原控制文件和数据文件
  48. SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/control.bak1 /u01/app/oracle/oradata/OraDB11g/control01.ctl

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

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

  1. SYS@seiang11g>startup
  2. ORACLE instance started.

  3. Total System Global Area 1252663296 bytes
  4. Fixed Size 2252824 bytes
  5. Variable Size 788533224 bytes
  6. Database Buffers 452984832 bytes
  7. Redo Buffers 8892416 bytes
  8. Database mounted.
  9. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


  10. --查看控制文件和数据文件头,发现并没有comsys表空间的相关记录
  11. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

  12.      FILE# CHECKPOINT_CHANGE# NAME
  13. ---------- ------------------ --------------------------------------------------
  14.          1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  15.          2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  16.          3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  17.          4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  18.          5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  19.          6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  20.          7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  21.          8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  22.          9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf


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

  24.      FILE# CHECKPOINT_CHANGE# NAME
  25. ---------- ------------------ --------------------------------------------------
  26.          1 1913765 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  27.          2 1913765 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  28.          3 1913765 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  29.          4 1913765 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  30.          5 1913765 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  31.          6 1913765 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  32.          7 1913765 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  33.          8 1913765 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  34.          9 1913765 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf


  35. --尝试完全恢复,提示使用备份的控制文件做恢复
  36. SYS@seiang11g>recover database;
  37. ORA-00283: recovery session canceled due to errors
  38. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


  39. --使用备份的控制文件做恢复
  40. SYS@seiang11g>recover database using backup controlfile;
  41. ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1
  42. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_1.log
  43. ORA-00280: change 1913766 for thread 1 is in sequence #1


  44. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  45. auto (该日志已归档,所以选择auto)
  46. ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1
  47. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950971495_2.log
  48. ORA-00280: change 1914386 for thread 1 is in sequence #2
  49. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950971495_1.log' no longer needed for this recovery


  50. ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1
  51. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_1.log
  52. ORA-00280: change 1914402 for thread 1 is in sequence #1


  53. ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1
  54. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_2.log
  55. ORA-00280: change 1936446 for thread 1 is in sequence #2
  56. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_1.log' no longer needed for this recovery


  57. ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1
  58. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_3.log
  59. ORA-00280: change 1937042 for thread 1 is in sequence #3
  60. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_2.log' no longer needed for this recovery


  61. ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1
  62. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950972396_4.log
  63. ORA-00280: change 1937100 for thread 1 is in sequence #4
  64. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950972396_3.log' no longer needed for this recovery


  65. ORA-00279: change 1937111 generated at 08/02/2017 16:23:53 needed for thread 1
  66. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_1.log
  67. ORA-00280: change 1937111 for thread 1 is in sequence #1


  68. ORA-00279: change 1955524 generated at 08/02/2017 22:00:32 needed for thread 1
  69. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_2.log
  70. ORA-00280: change 1955524 for thread 1 is in sequence #2
  71. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_1.log' no longer needed for this recovery


  72. ORA-00279: change 1981768 generated at 08/03/2017 08:19:54 needed for thread 1
  73. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_3.log
  74. ORA-00280: change 1981768 for thread 1 is in sequence #3
  75. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_2.log' no longer needed for this recovery


  76. ORA-00279: change 1986580 generated at 08/03/2017 10:00:19 needed for thread 1
  77. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_950977433_4.log
  78. ORA-00280: change 1986580 for thread 1 is in sequence #4
  79. ORA-00278: log file '/u01/app/oracle/arch/arch_1_950977433_3.log' no longer needed for this recovery


  80. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1
  81. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
  82. ORA-00280: change 1986880 for thread 1 is in sequence #1


  83. ORA-00308: cannot open archived log '/u01/app/oracle/arch/arch_1_951042827_1.log'
  84. ORA-27037: unable to obtain file status
  85. Linux-x86_64 Error: 2: No such file or directory
  86. Additional information: 3

  87. 出现此错误,因为当前的日志文件尚未归档,所以出现错误,所以接下来使用当前的日志文件来做恢复


  88. SYS@seiang11g>recover database using backup controlfile;
  89. ORA-00279: change 1986880 generated at 08/03/2017 10:33:47 needed for thread 1
  90. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
  91. ORA-00280: change 1986880 for thread 1 is in sequence #1


  92. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  93. /u01/app/oracle/oradata/OraDB11g/redo01.log (当前的日志文件)
  94. ORA-00283: recovery session canceled due to errors
  95. ORA-01244: unnamed datafile(s) added to control file by media recovery
  96. ORA-01110: data file 10: '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf'
  97. (从当前的日志文件中,我们发现了关于comsys表空间的相关记录)

  98. ORA-01112: media recovery not started


  99. 当再次使用备份的控制文件做恢复时,出现如下的错误提示
  100. SYS@seiang11g>recover database using backup controlfile;
  101. ORA-00283: recovery session canceled due to errors
  102. ORA-01111: name for data file 10 is unknown - rename to correct file
  103. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'
  104. ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
  105. ORA-01111: name for data file 10 is unknown - rename to correct file
  106. ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'

  107. --查看控制文件和数据文件头,有了关于comsys表空间的相关记录
  108. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

  109.      FILE# CHECKPOINT_CHANGE# NAME
  110. ---------- ------------------ --------------------------------------------------
  111.          1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  112.          2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  113.          3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  114.          4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  115.          5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  116.          6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  117.          7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  118.          8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  119.          9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  120.         10 1988334 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAME
  121.                               D00010


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

  123.      FILE# CHECKPOINT_CHANGE# NAME
  124. ---------- ------------------ --------------------------------------------------
  125.          1 1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  126.          2 1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  127.          3 1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  128.          4 1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  129.          5 1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  130.          6 1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  131.          7 1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  132.          8 1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  133.          9 1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  134.         10 0

  135. --创建数据文件,并对控制文件中记录未知的数据文件重命名
  136. SYS@seiang11g>alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010'
  137.   2 as '/u01/app/oracle/oradata/OraDB11g/comsys01.dbf';
  138. Database altered.

  139. (当前的日志文件)
  140. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

  141.      FILE# CHECKPOINT_CHANGE# NAME
  142. ---------- ------------------ --------------------------------------------------
  143.          1 1986883 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  144.          2 1986883 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  145.          3 1986883 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  146.          4 1986883 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  147.          5 1986883 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  148.          6 1986883 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  149.          7 1986883 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  150.          8 1986883 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  151.          9 1986883 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  152.         10 1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


  153. --再次查看控制文件和数据文件头
  154. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile_header;

  155.      FILE# CHECKPOINT_CHANGE# NAME
  156. ---------- ------------------ --------------------------------------------------
  157.          1 1988336 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  158.          2 1988336 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  159.          3 1988336 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  160.          4 1988336 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  161.          5 1988336 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  162.          6 1988336 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  163.          7 1988336 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  164.          8 1988336 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  165.          9 1988336 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  166.         10 1988334 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


  167. --再次使用备份的控制文件和当前日志做恢复
  168. SYS@seiang11g>recover database using backup controlfile;
  169. ORA-00279: change 1988334 generated at 08/03/2017 10:53:39 needed for thread 1
  170. ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_951042827_1.log
  171. ORA-00280: change 1988334 for thread 1 is in sequence #1


  172. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  173. /u01/app/oracle/oradata/OraDB11g/redo01.log
  174. Log applied.
  175. Media recovery complete.


  176. --恢复完成后,使用resetlogs打开数据库
  177. SYS@seiang11g>alter database open resetlogs;
  178. Database altered.


  179. --查看控制文件和数据文件头SCN一致
  180. SYS@seiang11g>select file#,checkpoint_change#,name from v$datafile;

  181.      FILE# CHECKPOINT_CHANGE# NAME
  182. ---------- ------------------ --------------------------------------------------
  183.          1 1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  184.          2 1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  185.          3 1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  186.          4 1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  187.          5 1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  188.          6 1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  189.          7 1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  190.          8 1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  191.          9 1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  192.         10 1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


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

  194.      FILE# CHECKPOINT_CHANGE# NAME
  195. ---------- ------------------ --------------------------------------------------
  196.          1 1989738 /u01/app/oracle/oradata/OraDB11g/system01.dbf
  197.          2 1989738 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf
  198.          3 1989738 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf
  199.          4 1989738 /u01/app/oracle/oradata/OraDB11g/users01.dbf
  200.          5 1989738 /u01/app/oracle/oradata/OraDB11g/example01.dbf
  201.          6 1989738 /u01/app/oracle/oradata/OraDB11g/rman01.dbf
  202.          7 1989738 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf
  203.          8 1989738 /u01/app/oracle/oradata/OraDB11g/wjq01.dbf
  204.          9 1989738 /u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf
  205.         10 1989738 /u01/app/oracle/oradata/OraDB11g/comsys01.dbf


  206. --查看已恢复test4表中的数据记录
  207. SYS@seiang11g>select * from seiang.test4;

  208.        AGE ADDRESS
  209. ---------- ----------
  210.         23 beijing
  211.         25 shanghai


作者:SEian.G(苦练七十二变,笑对八十一难)

ITPUBhttp://blog.itpub.net/31015730/

51CTOhttp://seiang.blog.51cto.com/


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31015730/viewspace-2143113/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-03-12

  • 博文量
    101
  • 访问量
    205896