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

首页 > 数据库 > Oracle > bbed模拟数据块损坏和恢复

bbed模拟数据块损坏和恢复

原创 Oracle 作者：dbSeeSee 时间：2016-06-12 23:15:20 0 删除 编辑

环境


点击(此处)折叠或打开

  1. SQL> select * from v$version;
  2.  
  3. BANNER
  4. -------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  6. PL/SQL Release 11.2.0.1.0 - Production
  7. CORE 11.2.0.1.0 Production
  8. TNS for Linux: Version 11.2.0.1.0 - Production
  9. NLSRTL Version 11.2.0.1.0 - Production

当前bbed配置


  1. [oracle@kvm61 ~]$ cat /home/oracle/bbed.conf
  2. 1 /oracle/oradata/sdk/system01.dbf 723517440
  3. 2 /oracle/oradata/sdk/sysaux01.dbf 639631360
  4. 3 /oracle/oradata/sdk/undotbs01.dbf 204472320
  5. 4 /oracle/oradata/sdk/users01.dbf 595066880
  6. 5 /oracle/oradata/sdk/example01.dbf 108134400
  7. 6 /oracle/scripts/sql/TBS_A.DBF 25165824

 

下面破坏一个数据块，找一个表，找到这边表对应的数据块位置：

例如scott.dept表

 


  1. SQL> set linesize 150
  2. SQL> set pagesize 100
  3. SQL> select rowid,
  4.   2 dbms_rowid.rowid_type(rowid) rowid_type,
  5.   3 dbms_rowid.rowid_object(rowid) data_object_id,
  6.   4 dbms_rowid.rowid_relative_fno(rowid) relative_file_number,
  7.   5 dbms_rowid.rowid_block_number(rowid) block_number,
  8.   6 dbms_rowid.rowid_row_number(rowid) row_number
  9.   7 from scott.dept;
  10.  
  11. ROWID ROWID_TYPE DATA_OBJECT_ID RELATIVE_FILE_NUMBER BLOCK_NUMBER ROW_NUMBER
  12. ------------------ ---------- -------------- -------------------- ----------------
  13. AAAR3vAAEAAAACHAAA 1 73199 4 135 0
  14. AAAR3vAAEAAAACHAAB 1 73199 4 135 1
  15. AAAR3vAAEAAAACHAAC 1 73199 4 135 2
  16. AAAR3vAAEAAAACHAAD 1 73199 4 135 3
 

dept表一共4行，位于第4号文件第135个数据块

 


  1. SQL> select * from scott.dept;
  2.  
  3.     DEPTNO DNAME LOC
  4. ---------- ---------------------------- --------------------------
  5.         10 ACCOUNTING NEW YORK
  6.         20 RESEARCH DALLAS
  7.         30 SALES CHICAGO
  8.         40 OPERATIONS BOSTON
  9.  
  10.  
  11. SQL> shutdown immediate
  12. 数据库已经关闭。
  13. 已经卸载数据库。
  14. ORACLE 例程已经关闭


对4号文件user表空间做离线备份

 

[oracle@kvm61 sdk]$ cp users01.dbf users01.dbf.2

[oracle@kvm61 sdk]$ pwd

/oracle/oradata/sdk

 

启动数据库

 


  1. SQL> startup
  2. ORACLE 例程已经启动。
  3.  
  4. Total System Global Area 513585152 bytes
  5. Fixed Size 2214856 bytes
  6. Variable Size 276825144 bytes
  7. Database Buffers 226492416 bytes
  8. Redo Buffers 8052736 bytes
  9. 数据库装载完毕。
  10. 数据库已经打开。

 

破坏数据块

 


  1. [oracle@kvm61 sdk]$ bbed
  2. Password:
  3.  
  4. BBED: Release 2.0.0.0.0 - Limited Production on 星期日 6月 12 22:53:04 2016
  5.  
  6. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
  7.  
  8. ************* !!! For Oracle Internal Use only !!! ***************
  9.  
  10. BBED> show
  11.         FILE# 1
  12.         BLOCK# 1
  13.         OFFSET 0
  14.         DBA 0x00400001 (4194305 1,1)
  15.         FILENAME /oracle/oradata/sdk/system01.dbf
  16.         BIFILE bifile.bbd
  17.         LISTFILE /home/oracle/bbed.conf
  18.         BLOCKSIZE 8192
  19.         MODE Edit
  20.         EDIT Unrecoverable
  21.         IBASE Dec
  22.         OBASE Dec
  23.         WIDTH 80
  24.         COUNT 512
  25.         LOGFILE log.bbd
  26.         SPOOL No
  27.  
  28. BBED> set file 4
  29.         FILE# 4
  30.  
  31. BBED> show
  32.         FILE# 4
  33.         BLOCK# 1
  34.         OFFSET 0
  35.         DBA 0x01000001 (16777217 4,1)
  36.         FILENAME /oracle/oradata/sdk/users01.dbf
  37.         BIFILE bifile.bbd
  38.         LISTFILE /home/oracle/bbed.conf
  39.         BLOCKSIZE 8192
  40.         MODE Edit
  41.         EDIT Unrecoverable
  42.         IBASE Dec
  43.         OBASE Dec
  44.         WIDTH 80
  45.         COUNT 512
  46.         LOGFILE log.bbd
  47.         SPOOL No

下面对指定的数据块进行修改

 


  1. BBED> modify 1000 file 4 block 135
  2. Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
  3.  File: /oracle/oradata/sdk/users01.dbf (4)
  4.  Block: 135 Offsets: 0 to 511 Dba:0x01000087
  5. ------------------------------------------------------------------------
  6.  03e80000 87000001 3b590e00 00000106 6c240000 01000000 ef1d0100 24590e00
  7.  00000000 02003200 80000001 0a001b00 32020000 cc58c100 5c002900 04200000
  8.  3b590e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  9.  00000000 00010400 ffff1a00 3c1f221f 221f0000 04007e1f 681f541f 3c1f0000
  10.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  11.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  12.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  13.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  14.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  15.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  16.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  17.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  18.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  19.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  20.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  21.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  22.  
  23.  <32 bytes per line>
  24.  
  25. BBED> verify
  26. DBVERIFY - Verification starting
  27. FILE = /oracle/oradata/sdk/users01.dbf
  28. BLOCK = 135
  29.  
  30. Block 135 is corrupt
  31. Corrupt block relative dba: 0x01000087 (file 0, block 135)
  32. Bad header found during verification
  33. Data in bad block:
  34.  type: 3 format: 0 rdba: 0x01000087
  35.  last change scn: 0x0000.000e593b seq: 0x1 flg: 0x06
  36.  spare1: 0x0 spare2: 0x0 spare3: 0x0
  37.  consistency value in tail: 0x593b0601
  38.  check value in block header: 0x246c
  39.  computed block checksum: 0x4a05
  40.  
  41.  
  42. DBVERIFY - Verification complete
  43.  
  44. Total Blocks Examined : 1
  45. Total Blocks Processed (Data) : 0
  46. Total Blocks Failing (Data) : 0
  47. Total Blocks Processed (Index): 0
  48. Total Blocks Failing (Index): 0
  49. Total Blocks Empty : 0
  50. Total Blocks Marked Corrupt : 1
  51. Total Blocks Influx : 0
  52. Message 531 not found; product=RDBMS; facility=BBED

 


  1. SQL> select * from scott.dept;
  2. select * from scott.dept
  3.                     *
  4. 第 1 行出现错误:
  5. ORA-01578: ORACLE 数据块损坏 (文件号 4, 块号 135)
  6. ORA-01110: 数据文件 4: '/oracle/oradata/sdk/users01.dbf'
 

从备份文件恢复数据块

 

bbed文件列表加入离线备份文件,编号7


切换到7号文件，讲对应的block复制到4号文件：

  1. [oracle@kvm61 ~]$ cat /home/oracle/bbed.conf
  2. 1 /oracle/oradata/sdk/system01.dbf 723517440
  3. 2 /oracle/oradata/sdk/sysaux01.dbf 639631360
  4. 3 /oracle/oradata/sdk/undotbs01.dbf 204472320
  5. 4 /oracle/oradata/sdk/users01.dbf 595066880
  6. 5 /oracle/oradata/sdk/example01.dbf 108134400
  7. 6 /oracle/scripts/sql/TBS_A.DBF 25165824
  8. 7 /oracle/oradata/sdk/users01.dbf.2 595066880

 


  1. BBED> set file 7
  2.         FILE# 7
  3.  
  4. BBED> show
  5.         FILE# 7
  6.         BLOCK# 135
  7.         OFFSET 0
  8.         DBA 0x01c00087 (29360263 7,135)
  9.         FILENAME /oracle/oradata/sdk/users01.dbf.2
  10.         BIFILE bifile.bbd
  11.         LISTFILE /home/oracle/bbed.conf
  12.         BLOCKSIZE 8192
  13.         MODE Edit
  14.         EDIT Unrecoverable
  15.         IBASE Dec
  16.         OBASE Dec
  17.         WIDTH 80
  18.         COUNT 512
  19.         LOGFILE log.bbd
  20.         SPOOL No
  21.  
  22. BBED> copy file 7 block 135 to file 4 block 135
  23.  File: /oracle/oradata/sdk/users01.dbf (4)
  24.  Block: 135 Offsets: 0 to 511 Dba:0x01000087
  25. ------------------------------------------------------------------------
  26.  06a20000 87000001 3b590e00 00000106 6c240000 01000000 ef1d0100 24590e00
  27.  00000000 02003200 80000001 0a001b00 32020000 cc58c100 5c002900 04200000
  28.  3b590e00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  29.  00000000 00010400 ffff1a00 3c1f221f 221f0000 04007e1f 681f541f 3c1f0000
  30.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  31.  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  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.  00000000 00000000 00000000 00000000 00000000 00000000 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.  
  43.  <32 bytes per line>
  44.  
  45. BBED> verify file 4
  46. DBVERIFY - Verification starting
  47. FILE = /oracle/oradata/sdk/users01.dbf
  48.  
  49.  
  50. DBVERIFY - Verification complete
  51.  
  52. Total Blocks Examined : 72640
  53. Total Blocks Processed (Data) : 58074
  54. Total Blocks Failing (Data) : 0
  55. Total Blocks Processed (Index): 7430
  56. Total Blocks Failing (Index): 0
  57. Total Blocks Empty : 4973
  58. Total Blocks Marked Corrupt : 0
  59. Total Blocks Influx : 0
  60. Message 531 not found; product=RDBMS; facility=BBED


表空间离线一下
 


  1. SQL> select * from scott.dept;
  2. select * from scott.dept
  3. *
  4. 第 1 行出现错误:
  5. ORA-01578: ORACLE 数据块损坏 (文件号 4, 块号 135)
  6. ORA-01110: 数据文件 4: '/oracle/oradata/sdk/users01.dbf'
  7.  
  8.  
  9. SQL> alter tablespace users offline;
  10.  
  11. 表空间已更改。
  12.  
  13. SQL> alter tablespace users online;
  14.  
  15. 表空间已更改。
  16.  
  17. SQL> select * from scott.dept;
  18.  
  19.     DEPTNO DNAME LOC
  20. ---------- ---------------------------- --------------------------
  21.         10 ACCOUNTING NEW YORK
  22.         20 RESEARCH DALLAS
  23.         30 SALES CHICAGO
  24.         40 OPERATIONS BOSTON


数据块恢复

一个简单的例子,理解bbed的基本使用。

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

0
0
上一篇： DB2 V10.1 HADR快速部署手册
下一篇： db2手工建库
请登录后发表评论 登录
全部评论
dbSeeSee

注册时间：2013-07-04

  • 博文量
    28
  • 访问量
    124531

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

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

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

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