网站建设的成果怎么写,网站建设实验总结,什么叫网站流量,wordpress怎么分页结论 1,本文主要分析ora-600错误从报错分为数字和函数 2,本文主要分析ora-600的数字报错 3,像ora-600错误大家我特定的操作有关#xff0c;涉及的ORACLE机制比较复杂#xff0c;具体说可能和REDO,UNDO等等有关#xff0c;首先要确认到底是哪块有问题了#xff0c;这样解决… 结论 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信息 ---此信息作用暂时未知 7ora-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_id2 and block_id105; 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: 0x26KTU SMU HEADER BLOCK ---可见是回滚段头块 Hex dump of block: st0, typ_found1 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. hdrs 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 [ora10gseconary udump]$ 可见undo segment header block在几个结构皆有scn,到底是哪个scn呢 要解决这个问题就要去理解undo segment header block的原理及结构了 转换思路我们看看trc文件有什么信息或者说我们重要要关注哪些部分的信息 ---仅列出最重要的信息 Unix process pid: 27479, image: oracleseconary (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 instrf ssd_unwind_bp: unhandled instruction at 0x1127327 instrf 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) stateFETCH curiob0x2b7fc994b5f8 curflg8007 fl2200000 par0x2b7fc96c16c0 ses0x95b8f060 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 hash6d11d7de2049577d933d2385337fc737 parent0x90271198 maxchild01 plk0x90f3ec08 ppnn cursor instantiation0x2b7fc994b5f8 used1448950758 child#0(0x95579688) pcs0x902707b8 clk0x90f3e420 ci0x90270490 pn0x9564a800 ctx0x8fc4a1b8 kgsccflg1 llk[0x2b7fc994b600,0x2b7fc994b600] idx70038 xscflge0141476 fl25000401 fl34022210c fl4100 Bind bytecodes Opcode 1 Unoptimized Offsi 48, Offsi 0 Opcode 1 Unoptimized Offsi 48, Offsi 32 kkscoacd Bind#0 oacdty02 mxl22(22) mxlc00 mal00 scl00 pre00 oacflg08 fl20001 frm00 csi00 siz24 off0 kxsbbbfp2b7fc9963ee8 bln22 avl03 flg05 value183 ---绑定变量:1 Bind#1 oacdty02 mxl22(22) mxlc00 mal00 scl00 pre00 oacflg08 fl20001 frm00 csi00 siz24 off0 kxsbbbfp2b7fc9963eb8 bln24 avl02 flg05 value4 --绑定变量: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.0957E36 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: oracleseconary (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 instrf ssd_unwind_bp: unhandled instruction at 0x1127327 instrf 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) stateFETCH curiob0x2b4080319810 curflg7 fl2200000 par0x2b4080301790 ses0x95b88508 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# hash767439bb115ad4a42c7b76fafb52493f parent0x901ff978 maxchild01 plk0x90f88f30 ppnn cursor instantiation0x2b4080319810 used1448950509 child#0(0x954f5ee0) pcs0x901fef98 clk0x90f8c480 ci0x901fec70 pn0x954f7198 ctx0x8fb289f0 kgsccflg1 llk[0x2b4080319818,0x2b4080319818] idx40 xscflge0141476 fl25000401 fl34022210c fl4100 Bind bytecodes Opcode 1 Unoptimized Offsi 48, Offsi 0 Opcode 1 Unoptimized Offsi 48, Offsi 32 kkscoacd Bind#0 oacdty02 mxl22(22) mxlc00 mal00 scl00 pre00 oacflg08 fl20001 frm00 csi00 siz24 off0 kxsbbbfp2b4080603d78 bln22 avl03 flg05 value4365 Bind#1 oacdty02 mxl22(22) mxlc00 mal00 scl00 pre00 oacflg08 fl20001 frm00 csi00 siz24 off0 kxsbbbfp2b4080603d48 bln24 avl02 flg05 value3 ---还是没有什么实质的思路回过头再看报错的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: 0x06trans data Hex dump of block: st0, typ_found1 ---还有索引块的信息不过这个索引和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 22712812200x87610044 kdxcolev 1 KDXCOLEV Flags - - - kdxcolok 0 kdxcoopc 0x80: opcode0: iot flags--- is convertedY kdxconco 3 kdxcosdc 1 kdxconro 31 kdxcofbo 900x5a kdxcofeo 77510x1e47 kdxcoavs 7661 kdxbrlmc 41963790x40081b kdxbrsno 30 kdxbrbksz 8056 kdxbr2urrc 0 row#0[7913] dba: 42174980x405a9a col 0; len 2; (2): c1 4b col 1; TERM row#1[7948] dba: 42172140x40597e col 0; len 3; (3): c2 02 5c col 1; len 2; (2): c1 0b col 2; TERM row#2[7896] dba: 42175000x405a9c 还有报错进程的信息 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 pid8, 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_event0 bits2 holding (efd23) 94bf6f88 Child cache buffers chains level1 child#7788 Location from where latch is held: kcbzib: finish free bufs: Context saved from call: 0 statebusy(exclusive) (val0x2000000000000008) 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: oracleseconary (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) flags2 fib0x93d97478 incno0 pending i/o cnt0 fname/home/ora10g/asia/asia/temp01.dbf fno201 lblksz8192 fsiz2560 (FOB) flags2 fib0x93d970c8 incno0 pending i/o cnt0 fname/home/ora10g/asia/asia/users01.dbf fno4 lblksz8192 fsiz640 (FOB) flags2 fib0x93d96d18 incno0 pending i/o cnt0 fname/home/ora10g/asia/asia/sysaux01.dbf fno3 lblksz8192 fsiz17920 (FOB) flags2 fib0x93d96968 incno0 pending i/o cnt0 fname/home/ora10g/asia/asia/undotbs01.dbf fno2 lblksz8192 fsiz40960 (FOB) flags2 fib0x93d965a0 incno5 pending i/o cnt0 fname/home/ora10g/asia/asia/system01.dbf fno1 lblksz8192 fsiz48640 ---------------------------------------- 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: handle9568bc38 mtx0x9568bd68(0) lct27 pct27 cdp0 nameSYS.CCOL$ hash8e1b2f8107c168f1eeb5a1594db547a0 timestamp09-12-2015 10:28:45 namespaceTABL flagsPKP/KGHP/TIM/KEP/SML/BSO/[02900002] kkkk-dddd-llll0101-0301-0309 lockN pin0 latch#3 hpc000c hlc000c lwt0x9568bce0[0x9568bce0,0x9568bce0] ltm0x9568bcf0[0x9568bcf0,0x9568bcf0] pwt0x9568bca8[0x9568bca8,0x9568bca8] ptm0x9568bcb8[0x9568bcb8,0x9568bcb8] ref0x9568bd10[0x9568bd10,0x9568bd10] lnd0x9568bd28[0x95661f98,0x9569add8] LIBRARY OBJECT: object903b8690 typeTABL flagsEXS/LOC[0005] pflags[0000] statusVALD load0 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/
