ITPub博客

首页 > 数据库 > Oracle > Oracle BBED 跳过归档实现完全恢复

Oracle BBED 跳过归档实现完全恢复

原创 Oracle 作者:yewushang 时间:2014-02-09 20:51:35 0 删除 编辑
很多时候丢失归档,但是客户不想resetlogs open库。可以使用bbed修改scn实现完全恢复。


点击(此处)折叠或打开

  1. [oracle@yws ~]$ df -h
  2. Filesystem Size Used Avail Use% Mounted on
  3. /dev/sda2 18G 9.1G 7.3G 56% /
  4. /dev/sda1 289M 16M 258M 6% /boot
  5. tmpfs 506M 0 506M 0% /dev/shm

  6. [oracle@yws yws]$ sqlplus / as sysdba

  7. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 18:59:57 2013

  8. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  9. Connected to:
  10. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  11. With the Partitioning, OLAP, Data Mining and Real Application Testing options


  12. SQL> shutdown immediate; --关库
  13. Database closed.
  14. Database dismounted.
  15. ORACLE instance shut down.
  16. SQL> exit
  17. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  18. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  19. [oracle@yws yws]$ mkdir /home/oracle/bak
  20. [oracle@yws yws]$ ls
  21. control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
  22. control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
  23. [oracle@yws yws]$ cp * /home/oracle/bak --物理全备
  24. [oracle@yws yws]$ sqlplus / as sysdba

  25. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:04:30 2013

  26. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

  27. Connected to an idle instance.

  28. SQL> startup
  29. ORACLE instance started.

  30. Total System Global Area 285212672 bytes
  31. Fixed Size 1273276 bytes
  32. Variable Size 192938564 bytes
  33. Database Buffers 88080384 bytes
  34. Redo Buffers 2920448 bytes
  35. Database mounted.
  36. Database opened.
  37. SQL> archive log list
  38. Database log mode Archive Mode
  39. Automatic archival Enabled
  40. Archive destination /arc
  41. Oldest online log sequence 140
  42. Next log sequence to archive 142
  43. Current log sequence 142
  44. SQL> set linesize 1000


  45. SQL> select * from v$log;

  46.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  47. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  48.          1 1 142 52428800 1 NO CURRENT 2241033 20-DEC-13
  49.          2 1 140 52428800 1 YES INACTIVE 2220503 18-DEC-13
  50.          3 1 141 52428800 1 YES INACTIVE 2230571 18-DEC-13

  51. SQL> alter system switch logfile; --切日志产生归档

  52. System altered.

  53. SQL> /

  54. System altered.

  55. SQL> /

  56. System altered.

  57. SQL> /

  58. System altered.

  59. SQL> /

  60. System altered.

  61. SQL> /

  62. System altered.

  63. SQL> /

  64. System altered.

  65. SQL> /

  66. System altered.

  67. SQL> /

  68. System altered.

  69. SQL> select * from v$Log; --看序列号

  70.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  71. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  72.          1 1 151 52428800 1 NO CURRENT 2242730 20-DEC-13
  73.          2 1 149 52428800 1 YES INACTIVE 2242726 20-DEC-13
  74.          3 1 150 52428800 1 YES INACTIVE 2242728 20-DEC-13


  75. SQL> archive log list;
  76. Database log mode Archive Mode
  77. Automatic archival Enabled
  78. Archive destination /arc
  79. Oldest online log sequence 149
  80. Next log sequence to archive 151
  81. Current log sequence 151
  82. SQL> exit
  83. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  84. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  85. [oracle@yws yws]$ cd /arc --查看归档
  86. [oracle@yws arc]$ ls
  87. 1_113_832788550.dbf 1_120_832788550.dbf 1_127_832788550.dbf 1_134_832788550.dbf 1_141_832788550.dbf 1_148_832788550.dbf
  88. 1_114_832788550.dbf 1_121_832788550.dbf 1_128_832788550.dbf 1_135_832788550.dbf 1_142_832788550.dbf 1_149_832788550.dbf
  89. 1_115_832788550.dbf 1_122_832788550.dbf 1_129_832788550.dbf 1_136_832788550.dbf 1_143_832788550.dbf 1_150_832788550.dbf
  90. 1_116_832788550.dbf 1_123_832788550.dbf 1_130_832788550.dbf 1_137_832788550.dbf 1_144_832788550.dbf
  91. 1_117_832788550.dbf 1_124_832788550.dbf 1_131_832788550.dbf 1_138_832788550.dbf 1_145_832788550.dbf
  92. 1_118_832788550.dbf 1_125_832788550.dbf 1_132_832788550.dbf 1_139_832788550.dbf 1_146_832788550.dbf
  93. 1_119_832788550.dbf 1_126_832788550.dbf 1_133_832788550.dbf 1_140_832788550.dbf 1_147_832788550.dbf
  94. [oracle@yws arc]$ mv 1_149_832788550.dbf /home/oracle/ --移除在此期间产生的归档
  95. [oracle@yws arc]$ mv 1_150_832788550.dbf /home/oracle/
  96. [oracle@yws arc]$ mv 1_143_832788550.dbf /home/oracle/
  97. [oracle@yws arc]$ mv 1_144_832788550.dbf /home/oracle/
  98. [oracle@yws arc]$ mv 1_145_832788550.dbf /home/oracle/

  99. [oracle@yws yws]$ rm -rf users01.dbf --删除数据文件
  100. [oracle@yws yws]$ sqlplus / as sysdba

  101. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:09:37 2013

  102. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  103. Connected to:
  104. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  105. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  106. SQL> conn scott/tiger
  107. Connected.
  108. SQL> select * from emp; --文件丢失 报错
  109. select * from emp
  110.               *
  111. ERROR at line 1:
  112. ORA-01116: error in opening database file 4
  113. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
  114. ORA-27041: unable to open file
  115. Linux Error: 2: No such file or directory
  116. Additional information: 3


  117. SQL> conn / as sysdba
  118. Connected.
  119. SQL> select status from v$instance;

  120. STATUS
  121. ------------
  122. OPEN


  123. SQL> show user
  124. USER is \"SYS\"
  125. SQL> select * from v$recover_file;

  126. no rows selected

  127. SQL> alter system checkpoint;

  128. System altered.

  129. SQL> select * from v$recover_file;

  130. no rows selected

  131. SQL> select name from v$datafile;

  132. NAME
  133. --------------------------------------------------------------------------------
  134. /u01/oracle/oradata/yws/system01.dbf
  135. /u01/oracle/oradata/yws/undotbs01.dbf
  136. /u01/oracle/oradata/yws/sysaux01.dbf
  137. /u01/oracle/oradata/yws/users01.dbf
  138. /u01/oracle/oradata/yws/example01.dbf

  139. SQL> select * from v$recover_file;

  140. no rows selected

  141. SQL> shutdown immedIATE; --正常关库报错 2.1应该可以 2.5可能有保护机制
  142. ORA-01116: error in opening database file 4
  143. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
  144. ORA-27041: unable to open file
  145. Linux Error: 2: No such file or directory
  146. Additional information: 3
  147. SQL> select status from v$instance;

  148. STATUS
  149. ------------
  150. OPEN

  151. SQL> shutdown abort; --强制停库
  152. ORACLE instance shut down.
  153. SQL> startup;
  154. ORACLE instance started.

  155. Total System Global Area 285212672 bytes
  156. Fixed Size 1273276 bytes
  157. Variable Size 192938564 bytes
  158. Database Buffers 88080384 bytes
  159. Redo Buffers 2920448 bytes
  160. Database mounted.
  161. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
  162. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


  163. SQL> select status from v$instance;

  164. STATUS
  165. ------------
  166. MOUNTED

  167. SQL> select * from v$recover_file; --查看需要恢复的文件

  168.      FILE# ONLINE ONLINE_
  169. ---------- ------- -------
  170. ERROR CHANGE#
  171. ----------------------------------------------------------------- ----------
  172. TIME
  173. ---------
  174.          4 ONLINE ONLINE
  175. FILE NOT FOUND 0



  176. [oracle@yws oradata]$ mv users01.dbf yws/ --把原来的备份拿回来
  177. [oracle@yws oradata]$ cd yws/
  178. [oracle@yws yws]$ ls
  179. control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
  180. control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
  181. [oracle@yws yws]$ sqlplus / as sysdba

  182. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:21:08 2013

  183. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


  184. Connected to:
  185. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  186. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  187. SQL> alter database open;
  188. alter database open
  189. *
  190. ERROR at line 1:
  191. ORA-01113: file 4 needs media recovery if it was restored from backup, or END
  192. BACKUP if it was not
  193. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


  194. SQL> recover datafile 4; --恢复
  195. ORA-00279: change 2242425 generated at 12/20/2013 19:00:23 needed for thread 1
  196. ORA-00289: suggestion : /arc/1_142_832788550.dbf
  197. ORA-00280: change 2242425 for thread 1 is in sequence #142


  198. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  199. auto --自动模式让他去选择
  200. ORA-00279: change 2242713 generated at 12/20/2013 19:06:21 needed for thread 1
  201. ORA-00289: suggestion : /arc/1_143_832788550.dbf
  202. ORA-00280: change 2242713 for thread 1 is in sequence #143
  203. ORA-00278: log file \'/arc/1_142_832788550.dbf\' no longer needed for this
  204. recovery


  205. ORA-00308: cannot open archived log \'/arc/1_143_832788550.dbf\' --需要seq为143的归档 此文件已经被我们移走
  206. ORA-27037: unable to obtain file status
  207. Linux Error: 2: No such file or directory
  208. Additional information: 3

使用bbed实现完全恢复


点击(此处)折叠或打开

  1. 点击(此处)折叠或打开
  2. SQL> select status from v$instance;
  3. STATUS
  4. ------------
  5. MOUNTED
  6. SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header; --查看数据文件头scn
  7. NAME CHECKPOINT_CHANGE#
  8. ---------------------------------------------------------------------------------------------------- ------------------
  9. /u01/oracle/oradata/yws/system01.dbf 2242908
  10. /u01/oracle/oradata/yws/undotbs01.dbf 2242908
  11. /u01/oracle/oradata/yws/sysaux01.dbf 2242908
  12. /u01/oracle/oradata/yws/users01.dbf 2242713
  13. /u01/oracle/oradata/yws/example01.dbf 2242908
  14. SQL> select to_char('2242713','XXXXXXXXX') from dual; --4号文件scn 转成16进制值
  15. TO_CHAR('2
  16. ----------
  17. 223899
  18. bbed 修改
  19. BBED> set dba 1,1
  20. DBA 0x00400001 (4194305 1,1)
  21. BBED> set offset 484
  22. OFFSET 484
  23. BBED> p
  24. pad
  25. ---
  26. ub1 pad @484 0x5c
  27. BBED> dump --查看1号数据文件的scn
  28. File: /u01/oracle/oradata/yws/system01.dbf (1)
  29. Block: 1 Offsets: 484 to 995 Dba:0x00400001
  30. ------------------------------------------------------------------------
  31. 5c392200 0000a8bf 1e68c031 0100b552 97000000 95000000 1000af2e 02000000
  32. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  33. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  34. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  35. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  36. 0a000a00 0a000100 00000000 00000000 00000000 02004000 00000000 00000000
  37. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  38. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  39. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  40. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  41. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  42. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  43. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  44. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  45. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  46. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  47. <32 bytes per line>
  48. BBED> set mode edit;
  49. MODE Edit
  50. BBED> modify /x 5c3922 dba 4,1 offset 484 --修改4号文件的数据文件头scn一致
  51. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
  52. File: /u01/oracle/oradata/yws/users01.dbf (4)
  53. Block: 1 Offsets: 484 to 995 Dba:0x01000001
  54. ------------------------------------------------------------------------
  55. 5c392200 00000000 ad66c031 01000000 8f000000 02000000 00000000 02000000
  56. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  57. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  58. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  59. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  60. 0a000a00 0a000100 00000000 00000000 00000000 02000001 00000000 00000000
  61. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  62. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  63. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  64. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  65. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  66. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  67. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  68. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  69. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  70. 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  71. <32 bytes per line>
  72. BBED> sum apply;
  73. Check value for File 4, Block 1:
  74. current = 0xcdd9, required = 0xcdd9
  75. BBED> exit
  76. [oracle@yws yws]$ sqlplus / as sysdba
  77. SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 20 19:39:04 2013
  78. Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
  79. Connected to:
  80. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  81. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  82. SQL> startup force;
  83. ORACLE instance started.
  84. Total System Global Area 285212672 bytes
  85. Fixed Size 1273276 bytes
  86. Variable Size 192938564 bytes
  87. Database Buffers 88080384 bytes
  88. Redo Buffers 2920448 bytes
  89. Database mounted.
  90. ORA-01113: file 4 needs media recovery if it was restored from backup, or END
  91. BACKUP if it was not
  92. ORA-01110: data file 4: '/u01/oracle/oradata/yws/users01.dbf'
  93. SQL> recover database;
    Media recovery complete.
    SQL> alter database open;


    Database altered.

  94. 成功open。



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

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

注册时间:2014-02-09

  • 博文量
    53
  • 访问量
    275985