# 一次ORA-8103 :object no longer exists 问题处理

---------------------------------------------------------------------------------

## APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 12.2.0.1 [Release 8.1.5 to 12.2]
Information in this document applies to any platform.

## SCOPE

This note is intended for general audience as initial starting point for beginning diagnosis of ORA-8103.

## DETAILS

Error: ORA 8103
Text: object no longer exists
-------------------------------------------------------------------------------
Cause: The object has been deleted by another user since the operation began.
Action: Remove references to the object.

### Description

ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement.

### 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 concurrently changing for the involved object while the affected SQL statement is executed.

These two causes might be due to an expected behavior or other problems.  Details are:

#### Expected behavior.

Tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.
Look if dba_objects.data_object_id is changing for the affected object while queries are being executed.
data_object_id is changed by DDL statements like:

truncate table
alter index .. rebuild
alter table .. move
alter table .. exchange partition
alter table .. split partition
etc.

For a truncate look for column TRUNCATED in DBA_TAB_MODIFICATIONS. Note that it indicates whether the table has been truncated since the last analyze.  See documentation.

#### Overlapped ASM extent.

If using ASM: overlapped ASM extents is when 2 different files and 2 different extents use the same Allocation Unit (AU) in the same ASM disk.

From the ASM instance run:

SQL> alter diskgroup <name> check all norepair;

In ASM alert log the next message is registered:

ERROR: file +data1.3551.721223631: F3551 PX3819 => D254 A78 => F3564 PX337: fnum mismatch

Meaning that ASM file number 3551 Physical Extent 3819 is using Allocation unit 78 in Disk 254 and at the same time ASM File Number 3564 has Physical Extent pointing to the same Allocation unit.

#### Oracle defect.

It can be an Oracle bug.  See section "known issues" below.

### Identify the affected object

Sometimes the SQL statement producing the ORA-8103 involves several tables and the affected object can be an index.  Follow the next procedures to identify the affected object:

#### Get the SQL statement that is causing the error.

Reproduce the error from SQL*Plus if possible by running the affected SQL statement.

#### Identify the affected object in trace file section "dump suspect buffer".

In some cases when a trace file is generated, either by default with no error printed or by enabling the 8103 errorstack event mentioned later, there is a section "dump suspect buffer" that can be used to extract the object_id of the affected segment and query dba_objects to identify it. Example from a trace file:

<Oracle Function>: dump suspect buffer ...
buffer tsn: .....
....
BH (0x0000000449F392B8) file#: 51 rdba: 0x0ccb247d (51/730237) class: 1 ba: 0x0000000448ADE000
set: 46 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 1 obj: 116770 objn: 76458 tsn: 14 afn: 51 ..
To identify the affected segment execute: select * from dba_objects where object_id = 76458;

#### Identify the affected object with ANALYZE.

Is it a TABLE or an INDEX causing the error?.  Exectute the analyze command and if there is an error, open the trace file as it may contain the suspect buffer:
For a TABLE run:
analyze table <table_name> validate structure;

Alternatively  identify if a full table scan is producing the error.
For an INDEX run:
analyze index <index_name> validate structure;

#### Identify the object with event 10236.

In 9.2.0.8 or above if ktrget is in the call stack trace for an ORA-8103, setting Event 10236 will include the block number in the trace file causing the error:

alter session set events '8103 trace name errorstack level 3';
alter session set events '10236 trace name context forever, level 1';
alter session set max_dump_file_size='UNLIMITED';
alter session set tracefile_identifier='ORA8103';

run the query that produces the error ORA-8103 and identify the trace with the form of <sid>_ora_<pid>_ORA8103.trc

Open the trace file, it may have:

KTRVAC: path typ=22, rdba=32810e06
or
KTRVAC: path typ=0, rdba=32810e06
KTRGET: pobj=0, dsobj=882063, dsd=32810e06, sd=32010005

Meaning that the error is produced in rdba=0x32810e06
Convert that number to decimal and get the relative_fno, block#:

from dual;

RELATIVE_FNO BLOCK#
------------ ----------
202 69126

Then use Note 819533.1 to identify the object; section "Identify the Corrupt Object".
In some cases the event 10236 may produce the next information, meaning that the error is produced by rdba=0x1965ee16:
KTRVAC: obj=774488, seg. obj=798088, seg rdba=18454f9b
KTRGET: pobj=774488, dsobj=798088, dsd=1965ee16, sd=18454f9b

#### Identify the object with event 10200.

Event 10200 can be used to identify the last accessed block when this error is produced. event trace_buffer_on can be used to generate a smaller trace file.  Errorstack and event 10236 can be added to get a more complete trace file:

alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name trace_buffer_on level 1048576';
alter session set events '10200 trace name context forever, level 1';
alter session set events '8103 trace name errorstack level 3';
alter session set events '10236 trace name context forever, level 1';
alter session set tracefile_identifier='ORA8103';

run the query that produces the error ORA-8103

alter session set events 'immediate trace name trace_buffer_off';
exit

Identify the trace with the form of <sid>_ora_<pid>_ORA8103.trc

In the trace file generated locate last information about:

"Consistent read started for block <XXX>: <YYY>"

where XXX is the tablespace number and YYY is the rdba for that block.  Normally this is the affected block.

Trace output example:

*** 2004-01-28 09:40:16.000
*** SESSION ID:(9.5) 2004-01-28 09:40:16.000
Consistent read started for block 0 : 00405302
env: (scn: 0x0000.001a9434 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 0sch: scn: 0x0000.00000000)
CR exa ret 9 on: 01FEE950 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0
Consistent read finished for block 0 : 405302
Consistent read finished for block 0 : 405302
Consistent read started for block 0 : 00405303

The error is produced while reading rdba=0x00405303 (File=1,Block=21251) as there is NOT a message like:
"Consistent read finished for block 0 : 00405303"

Then use Note 819533.1 to identify the object; section "Identify the Corrupt Object".

#### Identify object affected by Block Corruption.

Use DBVerify or RMAN to identify corrupted blocks associated to the datafiles where the object is stored.

A common corruption is when the block has been zeroed out (block type is also zero). DBVerify reports it as:

Completely zero block found during dbv

Use sections "DBVerify - Identify Datafile Block Corruptions" or "RMAN - Identify Datafile Block Corruptions" in Note 836658.1 and Note 819533.1 to identify the affected object.

#### Identify object affected by Overlapped Extents.

If overlapped extents in Locally Managed Tablespace is identified, use Note 887263.1 to identify the segments involved.

### ORA-8103 in XML objects

XDB status may be INVALID in DBA_registry or it could be due to XDB metadata corruption. Check Doc ID 1553078.1 for reference; although it mentions an ORA-1410 the treatment is the same.   Contact Oracle XDB support for futher information.

### Observations

• Setting event "8103 trace name errorstack level .." will report all the ORA-8103 errors produced in the database; it will also include those cases where the error is internally handled by the Oracle rdbms. This may give the false impression of an issue; there are cases where the error is handled internally by Oracle and the server process will not report the error to the user even though there is a trace reported for the ORA-8103.  If the error is not reported to the application then the error should not be a concern.

### Solution

#### Fix Block Corruption.

If error ORA-8103 is constantly reproduced by ANALYZE and if it has been determined that it is not the expected behavior, then it means that there is a possible block corruption. The possible solutions are:

#### Flush the buffer cache.

It might be a corruption only in the SGA memory (Buffer cache):

alter session set events 'immediate trace name flush_cache level 1';

In a RAC system, flushing the buffer cache may be needed in the additional rac instances.

If error persists, continue with the next steps:

#### Flush the shared pool.

In some cases the problem can be caused by an cursor invalidation problem for which flushing the shared_pool can be a workaround:

alter system flush shared_pool;

If error still persists, then it could be a permanent problem present on disk.  Continue with next steps:

#### Index Corruption.

Drop and recreate the index.

#### Table Corruption.

1. Consider to apply media recovery if it is determined that the problem was caused by a corrupt block (Like zeroed out blocks). RMAN BLOCKRECOVER can be used to repair a zeroed out block.

2. TRUNCATE or DROP it and reload the data from export dump. If the the same error is produced by dropping/truncating a PARTITION, then consider to EXCHANGE:

alter table <owner>.<table_name> exchange partition <partition_name> with table <owner>.<new_table>;

Note: <new_table> has to have the same structure as <table_name>.

3. If recover from a backup is not an option, table data can be saved skipping the blocks that are causing the ORA-8103 error:

#### OPTION 1 - No backup

If the table has an index use the plsql script provided in Note  1527738.1

#### OPTION 2 - No backup

If the table does not have an index, use script provided in Note 422547.1

Another solution is to determine if dbms_repair can be used to skip these blocks or if procedure described in Note 61685.1 can be used to skip corrupted blocks using rowid scans.

#### Temporary Segment Corruption

If it is identified that the ORA-8103 is caused by a temporary segment, use dbms_space_admin to drop the temporary segment and rebuild the tablespace bitmap:

sqlplus / as sysdba

#### Fix Overlapped Extents.

If the error is caused by Overlapped extents in a LOCALLY MANAGED Tablespace (LMT):
• Drop one of the affected objects. If the segment is converted to temporary then use dbms_space_admin.segment_corrupt and dbms_space_admin.segment_drop to remove the segment.
• Make sure that there are not more overlap extents by running dbms_space_admin.assm_tablespace_verify or dbms_space_admin.tablespace_verify
• Fix the overlap inconsistency by running dbms_space_admin.tablespace_rebuild_bitmaps.   Note that it will not fix the current affected blocks producing ORA-8103.

For a DICTIONARY MANAGED tablespace determine if the database has to be recreated.

---------------------------------------------------------------------------------

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120