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

最新推荐文章于 2019-07-26 14:55:10 发布
最新推荐文章于 2019-07-26 14:55:10 发布
阅读量132 收藏
点赞数
文章标签： 数据库 操作系统
很多时候丢失归档，但是客户不想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

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

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

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


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


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

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

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

  37. Connected to an idle instance.

  39. SQL> startup
  40. ORACLE instance started.

  42. Total System Global Area 285212672 bytes
  43. Fixed Size 1273276 bytes
  44. Variable Size 192938564 bytes
  45. Database Buffers 88080384 bytes
  46. Redo Buffers 2920448 bytes
  47. Database mounted.
  48. Database opened.
  49. SQL> archive log list
  50. Database log mode Archive Mode
  51. Automatic archival Enabled
  52. Archive destination /arc
  53. Oldest online log sequence 140
  54. Next log sequence to archive 142
  55. Current log sequence 142
  56. SQL> set linesize 1000


  59. SQL> select * from v$log;

  61.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  62. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  63.          1 1 142 52428800 1 NO CURRENT 2241033 20-DEC-13
  64.          2 1 140 52428800 1 YES INACTIVE 2220503 18-DEC-13
  65.          3 1 141 52428800 1 YES INACTIVE 2230571 18-DEC-13

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

  69. System altered.

  71. SQL> /

  73. System altered.

  75. SQL> /

  77. System altered.

  79. SQL> /

  81. System altered.

  83. SQL> /

  85. System altered.

  87. SQL> /

  89. System altered.

  91. SQL> /

  93. System altered.

  95. SQL> /

  97. System altered.

  99. SQL> /

  101. System altered.

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

  105.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  106. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
  107.          1 1 151 52428800 1 NO CURRENT 2242730 20-DEC-13
  108.          2 1 149 52428800 1 YES INACTIVE 2242726 20-DEC-13
  109.          3 1 150 52428800 1 YES INACTIVE 2242728 20-DEC-13


  112. SQL> archive log list;
  113. Database log mode Archive Mode
  114. Automatic archival Enabled
  115. Archive destination /arc
  116. Oldest online log sequence 149
  117. Next log sequence to archive 151
  118. Current log sequence 151
  119. SQL> exit
  120. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  121. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  122. [oracle@yws yws]$ cd /arc --查看归档
  123. [oracle@yws arc]$ ls
  124. 1_113_832788550.dbf 1_120_832788550.dbf 1_127_832788550.dbf 1_134_832788550.dbf 1_141_832788550.dbf 1_148_832788550.dbf
  125. 1_114_832788550.dbf 1_121_832788550.dbf 1_128_832788550.dbf 1_135_832788550.dbf 1_142_832788550.dbf 1_149_832788550.dbf
  126. 1_115_832788550.dbf 1_122_832788550.dbf 1_129_832788550.dbf 1_136_832788550.dbf 1_143_832788550.dbf 1_150_832788550.dbf
  127. 1_116_832788550.dbf 1_123_832788550.dbf 1_130_832788550.dbf 1_137_832788550.dbf 1_144_832788550.dbf
  128. 1_117_832788550.dbf 1_124_832788550.dbf 1_131_832788550.dbf 1_138_832788550.dbf 1_145_832788550.dbf
  129. 1_118_832788550.dbf 1_125_832788550.dbf 1_132_832788550.dbf 1_139_832788550.dbf 1_146_832788550.dbf
  130. 1_119_832788550.dbf 1_126_832788550.dbf 1_133_832788550.dbf 1_140_832788550.dbf 1_147_832788550.dbf
  131. [oracle@yws arc]$ mv 1_149_832788550.dbf /home/oracle/ --移除在此期间产生的归档
  132. [oracle@yws arc]$ mv 1_150_832788550.dbf /home/oracle/
  133. [oracle@yws arc]$ mv 1_143_832788550.dbf /home/oracle/
  134. [oracle@yws arc]$ mv 1_144_832788550.dbf /home/oracle/
  135. [oracle@yws arc]$ mv 1_145_832788550.dbf /home/oracle/

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

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

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


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

  149. SQL> conn scott/tiger
  150. Connected.
  151. SQL> select * from emp; --文件丢失 报错
  152. select * from emp
  153.               *
  154. ERROR at line 1:
  155. ORA-01116: error in opening database file 4
  156. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'
  157. ORA-27041: unable to open file
  158. Linux Error: 2: No such file or directory
  159. Additional information: 3


  162. SQL> conn / as sysdba
  163. Connected.
  164. SQL> select status from v$instance;

  166. STATUS
  167. ------------
  168. OPEN


  171. SQL> show user
  172. USER is \"SYS\"
  173. SQL> select * from v$recover_file;

  175. no rows selected

  177. SQL> alter system checkpoint;

  179. System altered.

  181. SQL> select * from v$recover_file;

  183. no rows selected

  185. SQL> select name from v$datafile;

  187. NAME
  188. --------------------------------------------------------------------------------
  189. /u01/oracle/oradata/yws/system01.dbf
  190. /u01/oracle/oradata/yws/undotbs01.dbf
  191. /u01/oracle/oradata/yws/sysaux01.dbf
  192. /u01/oracle/oradata/yws/users01.dbf
  193. /u01/oracle/oradata/yws/example01.dbf

  195. SQL> select * from v$recover_file;

  197. no rows selected

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

  207. STATUS
  208. ------------
  209. OPEN

  211. SQL> shutdown abort; --强制停库
  212. ORACLE instance shut down.
  213. SQL> startup;
  214. ORACLE instance started.

  216. Total System Global Area 285212672 bytes
  217. Fixed Size 1273276 bytes
  218. Variable Size 192938564 bytes
  219. Database Buffers 88080384 bytes
  220. Redo Buffers 2920448 bytes
  221. Database mounted.
  222. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
  223. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


  226. SQL> select status from v$instance;

  228. STATUS
  229. ------------
  230. MOUNTED

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

  234.      FILE# ONLINE ONLINE_
  235. ---------- ------- -------
  236. ERROR CHANGE#
  237. ----------------------------------------------------------------- ----------
  238. TIME
  239. ---------
  240.          4 ONLINE ONLINE
  241. FILE NOT FOUND 0



  245. [oracle@yws oradata]$ mv users01.dbf yws/ --把原来的备份拿回来
  246. [oracle@yws oradata]$ cd yws/
  247. [oracle@yws yws]$ ls
  248. control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
  249. control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
  250. [oracle@yws yws]$ sqlplus / as sysdba

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

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


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

  261. SQL> alter database open;
  262. alter database open
  263. *
  264. ERROR at line 1:
  265. ORA-01113: file 4 needs media recovery if it was restored from backup, or END
  266. BACKUP if it was not
  267. ORA-01110: data file 4: \'/u01/oracle/oradata/yws/users01.dbf\'


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


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


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

使用bbed实现完全恢复


点击(此处)折叠或打开

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


    Database altered.

  120. 成功open。



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

转载于:http://blog.itpub.net/29477587/viewspace-1078561/

cizhaorun3127
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle实验中的错误,【案例】Oracle报错ORA-01190的产生原因和2种官方解决办法
weixin_29964487的博客
04-09 835
【案例】Oracle报错ORA-01190的产生原因和2种官方解决办法时间:2016-10-21 19:34来源:Oracle研究中心作者:HTZ点击:次天萃荷净运维DBA反映生产环境数据库在宕机后数据文件的SCN不一致并报错ORA-01190.总结出两种方法来修复数据文件头SCN不一致的方法。ORA-01190这个错误比较常见.特别在没有正规DBA运维的环境中比较常见.先看看O...
Oracle BBED单个数据文件跳过丢失的归档继续恢复(一)
dbhang的博客
10-09 439
场景描述：       数据库利用备份进行恢复时，需要执行restore+recover，restore利用备份片恢复数据文件，recover是进行归档日志应用到最新状态，当我们归档日志断档丢失时，可以利用bbed跳过丢失的归档日志，继续恢复下去，在生产环境中bbed是极其危险的操作，谨慎使用，本次测试是单个数据文件测试。 环境构建： SQL> archive log list; Database log mode
Oracle 之利用BBED跳过归档日志实现恢复（二）
雨花石
02-22 951
oracle bbed 修复 bbed 跳过归档日志恢复
通过BBED跳过缺失归档做恢复
weixin_34321977的博客
11-22 115
环境：Linux+oracle11.2.0.2描述：这次主要是模拟在恢复过程中有丢失归档日志,但有想跳过这个归档日志继续恢复,简单的思路是这样子,如果我们把需要恢复的datafileheaderscn改到跟下个归档日志的scn一致(v$archived_log.next_change#)是否就可以跳过了呢？答案是否定的,下面我们就开始模拟测试下。1.模拟环境[oracl...
oracle11g 32位和64位linux 安装,编译bbed及三个文件
04-26
oracle11g 32位和64位linux 安装,编译bbed及三个文件
Oracle11g for linux_x64安装BBED需要的3个文件
05-08
Oracle11g安装 BBED 需要的3个文件：sbbdpt.o、ssbbded.o和bbedus.msb
非常规恢复使用BBED跳过归档
12-17
在Oracle数据库管理领域，当遇到一些特殊的情况时，可能需要采用非常规的方式来恢复数据库，其中一种方法就是利用BBED工具来跳过归档日志进行恢复。这种方法主要用于解决在归档日志丢失或损坏的情况下，如何尽可能地...
Oracle bbed工具
03-26
Oracle bbed工具是Oracle数据库历史上一个著名的实用程序，主要用于数据修复和数据库的低级别编辑。这个工具在Oracle 11g版本之后不再被官方提供，但它的存在仍然是许多DBA和数据恢复专家的重要参考，特别是在处理旧...
Oracle bbed工具 包含Linux和Windows版本
06-21
Oracle bbed工具是Block Browser and Editor（块浏览编辑器）的缩写，它是Oracle数据库在安装时一起附带的工具。一般此工具倾向于仅作为Oracle内部使用，且Oracle公司并不公开此工具使用细节。由于bbed可以对Oracle...
oracle bbed for windows
03-31
找到能在Win10上运行的Oracle BBED版本，意味着该版本可能已经解决了这些问题，或者通过特定的兼容性设置实现了运行。 Oracle BBED主要功能包括： 1. **数据查看**：可以查看数据库中的任何数据块，包括表空间、...
在linux下安装oracle bbed
04-11 85
[oracle@localhost lib]$ cd $ORACLE_HOME/rdbms/lib　　[oracle@localhost lib]$ls ins_rdbms.mk　　ins_rdbms.mk　　[oracle@l...
BBED的安装及简单的使用方法
Data & Analysis
07-26 389
记录下BBED的安装--ORACLE 11.2.0.4下 1.在ORACLE 11G下安装BBED，需要从ORACLE 10G中复制三个包 bbedus.msb sbbdpt.o ssbbded.o 下载地址：32位 http://download.csdn.net/detail/q947817003/658700164位 htt...
oracle 11g for linux bbed - Oracle BBED工具安装
Data & Analysis
09-07 725
一、工具简介 这里我们简单介绍下Oracle bbed工具。bbed是Block Browser and Editor（块浏览编辑器）的缩写，它是Oracle数据库在安装时一起附带的工具。一般此工具倾向于仅作为Oracle内部使用，且Oracle公司并不公开此工具使用细节。由于bbed可以对Oracle数据库中的数据块进行内容修改和破坏，因此此工具功能强大同时伴有很高的使用风险。 如果你需要使...
Oracle 之BBED 工具介绍
雨花石
02-21 2629
oracle bbed
Oracle db 11.2.0.4 linux 6.3下编译bbed
msdnchina的专栏@JiNan,ShanDong
04-22 1617
11.2.0.4 linux 6.3下编译bbed
AUL 挖取11g UNDO段名称
cizhaorun3127的博客
02-16 126
挖取11g undosegment 名称。 下载老楼的AUL. 1.创建配置文件。 点击(此处)折叠或打开 SQL> spool d...
ORA-01173:data dictionary indicates missing data file from system tablespace恢复
cizhaorun3127的博客
02-15 1045
问题：一朋友的库开库报错：ORA-01173: data dictionary indicates missing data file from system tablespace 我的是11.2.0.1 他的是11.2....
ORA-12170: TNS:Connect timeout occurred
cizhaorun3127的博客
02-10 323
ORA-12170: TNS:Connect timeout occurred 点击(此处)折叠或打开 ORA-12170: TNS:Connect ...
BBED：跳过归档的非常规恢复策略
本文将深入探讨非常规恢复在Oracle数据库中的应用，特别是利用BBED（Backup Block Extraction and Dump）技术跳过归档恢复的过程。首先，作者GuoyJoe作为资深Oracle DBA，强调备份在数据库管理中的核心地位，特别是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值