深入分析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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1852078/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1852078/