結論

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

深入分析ora-600 2662錯誤系列一

結論

擴充套件問題

分析思路

測試

相關文章