EVENT: 10231 "skip corrupted blocks on _table_scans_"

Comments

3 responses to “EVENT: 10231 "skip corrupted blocks on _table_scans_"”

1. 

   admin

   August 21, 2010

   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.

   Reply
2. 

   admin

   August 21, 2010

   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:

   Reply
3. 

   admin

   August 21, 2010

   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.

   Reply