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

首页 > 数据库 > Oracle > bbed copy修复坏块和delete的数据

bbed copy修复坏块和delete的数据

原创 Oracle 作者：goolen 时间：2014-05-07 14:28:33 0 删除 编辑
我们来模拟一下非系统表空间数据文件出现坏块，或者误删除数据后使用bbed的copy命令来恢复的实验，前提是我们有之前此数据文件的备份，因为我们需要从老数据文件中copy block来恢复
SQL> create table goolen as select object_id,object_name,object_type from dba_objects where rownum <=400;
Table created.
SQL> select tablespace_name from dba_segments where segment_name='GOOLEN';
TABLESPACE_NAME
------------------------------
USERS
SQL> select file_name from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/goolen/users01.dbf
SQL> shut immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--备份一个数据文件，恢复的时候需要用到
SQL> ! cp /opt/app/oracle/oradata/goolen/users01.dbf /opt/app/oracle/oradata/goolen/users01-bk.dbf
SQL> select
  2  rowid,
  3  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  4  dbms_rowid.rowid_block_number(rowid)blockno,
  5  dbms_rowid.rowid_row_number(rowid) rowno
  6  from goolen where rownum =1;
select
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 18992
Session ID: 96 Serial number: 7
SQL> startup
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size                  2214936 bytes
Variable Size            1207960552 bytes
Database Buffers          872415232 bytes
Redo Buffers                5189632 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> select
  2  rowid,
  3  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  4  dbms_rowid.rowid_block_number(rowid)blockno,
  5  dbms_rowid.rowid_row_number(rowid) rowno
  6  from goolen where rownum =1;
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASOtAAEAAAACrAAA          4        171          0
SQL> select count(*) from goolen;
  COUNT(*)
----------
       400
--bbed数据文件列表中添加一行，把备份的数据文件加进去：
[oracle@localhost bbed]$ vim filelist.txt      
4 /opt/app/oracle/oradata/goolen/users01.dbf                57671680
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf              31457280
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf              587202560
1 /opt/app/oracle/oradata/goolen/system01.dbf              713031680
5 /opt/app/oracle/oradata/goolen/users01-bk.dbf            57671680
[oracle@localhost bbed]$ bbed parfile=bbed.par 
Password: 
BBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 24 15:42:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /opt/app/oracle/oradata/goolen/system01.dbf                      87040
     2  /opt/app/oracle/oradata/goolen/sysaux01.dbf                      71680
     3  /opt/app/oracle/oradata/goolen/undotbs01.dbf                      3840
     4  /opt/app/oracle/oradata/goolen/users01.dbf                        7040
     5  /opt/app/oracle/oradata/goolen/users01-bk.dbf                     7040
--修改4号文件的171块
BBED> m /x 1234 dba 4,171
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 171              Offsets:    0 to  511           Dba:0x010000ab
------------------------------------------------------------------------
 12340000 ab000001 f98e1a00 00000204 265b0000 01000000 ad230100 f78e1a00 
 00000000 03003200 a8000001 ffff0000 00000000 00000000 00000000 00800000 
 f78e1a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00011101 
 ffff3402 67053303 33030000 11016e1f 5a1f491f 371f211f 0e1ff11e de1eca1e 
 b71e961e 841e731e 581e441e 311e1e1e 0c1ef81d e71dd61d c21dae1d 9d1d8b1d 
 771d661d 4e1d3a1d 201d0d1d fa1ce71c d41cc01c b01c931c 7d1c691c 521c411c 
 2e1c1c1c 0a1cf71b e31bd21b bf1baa1b 991b871b 731b5f1b 4b1b371b 241b101b 
 ef1ad91a c41aad1a 961a821a 6c1a561a 451a341a 221a0a1a f419dc19 c419b319 
 9f198519 72195f19 4c193b19 28191519 0019e918 d218ba18 a0188e18 7a186618 
 52183e18 28181218 fd17e617 c717b417 9f178817 70175117 3b172217 0d17f216 
 d716c016 aa168516 6d165516 3f162316 1116fd15 e715d115 bb15a315 8b157815 
 53153d15 2c151915 0615f214 dc14c114 a4149114 76146414 4f143b14 29141614 
 fd13e013 c413a513 8c137613 5f134513 20130b13 f412dc12 c412a912 84126612 
 48123212 19120112 e911ce11 b3119811 7d116211 47112b11 0f11f310 d710bf10 
 a5108e10 77105e10 42102910 0e10f40f cf0fb30f 970f820f 680f4c0f 340f150f 
 <32 bytes per line>
BBED> sum apply;
Check value for File 4, Block 171:
current = 0xcd32, required = 0xcd32
--我们再查询这个表的时候，oracle就会检测到此表中存在坏块，并且报错
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from goolen;
select count(*) from goolen
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 171)
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
--通过copy老数据文件中的block来恢复这个坏块
BBED> copy file 5 block 171 to file 4 block 171
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 171              Offsets:    0 to  511           Dba:0x010000ab
------------------------------------------------------------------------
 06a20000 ab000001 f98e1a00 00000204 265b0000 01000000 ad230100 f78e1a00 
 00000000 03003200 a8000001 ffff0000 00000000 00000000 00000000 00800000 
 f78e1a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00011101 
 ffff3402 67053303 33030000 11016e1f 5a1f491f 371f211f 0e1ff11e de1eca1e 
 b71e961e 841e731e 581e441e 311e1e1e 0c1ef81d e71dd61d c21dae1d 9d1d8b1d 
 771d661d 4e1d3a1d 201d0d1d fa1ce71c d41cc01c b01c931c 7d1c691c 521c411c 
 2e1c1c1c 0a1cf71b e31bd21b bf1baa1b 991b871b 731b5f1b 4b1b371b 241b101b 
 ef1ad91a c41aad1a 961a821a 6c1a561a 451a341a 221a0a1a f419dc19 c419b319 
 9f198519 72195f19 4c193b19 28191519 0019e918 d218ba18 a0188e18 7a186618 
 52183e18 28181218 fd17e617 c717b417 9f178817 70175117 3b172217 0d17f216 
 d716c016 aa168516 6d165516 3f162316 1116fd15 e715d115 bb15a315 8b157815 
 53153d15 2c151915 0615f214 dc14c114 a4149114 76146414 4f143b14 29141614 
 fd13e013 c413a513 8c137613 5f134513 20130b13 f412dc12 c412a912 84126612 
 48123212 19120112 e911ce11 b3119811 7d116211 47112b11 0f11f310 d710bf10 
 a5108e10 77105e10 42102910 0e10f40f cf0fb30f 970f820f 680f4c0f 340f150f 
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 171:
current = 0x5b26, required = 0x5b26
--再次查询这个表，已经可以正常查询
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from goolen;
  COUNT(*)
----------
       400
--我们删除几行数据
SQL> delete goolen where rownum <=100;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from goolen;
  COUNT(*)
----------
       300
BBED> copy file 5 block 171 to file 4 block 171
 File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
 Block: 171              Offsets:    0 to  511           Dba:0x010000ab
------------------------------------------------------------------------
 06a20000 ab000001 f98e1a00 00000204 265b0000 01000000 ad230100 f78e1a00 
 00000000 03003200 a8000001 ffff0000 00000000 00000000 00000000 00800000 
 f78e1a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00011101 
 ffff3402 67053303 33030000 11016e1f 5a1f491f 371f211f 0e1ff11e de1eca1e 
 b71e961e 841e731e 581e441e 311e1e1e 0c1ef81d e71dd61d c21dae1d 9d1d8b1d 
 771d661d 4e1d3a1d 201d0d1d fa1ce71c d41cc01c b01c931c 7d1c691c 521c411c 
 2e1c1c1c 0a1cf71b e31bd21b bf1baa1b 991b871b 731b5f1b 4b1b371b 241b101b 
 ef1ad91a c41aad1a 961a821a 6c1a561a 451a341a 221a0a1a f419dc19 c419b319 
 9f198519 72195f19 4c193b19 28191519 0019e918 d218ba18 a0188e18 7a186618 
 52183e18 28181218 fd17e617 c717b417 9f178817 70175117 3b172217 0d17f216 
 d716c016 aa168516 6d165516 3f162316 1116fd15 e715d115 bb15a315 8b157815 
 53153d15 2c151915 0615f214 dc14c114 a4149114 76146414 4f143b14 29141614 
 fd13e013 c413a513 8c137613 5f134513 20130b13 f412dc12 c412a912 84126612 
 48123212 19120112 e911ce11 b3119811 7d116211 47112b11 0f11f310 d710bf10 
 a5108e10 77105e10 42102910 0e10f40f cf0fb30f 970f820f 680f4c0f 340f150f 
 <32 bytes per line>
BBED> sum apply;
Check value for File 4, Block 171:
current = 0x5b26, required = 0x5b26
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from goolen;
  COUNT(*)
----------

       400


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

0
0
上一篇： mongodb安装及简介
下一篇： physical standby 归档出现gap的解决办法
请登录后发表评论 登录
全部评论
goolen

注册时间：2011-10-20

  • 博文量
    121
  • 访问量
    731655

最新文章

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

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

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

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