In this Document

Applies to:

Purpose

This article provides information about error ORA-8103 and possible actions.

Scope

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

Details

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 changing for the involved objects 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
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.

In 11g parameter enable_ddl_logging can be set to TRUE to print DDL statements in the alert log and identify what DDL's are run that may potentially cause this error.

Block Corruption.

Hardware, IO subsystem or OS problems may cause block corruptions overwriting the Block Type in the block header causing the error ORA-8103.

The block is healthy but it is an "OLD/STALE" block. These kind of corruptions might be caused by LOST IO/LOST WRITE or a bug in external non-oracle tools that migrate file systems while the database is OPEN. In that case the data_object_id for the affected object could have changed but the actual block does not reflect it.

Note that the block may also be temporarily corrupted in the buffer cache (SGA Memory).

Overlapped Extents.

Overlapped Extents means that two or more segments incorrectly use the same block. This is a LMT Bitmap Extent inconsistency or Data Dictionary inconsistency.

To identify overlapped extents run the next checks:

For LMT (Locally Managed Tablespaces) run :

sqlplus / as sysdba

execute dbms_space_admin.tablespace_verify('&tablespace_name')
oradebug setmypid
oradebug tracefile_name

In 10gR2 and forward, for locally managed tablespaces using ASSM (DBA_TABLESPACES.SEGMENT_SPACE_MANAGEMENT=AUTO) run:

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

Note that the session running dbms_space_admin does not say in the screen if an inconsistency is detected. dbms_space_admin will generate a trace file instead. So check if "oradebug tracefile_name" provides a trace file.

For Data Dictionary managed tablespaces run the hcheck script. in:

Note 136697.1 (it checks for overlapped extents through fet$/uet$).

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 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 with ANALYZE.

Is it a TABLE or an INDEX causing the error?:

For a TABLE run:

analyze table validate structure;

Alternatively identify if a full table scan is producing the error.

For an INDEX run:

analyze index 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 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 tracefile_identifier='ORA8103';

run the query that produces the error ORA-8103 and identify the trace with the form. of _ora__ORA8103.trc

Open the trace file and if function ktrget is in the call stack trace, then the trace file may have:

KTRVAC: path typ=22, rdba=32810e06

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

select dbms_utility.data_block_address_file(847318534) relative_fno
,dbms_utility.data_block_address_block(847318534) block#
from dual;

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

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

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 db_file_multiblock_read_count=1;
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 _ora__ORA8103.trc

In the trace file generated locate last information about:

"Consistent read started for block : "

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 <> 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

@ Using the call stack trace arguments to identify the block producing the ORA-8103.

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 still persists it means the the block is invalid also in disk, so continue with the 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 . exchange partition with table .;

Note: has to have the same structure as .

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

REM Create a new table based on the affected one with no rows:

create table
as
select *
from
where 1=2;

REM Create the table to keep track of ROWIDs pointing to affected rows:

create table bad_rows (row_id rowid
,oracle_error_code number);
set serveroutput on

DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

CURSOR c1 IS select /*+ index(tab1) */ rowid
from tab1
where is NOT NULL;

r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into
select /*+ ROWID(A) */
from A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

Notes:

• The idea is to get the rowid's from the index, then get all the columns from the table for each rowid and insert these rows into another table. Using the above "index" hint, will allow the optimizer to choose the most appropriated index to scan the table based on the indexed column.

• Make sure that the the select in the plsql above is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:

set autotrace trace explain
select /*+ index(tab1) */ rowid
from tab1
where is NOT NULL;

• Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it required to change this, adjust the value of rows. e.g.:

rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.

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

execute dbms_space_admin.segment_corrupt('&tablespace_name',&relative_fno,&block_number)
execute dbms_space_admin.segment_drop_corrupt('&tablespace_name',&relative_fno,&block_number)
execute dbms_space_admin.tablespace_rebuild_bitmaps('&tablespace_name')

Fix Overlapped Extents.

If the error is caused by Overlapped extents in a LOCALLY MANAGED Tablespace (LMT), dropping one of the affected objects and rebuilding the tablespace bitmaps (dbms_space_admin.tablespace_rebuild_bitmaps) can fix the overlapped extent problem but will not fix the current affected blocks. For a DICTIONARY MANAGED tablespace determine if the database has to be recreated.

Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB	Bug	Fixed	Description
	13910420	11.2.0.3.BP09, 12.1.0.0	ORA-8103 during insert / update of basicfile LOB in assm segment using space search cache	V11020001 V11020002 V11020003 BITSEG ERROR LOB
	12747437	12.1.0.0	ORA-600 [ktspfmdb:objdchk_kcbnew_3] after purging single consumer queue table	V11020001 V11020002 V11020003 AQ ERROR OERI
	12582839	11.2.0.3, 12.1.0.0	ORA-8103/ORA-600 [3020] on RMAN recovered locally managed tablespace	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 CORR ERROR OERI RMAN SPACELOC
	12321309	12.1.0.0	ORA-600 / ORA-8103 UNUSABLE state of partitioned index is not carried across by TABLESPACE transport using DataPump	V11020001 V11020002 V11020003 CORR/DIC ERROR EXPDP OERI PART TRANSPORT
	11937253	11.2.0.2.6, 11.2.0.2.BP11, 11.2.0.3, 12.1.0.0	A Parallel query fails with ORA-8103 on an Active Dataguard Enviroment.	V11010006 V11010007 V11020001 V11020002 ADG ERROR PQO STANDBY
	11850492	11.2.0.3, 12.1.0.0	ORA-8103 ORA-600 ORA-3113 on temporary tables using INDEX FAST FULL SCAN and DIRECT read	V11020001 V11020002 CORR/LOG DPATH ERROR EXADATA GLOBTEMP OERI WRONGRES
	10385812	11.2.0.3, 12.1.0.0	ORA-1410 or ORA-8103 by queries with DIRECT READ while concurrent DIRECT INSERT	V11020001 V11020002 CORR/FALSE DPATH ERROR EXADATA
	10329146	11.2.0.1.BP10, 11.2.0.2.2, 11.2.0.2.BP03, 11.2.0.2.GIBUNDLE02, 11.2.0.2.GIPSU02, 11.2.0.3, 12.1.0.0	Lost write in ASM with multiple DBWs and a disk is offlined and then onlined	V11010006 V11010007 V11020001 V11020002 ASM CORR/PHY ERROR OERI
+	10209232	11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2.1, 11.2.0.2.BP02, 11.2.0.2.GIBUNDLE01, 11.2.0.3, 12.1.0.0	ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM	V11010006 V11010007 V11020001 V11020002 ASM CORR CORR/LOG CORR/PHY ERROR OERI
	10136415	11.2.0.3, 12.1.0.0	ORA-8103 on Partitioned IOT after partition maintenance	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 CORR/DIC ERROR IOT PART
	9965085	11.2.0.3, 12.1.0.0	ORA-1578 / ORA-8103 Temporary table block corruption / space wastage from PDML	V11020001 V11020002 CORR/PHY ERROR GLOBTEMP PQO SPACEUSE
	9659614	10.2.0.5.3, 11.2.0.2, 11.2.0.3.BP05, 12.1.0.0	Large trace file for ORA-8103	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 V11020003 UNWANTEDTRACE
	9651350	11.2.0.2.2, 11.2.0.2.BP05, 11.2.0.3, 12.1.0.0	Large redo dump and ORA-308 might be raised due to ORA-8103	V11010006 V11010007 V11020001 V11020002 DIAG ERROR PERF UNWANTEDTRACE
	9275027	11.2.0.2, 12.1.0.0	ORA-600 [kcbnew_3] can occur after TRUNCATE / DROP	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 OERI TRUNCATE
	9272086	11.1.0.7.4, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.0	ORA-8103 by a query on DBA_EXTENTS. Trace file with Block type: 0x44=NGLOB: Extent Map	V11010006 V11010007 V11020001 ERROR LOB
	8754670	11.2.0.2, 12.1.0.0	IMP-17 / ORA-8103 transporting a large dictionary managed tablespace	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 ERROR TRANSPORT
	8740993	11.1.0.7.8, 11.2.0.2, 12.1.0.0	ORA-1410 / ORA-8103 on ADG STANDBY during table scan after DROP/TRUNCATE/SHRINK in PRIMARY	V11010006 V11010007 V11020001 ADG ERROR SPACELOC STANDBY TRUNCATE
	8725282	11.2.0.1.BP08, 11.2.0.2, 12.1.0.0	Corruption from cross platform. transport of tablespace with securefile objects	V11010006 V11010007 V11020001 CORR/PHY LOB OERI SECUREFILE TRANSPORT
	8716064	11.2.0.2, 12.1.0.0	Analyze Table Validate Structure fails on ADG standby with several errors	V11010006 V11010007 V11020001 ADG CORR/FALSE ERROR OERI STANDBY
+	8597106	11.2.0.1.BP06, 11.2.0.2, 12.1.0.0	Lost Write in ASM when normal redundancy is used	V11010006 V11010007 V11020001 ASM CORR CORR/PHY ERROR OERI
	8428523	11.2.0.2, 12.1.0.0	Alter Table Rename causes wrong results/ora-8103/hangs on ADG Standby.	V11010006 V11010007 V11020001 ADG ERROR HANG/DB RODB STANDBY WRONGRES
	7710827	11.2.0.2, 12.1.0.0	Index rebuild or Merge partition causes wrong results in concurrent reads instead of ORA-8103	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR PART WRONGRES
	7519406	10.2.0.5.1, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.0	Larger trace than needed for ORA-8103 under kteinicnt1	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 UNWANTEDTRACE
P	12330911	12.1	EXADATA LSI firmware for lost writes	V11010006 V11010007 V11020001 V11020002 V11020003 ASM CORR/LOG CORR/PHY ERROR EXADATA OERI P226
	8876094	11.1.0.7.2, 11.2.0.2	ORA-8103 by DBA_UNDO_EXTENTS or DBMS_SPACE_ADMIN.TABLESPACE_VERIFY on Block type: 0x25	V11010006 V11010007 V11020001 ERROR SMU
	9167831	11.2.0.2	ORA-8103 instead of ORA-1410	V11020001 DIAG ERROR
	7650993	11.1.0.7.1, 11.2.0.1	ORA-8103 in a select at ADG standby database from table stored in ASSM tablespace	V11010006 V11010007 ADG BITSEG ERROR SPACELOC STANDBY TRUNCATE
	7432556	11.1.0.7.1, 11.2.0.1	ORA-8103 by Parallel Query on Partitioned Tables in BIGFILE Tablespaces	V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 BIGFILE ERROR PART PQO
	7390324	11.2.0.1	ANALYZE signals OERI [kcbgtcr_12]/ORA-8103 on bitmap index	V09020008 V10010005 V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 BITMAP ERROR OERI SPACELOC
	7117200	11.2.0.1	ORA-8103 after TSPITR/PLUGIN tablespace from a restored Level 1 Backup	V10020003 V10020004 V10020005 V11010006 V11010007 BADBACKUP CORR/PHY ERROR RMAN TRANSPORT
	8825048	11.1.0.7.3	ORA-308/ORA-27037 when dumping archived log for ORA-8103. Dump when event 10736 level 4 is set	V11010006 V11010007 ENH ERROR
	6337376	11.1.0.7	OERI:kcbgcur_3 / ORA-8103 after truncating a partition table with LOBs	V10020002 V10020003 V10020004 V10020005 V11010006 ERROR LOADER LOB OERI TRUNCATE
	13618170		ORA-8103 for create index online when the fix of bug 10027403 is installed	V10020002 V10020003 V10020004 V10020005 ERROR ONLINE
	9711472	11.1.0.6	ORA-8103 on operations for a partitioned LOB if any different partition is dropped	V10020002 V10020003 V10020004 V10020005 ERROR LOB PART
	5637976	10.2.0.4, 11.1.0.6	ORA-8103/ORA-1410 from concurrent INSERT / export on ASSM tables	V10020002 V10020003 BITSEG ERROR
	5083393	10.2.0.4, 11.1.0.6	DBA_FREE_SPACE FILE_ID / REL_FNO may be wrong	V10010005 V10020002 V10020003 RA103 RA201 RECYCLE REGRESSION WRONGRES
	4592596	10.2.0.4, 11.1.0.6	Corruption (ORA-1410 / ORA-8103) from multi-table insert with direct load	V09020008 V10010005 V10020002 V10020003 CORR/PHY DPATH MTI
	6864586	10.2.0.5	ORA-8103 on partitioned table with a LOB column during analyze table with concurrent add/drop partition.	V10020002 V10020003 V10020004 ERROR LOB PART
	3569503	9.2.0.6, 10.2.0.4	PQ may signal a false ORA-8103 under load	V10010005 V10020002 V10020003 CORR/FALSE ERROR PQO
	3966709	9.2.0.7, 10.1.0.4, 10.2.0.1	Range/object reuse prematurely (ORA-8103)	CORR TRUNCATE
	3868753	9.2.0.7, 10.1.0.5, 10.2.0.1	Concurrent export / INSERT of ASSM segment can fail with ORA-1410 / ORA-8103	BITSEG CORR/FALSE DPATH EXP
+	5523799		Various OERI (eg kcbgtcr_12) using ASSM managed segments - superceded	V09020008 V10010005 BITSEG OERI R9208 RA105 REGRESSION TRUNCATE
P*	6047085		Linux x64-64: SGA corruption / crash following any ORA-7445	V09020008 CRASH DUMP ERROR MEMCOR OERI P226 R9207 REGRESSION
*	3785200	9.2.0.6, 10.1.0.2	Corruption possible in automatic space managed segments	BITSEG CORR/PHY ERROR R9205 REGRESSION
	3083560	9.2.0.5, 10.1.0.2	ORA-1410 / ORA-8103 from direct path export if concurrent DML occurs	DPATH ERROR EXP
	2619867	9.2.0.3, 10.1.0.2	OERI:[KCBGTCR_12] / ORA-8103 / ORA-1410 SELECTing from bitmap managed segment	BITSEG ERROR OERI
	2551000	9.2.0.4, 10.1.0.2	False ORA-1410 / ORA-8103 possible from ANALYZE COMPUTE/ESTIMATE STATISTICS	CORR/FALSE ERROR
	2333731	9.2.0.2	ORA-8103 possible in PQ slave	ERROR PQO
	2105419	9.0.1.3, 9.2.0.1	ORA-8103 possible from PQ on bitmap managed segments with concurrent inserts	BITSEG ERROR PQO
	1998455	8.1.7.3, 9.0.1.3, 9.2.0.1	OERI:KCBGTCR_4 possible from long running DDL if referenced object dropped/truncated	ERROR OERI
	1804299	9.0.1.1, 9.2.0.1	Rollback of Direct load can corrupt BITMAP managed segments / ORA-8103	BITSEG CORR/PHY ERROR
	1698789	9.2.0.1	Wrong results, ORA-1410, ORA-8103, OERI:25012 on SELECT of UNSCOPED REF with ROWID	ERROR OBJECT OERI WRONGRES
	1504967	9.2.0.1	ORA-8103 possible on READ ONLY standby after TRUNCATE on primary	ERROR RECOVERY RODB
	1400739	8.1.7.1, 9.0.1.0	Block corruption/OERI:2023 /ORA-8103 can occur if TRUNCATE is interrupted (Ctrl-C)	CORR/PHY ERROR OERI
	1283521	8.1.7.0	ORA-8103 can occur on TRUNCATED cluster table	CORR/PHY ERROR HCHECK REGRESS78
	589855	7.3.3.6, 7.3.4.1	ORA:1578 or ORA:8103 selecting invalid ROWID	ERROR
P	1053863	8.0.5.2, 8.0.6.2	NCR: ORA-8103 / corrupt read possible using async IO	CORR/PHY ERROR P358

• '*' indicates that an alert exists for that issue.
• '+' indicates a particularly notable issue / bug.
• 'P' indicates a port specific bug.
• Fixed versions use "BPnn" to indicate Exadata bundle nn.
• "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].

References