ORACLE数据库CLOB字段查坏块相关操作

情形：
用户报一报表格式无法更新。报以下错误信息
2009-09-14 16:01:36     [1002][-1][ORACLE]    OracleFactory(3): SP=ARGTempletDetailSet, Source=System.Data.OracleClient, ERR=ORA-01578: ORACLE data block corrupted (file # 5, block # 254268)
ORA-01110: data file 5: '/investdata/investdb.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "testuser.ARGTEMPLETDETAILSET", line 70
ORA-06512: at line 1
2009-09-14 16:01:42     [1002][-1][ORACLE]    OracleFactory(3): SP=ARGTempletDetailSet, Source=System.Data.OracleClient, ERR=ORA-01578: ORACLE data block corrupted (file # 5, block # 254268)
ORA-01110: data file 5: '/investdata/investdb.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "testuser.ARGTEMPLETDETAILSET", line 70
ORA-06512: at line 1
------------------------------------------
排查思路：
排查testuser.ARGTEMPLETDETAILSET此存储过程发现其在update fbmtempletdetail的表；
而此表中有 两个 CLOB字段；怀疑坏块可能和此类字段有关；
排查步骤：
先备份：
RMAN备份： 运行NAS备份命令；
EXP备份： exp system/password file=testuser.dmp log=testuser.log full=y
-------------以上操作都没有报坏块情况--------------
 
1\ 用DBV来校验物理文件是否有坏块：

Dbv file=\investdata\investdb.dbf blocksize=8192;
DBVERIFY: Release 10.2.0.3.0 - Production on Tue Sep 15 16:47:13 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /investdata/investdb.dbf

DBV-00200: Block, dba 21178795, already marked corrupted
DBV-00200: Block, dba 21178866, already marked corrupted
DBV-00200: Block, dba 21178868, already marked corrupted
DBV-00200: Block, dba 21178869, already marked corrupted
DBV-00200: Block, dba 21178870, already marked corrupted
DBV-00200: Block, dba 21178872, already marked corrupted
DBV-00200: Block, dba 21225788, already marked corrupted
DBV-00200: Block, dba 21225789, already marked corrupted

DBVERIFY - Verification complete
Total Pages Examined      : 530944
Total Pages Processed (Data) : 484675
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 22228
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 5400
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty          : 18641
Total Pages Marked Corrupt  : 8
Total Pages Influx          : 0
Highest block SCN         : 1505343957 (5.1505343957)

2\ 找到相关的数据文件和坏块号：

Relative File number:
SQL> select dbms_utility.data_block_address_file(21178795) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(54528484)
----------------------------------------------
5

Block Number:
SQL> select dbms_utility.data_block_address_block(21178795) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(54528484)
-----------------------------------------------
2532 

依次对以上中8的坏道进行分析找到相关块号：对用关系如下：

             坏块号：
21178795     207275
21178866     207346
21178868     207348
21178869     207349
21178870     207350
21178872     207352
21225788     254268
21225789     254269

3、 查询用户\segment_name,sengment_type----确定是表还是索引或其他

select owner, segment_name, segment_type from   dba_extents
where  file_id = 5
and  207275 between block_id and block_id + blocks - 1;

207275

1     testuser       SYS_LOB0000053836C00011$$   LOBSEGMENT

在通过此结果查询相关表

select table_name, column_name
from   dba_lobs
where  segment_name = '上面查出的segment_name'
and    owner = 'testuser';

207275
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
--------------------

207346
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
----------------------

207348
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
--------------------------

207349
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
---------

207350
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
------------------

207352
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
-------

254268
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE
--------

254269
1     testuser      SYS_LOB0000053836C00011$$  LOBSEGMENT
1     FBMTEMPLETDETAIL       SQLWHERE

4、 克隆一张和有问题的表结构及数据一摸一样的表：

Create table testuser.FBMTEMPLETDETAIL_20090914
As
Select * from testuser.FBMTEMPLETDETAIL

5、将有问题的表更改名字，将新创建的表更改为老表名字：
testuser.FBMTEMPLETDETAIL-----》 testuser.FBMTEMPLETDETAIL_20090914_old
testuser.FBMTEMPLETDETAIL_20090914-》 testuser.FBMTEMPLETDETAIL

以上操作参考文档：

metalink: 293515.1