二、模拟实验
该实验我参照了yangtingkun老师的技术贴http://space.itpub.net/4227/viewspace-68509，并根据我的理解加以改进。不足之处，请大家批评指正。
1. 实验准备：
为了演示的方便，我们建一个1MB的表空间test，共有128个块，每块大小为8192。
sys@STUDY>create tablespace test
datafile 'C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF' size 1m;
Tablespace created.
sys@STUDY>select file_id, file_name from dba_data_files where tablespace_name='TEST';
FILE_ID FILE_NAME
---------- -------------------------------
14 C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF
sys@STUDY>show parameter db_block_size
NAME TYPE VALUE
--------------------------------
db_block_size integer 8192
在其中我们创建数据表T1和索引ID_T1。创建索引的目的是为了做DBMS_REPAIR实验。T1表块号从12到67。
sys@STUDY>create table t1 tablespace test as select * from dba_tables;
Table created.
sys@STUDY>select count(*) from t1;
COUNT(*)
----------
1735
sys@STUDY>select min(dbms_rowid.rowid_block_number(rowid)) from t1;
MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
-----------------------------------------
12
sys@STUDY>select max(dbms_rowid.rowid_block_number(rowid)) from t1;
MAX(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
-----------------------------------------
67
sys@STUDY>create index id_t1 on t1(table_name) tablespace test;
Index created.
启动RMAN，执行带validate的备份和镜像拷贝。查看v$Database_Block_Corrpution没有发现错误。
RMAN> backup validate tablespace "TEST";
Starting backup at 2010-06-05 11:07:05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00014 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2010-06-05 11:07:06
RMAN> backup as copy validate tablespace "TEST";
Starting backup at 2010-06-05 11:11:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00014 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2010-06-05 11:11:16
sys@STUDY>select * from v$database_block_corruption;
no rows selected
2. 模拟坏块
模拟之前，我们先回顾一下块的基本结构。块由块头、块尾和内容组成。其中ASSM将内容分为4个级别，由位图进行管理。数据存放的方式由后向前，达到pctfree时为块满。
块头 (checksum) | FS1 100% | FS2 75% | FS3 50% | FS4 25% | 块尾 |
关于Oracle内部块的详细结构的说明，请查看以下文章。这里我对需要进行修改的几个字段所处的Block（以8192个字节为例）中的相对位置进行说明：
http://dongguangit.itpub.net/post/24185/303231
l 块头：长度不固定。主要包括：
第1个16位中：
Ø 第0位：type
Ø 第8-13位：scn
Ø 第14位：seq
第2个16位中：
Ø 第1-2位：chkval，校验位
l 块尾：长度为4个字节
Ø 第1位：对应head中的seq
Ø 第2位：对应head中的type
为了达到较好的演示效果，我们选取12-67块中第16，20，24，28这4个数据块来模拟all zero，factured，checksum，logical这4种块的错误。其中前三种为介质错误，后面一种为逻辑错误。此外corrupt类型块错误，为系统无法识别的块，我尚不知如何进行模拟。
资料来源：/oracle102/server.102/b14237/dynviews_1074.htm#sthref3571
l All zero： 块头丢失，内容全为0。
l Fractured： 块头和块尾版本不同
l Checksum：校验码错误，通常为块中间内容发生改变
l Logical： 逻辑错误
首先定位需要修改的数据块的地址：
sys@STUDY>select to_char(16*8192, 'XXXXXX') s_addr, to_char(17*8192-1, 'XXXXXX') e_addr from dual;
S_ADDR E_ADDR
------- -------
20000 21FFF
sys@STUDY>select to_char(20*8192, 'XXXXXX') s_addr, to_char(21*8192-1, 'XXXXXX') e_addr from dual;
S_ADDR E_ADDR
------- -------
28000 29FFF
sys@STUDY>select to_char(24*8192, 'XXXXXX') s_addr, to_char(25*8192-1, 'XXXXXX') e_addr from dual;
S_ADDR E_ADDR
------- -------
30000 31FFF
sys@STUDY>select to_char(28*8192, 'XXXXXX') s_addr, to_char(29*8192-1, 'XXXXXX') e_addr from dual;
S_ADDR E_ADDR
------- -------
38000 39FFF
执行checkpoint，将buffer cache中的内容写到数据块中；将test表空间offline，以便可以离线修改test01.dbf文件；
sys@STUDY>alter system checkpoint;
System altered.
sys@STUDY>alter tablespace test offline;
Tablespace altered.
使用UltraEdit打开test01.dbf文件，以十六进制模式显示。依据上面计算的地址，分别定位到0X20000（第16块），0X28000（第20块），0X30000（第24块），0X38000（第28块）。
l 第16块：
将0X20000--0X200F位全部设为0，模拟块头丢失的情况。
l 第20块：
将0X280000E位设为0，即改变该块块头的seq位，使他和块尾的seq位不一致。模拟块头和块尾版本不同的情况。
l 第24块：
将0X30010--0X30011位设为0，清空checksum的值。
l 第28块：
将0X38458位设定为0。我们在T1表的table_name字段建立了索引，该字段是字符型，其值可以在UltraEdit编辑栏右侧显示。我们修改的这个位，刚好存放了一个table_name值。这样，数据表中的内容将与索引中的内容不一致。从而模拟一个Logical错误。
这样我们完成了所需实验的准备工作。由于下面我们将尝试运用多个工具来进行恢复操作，所以有必要将修改好的数据文件进行保存，以便可以多次调用。
sys@STUDY> host copy test01.dbf test01.bak
将Test表空间online，开始进入下一步检验工作。
sys@STUDY>alter tablespace test online;
Tablespace altered.
3. 使用工具进行检验
l 运用DBV对TEST01.dbf进行检验
dbv file=test01.dbf
DBVERIFY - Verification starting : FILE = test01.dbf
Page 16 is influx - most likely media corrupt
Corrupt block relative dba: 0x03800010 (file 14, block 16)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf3520601
check value in block header: 0x5b16
block checksum disabled
Page 20 is influx - most likely media corrupt
Corrupt block relative dba: 0x03800014 (file 14, block 20)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03800014
last change scn: 0x0000.001cf352 seq: 0x0 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf3520601
check value in block header: 0xd98d
computed block checksum: 0x1
Block Checking: DBA = 58720280: Block Type byte corrupted
Block Checking: DBA = 58720280: Block Type byte corrupted
Page 24 is influx - most likely media corrupt
Corrupt block relative dba: 0x03800018 (file 14, block 24)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 2 rdba: 0x03800018
last change scn: 0x0000.00000001 seq: 0x1 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00010001
check value in block header: 0x0
block checksum disabled
Page 28 is marked corrupt
Corrupt block relative dba: 0x0380001c (file 14, block 28)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0380001c
last change scn: 0x0000.001cf352 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf3520602
check value in block header: 0xf5df
computed block checksum: 0x62
DBVERIFY - Verification complete
Total Pages Examined : 128
Total Pages Processed (Data) : 95
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8
Total Pages Failing (Index): 0
Total Pages Processed (Other): 22
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 4
Total Pages Influx : 3
Highest block SCN : 1897456 (0.1897456)
l 运用RMAN的validate，对数据块进行检验
RMAN> backup validate tablespace "TEST";
Starting backup at 2010-06-05 15:54:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00014 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2010-06-05 15:54:23
sys@STUDY>select file#, block#, MARKED_CORRUPT, CORRUPTION_TYPE from v$backup_corruption;
FILE# BLOCK# MAR CORRUPTIO
---------- ---------- --- ---------
14 16 YES FRACTURED
14 20 YES FRACTURED
14 28 YES CHECKSUM
这里缺一个24号块，目前尚未定位出原因。
RMAN> backup as copy validate tablespace "TEST";
Starting backup at 2010-06-05 15:59:45
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00014 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2010-06-05 15:59:46
sys@STUDY>select file#, block#, MARKED_CORRUPT, CORRUPTION_TYPE from v$copy_corruption;
FILE# BLOCK# MAR CORRUPTIO
---------- ---------- --- ---------
14 16 YES FRACTURED
14 20 YES FRACTURED
14 28 YES CHECKSUM
这里也少了一个24号块。
sys@STUDY>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
14 28 1 0 CHECKSUM
14 20 1 0 FRACTURED
14 16 1 0 FRACTURED
l 运用EXP在导出的过程中对数据进行检验
sys@STUDY>grant exp_full_database to scott;
exp scott/tiger tablespaces=test
Export: Release 10.2.0.1.0 - Production on Sat Jun 5 16:16:00 2010
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export selected tablespaces ...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
以表空间的方式导出，EXP并没有发现错误。
由于我将T1表存放到了SYS Schema下了，直接用exp sys ...系统会报错。因此，我编写了一个参数文件t1.par
userid="sys/oracle@study as sysdba"
file=t1.dmp
tables=(T1)
然后执行：
exp parfile=t1.par
Export: Release 10.2.0.1.0 - Production on Sat Jun 5 16:24:59 2010
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 ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T1
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 14, block # 16)
ORA-01110: data file 14: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF'
Export terminated successfully with warnings.
在table模式下，exp发现了块错误。但很明显，当exp发现了第一个块异常后，就自动停止执行导出操作了。
l analyze ...validate structure
sys@STUDY>analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 16)
ORA-01110: data file 14: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF'
Analyze也能检验出第一个错误块，但其余的数据块无法得到。
l 小结
这里我们采用了DBV，RMAN...validate，EXP，Analyze....validate structure四种工具来检查数据块错误。很明显，DBV的功能是最强的，能够查出所有4种错误；其次是RMAN...validate，在备份或镜像的过程中进行校验，对于重要的数据来说也是非常必要的，不过这种方式可能发生漏检。EXP和analyze工具不适合进行坏块的检验，最多可以作为发现坏块的手段。
[@more@]《第07章 处理数据库损坏》学习笔记.pdf
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20162/viewspace-1034192/ ，如需转载，请注明出处，否则将追究法律责任。
