oracle查看坏块属于表,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