Event: 10231
Text: skip corrupted blocks on _table_scans_
-------------------------------------------------------------------------------
Cause:
Action: Corrupt blocks are skipped in table scans, and listed in trace files.
Explanation:
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by ST support.
8.1 onwards:
~~~~~~~~~~~~
The "7.2 onwards" notes below still apply but in Oracle8i
there is a PL/SQL <Package:DBMS_REPAIR> which can be used
to check corrupt blocks. See <DocIndex:DBMS_REPAIR>.
It is possible to simulate 10231 on a table using
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('schema','table').
The SKIP_CORRUPT column of DBA_TABLES shows tables which
have been marked to allow skipping of corrupt blocks.
7.2 onwards:
~~~~~~~~~~~~
Event 10231 causes SOFTWARE CORRUPT or MEDIA corrupt blocks
to be skipped on FULL TABLE SCANS only. (E.g: on export)
Software corrupt blocks are defined below. Media corrupt
blocks are Oracle blocks where the header field information
is not what was expected. These can now be skipped with
the 10231 event.
Before 7.2:
~~~~~~~~~~~
Event 10231 causes SOFTWARE CORRUPT blocks to be skipped on
FULL TABLE SCANS only. (E.g: on export).
A 'software corrupt' block is a block that has a SEQ number of ZERO.
This raises an ORA-1578 error.
NB: Blocks may be internally corrupt and still cause problems or
raise ORA-1578. If a block is physically corrupt and the SEQ
is not set to ZERO, you cannot use 10231 to skip it. You have
to try to scan around the block instead.
To manually corrupt a block and cause it to be skipped you
must: Set SEQ to ZERO.
Set the INCSEQ at the end of the block to match.
You can set event numbers 10210, 10211, and 10212 to check blocks
at the data level and mark them software corrupt if they are found
to be corrupt. You CANNOT use these events to mark a physically
corrupt block as software corrupt because the block never reaches
the data layer.
When a block is skipped, any data in the block is totally ignored.
Usage: Event="10231 trace name context forever, level 10".
This should be removed from the instance parameters immediately after
it has been used.
Alternatively it can be set at session level:
alter session set events '10231 trace name context forever, level 10'
@Articles:
@ Customer FAX Explaining How to Use Event 10231 Note 33405.1
@ Data, Index & Cluster Block <Event:10210><Event:10211><Event:10212>
@ Skip Blocks on Index Range Scan <Event:10233>
@ Physical Oracle Data Block Layout Note 33242.1
© 2006, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Setting an Oracle event:The structure of the trace syntax
- How to Perform a Health Check on the Database [ID 122669.1]
- Script to Collect RAC Diagnostic Information (racdiag.sql)
- EVENT: 10015 "Write Trace for Undo Segment Recovery"
- Data Block Cache Header Format Changes (Oracle8 Physical layout)
- DBMS_REPAIR example Internal Only
Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231
*****************
*** 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.
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 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 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;
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;
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.
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.
Using The Export Utility To Check For Database Corruption
PURPOSE
——-
This document provides information how the Export utility can be used
to check the consistency of a database. In addition, the document provides
examples how to export the database without creating an export dump file
(i.e. writing to a null device).
SCOPE & APPLICATION
——————-
The article is intended for Database Administrators who need to check the
consistency of the data in an Oracle7, Oracle8, Oracle8i, or Oracle9i database.
The article does not deal with how to resolve the data corruption (see other
articles mentioned in the “Related Documents”).
USING THE EXPORT UTILITY TO CHECK FOR DATABASE CORRUPTION
=========================================================
INTRODUCTION
————
1. When an Oracle data block is corrupted, it can be:
a. Physical corrupt
—————-
This can be defined as a damage to the internal data structure in a way
that prevents Oracle from being able to read the data in the corrupted
blocks. Physical corruption can be due to a hardware or software and can
occur in MEMORY or on DISK. The information in the block doesn’t make
any sense after the read from disk (also called: media corrupt).
b. Logical Corrupt
—————
The Oracle RDBMS is able to find the data, but the data values are
incorrect: the block is marked as corrupt after detecting an
inconsistency (also called: soft corrupt).
2. A full database export can be used to check the consistency of a database.
This full database export implies a full table scan on all the tables
to retrieve the data. This is a good method for checking the logical
consistency and detecting both for physical corruption (in used blocks)
and for logical corruption (data-dictionary issues).
3. When any inconsistency is found, the following errors are reported
(example):
EXP-00056 Oracle error 1578 encountered‘
ORA-01578 Oracle data block corrupted (file # 9, block # 12731)”
ORA-01110 data file 9: ‘
The data block indicated was corrupt, mostly due to software errors.
See the following note how to resolve this corruption:
Note:28814.1 “Handling Oracle Block Corruptions in Oracle7/8/8i/9i”
LIMITATIONS
———–
1. Export only reads:
– user data below the high water mark;
– parts of the data dictionary, while looking up information concerning
the objects being exported.
2. Only a Conventional path export is able to detect logical corrupt blocks.
When using a Direct path export , the data is read from disk directly
into the export session’s program global area (PGA): the rows are
transferred directly to the Export session’s private buffer. This means
that the SQL command-processing layer (evaluation buffer) is bypassed,
because the data is already in the format that Export expects.
Therefore, do not specify the export parameter DIRECT=Y but use DIRECT=N
which is also the default value for the DIRECT parameter.
3. Export does not detect all corruptions:
– does not detect disk corruptions above the high water mark;
– does not detect corruptions in indexes, or in free or temporary extents;
– does not detect all corruptions in the data dictionary (the export
utility does not read all of the data dictionary, so there could still
be undetected corruptions in the SYSTEM tablespace).
Note 1: The High Water Mark is the boundary between used and unused space in
a segment. As requests for new free blocks that cannot be satisfied
by existing free lists are received, the block to which the high water
mark points becomes a used block, and the high water mark is advanced
to the next block. In other words, the segment space to the left of
the high water mark is used, and the space to the right of it is
unused. When a COMMIT runs, the high-water mark is updated to the
new value, making the data visible to users.
Note 2: See the following article for an overview of other available database
block checking features:
Note:32969.1 “Database Block Checking Features”
EXAMPLE
——-
1. You do not have to create a dump file when you perform an export if all you
want is to check the consistency of the data. On UNIX systems, use
/dev/null as the file name, for OpenVMS use NL:, and for Windows use NUL.
When you do this, the export utility reads all the data but writes it to
the null device.
a. Example for Windows platforms
—————————–
exp system/manager full=y log=exp_db_chk.log file=nul
b. Example for UNIX platforms
————————–
exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g
The VOLSIZE parameter is needed on the UNIX platform in order to prevent for no restriction) >
the following question (after starting the Export utility) when exporting
to /dev/null: Volume size (
c. Example for VMS platform
————————
exp system/manager full=y log=exp_db_chk.log file=nl:
Extracting Data from a Corrupt Table using ROWID or Index Scans in Oracle7
Salvaging data from a TABLE in Oracle7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This article is an extension to Note:28814.1 which discusses the
options available for handling block corruptions in Oracle.
Here we describe how to retrieve data from a table which contains a
corrupt block (or blocks) in Oracle7 releases.
*** This article does NOT cover Oracle8 – see Note:61685.1 instead ***
In order to use the steps here you need to have a list of all corrupt
File/Blocks in the table. Ideally you should have the following
information to hand:
– Original Error in this article in this article
– File# - Referred to as
– Block# – Referred to as
– Object Type – eg: TABLE or CLUSTER
– Object Owner.Name
– Related Objects – eg: Indexes, Foreign key constraints etc…
If not then see Note:28814.1 for details of how to get this information.
===========
Options
===========
There are several ways to extract data from a corrupt table:
(1) Easiest and first choice:
– From Oracle 7.2 onwards there is an Oracle event which
can be used to SKIP over corrupt blocks.
This is by far the simplest option to extract table data
and is discussed in Note:33405.1. Note that this event
can only be used if the corrupt block reports ORA-1578.
(2) Second choice (which is more complicated):
– From Oracle 7.1 onwards you can use a ROWID range scan.
This method is discussed in this article.
(3) Third choice (which can be time consuming):
– If there is a primary key you can select table data via
this index.
It may also be possible to select some of data via any
other index. This can be slow and time consuming.
This method is also discussed in this article but it not
normally needed in current Oracle releases.
If (1) to (3) above fail or are not possible then there is a 4th option:
– There are various salvage programs / PLSQL scripts which
can be used to salvage data from a table. These can take
longer to set up and use than the above methods but can
often cope with various kinds of corruption.
These are listed in Note:28814.1 and are not covered here.
Before looking at extracting the data you need to understand what a
‘ROWID’ is. This is discussed first, followed by the following topics:
==================
Oracle7 ROWIDs
==================
*** Note that the information here ONLY applies to Oracle7.
Each row in a table in the database can be uniquely referenced by
a ‘ROWID’ psuedo-column. A rowid is a hexadecimal representation of
of a rows location in the database thus:
Eg: If we issued SELECT EMPNO, ROWID FROM EMP we would get something
like:
EMPNO ROWID
—– —–
100 00000003.0000.000A
101 00000003.0001.000A
… etc
Hence these 2 rows are in file 0xA which is 10 decimal, block 3.
For any bad block we can make a template ROWID by converting and
from the error into hexadecimal and using the above ROWID format:
Eg: For file 11, block 22 we would have:
Hence the rowid’s for rows in this block could range from
00000016.0000.000B through to 00000016.7FFF.000B
as the maximum row-slot number is 7FFF hex.
=====================
Rowid Range Scans
=====================
In Oracle 7.1 onwards you can select data from a base table
using a ROWID range scan hint. This allows you to select
AROUND a corrupt block.
The syntax of the command is simply:
SELECT /*+ ROWID(tablename) */ column list
FROM tablename
WHERE rowid …
The WHERE clause has to do a ROWID range comparison such as:
WHERE rowid BETWEEN ‘low-rowid’ and ‘high-rowid’;
We can use this type of statement several times to select data around
the bad blocks and insert it into a SALVAGE table. Note that when
constructing a “WHERE” clause we must not reference a ROWID in the
corrupt block directly but use the ROWIDs either side of the corrupt
block.
Eg:
SQL> SELECT count(*) FROM bigemp;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 8, block # 8147)
ORA-01110: data file 8: ‘/oracle/m4/v714/instance/dbs/usr3714.dbf’
Corrupt File ID: 8 == 8 Hex: 8147 == 1FD3 Hex
Corrupt Block ID
Hence:
First ROWID in the corrupt block: 00001FD3.0000.0008
Last possible ROWID before this block: 00001FD2.7FFF.0008
First ROWID after this block: 00001FD4.0000.0008
(The last rowid should always be bno.7FFF.fno)
So we can:
Create a copy table to put the data in:
CREATE TABLE salvage AS SELECT * FROM bigemp WHERE 1 = 2;
Insert data from BEFORE the corrupt block:
INSERT INTO salvage
SELECT /*+ ROWID(bigemp) */ * FROM bigemp
WHERE rowid < = '00001FD2.7FFF.0008';
Insert data from AFTER the corrupt block:
INSERT INTO salvage
SELECT /*+ ROWID(bigemp) */ * FROM bigemp
WHERE rowid >= ’00001FD4.0000.0008′;
Note we are using < = and >= with rowid’s either side of the corrupt
block. If you include a ROWID in the corrupt block then an error
will occur.
If a series of blocks are corrupted, you need to modify the rowids in
your range comparison appropriately. If there are blocks corrupted
in different parts of the table, you will need several insert
statements.
==============================================
Pulling Data Out Of Tables Through Indexes
==============================================
This option is really for use in versions before Oracle 7.1 where ROWID
range scans do not exist. Suppose you have the same corrupted table
BIGEMP with columns EMPNO, ENAME and DEPTNO and that you have a
unique index on the EMPNO column. Assuming the index is also not
corrupt and that we are looking at a not null column we can select
data from the table via the index as the index holds the ROWIDs for
each row in the table.
Eg: SELECT rowid, empno FROM bigemp WHERE empno>=0;
ROWID EMPNO
—————— ———-
00001FC3.000C.0008 177
00001FC3.000D.0008 178
00001FC3.0010.0008 181
00001FC3.0011.0008 182
…
We use EMPNO>=0 to enable the index. This does not error as the
above query is satisfied wholly from the INDEX.
Note that if the index were on a character column we would change
the above where clause to something like WHERE EMPNO > ”;
As the index holds the ROWID we can use a filter in the WHERE clause.%.‘
to identify indexed column values that reside in the bad block. We use
a where clause of the form:
ROWIDTOCHAR( ROWID ) LIKE ‘
Eg: Using the previous values for the corrupt block and file:
SQL> SELECT empno from BIGEMP
WHERE EMPNO >= 0
AND ROWIDTOCHAR(ROWID) LIKE ’00001FD3.%.0008′;
EMPNO
———-
821
822
….
861
If there are only a few rows or they are contiguous values you can
select the data out using a clause like:
WHERE empno <821
Eg: Create a duplicate table:
CREATE TABLE TEMP AS SELECT * FROM BIGEMP WHERE 1 = 2;
and then insert into that table around the corruption:
INSERT INTO TEMP SELECT * FROM BIGEMP WHERE EMPNO < 821;
INSERT INTO TEMP SELECT * FROM BIGEMP WHERE EMPNO > 861;
If there are a lot of rows in the corrupt block OR the indexed values
are not contiguous you need to use a more complex method of extraction:
Eg: To get a list of key values that we want to extract:
CREATE TABLE pk_table AS
SELECT empno FROM bigemp
WHERE empno >= 0
AND ROWIDTOCHAR(ROWID) NOT LIKE ’00001FD3.%.0008′;
Now build the destination table:
CREATE TABLE temp AS SELECT * FROM bigemp WHERE 1 = 2;
And insert the data:
INSERT INTO temp SELECT bigemp.* FROM bigemp, pk_table
WHERE bigemp.empno >= 0
AND bigemp.empno = pk_table.empno;
=====================
Possible Problems
=====================
Chained Rows:
It is possible for a row to be either “chained” or “migrated”.
Such a row spans more than one Oracle block. The ‘ROWID’ is the
location of the first portion (or ‘head’) of the row. If there is
a corruption on a block containing a piece of a chained row other
than its ‘head’ block then you will need to adjust the above
procedures to exclude not only the corrupt block but also any rows
which have part of their row-piece in the corrupted block.
Such rows can be identified by selecting ‘ROWID , TABLE.*’ from the
table using a relevant WHERE clause to avoid the known corrupt block.
If you get an error then the last row returned by this query should
show the ROWID before the row which has part of its contents in the
corrupt block. You should adjust the relevant query above to avoid
this “chained” or “migrated” row also.
It may require several attempts at building ‘salvage’ queries before
it is possible to extract all available data without error.
Long Columns:
LONG columns have restrictions in that only certain SQL operations
are allowed on them. You can use the same methods as described
above to extract the data but you may need to use the SQLPLUS
‘COPY’ command to actually copy the data. Note that sqlplus
COPY has a port specific limit on the maximum size of LONG you can
copy. Refer to the SQLPLUS User Guide and your port specific
documentation to determine if this is feasible or not.
===========================
What data have I lost ?
===========================
If you have an index on the corrupt table you can often determine
something about the data you have lost using the ‘Index Scan’ method
described above.
Suppose you have the same corrupted table BIGEMP with columns EMPNO,
ENAME and DEPTNO and that you have a unique index on the EMPNO column.
You can select data from the index for the corrupt block thus:
SQL> SELECT empno from BIGEMP
WHERE EMPNO >= 0
AND ROWIDTOCHAR(ROWID) LIKE ’00001FD3.%.0008′;
Where “EMPNO >= 0″ forces an index range scan
and “ROWIDTOCHAR(ROWID) LIKE ’00001FD3.%.0008′” restricts the results
to those entries which would fall in the bad block.
You can use this approach on all indexes on the table to get as much
information as possible about the missing data.