Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231

9876

Checked for relevance on 17-Dec-1013

*****************

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