深入分析ORA-600[2662] 错误系列一
深入分析ORA-600[2662] 错误系列一
结论
1,本文主要分析ora-600错误从报错分为数字和函数
2,本文主要分析ora-600的数字报错
3,像ora-600错误大家我特定的操作有关，涉及的Oracle机制比较复杂，具体说可能和REDO,UNDO等等有关，首先要确认到底是哪块有问题了，这样解决问题才有针对性
4，由3就引申出来，一定要全方位查阅MOS文章，获取ORA-600方面的分析文章，ID175982.1
此文极佳，建议大家精读，把报错区别为不同的模块或层次，这样解决问题就有进一步的思路了
5，由于ORA-600错误多半很复杂，所以需要你对ORACLE原理及机制很熟悉或理解，更具体一些，就是要读懂TRC文件
6，从本文测试可知，ORA-600 2662错误，其TRC文件的重点关注结构有：
报错的进程
报错的SQL --由此可知到底是什么对象出错了，进行针对性分析，比如换个对象会不会不出错，进行对比分析
报错的波函数调用堆栈 --以此信息在MOS进行查找，获取进一步的文档或信息，以进行进一步的分析，当然也可能是BUG
library cache dump --
报错进程的PROCESS STATE DUMP --可以获取报错进程当前在等待什么事件以及历史的等待事件，等待哪个数据文件及数据块，获取进一步的分析线索
UNDO BLOCK相关信息 ---这个暂时作用未知
绑定变量信息 --用此可知报错SQL的具体内容是什么，或者是什么对象，进行进一步的分析
进程的MAP信息 ---此信息作用暂时未知
7，ora-600错误非常复杂，后面的参数有数不清，引申就是一定要对ORACLE的原理有进一步精深的理解，方可诊断ORA-600错误
8，由ora-600 2662可见，涉及到UNDO BLOCK,UNDO SEGMENT HEADER BLOCK,SCN，你要完全搞懂这个报错，一定要把这些数据结构全搞通方可
当然你去查MOS，也会告诉你现成答案，不知我想大家都想知其所以然，所以掌握原理极为重要，道方为根本
9，对了，再补充一个分析，在ALERT报错ORA-600 2662错误前，一般也会有伴随性的其它报错，也可以分析这些报错，即可以进一步理解为何会报600错误
扩展问题
1,进一步学习undo block,undo segment header block,data block以及scn的概念及相互关系
最终达到的目标就是更多了解ORACLE在这些方面的机制是什么，可以快速诊断ORACLE一些怪异的ORA-600报错
分析思路
测试
---分析600错误时，要通过alert去查看近邻之前有无什么特殊的操作及报错，由下可见600错误是和恢复有关
Errors in file /home/ora10g/admin/asia/bdump/asia_cjq0_27483.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08176: consistent read failure; rollback data not available
Tue Dec 01 01:19:19 EST 2015
Errors in file /home/ora10g/admin/asia/bdump/asia_smon_27479.trc:
ORA-00600: internal error code, arguments: [2662], [0], [115], [0], [333574], [8388713], [], []
经查MOS一文：ID175982.1，详细 列举了600错误的分类，共计13个不同的layers 和 modules
可见2662属于2000，即Cache Layer，和数据一致性有关，即和恢复有关，而数据一致性一般和SCN有关，即CHECKPOINT_CHANGE有关
再查阅MOS，找到2662相关的文章，获取各个参数的含义
ORA-600[2662] Block SCN is ahead of Current SCN [ID 28929.1]
ORA-00600: internal error code, arguments: [2662], [0], [115], [0], [333574], [8388713], [], []
ORA-600 各个参数的格式和说明如下：
ERROR:
ORA-600 [2662] [a] [b] [c] [d] [e]
ARGUMENTS:
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA wherethe dependent SCN came from
可见2662代表就是数据库哪个层有问题
0代表current scn wrap
115代表current scn base
0代表dependent scn wrap
333574代表depdendent scn base
8388713代表dependent scn是源于哪个数据块即dba,也就是这个参数和前述的2个参数dependent scn base及dependent scn wrap就关联起来了，我理解可能是某个具体数据块
FUNCTION DATA_BLOCK_ADDRESS_BLOCK RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DBA NUMBER IN
FUNCTION DATA_BLOCK_ADDRESS_FILE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DBA NUMBER IN
--获取600错误最后一个参数 8388713的含义，到底是哪个文件，哪个数据块
SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(8388713) file_id,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(8388713) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
2 105
可见是和回滚段 有关，也证明我前面的分析，2662就是和数据一致性有关，即数据恢复
SQL> select owner,segment_name,segment_type from dba_extents where file_id=2 and block_id=105;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
SYS _SYSSMU7$
--从另一个角度分析可见上述报错涉及的回滚段共计占用66个数据块
SQL> select count(*) from dba_extents where segment_name='_SYSSMU7$'
2 ;
COUNT(*)
----------
66
SQL> select distinct block_id from dba_extents where segment_name='_SYSSMU7$' order by 1;
BLOCK_ID
----------
105
113
249
中间略
37065
37257
38537
39945
66 rows selected.
--既然是2号文件的105数据块报错，DUMP这个数据进行分析
Start dump data blocks tsn: 1 file#: 2 minblk 105 maxblk 105
buffer tsn: 1 rdba: 0x00800069 (2/105)
scn: 0x0001.00000e73 seq: 0x02 flg: 0x00 tail: 0x0e732602 ---scn
frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK ---可见是回滚段头块
Hex dump of block: st=0, typ_found=1
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 66 #blocks: 3647
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800158 ext#: 4 blk#: 7 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 4
Unlocked
Map Header:: next 0x00000000 #extents: 66 obj#: 0 flag: 0x40000000
---extent map控制extent分配的情况，对应dba_extents的extents总数
Extent Map
-----------------------------------------------------------------
0x0080006a length: 7
0x00800071 length: 8
0x008000f9 length: 8
0x00800131 length: 8
0x00800151 length: 8
0x00800159 length: 8
0x008001e1 length: 8
0x00800201 length: 8
0x008002c1 length: 8
0x008002e9 length: 8
0x00800329 length: 8
0x00800381 length: 8
0x008003c1 length: 8
0x008003e9 length: 8
0x00800449 length: 8
0x00800461 length: 8
0x00800509 length: 128
0x00800a89 length: 128
0x00801309 length: 128
0x00801709 length: 128
0x00801c89 length: 128
0x00802109 length: 128
0x00802689 length: 128
0x00802e89 length: 128
0x00803109 length: 128
0x00803689 length: 128
0x00803b09 length: 128
0x00804f89 length: 128
0x00805009 length: 128
0x00805089 length: 128
0x00805509 length: 128
0x00806089 length: 128
0x00806109 length: 128
0x00806209 length: 128
0x008068c1 length: 8
0x008068c9 length: 8
0x008068d9 length: 8
0x008068f1 length: 8
0x008068f9 length: 8
0x00806909 length: 128
0x00806e89 length: 128
0x00807789 length: 8
0x00807791 length: 8
0x008077a9 length: 8
0x008077b1 length: 8
0x008077f9 length: 8
0x00807801 length: 8
0x00807a09 length: 8
0x00807a11 length: 8
0x00807a51 length: 8
0x00807a71 length: 8
0x00807a89 length: 128
0x00808199 length: 8
0x008081b9 length: 8
0x008081c9 length: 8
0x00808309 length: 128
0x00808789 length: 128
0x00808e21 length: 8
0x00808e29 length: 8
0x00809089 length: 8
0x00809091 length: 8
0x00809099 length: 8
0x008090c9 length: 8
0x00809189 length: 128
0x00809689 length: 128
0x00809c09 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1448938837
Extent Number:1 Commit Time: 1448938849
Extent Number:2 Commit Time: 1448948373
Extent Number:3 Commit Time: 1448951574
Extent Number:4 Commit Time: 1448960452
Extent Number:5 Commit Time: 1442068172
Extent Number:6 Commit Time: 1442068173
Extent Number:7 Commit Time: 1442068174
Extent Number:8 Commit Time: 1442068175
Extent Number:9 Commit Time: 1442068176
Extent Number:10 Commit Time: 1442068177
Extent Number:11 Commit Time: 1442068178
Extent Number:12 Commit Time: 1442068179
Extent Number:13 Commit Time: 1442068180
Extent Number:14 Commit Time: 1442068180
Extent Number:15 Commit Time: 1442068181
Extent Number:16 Commit Time: 1442068205
Extent Number:17 Commit Time: 1442068236
Extent Number:18 Commit Time: 1442068255
Extent Number:19 Commit Time: 1442068290
Extent Number:20 Commit Time: 1442068335
Extent Number:21 Commit Time: 1442068385
Extent Number:22 Commit Time: 1442068427
Extent Number:23 Commit Time: 1442068450
Extent Number:24 Commit Time: 1442068513
Extent Number:25 Commit Time: 1442068541
Extent Number:26 Commit Time: 1442068634
Extent Number:27 Commit Time: 1442068634
Extent Number:28 Commit Time: 1442068634
Extent Number:29 Commit Time: 1442068667
Extent Number:30 Commit Time: 1442068689
Extent Number:31 Commit Time: 1442068689
Extent Number:32 Commit Time: 1442068691
Extent Number:33 Commit Time: 1442068739
Extent Number:34 Commit Time: 1442068759
Extent Number:35 Commit Time: 1442068764
Extent Number:36 Commit Time: 1442068770
Extent Number:37 Commit Time: 1442068770
Extent Number:38 Commit Time: 1442068770
Extent Number:39 Commit Time: 1442068795
Extent Number:40 Commit Time: 1442068830
Extent Number:41 Commit Time: 1442068831
Extent Number:42 Commit Time: 1442068831
Extent Number:43 Commit Time: 1442068834
Extent Number:44 Commit Time: 1442068837
Extent Number:45 Commit Time: 1442068837
Extent Number:46 Commit Time: 1442068837
Extent Number:47 Commit Time: 1442068839
Extent Number:48 Commit Time: 1442068840
Extent Number:49 Commit Time: 1442068840
Extent Number:50 Commit Time: 1442068842
Extent Number:51 Commit Time: 1442068872
Extent Number:52 Commit Time: 1442068872
Extent Number:53 Commit Time: 1442068872
Extent Number:54 Commit Time: 1442068873
Extent Number:55 Commit Time: 1442068914
Extent Number:56 Commit Time: 1442068952
Extent Number:57 Commit Time: 1442068956
Extent Number:58 Commit Time: 1442068957
Extent Number:59 Commit Time: 1442068957
Extent Number:60 Commit Time: 1442068958
Extent Number:61 Commit Time: 1442068958
Extent Number:62 Commit Time: 1442068961
Extent Number:63 Commit Time: 1442068975
Extent Number:64 Commit Time: 1442069033
Extent Number:65 Commit Time: 1448938830
--trn ctl是事务表，和事务相关,事务表也有scn
TRN CTL:: seq: 0x0046 chd: 0x0001 ctl: 0x002a inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800158.0046.37 scn: 0x0000.028f6415 --scn
Version: 0x01
---free block pool自由块池我理解可能是undo segment中的空闲块的存储信息，因为undo segment也是循环使用的啊
FREE BLOCK POOL::
uba: 0x00800158.0046.3a ext: 0x4 spc: 0x302
uba: 0x00000000.0046.25 ext: 0x4 spc: 0x904
uba: 0x00000000.0043.14 ext: 0x1 spc: 0x13fe
uba: 0x00000000.0013.09 ext: 0x13 spc: 0x1b8c
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
---事务表的条目，共计48条，可能不同数据库版本也会有差异,可见事务表条目也有scn
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x00b0 0x000a 0x0001.000009bb 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448957928
0x01 9 0x00 0x00ae 0x002d 0x0000.028f6419 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x02 9 0x00 0x00af 0x0023 0x0000.028f64b2 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x03 9 0x00 0x00af 0x0011 0x0001.00000197 0x00800157 0x0000.000.00000000 0x00000002 0x00000000 1448953786
0x04 9 0x00 0x00af 0x0027 0x0001.00000a36 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448957928
0x05 9 0x00 0x00ae 0x0017 0x0001.00000341 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x06 9 0x00 0x00af 0x0024 0x0001.00000417 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x07 9 0x00 0x00af 0x000c 0x0001.0000032e 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x08 9 0x00 0x00af 0x000f 0x0001.000007e6 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448956848
0x09 9 0x00 0x00ae 0x0005 0x0001.0000033b 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x0a 9 0x00 0x00af 0x0020 0x0001.000009da 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448957928
0x0b 9 0x00 0x00ae 0x0003 0x0001.0000017d 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448953725
0x0c 9 0x00 0x00ae 0x0029 0x0001.00000331 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x0d 9 0x00 0x00ae 0x002c 0x0001.000002a1 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x0e 9 0x00 0x00af 0x0018 0x0001.00000366 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x0f 9 0x00 0x00af 0x0000 0x0001.0000082d 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448956850
0x10 9 0x00 0x00af 0x0021 0x0001.00000d8c 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448960328
0x11 9 0x00 0x00ae 0x000d 0x0001.000001fd 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448953867
0x12 9 0x00 0x00ae 0x0019 0x0001.000003b0 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x13 9 0x00 0x00af 0x001e 0x0001.000003c4 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x14 9 0x00 0x00af 0x0012 0x0001.000003a8 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x15 9 0x00 0x00ae 0x0026 0x0001.000002f3 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x16 9 0x00 0x00af 0x0008 0x0001.000006ca 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448956127
0x17 9 0x00 0x00af 0x000e 0x0001.00000362 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x18 9 0x00 0x00af 0x001d 0x0001.0000036c 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x19 9 0x00 0x00af 0x0013 0x0001.000003c2 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1a 9 0x00 0x00ae 0x0016 0x0001.00000445 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1b 9 0x00 0x00af 0x002b 0x0001.000003f7 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1c 9 0x00 0x00af 0x002e 0x0001.000003de 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1d 9 0x00 0x00ae 0x002f 0x0001.00000384 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1e 9 0x00 0x00af 0x001c 0x0001.000003c8 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x1f 9 0x00 0x00aa 0x0002 0x0000.028f649e 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x20 9 0x00 0x00ae 0x0004 0x0001.00000a28 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448957928
0x21 9 0x00 0x00af 0x002a 0x0001.00000dad 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448960328
0x22 9 0x00 0x00ad 0x0028 0x0000.028f6482 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x23 9 0x00 0x00ae 0x000b 0x0001.00000083 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1448953127
0x24 9 0x00 0x00af 0x001a 0x0001.00000427 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x25 9 0x00 0x00ae 0x0007 0x0001.00000306 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x26 9 0x00 0x00ae 0x0025 0x0001.00000304 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x27 9 0x00 0x00af 0x0010 0x0001.00000a56 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448957928
0x28 9 0x00 0x00ae 0x001f 0x0000.028f6492 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x29 9 0x00 0x00ae 0x0009 0x0001.00000335 0x00800155 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x2a 9 0x00 0x00af 0xffff 0x0001.00000e73 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448960452
0x2b 9 0x00 0x00ae 0x0006 0x0001.00000401 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x2c 9 0x00 0x00ae 0x0015 0x0001.000002d2 0x00800157 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x2d 9 0x00 0x00ae 0x0022 0x0000.028f6466 0x00800156 0x0000.000.00000000 0x00000001 0x00000000 1448952168
0x2e 9 0x00 0x00af 0x001b 0x0001.000003e4 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
0x2f 9 0x00 0x00ae 0x0014 0x0001.000003a2 0x00800158 0x0000.000.00000000 0x00000001 0x00000000 1448954328
End dump data blocks tsn: 1 file#: 2 minblk 105 maxblk 105
[ora10g@seconary udump]$
可见undo segment header block在几个结构皆有scn,到底是哪个scn呢
要解决这个问题，就要去理解undo segment header block的原理及结构了
转换思路，我们看看trc文件有什么信息，或者说我们重要要关注哪些部分的信息
---仅列出最重要的信息
Unix process pid: 27479, image: oracle@seconary (SMON) ---可见是smon进程在工作时报了ora-600 2662错误，从这儿也可以看出SMON进程是负责数据库一致性，进行实例恢复和数据一致性校验工作的
*** SERVICE NAME:(SYS$BACKGROUND) 2015-12-01 01:18:30.725
*** SESSION ID:(164.1) 2015-12-01 01:18:30.725
*** 2015-12-01 01:18:30.725
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [2662], [0], [91], [0], [333574], [8388713], [], [] --ORA-600具体报错
----引发ORA-600报错的SQL
Current SQL statement for this session:
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
--报错时产生的调用波函数堆栈，用这个去MOS进行匹配，可以确认是否是BUG或获取相关MOS文章，进行进一步分析
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ssd_unwind_bp: unhandled instruction at 0x76b061 instr=f
ssd_unwind_bp: unhandled instruction at 0x1127327 instr=f
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
7FFF208BF9F0 ? 7FFF208BFA50 ?
7FFF208BF990 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
7FFF208BF9F0 ? 7FFF208BFA50 ?
7FFF208BF990 ? 000000000 ?
中间略
+244 000000004 ? 7FFF208CF558 ?
000000000 ? 7FFF00000000 ?
_start()+41 call __libc_start_main() 00072D108 ? 000000001 ?
7FFF208CF718 ? 000000000 ?
000000000 ? 000000003 ?
我们从报错的SQL进行分析
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
---可见报错SQL涉及的表有9768条记录
SQL> select count(*) from hist_head$;
COUNT(*)
----------
9768
---报错表好像和收集统计信息有用
SQL> desc hist_head$;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
COL# NOT NULL NUMBER
BUCKET_CNT NOT NULL NUMBER
ROW_CNT NOT NULL NUMBER
CACHE_CNT NUMBER
NULL_CNT NUMBER
TIMESTAMP# DATE
SAMPLE_SIZE NUMBER
MINIMUM NUMBER
MAXIMUM NUMBER
DISTCNT NUMBER
LOWVAL RAW(32)
HIVAL RAW(32)
DENSITY NUMBER
INTCOL# NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
AVGCLN NUMBER
SPARE3 NUMBER
SPARE4 NUMBER
SQL> select count(obj#),count(distinct obj#) from hist_head$;
COUNT(OBJ#) COUNT(DISTINCTOBJ#)
----------- -------------------
9768 1001
继续在TRC文件查找报错SQL绑定变量的值
Cursor#2(0x2b7fc96c1728) state=FETCH curiob=0x2b7fc994b5f8
curflg=8007 fl2=200000 par=0x2b7fc96c16c0 ses=0x95b8f060
sqltxt(0x955798b8)=select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
hash=6d11d7de2049577d933d2385337fc737
parent=0x90271198 maxchild=01 plk=0x90f3ec08 ppn=n
cursor instantiation=0x2b7fc994b5f8 used=1448950758
child#0(0x95579688) pcs=0x902707b8
clk=0x90f3e420 ci=0x90270490 pn=0x9564a800 ctx=0x8fc4a1b8
kgsccflg=1 llk[0x2b7fc994b600,0x2b7fc994b600] idx=70038
xscflg=e0141476 fl2=5000401 fl3=4022210c fl4=100
Bind bytecodes
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 0
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 32
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b7fc9963ee8 bln=22 avl=03 flg=05
value=183 ---绑定变量:1
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b7fc9963eb8 bln=24 avl=02 flg=05
value=4 --绑定变量:2
SQL> select obj#,name from obj$ where obj#=183;
OBJ# NAME
---------- ------------------------------
183 TYPE$
SQL> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=183 and intcol#=4;
BUCKET_CNT ROW_CNT CACHE_CNT NULL_CNT TIMESTAMP SAMPLE_SIZE MINIMUM MAXIMUM DISTCNT LOWVAL HIVAL DENSITY COL# SPARE1 SPARE2 AVGCLN
---------- ---------- ---------- ---------- --------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
1 0 0 0 30-NOV-15 1390 0 1.0957E+36 1390 00000000000000000000000000000001 D307723624873404E0340003BA0FD53F .000719424 4 1390 2 17
SQL>
---再看看type$这个底层表，关于此表含义请见dcore.sql
SQL> desc type$;
Name Null? Type
----------------- -------- ------------
TOID NOT NULL RAW(16)
VERSION# NOT NULL NUMBER
VERSION NOT NULL VARCHAR2(30)
TVOID NOT NULL RAW(16)
TYPECODE NOT NULL NUMBER
PROPERTIES NOT NULL NUMBER
ATTRIBUTES NUMBER
METHODS NUMBER
HIDDENMETHODS NUMBER
SUPERTYPES NUMBER
SUBTYPES NUMBER
EXTERNTYPE NUMBER
EXTERNNAME VARCHAR2(400
0)
HELPERCLASSNAME VARCHAR2(400
0)
LOCAL_ATTRS NUMBER
LOCAL_METHODS NUMBER
TYPEID RAW(16)
ROOTTOID RAW(16)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SUPERTOID RAW(16)
HASHCODE RAW(17)
--共计1390条记录
SQL> select count(*) from type$;
COUNT(*)
----------
1390
SQL>
---从上述思路没法继续分析了，转换个思路，找找报ORA-600 2662错误前的报错TRC是什么，看不能找到相关的线索
---列出重点关注内容
Unix process pid: 27483, image: oracle@seconary (CJQ0)
*** SERVICE NAME:(SYS$BACKGROUND) 2015-12-01 01:14:21.173
*** SESSION ID:(162.1) 2015-12-01 01:14:21.173
*** 2015-12-01 01:14:21.173
ORA-00604: error occurred at recursive SQL level 1
ORA-08176: consistent read failure; rollback data not available
---600错误信息
*** 2015-12-01 01:15:11.446
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [2662], [0], [20], [0], [333589], [0], [], []
--引发600错误的SQL
Current SQL statement for this session:
select u.name, o.name, a.interface_version#, o.obj# from association$ a, user$ u, obj$ o where a.obj# = :1 and a.property = :2 and a.statstype# = o.obj# and u.user# = o.owner#
--报错的函数调用堆栈
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ssd_unwind_bp: unhandled instruction at 0x76b061 instr=f
ssd_unwind_bp: unhandled instruction at 0x1127327 instr=f
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
7FFF83DBDC10 ? 7FFF83DBDC70 ?
7FFF83DBDBB0 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
7FFF83DBDC10 ? 7FFF83DBDC70 ?
7FFF83DBDBB0 ? 000000000 ?
ksfdmp()+63 call ksedmp() 000000003 ? 000000001 ?
7FFF83DBDC10 ? 7FFF83DBDC70 ?
7FFF83DBDBB0 ? 000000000 ?
kgeriv()+176 call ksfdmp() 006AE9A20 ? 000000003 ?
---找到报错SQL的绑定变量
7FFF83DBDC10 ? 7FFF83DBDC7
Cursor#4(0x2b40803017f8) state=FETCH curiob=0x2b4080319810
curflg=7 fl2=200000 par=0x2b4080301790 ses=0x95b88508
sqltxt(0x954f6068)=
select u.name, o.name, a.interface_version#, o.obj# from association$ a, user$ u, obj$ o where a.obj# = :1 and a.property = :2 and a.statstype# = o.obj# and u.user# = o.owner#
hash=767439bb115ad4a42c7b76fafb52493f
parent=0x901ff978 maxchild=01 plk=0x90f88f30 ppn=n
cursor instantiation=0x2b4080319810 used=1448950509
child#0(0x954f5ee0) pcs=0x901fef98
clk=0x90f8c480 ci=0x901fec70 pn=0x954f7198 ctx=0x8fb289f0
kgsccflg=1 llk[0x2b4080319818,0x2b4080319818] idx=40
xscflg=e0141476 fl2=5000401 fl3=4022210c fl4=100
Bind bytecodes
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 0
Opcode = 1 Unoptimized
Offsi = 48, Offsi = 32
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b4080603d78 bln=22 avl=03 flg=05
value=4365
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b4080603d48 bln=24 avl=02 flg=05
value=3
---还是没有什么实质的思路，回过头再看报错的ORA-600 2662的TRC文件的其它内容
--发现还有UNDO BLOCK的信息
UNDO BLK:
xid: 0x0006.011.000000b5 seq: 0x57 cnt: 0x3 irb: 0x3 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e98
*-----------------------------
* Rec #0x1 slt: 0x2d objn: 257(0x00000101) objd: 257 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 21 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00800060
*-----------------------------
index general undo (branch) operations
KTB Redo
op: 0x05 ver: 0x01
op: R itc: 2
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Dump kdige : block dba :0x0040a7ba, seghdr dba: 0x00400819
make leaf block empty
(2): 01 00
*-----------------------------
* Rec #0x2 slt: 0x2d objn: 257(0x00000101) objd: 257 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 21 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index general undo (branch) operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.013.0000008a uba: 0x008064fc.0023.02
flg: C--- lkc: 0 scn: 0x0000.00033cd2
Dump kdige : block dba :0x0040081a, seghdr dba: 0x00400819
branch block row purge
(4): 01 00 1e 00
*-----------------------------
* Rec #0x3 slt: 0x11 objn: 256(0x00000100) objd: 256 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 21 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080005f.0056.01 ctl max scn: 0x0000.0004914b prv tx scn: 0x0000.0004914d
txn start scn: scn: 0x0000.0004a85f logon user: 0
prev brb: 0 prev bcl: 0
index general undo (branch) operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.016.0000008a uba: 0x008064fe.0023.01
flg: CB-- lkc: 0 scn: 0x0000.00033cd5
Dump kdige : block dba :0x0040a7c1, seghdr dba: 0x00400811
unlock block
(1): 01
----------------------------------------
SO: 0x944a5578, type: 24, owner: 0x95bb9558, flag: INIT/-/-/0x00
(buffer) (CR) PR: 0x95a6ba30 FLG: 0x100000
class bit: (nil)
kcbbfbp: [BH: 0x877ed898, LINK: 0x944a55b8]
where: kdiwh08: kdiixs, why: 0
BH (0x877ed898) file#: 1 rdba: 0x0040081a (1/2074) class: 1 ba: 0x87610000
set: 12 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 4
dbwrid: 0 obj: 257 objn: 257 tsn: 0 afn: 1
hash: [94b10178,94b10178] lru: [877eda28,877ed808]
ckptq: [NULL] fileq: [NULL] objq: [90f57fc8,877ed218]
use: [944a55b8,944a55b8] wait: [NULL]
st: XCURRENT md: SHR tch: 3
flags:
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Using State Objects
----------------------------------------
SO: 0x944a5578, type: 24, owner: 0x95bb9558, flag: INIT/-/-/0x00
(buffer) (CR) PR: 0x95a6ba30 FLG: 0x100000
class bit: (nil)
kcbbfbp: [BH: 0x877ed898, LINK: 0x944a55b8]
where: kdiwh08: kdiixs, why: 0
buffer tsn: 0 rdba: 0x0040081a (1/2074)
scn: 0x0000.0004a862 seq: 0x01 flg: 0x04 tail: 0xa8620601
frmt: 0x02 chkval: 0x07dc type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
---还有索引块的信息，不过这个索引和ORA-600 2662报错有何关系，暂时不知
Block header dump: 0x0040081a
Object id on Block? Y
seg/obj: 0x101 csc: 0x00.4a862 itc: 1 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.02d.000000b4 0x00800061.0057.02 C--- 0 scn 0x0000.0004a85f
Branch block dump
=================
header address 2271281220=0x87610044
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 31
kdxcofbo 90=0x5a
kdxcofeo 7751=0x1e47
kdxcoavs 7661
kdxbrlmc 4196379=0x40081b
kdxbrsno 30
kdxbrbksz 8056
kdxbr2urrc 0
row#0[7913] dba: 4217498=0x405a9a
col 0; len 2; (2): c1 4b
col 1; TERM
row#1[7948] dba: 4217214=0x40597e
col 0; len 3; (3): c2 02 5c
col 1; len 2; (2): c1 0b
col 2; TERM
row#2[7896] dba: 4217500=0x405a9c
还有报错进程的信息
PROCESS STATE
-------------
Process global information:
process: 0x95a6ba30, call: 0x95bb8a40, xact: (nil), curses: 0x95b8f060, usrses: 0x95b97130
----------------------------------------
SO: 0x95a6ba30, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 0x95bb8a40/0x95bb76d0, flag: (16) SYSTEM
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 33
last post received-location: ksrpublish
last process to post me: 95a6f1f8 2 0
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 95a6aa40 1 6
(latch info) wait_event=0 bits=2
holding (efd=23) 94bf6f88 Child cache buffers chains level=1 child#=7788
Location from where latch is held: kcbzib: finish free bufs:
Context saved from call: 0
state=busy(exclusive) (val=0x2000000000000008) holder orapid = 8
Process Group: DEFAULT, pseudo proc: 0x95ab38f0
O/S info: user: ora10g, term: UNKNOWN, ospid: 27479
OSD pid info: Unix process pid: 27479, image: oracle@seconary (SMON)
----------------------------------------
SO: 0x951af218, type: 11, owner: 0x95a6ba30, flag: INIT/-/-/0x00
(broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 0x95a6ba30,
event: 1, last message event: 1,
last message waited event: 1, next message: (nil)(0), messages read: 0
channel: (0x951b7af0) Get Segment Information Channel
scope: 0, event: 1, last mesage event: 0,
publishers/subscribers: 0/1,
messages published: 0
heuristic msg queue length: 0
----------------------------------------
SO: 0x951aefd8, type: 11, owner: 0x95a6ba30, flag: INIT/-/-/0x00
(broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 0x95a6ba30,
event: 2, last message event: 4,
last message waited event: 4, next message: (nil)(0), messages read: 1
channel: (0x951b7cc0) File Extent Array Channel
scope: 0, event: 4, last mesage event: 4,
publishers/subscribers: 0/1,
messages published: 1
heuristic msg queue length: 0
(FOB) flags=2 fib=0x93d97478 incno=0 pending i/o cnt=0
fname=/home/ora10g/asia/asia/temp01.dbf
fno=201 lblksz=8192 fsiz=2560
(FOB) flags=2 fib=0x93d970c8 incno=0 pending i/o cnt=0
fname=/home/ora10g/asia/asia/users01.dbf
fno=4 lblksz=8192 fsiz=640
(FOB) flags=2 fib=0x93d96d18 incno=0 pending i/o cnt=0
fname=/home/ora10g/asia/asia/sysaux01.dbf
fno=3 lblksz=8192 fsiz=17920
(FOB) flags=2 fib=0x93d96968 incno=0 pending i/o cnt=0
fname=/home/ora10g/asia/asia/undotbs01.dbf
fno=2 lblksz=8192 fsiz=40960
(FOB) flags=2 fib=0x93d965a0 incno=5 pending i/o cnt=0
fname=/home/ora10g/asia/asia/system01.dbf
fno=1 lblksz=8192 fsiz=48640
----------------------------------------
SO: 0x951ae7e0, type: 11, owner: 0x95a6ba30, flag: INIT/-/-/0x00
(broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 0x95a6ba30,
event: 1, last message event: 1,
last message waited event: 1, next message: (nil)(0), messages read: 0
channel: (0x951b7750) obj stat del channel
scope: 4, event: 1, last mesage event: 0,
publishers/subscribers: 0/1,
messages published: 0
heuristic msg queue length: 0
----------------------------------------
SO: 0x951ad358, type: 11, owner: 0x95a6ba30, flag: INIT/-/-/0x00
(broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 0x95a6ba30,
event: 1, last message event: 1,
last message waited event: 1, next message: (nil)(0), messages read: 0
channel: (0x951bd1f0) KTF time scn map
scope: 7, event: 1, last mesage event: 0,
publishers/subscribers: 0/1,
messages published: 0
heuristic msg queue length: 0
----------------------------------------
SO: 0x951ad238, type: 11, owner: 0x95a6ba30, flag: INIT/-/-/0x00
(broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: 0x95a6ba30,
event: 7, last message event: 13,
last message waited event: 13, next message: (nil)(0), messages read: 1
channel: (0x951bc8e0) scumnt mount lock
scope: 1, event: 13, last mesage event: 13,
publishers/subscribers: 0/11,
messages published: 1
heuristic msg queue length: 0
还有library cache dump信息
LIBRARY OBJECT HANDLE: handle=9568bc38 mtx=0x9568bd68(0) lct=27 pct=27 cdp=0
name=SYS.CCOL$
hash=8e1b2f8107c168f1eeb5a1594db547a0 timestamp=09-12-2015 10:28:45
namespace=TABL flags=PKP/KGHP/TIM/KEP/SML/BSO/[02900002]
kkkk-dddd-llll=0101-0301-0309 lock=N pin=0 latch#=3 hpc=000c hlc=000c
lwt=0x9568bce0[0x9568bce0,0x9568bce0] ltm=0x9568bcf0[0x9568bcf0,0x9568bcf0]
pwt=0x9568bca8[0x9568bca8,0x9568bca8] ptm=0x9568bcb8[0x9568bcb8,0x9568bcb8]
ref=0x9568bd10[0x9568bd10,0x9568bd10] lnd=0x9568bd28[0x95661f98,0x9569add8]
LIBRARY OBJECT: object=903b8690
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 9568cd60 903b87e8 I/P/A/-/- 0 NONE 00
8 903b8990 8ffa62a8 I/P/A/-/- 0 NONE 00
9 9039c4e8 8feed2c8 I/-/A/-/- 0 NONE 00
----------------------------------------
SO: 0x90f43220, type: 53, owner: 0x95b97130, flag: INIT/-/-/0x00
----- End of Call Stack Trace -----
******************* Dumping process map ****************
00400000-0685e000 r-xp 00000000 fd:00 7707660 /home/ora10g/product/10.2.0/db_1/bin/oracle
06a5e000-06aea000 rwxp 0645e000 fd:00 7707660 /home/ora10g/product/10.2.0/db_1/bin/oracle
06aea000-06b10000 rwxp 06aea000 00:00 0
0f673000-0f6d7000 rwxp 0f673000 00:00 0 [heap]
60000000-95e00000 rwxs 00000000 00:0d 61571082 /22 (deleted)
3d55600000-3d5561c000 r-xp 00000000 fd:00 4358440 /lib64/ld-2.5.so
3d5581b000-3d5581c000 r-xp 0001b000 fd:00 4358440 /lib64/ld-2.5.so
3d5581c000-3d5581d000 rwxp 0001c000 fd:00 4358440 /lib64/ld-2.5.so
3d55a00000-3d55b4d000 r-xp 00000000 fd:00 4358441 /lib64/libc-2.5.so
3d55b4d000-3d55d4d000 ---p 0014d000 fd:00 4358441 /lib64/libc-2.5.so
3d55d4d000-3d55d51000 r-xp 0014d000 fd:00 4358441 /lib64/libc-2.5.so
3d55d51000-3d55d52000 rwxp 00151000 fd:00 4358441 /lib64/libc-2.5.so
3d55d52000-3d55d57000 rwxp 3d55d52000 00:00 0
3d55e00000-3d55e82000 r-xp 00000000 fd:00 4358442 /lib64/libm-2.5.so
3d55e82000-3d56081000 ---p 00082000 fd:00 4358442 /lib64/libm-2.5.so
3d56081000-3d56082000 r-xp 00081000 fd:00 4358442 /lib64/libm-2.5.so
3d56082000-3d56083000 rwxp 00082000 fd:00 4358442 /lib64/libm-2.5.so
3d56200000-3d56202000 r-xp 00000000 fd:00 4358443 /lib64/libdl-2.5.so
3d56202000-3d56402000 ---p 00002000 fd:00 4358443 /lib64/libdl-2.5.so
3d56402000-3d56403000 r-xp 00002000 fd:00 4358443 /lib64/libdl-2.5.so
3d56403000-3d56404000 rwxp 00003000 fd:00 4358443 /lib64/libdl-2.5.so
评论暂时关闭