OBJ$对象出现坏块可能会导致EXP导出失败

exp导出的时候出现ORA-01758错误。

[oracle@htbbs ~]$ exp userid=yansp/test tables=test file=test.dmp

Export: Release 11.2.0.3.0 - Production on Sat Jun 8 11:04:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 10624)
ORA-01110: data file 1: '/u01/app/oracle/oradata/htdb/system01.dbf'
EXP-00000: Export terminated unsuccessfully


下面的过程可以验证坏块所在的物理对象。

RMAN> validate check logical datafile 1;

Starting validate at 2013-06-08 10:45:49
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle/oradata/htdb/system01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              16495        94720           2413143   
  File Name: /u01/app/oracle/oradata/htdb/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              61209           
  Index      0              13652           
  Other      1              3364            

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/htdb/htdb/trace/htdb_ora_18220.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              600             
Finished validate at 2013-06-08 10:46:16



sys@HTDB> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2       , greatest(e.block_id, c.block#) corr_start_block#
  3       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
  5         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6       , null description
  7    FROM dba_extents e, v$database_block_corruption c
  8   WHERE e.file_id = c.file#
  9     AND e.block_id <= c.block# + c.blocks - 1
 10     AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13       , header_block corr_start_block#
 14       , header_block corr_end_block#
 15       , 1 blocks_corrupted
 16       , 'Segment Header' description
 17    FROM dba_segments s, v$database_block_corruption c
 18   WHERE s.header_file = c.file#
 19     AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22       , greatest(f.block_id, c.block#) corr_start_block#
 23       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
 25         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26       , 'Free Block' description
 27    FROM dba_free_space f, v$database_block_corruption c
 28   WHERE f.file_id = c.file#
 29     AND f.block_id <= c.block# + c.blocks - 1
 30     AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, corr_start_block#;

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------ ------------ ---------- ---------- ----------------- --------------- ---------------- --------------------
SYS   TABLE        OBJ$                             1             10624           10624                1

可以看到坏块出现在OBJ$表上，这也是导致EXP导出失败的原因。




通过设置10231事件暂时窥闭了这个问题.

sys@HTDB> alter system set events '10231 trace name context forever,level 10';

System altered.





[oracle@htbbs ~]$ exp userid=yansp/test tables=test file=test.dmp

Export: Release 11.2.0.3.0 - Production on Sat Jun 8 11:05:18 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST      72522 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.


测试发现exp 在登录阶段就会去查询OBJ$表，测试还没有实际的导出。

[oracle@htbbs ~]$ exp userid=yansp/test

Export: Release 11.2.0.3.0 - Production on Sat Jun 8 11:28:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 1, block # 10624)
ORA-01110: data file 1: '/u01/app/oracle/oradata/htdb/system01.dbf'
EXP-00000: Export terminated unsuccessfull


跟踪文件也发现，ORACLE在EXP的时候会对OBJ$表进行一次全表扫描。

Byte offset to file# 1 block# 10624 is 87031808
DDE: Problem Key 'ORA 1578' was flood controlled (0x2) (incident: 81871)
ORA-01578: ORACLE data block corrupted (file # 1, block # 10624)
ORA-01110: data file 1: '/u01/app/oracle/oradata/htdb/system01.dbf'
WAIT #8784420: nam='Disk file operations I/O' ela= 79 FileOperation=2 fileno=1 filetype=2 obj#=-1 tim=1370661897932083
WAIT #8784420: nam='RMAN backup & recovery I/O' ela= 41549 count=1 intr=0 timeout=4294967295 obj#=-1 tim=1370661897973656
Action (ID=34340895) was flood controlled by a FC Qualifier
FETCH #8784420:c=44994,e=99894,p=0,cr=57,cu=0,mis=0,r=0,dep=0,og=1,plh=23986678,tim=1370661897973851
STAT #8784420 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=7 us)'
STAT #8784420 id=2 cnt=0 pid=1 pos=1 obj=18 op='TABLE ACCESS FULL OBJ$ (cr=0 pr=0 pw=0 time=1 us cost=257 size=17605 card=503)'
WAIT #8784420: nam='SQL*Net break/reset to client' ela= 9 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1370661897973960
WAIT #8784420: nam='SQL*Net break/reset to client' ela= 48 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1370661897974022
WAIT #8784420: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1370661897974035
WAIT #8784420: nam='SQL*Net message from client' ela= 270 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1370661897974330
WAIT #0: nam='ADR file lock' ela= 6  =0  =0  =0 obj#=-1 tim=1370661897974388
WAIT #0: nam='ADR file lock' ela= 8  =0  =0  =0 obj#=-1 tim=1370661897974410
WAIT #0: nam='ADR block file read' ela= 169  =0  =0  =0 obj#=-1 tim=1370661897974796
WAIT #0: nam='ADR block file write' ela= 224  =0  =0  =0 obj#=-1 tim=1370661897975046
XCTEND rlbk=0, rd_only=1, tim=1370661897975090