使用BBED模拟坏块并修复

2011年9月2日
使用BBED模拟坏块，练习修复。
1.创建测试表
SQL> show user
USER is "ALONE"
SQL> create table bbed tablespace test as select * from dba_tables;
Table created.

SQL> select count(*) from bbed;
COUNT(*)
----------
1577

SQL> col segment for a10
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='BBED';
SEGMENT_NA FILE_ID BLOCK_ID
---------- ---------- ----------
BBED 6 9
BBED 6 17
BBED 6 25
BBED 6 33
BBED 6 41
BBED 6 49
BBED 6 57

7 rows selected.

2.使用BBED
[oracle@test001 ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 1 23:44:04 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 6
FILE# 6
BBED> show
FILE# 6
BLOCK# 1
OFFSET 0
DBA 0x01800001 (25165825 6,1)
FILENAME /u01/oracle/oradata/alonedba/test01.dbf
BIFILE bifile.bbd
LISTFILE /u01/oracle/product/10.2.0/rdbms/bbed/bbed.log
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED>

3.回滚误操作
如果操作中发生误操作，可以使用revert命令回滚。
BBED> modify /x 0x01800001
File: /u01/oracle/oradata/alonedba/test01.dbf (6)
Block: 1 Offsets: 0 to 511 Dba:0x01800001
------------------------------------------------------------------------
01800001 01008001 00000000 00000104 f4390000 00000000 0001200a 220453b0
414c4f4e 45444241 ad020000 00190000 00200000 06000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 d7350900 00000000 ae86432d 62fe3a2d 7bce0600 00000000 00000000
00000000 00000000 00000400 21000000 fef7572d 20000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 07000000 04005445 53543100 00000000 00000000
00000000 00000000 00000000 00000000 06000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
74ef8421 01000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 98f80c00 0000ffbf fef7572d 0100bc0c 10000000 02000000 10000000
<32 bytes per line>

BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) Y
Reverted file '/u01/oracle/oradata/alonedba/test01.dbf', block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

4.改写数据块(模拟坏块)
BBED> modify 1000 file 6 block 17
File: /u01/oracle/oradata/alonedba/test01.dbf (6)
Block: 17 Offsets: 0 to 511 Dba:0x01800011
------------------------------------------------------------------------
03e80000 11008001 82200d00 00000104 fb020000 01000000 a4cd0000 7b200d00
00000000 03003201 09008001 ffff0000 00000000 00000000 00000000 00800000
7b200d00 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00012100
ffff5400 2b04d703 d7030000 2100b21e e01d121d 3e1c731b a31ad319 09193b18
6d179b16 c915f514 21144d13 7912aa11 d410ff0f 280f540e 7b0da20c cb0bee0a
140a3c09 64088807 b206dd05 08052b04 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

5.检查数据块损坏
使用verify命令，可以发现刚才修改的file 6 block 17已经被标记为损坏。
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/alonedba/test01.dbf
BLOCK = 17

Block 17 is corrupt
Corrupt block relative dba: 0x01800011 (file 0, block 17)
Bad header found during verification
Data in bad block:
type: 3 format: 0 rdba: 0x01800011
last change scn: 0x0000.000d2082 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x20820601
check value in block header: 0x2fb
computed block checksum: 0x4a05

DBVERIFY - Verification complete

Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0

BBED> quit

6.使用DBV检查
[oracle@test001 alonedba]$ dbv file=test01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Sep 1 23:58:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = test01.dbf
Page 17 is marked corrupt
Corrupt block relative dba: 0x01800011 (file 6, block 17)
Bad header found during dbv:
Data in bad block:
type: 3 format: 0 rdba: 0x01800011
last change scn: 0x0000.000d2082 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x20820601
check value in block header: 0x2fb
computed block checksum: 0x4a05

DBVERIFY - Verification complete

Total Pages Examined : 6400
Total Pages Processed (Data) : 871
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 212
Total Pages Failing (Index): 0
Total Pages Processed (Other): 59
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 5257
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 860306 (0.860306)
[oracle@test001 alonedba]$

7.全表扫描时出现ORA-01578的错误
重新启动数据库，执行全表扫描，此时出现错误：
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 75499764 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> conn alone/alone
Connected.
SQL> select count(*) from bbed;
select count(*) from bbed
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 17)
ORA-01110: data file 6: '/u01/oracle/oradata/alonedba/test01.dbf'
SQL>

8.数据块损坏，需要恢复。
在这种情况下，如果有备份，需要从备份中恢复；如果没有备份，那么坏块部分的数据库就要丢失了。检查损坏的对象：
SQL> set lines 120
SQL> col segment_name for a20
SQL> col tablespace_name for a20
SQL> col owner for a10
SQL> select tablespace_name,segment_type,owner,segment_name from dba_extents
where file_id=6 and block_id between 17 and 24;

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
-------------------- ------------------ ---------- --------------------
TEST TABLE ALONE BBED

如果损失的是数据，可以设置内部事件，使得全表扫描跳过那些损坏的block
SQL> alter system set events='10231 trace name context forever,level 10';
System altered.
统计表中的数据
SQL> select count(*) from bbed;
COUNT(*)
----------
1544
在本次实验中丢失的数据库：1577-1544=33行数据

9.将该用户下的数据导出，删除后，再重建该表，并去取消10231事件
导出数据：
[oracle@test001 test]$ exp alone/alone file=alone.dmp
Export: Release 10.2.0.1.0 - Production on Fri Sep 2 00:30:34 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
...
. . exporting table BBED 1544 rows exported
...
Export terminated successfully without warnings.

删除表：
SQL> show user
USER is "ALONE"
SQL> drop table bbed purge;
Table dropped.

导入数据：
[oracle@test001 test]$ imp alone/alone file=alone.dmp fromuser=alone touser=alone
Import: Release 10.2.0.1.0 - Production on Fri Sep 2 00:35:09 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table "BBED" 1544 rows imported
Import terminated successfully without warnings.

查看用户表中的数据：
SQL> select count(*) from bbed;
COUNT(*)
----------
1544

取消10231事件：
SQL> alter system set events='10231 trace name context off';
System altered.

10.完成数据恢复。

留下的疑问：如果不发生数据库丢失，能完成恢复吗？

[@more@]

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

转载于:http://blog.itpub.net/26121829/viewspace-1054697/