早上维护人员找我，说有个系统，昨天晚上报错ORA-01410: invalid ROWID
偶第一个反应是，有用ROWID=xxxx的SQL，但是维护给我说绝对没有
于是让他把SQL发给我

SELECT SUBSTR(COMP_PRD_INST_ID, 5, 12)
  FROM (SELECT *
   FROM F_1_SERV_D PARTITION(P028)
  WHERE COMP_PROD_FLAG = 0
    AND URBAN_RURAL_ID = 2) A,
       (SELECT *
   FROM PU_MODELDATA.TB_PRD_COMP_INST_DETAIL SUBPARTITION(P2800)) B
 WHERE SUBSTR(B.MEN_PROD_INST_ID, 5, 12) = A.SERV_ID

环境：AIX 5300-06 ORACLE 9.2.0.6

先测试了下
select count(*) from F_1_SERV_D PARTITION(P028)
select count(*) from PU_MODELDATA.TB_PRD_COMP_INST_DETAIL SUBPARTITION(P2800)
由于本来这些表就没索引，偶就没加提示了
发现F_1_SERV_D PARTITION(P028)上报错

本打算用ANALYZE分析下结构，可是上午有ETL，偶怕这破语句可能导致性能降级，没敢做
考虑到0RA-1410可能是逻辑损坏，DBV也没用，于是考虑用backup check logical validate

一查这个数据库的数据文件，有402个。。。估计用backup check logical validate来找，猴年马月才能找到哦

想到了熊哥在处理ORA-8103时用到的10200 event，马上翻出来试验了一把（METALINK ID 268302.1）

 SQL> alter session set max_dump_file_size=unlimited;
 
 Session altered.
 
 SQL> alter session set db_file_multiblock_read_count=1;
 
 Session altered.
 
 SQL> alter session set events '10200 trace name context forever, level 1';
 
 Session altered.
 
 SQL> SELECT COUNT(*) FROM tbas.F_1_SERV_D PARTITION(P028);
 SELECT COUNT(*) FROM tbas.F_1_SERV_D PARTITION(P028)
                           *
 ERROR at line 1:
 ORA-01410: invalid ROWID

查看TRC文件，最后结尾是停在b8623575这个块上面

 Consistent read finished for block 135 : b8623574
 Consistent read finished for block 135 : b8623574
 Consistent read started for block 135 : b8623575
   env: (scn: 0x0a02.f2994ea1  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x00
 00.00000000 0sch: scn: 0x0000.00000000)
 
 
 SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(3093443957),
        DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(3093443957)
   FROM DUALSQL>   2    3  ;
 
 DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(3093443957)
 ------------------------------------------------
 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(3093443957)
 -------------------------------------------------
                                              737
                                           2241909
                                         
DUMP下个块看看
 *** SESSION ID:(312.21876) 2009-12-25 13:13:52.911
 Start dump data blocks tsn: 135 file#: 737 minblk 2241909 maxblk 2241909
 buffer tsn: 135 rdba: 0xb8623575 (737/2241909)
 scn: 0x0a02.ab087840 seq: 0x02 flg: 0x04 tail: 0x78400602
 frmt: 0x02 chkval: 0xb85a type: 0x06=trans data
 Block header dump:  0xb8623575
  Object id on Block? Y
  seg/obj: 0xa91b98  csc: 0xa02.ab087454  itc: 3  flg: E  typ: 1 - DATA
      brn: 0  bdba: 0xb8623489 ver: 0x01
      inc: 0  exflg: 0
 
DATA_OBJECT_ID=a91b98=11082648
  
 SQL> SELECT * FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = 11082648;
 
 no rows selected
 
 SQL> SELECT * FROM dba_objects WHERE object_name ='F_1_SERV_D' AND subobject_name='P028';
 
 OWNER
 ------------------------------
 OBJECT_NAME
 --------------------------------------------------------------------------------
 SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
 ------------------------------ ---------- -------------- ------------------
 CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
 --------- --------- ------------------- ------- - - -
 TBAS
 F_1_SERV_D
 P028                              4708522       11095005 TABLE PARTITION
 21-MAR-09 25-DEC-09 2009-03-21:11:10:24 VALID   N N N
 
没有这个对象..........
其实该对象的data_object_id应该是11095005

考虑下DUMP b8623574这个块来看看
 Start dump data blocks tsn: 135 file#: 737 minblk 2241908 maxblk 2241908
 buffer tsn: 135 rdba: 0xb8623574 (737/2241908)
 scn: 0x0a02.e5950176 seq: 0x01 flg: 0x04 tail: 0x01760601
 frmt: 0x02 chkval: 0x190f type: 0x06=trans data
 Block header dump:  0xb8623574
  Object id on Block? Y
  seg/obj: 0xa94bdd  csc: 0xa02.e4cbde92  itc: 3  flg: E  typ: 1 - DATA
      brn: 0  bdba: 0xb8623489 ver: 0x01
      inc: 0  exflg: 0

DATA_OBJECT_ID=a94bdd=11095005
这个块就是正常的
然后又DUMP了这个块后面的块，发现data_object_id也是正常的。

比对了下2个数据块的数据，发现错误的数据块，数据只有4列，而正常的应该有43列

由于这个是ETL的库，数据可以重新抽取，偶就没隔离这个块，直接重抽数据。

最后，偶还是很喜欢RMAN，于是又试验了一次

 RMAN> BACKUP DEVICE TYPE DISK check logical VALIDATE DATAFILE 737;
 
 Starting backup at 25-DEC-09
 using target database controlfile instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=855 devtype=DISK
 channel ORA_DISK_1: starting full datafile backupset
 channel ORA_DISK_1: specifying datafile(s) in backupset
 input datafile fno=00737 name=/dev/rlv_sata06_28
 channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
 Finished backup at 25-DEC-09

RMAN竟然检查不出来这个逻辑错误。。。。

感觉是APPEND装载的问题，正在METALINK看看