数据库版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio NLSRTL Version 10.2.0.4.0 - Production
找出报错对象
--方法1
*** SESSION ID:(795.16405) 2012-04-05 09:36:11.958
row 080095ee.26 continuation at
file# 32 block# 38382 slot 39 not found
**************************************************
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 39 ..... nrows: 19
**************************************************
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
2 FROM DBA_EXTENTS A
3 WHERE FILE_ID = &FILE_ID
4 AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 32
old 3: WHERE FILE_ID = &FILE_ID
new 3: WHERE FILE_ID = 32
Enter value for block_id: 38382
old 4: AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new 4: AND 38382 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE TABLESPACE_NAME PARTITION_NAME
------------------ ------------------------------ ------------------------------
AHV8
TBL_IVR_LOG
TABLE PARTITION CSS_PARTITION IVR_LOG_2012_MONTH04
--方法2
*** 2012-04-05 09:36:11.965
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO TBL_CONTACT_INFO_FAILED_TMP
select * from TBL_IVR_LOG
SO: 70000017f954f50, type: 4, owner: 70000017f65a840, flag: INIT/-/-/0x00
(session) sid: 795 trans: 70000017464a1e8, creator: 70000017f65a840, flag: (40110041) USR/- BSY/-/-/-/-/-
DID: 0002-0067-000305BD, short-term DID: 0002-0067-000305BE
txn branch: 0
oct: 2, prv: 0, sql: 70000015180ee98, psql: 700000180d67550, user: 49/AHV8
service name: SYS$USERS
O/S info: user: oracle10, term: UNKNOWN, ospid: 12976218, machine: zwq_kfdb2
program: oracle@zwq_kfdb2 (J002)
last wait for 'db file sequential read' blocking sess=0x0 seq=226 wait_time=17071 seconds since wait started=1
file#=20, block#=95ee, blocks=1
--方法3
Block header dump: 0x080095ee
Object id on Block? Y
seg/obj: 0x11eeb csc: 0x6f2.848e814 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x7c09c89 ver: 0x01 opc: 0
inc: 0 exflg: 0
SQL> select to_number('11eeb','xxxxxxxx') from dual;
TO_NUMBER('11EEB','XXXXXXXX')
-----------------------------
73451
SQL> select owner,object_name,subobject_name,object_type from dba_objects where data_object_id='73451';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AHV8
TBL_IVR_LOG
IVR_LOG_2012_MONTH04 TABLE PARTITION
验证是否真的坏块
SQL> select name from v$datafile where file#=32; NAME ------------------------------------------------------ /dev/rdb1_data27 [zwq_kfdb2:/home/oraeye]dbv file='/dev/rdb1_data27' blocksize=8192 DBVERIFY: Release 10.2.0.4.0 - Production on Fri Apr 13 15:33:10 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /dev/rdb1_data27 DBVERIFY - Verification complete Total Pages Examined : 1048448 Total Pages Processed (Data) : 947357 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 4756 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 96335 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 297329920 (1778.297329920) SQL> select count(*) from AHV8.TBL_IVR_LOG partition(IVR_LOG_2012_MONTH04); COUNT(*) ---------- 8798030
总结：很明显这次出现这个问题，因为内存中出现坏块导致，经过一段时间buffer cache中的坏块内容已经被老化，所以现在不能重现(甚至不用做任何操作)。如果内存中出现了坏块，而且还没有被老化掉，可以刷新data buffer;如果是数据块出现坏块，根据实际情况决定处理
加我QQ(107644445)
