EVENT: 10231 "skip corrupted blocks on _table_scans_"

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

Comments

  1. admin says

    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.

  2. admin says

    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
    the following question (after starting the Export utility) when exporting
    to /dev/null: Volume size ( for no restriction) >

    c. Example for VMS platform
    ————————

    exp system/manager full=y log=exp_db_chk.log file=nl:

  3. admin says

    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
    – File# – Referred to as in this article
    – Block# – Referred to as in this article
    – 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:

    Rowid Range Scans
    Pulling Data out through Indexes
    Possible Problems
    What data have I lost ?

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

    .ROW-SLOT.

    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:

    = File 11 == B hex
    = Block 22 == 16 hex

    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
    Corrupt Block ID : 8147 == 1FD3 Hex

    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.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号