诊断ORA-08103错误
ORA-08103问题的诊断最好是能生成8103错误的ERROR STACK TRACE, 在TRACE中会记录具体引发8103的对象的OBJ和OBJD,这便于我们定位可能存在corruption的对象。
问题在于往往前台进程遇到ORA-08103错误不会在后台生成TRACE文件,这需要我们手动设置8103 触发ERRORSTACK的EVENTS:
ALTER SYSTEM SET EVENTS ' 8103 TRACE NAME ERRORSTACK LEVEL 3';
解决思路包括:
1. 通过OBJD和DBA定位到具体的表名和块号
2. 有条件的情况下对该表做一个analyze .. validate structure
3. 有条件的情况下对该表所在tablespace做一个 dbms_space_admin.ASSM_TABLESPACE_VERIFY
4. 有条件的情况下move这张表或者相关的分区,尝试绕过该问题
5. 有条件的情况下降该表或分区移动到MSSM表空间上,绕过该问题
[oracle@nas ~]$ oerr ora 8103 08103, 00000, "object no longer exists" // *Cause: The object has been deleted by another user since the operation // began, or a prior incomplete recovery restored the database to // a point in time during the deletion of the object. // *Action: Delete the object if this is the result of an incomplete // recovery. @ Using the call stack trace arguments to identify the block producing the ORA-8103. @ struct kcbds @ { @ ktid kcbdstid; /* full relative DBA plus object number */ @ .....@ struct ktid /* relative dba + objd */ @ { @ kdbafr dbr_ktid; /* a relative dba */ @ kobjd objd_ktid; /* data object number */ @ kobjn objn_ktid; /* dictionary object number */@ struct kdbafr /* full relative dba */ @ { @ ktsn tsn_kdbafr; 4bytes /* a tablespace number */ @ krdba dba_kdbafr; 4bytes /* a relative dba */ @ }; @ alter session set db_file_multiblock_read_count=1; @ alter session set events '8103 trace name errorstack level 3'; @ kcbgtcr(kcbds *ds,... @ ktecgshx(sdes, ...) @ kcbds *sdes; @ ktecgetsh(cdes, ...) @ kcbds *cdes; @ Example from a trace file with function ktecgshx being called by kteinicnt1: @ kteinicnt1()+796 CALL ktecgshx() FFFFFFFF7FFF8F78 ? @ 000000003 ? 000000004 ? @ 0000001BC ? 000000000 ? @ 1007AA000 ? @ Argument/Register addr=0xFFFFFFFF7FFF8F78. @ Dump of memory from 0xFFFFFFFF7FFF8F38 to 0xFFFFFFFF7FFF9078 @ FFFFFFFF7FFF8F30 00000000 00000000 [........] @ FFFFFFFF7FFF8F40 00000000 00000000 FFFFFFFF 00000001 [................] @ FFFFFFFF7FFF8F50 00000000 00000000 00000000 00000000 [................] @ Repeat 1 times @ FFFFFFFF7FFF8F70 00000000 00000000 0000000C 01006402 [..............d.] After increase in load, queries against ASSM table intermittently fail with ORA-8103 when executed in parallel if there are concurrent updates performed on the table. This appears to only manifest when access is in parallel. Cause This is caused by Bug 5637976 ORA-8103 EVEN WITH THE WORKAROUND FROM Bug 3569503 fixed in 11.1g. Concurrent inserts and direct path exports on an ASSM table causes ORA-8103/ORA-1410. This is due to the fact that newly formatted blocks between low and high water mark do not get flushed to disk and query sees old copies from disk. Rediscovery Information: 1. Concurrent inserts and exports on ASSM tables 2. ORA-8103/ORA-1410 3. redo dump shows 'ktspbfredo - Format Pagetable Datablock' for that rdba REDO RECORD - Thread:2 RBA: 0x00045b.001887a1.0028 LEN: 0x008c VLD: 0x01 SCN: 0x0578.6eddf7be SUBSCN: 1 07/19/2012 12:11:00 CHANGE #1 TYP:1 CLS: 4 AFN:370 DBA:0x5ca5f32e OBJ:1638047 SCN:0x0578.6eddf7bd SEQ: 1 OP:13.17 ktsphfredo - Format Pagetable Segment Header StartDBA 0x5ca5f32b nblks: 32 ForceL3 :1 Tsn: 15 objd: 1638047 REDO RECORD - Thread:2 RBA: 0x00045b.001887a5.0198 LEN: 0x008c VLD: 0x01 SCN: 0x0578.6eddf7c7 SUBSCN: 1 07/19/2012 12:11:00 CHANGE #1 TYP:1 CLS: 4 AFN:284 DBA:0x4718cbee OBJ:1638047 SCN:0x0578.6eddf7c2 SEQ: 1 OP:13.17 ktsphfredo - Format Pagetable Segment Header BH (70000039ffb5108) file#: 370 rdba: 0x5ca5f32e (370/2487086) class: 7 ba: 70000039f230000 set: 94 blksize: 32768 bsi: 0 set-flg: 0 pwbcnt: 0 dbwrid: 5 obj: 1638047 objn: 148393 tsn: 15 afn: 370 hash: [700000fde5e6380,700000fde5e6380] lru: [7000005e7fcbdc0,700000b91fb4ce8] lru-flags: hot_buffer ckptq: [NULL] fileq: [NULL] objq: [700000f7c3f8288,70000063cfbac28] st: SCURRENT md: NULL tch: 2 le: 70000069bff76a0 flags: remote_transfer LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] buffer tsn: 15 rdba: 0x5ca5f32e (370/2487086) scn: 0x0578.6eded558 seq: 0x01 flg: 0x00 tail: 0xd5582401 frmt: 0x02 chkval: 0x0000 type: 0x24=PAGETABLE EXTENT MAP BLOCK Hex dump of block: st=0, typ_found=1 EMB Dump: Map Header:: next 0x4718cbee #extents: 1112 obj#: 1638047 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x5ca5f32b length: 32 0x5ceff1eb length: 32 0x5d15360b length: 32 0x5d5ddbcb length: 32 0x5d9d106b length: 32 0x5dc000ab length: 32 0x5e09e1ab length: 32 0x5e4a8c0b length: 32 0x5e80d24b length: 32 0x5ec9a10b length: 32 0x5f009feb length: 32 0x5f40b74b length: 32 0x5f895f2b length: 32 0x5fd254cb length: 32 BH (700000dbcfc0ea8) file#: 284 rdba: 0x4718cbee (284/1625070) class: 7 ba: 700000dbc750000 set: 67 blksize: 32768 bsi: 0 set-flg: 0 pwbcnt: 0 dbwrid: 2 obj: 1638047 objn: 148393 tsn: 15 afn: 284 hash: [700000fdc387588,700000fdc387588] lru: [7000002f1fbcf90,700000a77fcfc30] lru-flags: hot_buffer ckptq: [NULL] fileq: [NULL] objq: [700000fc67dd420,700000453fb1828] st: SCURRENT md: NULL tch: 143 le: 700000665fd8200 flags: remote_transfer LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] buffer tsn: 15 rdba: 0x4718cbee (284/1625070) scn: 0x0578.6ee3867a seq: 0x01 flg: 0x00 tail: 0x867a2401 frmt: 0x02 chkval: 0x0000 type: 0x24=PAGETABLE EXTENT MAP BLOCK Hex dump of block: st=0, typ_found=1 EMB Dump: Map Header:: next 0x00000000 #extents: 1983 obj#: 1638047 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x4718cbeb length: 32 0x475b598b length: 32 0x47989f6b length: 32 0x47d84f2b length: 32 ORA-8103 - objd: 1638108 objn: 1338416 tsn: 15 rdba: 0x4b8bf059 ksedmp: internal or fatal error ORA-08103: object no longer exists Current SQL statement for this session: ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst+001c bl ksedst1 000000001 ? 000000000 ? ksedmp+0290 bl ksedst 104C23090 ? ksddoa+0308 bl _ptrgl ksdpcg+0104 bl ksddoa 110490160 ? 11048ACB8 ? ksdpec+00e8 bl ksdpcg FFFFFFFFFFEEF20 ? 700000010007FE0 ? FFFFFFFFFFEEFF0 ? ksfpec+00a4 bl 03F37234 kgesev+007c bl _ptrgl ksesec0+0048 bl kgesev 000007FE8 ? 104FD1FE0 ? 000000000 ? 000000000 ? FFFFFFFFFFEF410 ? kteinicnt1+0384 bl 01FC3F98 qertbFetch+0288 bl 03F386EC qertqoFetch+0298 bl 01FC3FD8 qerpx_resume+0370 bl 01FC3FD8 qerpxFetch+0e08 bl qerpx_resume 000000000 ? 11055A520 ? rwsfcd+0054 bl _ptrgl insfch+00b4 bl _ptrgl insdrv+042c bl insfch 104C2BAE8 ? 000000000 ? inscovexe+02d8 bl insdrv 1104A81B0 ? insExecStmtExecIniE bl _ptrgl ngine+005c insexe+0318 bl insExecStmtExecIniE 000000000 ? 000000400 ? ngine 11048A818 ? opiexe+2840 bl insexe 1104BF320 ? FFFFFFFFFFF1678 ? opipls+1888 bl opiexe FFFFFFFFFFF29C8 ? FFFFFFFFFFF2AB0 ? FFFFFFFFFFF2968 ? opiodr+0b2c bl _ptrgl rpidrus+01dc bl opiodr 66FFFF47D0 ? 6FFFF4800 ? FFFFFFFFFFF5900 ? A00000000 ? skgmstack+00c8 bl _ptrgl rpidru+0088 bl skgmstack 000000003 ? 000000003 ? 000000002 ? 000000000 ? FFFFFFFFFFF50B0 ? rpiswu2+0368 bl _ptrgl rpidrv+097c bl rpiswu2 70000100553C598 ? 000000000 ? 700000010003520 ? 110566428 ? 110566464 ? 96FFFF5B30 ? 1104C6010 ? 000000000 ? Argument/Register addr=0x0FFFFFFFFFFEF410. Dump of memory from 0x0FFFFFFFFFFEF3D0 to 0x0FFFFFFFFFFEF510 FFFFFFFFFFEF3D0 00000000 00000000 00000001 1048A818 [.............H..] FFFFFFFFFFEF3E0 00000000 00002000 00000001 1019C060 [...... ........`] FFFFFFFFFFEF3F0 0FFFFFFF FFFEF5E0 48220080 00000B9D [........H"......] FFFFFFFFFFEF400 00000000 00000000 00000000 00000000 [................] FFFFFFFFFFEF410 0000000F 4B8BF059 0018FEDC 00146C30 [....K..Y......l0] FFFFFFFFFFEF420 00080003 00007FE8 00000000 100733A8 [..............3.] 00146C30=> 1338416=> ORA-8103 - objd: 1638108 objn: 1338416 kjbhistory[0xbf059.12e0000,(pkey 4294967295.0)(where 1)] *** 2012-07-19 15:05:23.818 GLOBAL CACHE ELEMENT DUMP (address: 70000018cfe95a0): id1: 0xbf059 id2: 0x12e0000 pkey: INVALID block: (302/782425) lock: NC rls: 0x0000 acq: 0x0003 latch: 20 flags: 0xc1 fair: 0 recovery: 0 fpin: 'ktewh25: kteinicnt' bscn: 0x578.6ee51801 bctx: 0 write: 0 scan: 0x0 xflg: 0 xid: 0x0.0.0 lcp: 700000fd843f070 lnk: [700000fd843f090,700000fd843f090] lch: [700000bdbfbb338,700000bdbfbb338] seq: 25664 hist: 7 352 477 329 144:6 384 7 352 477 329 LIST OF BUFFERS LINKED TO THIS GLOBAL CACHE ELEMENT: flg: 0x00080000 state: READING mode: EXCL pin: 'ktewh25: kteinicnt' addr: 700000bdbfbb228 obj: 1638108 cls: SEG HEAD bscn: 0x577.a4f2674f Note= OERR: ORA-8103 "object no longer exists" / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1) ==> Cause ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type=6) was expected but the actual block information is not a data block (Type!=6). ORA-8103 is also caused by an unexpected data_object_id where it is changing for the involved objects while the affected SQL statement is executed. $sqlplus / as sysdba Note: please replace literal '<owner>' with actual owner --------------------< set lines 500 set long 9999 set pages 999 set serveroutput on size 1000000 set feedback off SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title><STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>" spool query_result.html set echo off alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'; SELECT * FROM DBA_TAB_MODIFICATIONS where table_owner = '<owner>' and table_name in ('RAW_BORM','MG_34_FEE_DTL','RAW_BOIS','MG_34_CA_AMT_BK','RAW_BLDVNI'); spool off SET MARKUP HTML OFF set echo on --------------------> 1. run the hcheck script against the database "using note hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g (Doc ID 136697.1) and provide the output to SR. Please do not provide a print screen, but the spool file obtained 2. set event for ORA 8103 to capture the errorstack alter system set events='8103 trace name errorstack, level 3'; 3. wait for the error to reproduce and upload the trace file created for the error
posted on 2010-07-21 21:23 Oracle和MySQL 阅读(1797) 评论(0) 编辑 收藏 举报