ORA-08103: 現在、指定したオブジェクトは存在しません エラを診断する

プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com

 

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. できればそのテーブルあるいは関するパーティションを移して、このトラブルを避けてみてください
   5. できればそのテーブルあるいはパーティションをMSSMテーブルスペースに移して、このトラブルを避けられる

execute dbms_space_admin.tablespace_verify(‘&tablespace_name’)
oradebug setmypid
oradebug tracefile_name

 

execute dbms_space_admin.assm_tablespace_verify(‘&tablespace_name’,dbms_space_admin.TS_VERIFY_BITMAPS)
oradebug setmypid
oradebug tracefile_name

 

[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