*****************
*** IMPORTANT ***
*****************
This note is an extension to article Note:28814.1 about handling
block corruption errors where the block wrapper of a datablock indicates
that the block is bad. (Typically for ORA-1578 errors).
The details here will NOT work if only the block internals are
corrupt (eg: for ORA-600 or other errors). In this case then
from Oracle8i onwards it is possible to use DBMS_REPAIR to mark
problem blocks as soft corrupt such that they will then signal
ORA-1578 when accessed. See the 10.2 documentation for details
of using DBMS_REPAIR.CHECK_OBJECT / FIX_CORRUPT_BLOCKS.
Please read Note:28814.1 before reading this note.
This method only applies to heap-organized tables.
For Index-organized tables (IOT) refer to Note 794772.1 - EVENT 43810 "check-and-skip corrupt blocks in index scans" including IOT's
Introduction
~~~~~~~~~~~~
This short article explains how to skip corrupt blocks on an object
either using the SKIP_CORRUPT table flag (available from Oracle8i
onwards) or the special Oracle event number 10231 which is available
in Oracle releases 7 through 8.1 inclusive.
The information here explains how to use these options.
Before proceeding you should:
a) Be certain that the corrupt block is on a USER table.
(ie: not a data dictionary table)
b) Have contacted Oracle Support Services and been advised to
use event 10231 or the SKIP_CORRUPT flag.
c) Have decided how you are to recreate the table.
Eg: Export , and disk space is available etc..
d) You have scheduled down-time to attempt the salvage
operation
OR
have restored a copy of the problem database elsewhere
in order to perform the data extraction on the "copy".
e) Have a backup of the database.
f) Have the SQL to rebuild the problem table, its indexes
constraints, triggers, grants etc...
This SQL should include relevant storage clauses.
What is event 10231 ?
~~~~~~~~~~~~~~~~~~~~~
This event allows Oracle to skip certain types of corrupted blocks
on full table scans ONLY hence allowing export or "create table as
select" type operations to retrieve rows from the table which are not
in the corrupt block. Data in the corrupt block is lost.
The scope of this event is limited for Oracle versions prior to
Oracle 7.2 as it only allows you to skip 'soft corrupt' blocks.
Most ORA 1578 errors are a result of media corruptions and in such
cases event 10231 is useless.
From Oracle 7.2 onwards the event allows you to skip many forms of
media corrupt blocks in addition to soft corrupt blocks and so is
far more useful. It is still *NOT* guaranteed to work.
Note:28814.1 describes alternatives which can be used if this event
fails.
What is the SKIP_CORRUPT flag ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From Oracle8i onwards the functionality of the 10231 event has been
externalised on a PER-SEGMENT basis such that it is possible to mark
a TABLE to skip over corrupt blocks when possible.
The flag is set or cleared using the DBMS_REPAIR package. DBA_TABLES
has a SKIP_CORRUPT column which indicates if this flag is set for an
object or not.
Setting the event or flag
~~~~~~~~~~~~~~~~~~~~~~~~~
The event can either be set within the session or at database instance
level. If you intend to use a CREATE TABLE AS SELECT or ALTER TABLE <> MOVE then setting
the event in the session may suffice. If you want to EXPORT the table
data then it is best to set the event at instance level, or set the
SKIP_CORRUPT table attribute if on Oracle8i (or higher).
Oracle8i,9i,10g,11g
~~~~~~~~~~~~~~~~~~~
Connect as a SYSDBA user and mark the table as needing to skip
corrupt blocks thus:
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','');
Now you should be able to issue a CREATE TABLE AS SELECT / ALTER TABLE <> MOVE operation
against the corrupt table to extract data from all non-corrupt
blocks, or EXPORT the table.
Eg:
CREATE TABLE salvage_emp
AS SELECT * FROM corrupt_emp;
or
ALTER TABLE <> MOVE
To clear the attribute for a table use:
execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','',
flags=>dbms_repair.noskip_flag);
Note that when a session skips a corrupt block due to SKIP_CORRUPT
being set then a message is written to the trace file (not the
alert log) for each block skipped in the form:
table scan: segment: file# 6 block# 11
skipping corrupt block file# 6 block# 12
Setting the Event in a Session
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connect to Oracle as a user with access to the corrupt table and
issue the command:
ALTER SESSION SET EVENTS
'10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
Now you should be able to issue a CREATE TABLE AS SELECT operation
against the corrupt table to extract data from all non-corrupt
blocks, but an export would still fail as the event is only set
within your current session.
Eg:
CREATE TABLE salvage_emp
AS SELECT * FROM corrupt_emp;
or
ALTER TABLE <> MOVE
Setting the Event at Instance level
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This requires that the event be added to the init$ORACLE_SID.ora file
used to start the instance:
shutdown the database
Edit your init.ora startup configuration file and ADD
a line that reads:
event="10231 trace name context forever, level 10"
Make sure this appears next to any other EVENT= lines in the
init.ora file.
If you are using an spfile please refer to Note:160178.1
'How to set EVENTS in the SPFILE'.
STARTUP
If the instance fails to start check the syntax
of the event parameter matches the above exactly.
Note the comma as it is important.
SHOW PARAMETER EVENT
To check the event has been set in the correct place.
You should see the initial portion of text for the
line in your init.ora file. If not check which
parameter file is being used to start the database.
Select out the data from the table using a full table scan
operation.
Eg: Use a table level export
or create table as select or ALTER TABLE <> MOVE
Export Warning: If the table is very large then some versions of export
may not be able to write more than 2Gb of data to the
export file. See Note:62427.1 for general information
on 2Gb limits in various Oracle releases.
Salvaging data from the corrupt block itself
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SKIP_CORRUPT and event 10231 extract data from good blocks but
skip over corrupt blocks. To extract information from the corrupt
block there are three main options:
- Select column data from any good indexes
This is discussed towards the end of the following 2 articles:
Oracle7 - using ROWID range scans Note:34371.1
Oracle8/8i - using ROWID range scans Note:61685.1
- See if Oracle Support can extract any data from HEX dumps of the
corrupt block.
- It may be possible to salvage some data using Log Miner
Once you have the data extracted
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Once you have the required data extracted either into an export file
or into another table make sure you have a valid database backup before
proceeding. The importance of this cannot be over-emphasised.
Double check you have the SQL to rebuild the object and its indexes
etc..
Double check that you have any diagnostic information if requested by
Oracle support. Once you proceed with dropping the object certain
information is destroyed so it is important to capture it now.
Now you can:
If 10231 was set at instance level:
Remove the 'event' line from the init.ora file
SHUTDOWN and RESTART the database.
SHOW PARAMETER EVENT
Make sure the 10231 event is no longer shown
RENAME or DROP the problem table
If you have space it is advisable to RENAME the
problem table rather than DROP it at this stage.
Recreate the table.
Eg: By importing.
Take special care to get the storage clauses
correct when recreating the table.
Create any indexes, triggers etc.. required
Again take care with storage clauses.
Re-grant any access to the table.
If you RENAMEd the original table you can drop it once
the new table has been tested.
