In this Document
|Overlapped ASM extent.|
|Identify the affected object|
|Get the SQL statement that is causing the error.|
|Identify the affected object with ANALYZE.|
|Identify the object with event 10236.|
|Identify the object with event 10200.|
|Identify object affected by Block Corruption.|
|Identify object affected by Overlapped Extents.|
|@Using the call stack trace arguments to identify the block producing the ORA-8103.|
|Fix Block Corruption.|
|Flush the buffer cache.|
|OPTION 1 – No backup|
|OPTION 2 – No backup|
|Temporary Segment Corruption|
|Fix Overlapped Extents.|
Oracle Server – Enterprise Edition – Version 220.127.116.11 to 18.104.22.168 [Release 8.1.5 to 11.2]Information in this document applies to any platform.
This article provides information about error ORA-8103 and possible actions.
This note is intended for general audience as initial starting point for beginning diagnosis of ORA-8103.
ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement.
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:
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:
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.
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 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 :
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:
In ASM alert log the next message is registered:
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.
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:
Alternatively identify if a full table scan is producing the error.
For an INDEX run:
Identify the object with event 10236.
In 22.214.171.124 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:
Open the trace file and if function ktrget is in the call stack trace, then the trace file may have:
Meaning that the error is produced in rdba=0x32810e06 . Convert that number to decimal and get the relative_fno, block#:
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:
In the trace file generated locate last information about:
Trace output example:
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:
Use sections “DBVerify – Identify Datafile Block Corruptions” or “RMAN – Identify Datafile Block Corruptions” inNote 836658.1andNote 819533.1to identify the affected object.
Identify object affected by Overlapped Extents.
If overlapped extents in Locally Managed Tablespace is identified, useNote 887263.1
@Using the call stack trace arguments to identify the block producing the ORA-8103.
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):
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:
Drop and recreate the index.
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:
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
- 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:
- 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.:
OPTION 2 – No backup
If the table does not have an index, use script. provided inNote 422547.1Another solution is to determine if dbms_repair can be used to skip these blocks or if procedure described inNote 61685.1can 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:
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-739681/，如需转载，请注明出处，否则将追究法律责任。