Data block corruption error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Data block corruption error

  1. #1
    Join Date
    Sep 2001
    Location
    ahmedabad,india
    Posts
    1
    I am working with CMC INDIA ltd. As a Junior DBA. I got a problem of DATA BLOCK CORRUPTION error in one module. how can I solve this?

    Pl. guide me .

    thanks
    kanaiya rami

  2. #2
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    this is the way as documented in metalink

    Introduction
    This article discusses how to handle one or more block corruptions
    on an Oracle datafile and describes the main actions to take to deal
    with them. Please read the complete article before taking any action.

    Note: If the problem is an ORA-1578 on STARTUP then please
    contact your local support center for advice referencing

    - this note is not visible to customers
    but the relevant steps from it can be supplied by an experienced
    support analyst.

    You may be referred to this article from many places for many forms of
    error - it is important that you have the following information for each
    corrupt block:
    An absolute FILE NUMBER of the file containing the corrupt block.
    Referred to as "" in this article.

    The file name of the file containing the corrupt block.
    Referred to as "" in this article.
    ( If you know the FILE NUMBER but not its name then V$DATAFILE
    can be used to get the file name:
    SELECT name FROM v$datafile WHERE file#=;
    If the file number does not appear in V$DATAFILE in Oracle8i
    AND is greater than the DB_FILES parameter
    value then it is probably a TEMPFILE. In this case the filename
    can be found using:
    SELECT name FROM v$tempfile
    WHERE file#= - ;
    )
    The BLOCK NUMBER of the corrupt block in that file.
    Referred to as "" in this article.
    The database block size.
    Referred to as "" in this article.
    ( eg: SHOW PARAMETER db_block_size )

    Eg: For the ORA-1578 error:
    ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
    ORA-01110: data file 22: '/oracle1/oradata/V816/oradata/V816/users01.dbf'
    then:
    is "22" (from the ORA-1110 portion of the error)
    is "12698" (from the "block #" in the ORA-1578)
    is '/oracle1/oradata/V816/oradata/V816/users01.dbf'

    For other errors (ORA-600 , ORA-1498 etc...) the above values should
    either be given to you by Oracle Support, or be given to you from the
    article which covers the relevant error.



    Overview of Steps to handle a Corruption
    There are many possible causes of a block corruption including:

    - Bad IO hardware / firmware
    - OS problems
    - Oracle problems
    - Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions
    (in which case ORA-1578 is expected behaviour - see below)

    The point in time when an Oracle error is raised may be much later than
    when any corruption initially occurred.

    As the root cause is not usually known at the time the corruption is
    encountered, and as in most cases the key requirement is to get up
    and running again, then the steps used tackle corruption problems in
    this article are:

    1) Determine the extent of the corruption problems
    and also determine if the problems are permanent or transient.

    If the problem is widespread or the errors move about
    then focus on identifying the cause first (check hardware
    etc..). This is important as there is no point recovering
    a system if the underlying hardware is faulty.

    2) Replace or move away from any faulty or suspect hardware.

    3) Determine which database objects are affected.

    4) Choose the most appropriate database recovery / data salvage
    option.

    For all steps above it is sensible to collect evidence and
    document exactly what actions are being taken. The 'Evidence>>'
    tags in this article list the information which should be collected
    to assist with identifying the root cause of the problem.


    Corruption due to NOLOGGING or UNRECOVERABLE
    If a NOLOGGING (or UNRECOVERABLE) operation is performed on an
    object and the datafile containing that object is subsequently
    recovered then the data blocks affected by the NOLOGGING operation
    are marked as corrupt and will signal an ORA-1578 error when
    accessed. In Oracle8i an ORA-26040 is also signalled
    ("ORA-26040: Data block was loaded using the NOLOGGING option" )
    which makes the cause fairly obvious, but earlier releases have no
    additional error message. If a block is corrupt due to recovery
    through a NOLOGGING operation then you can use this article from
    Section 3 "Which Objects are Affected ?" onwards but note that:
    (a) Recovery cannot retrieve the NOLOGGING data
    (b) No data is salvagable from inside the block


    (1) Determine the Extent of the Corruption Problem
    Whenever a corruption error occurs note down the FULL error message/s
    and look in the instance alert log and trace files for any associated
    errors. It is important to do this first to assess whether this is
    a single block corruption, an error due to an UNRECOVERABLE operation
    or a more severe issue.

    It is a good idea to scan affected files (and any important files)
    with DBVERIFY to check for other corruptions in order to determine
    the extent of the problem.
    For details of using DBVERIFY see

    Once you have determined a list of corrupt file/block combinations
    then the steps below can be used to help determine what action
    can be taken.

    Evidence>>
    - Record the original error in full, along with details of
    the application which encountered the error.
    - Save an extract from the alert log from a few hours before
    the FIRST recorded problem up to the current point in time.
    - Save any tracefiles mentioned in the alert log.
    - Record any recent OS problems you have encountered.
    - Note if you are using any special features - Eg: ASYNC IO,
    fast write disk options etc..
    - Record your current BACKUP position (Dates, Type etc...)
    - Note if your database is in ARCHIVELOG mode or not
    Eg: Issue "ARCHIVE LOG LIST" in Server Manager.


    (2) Replace or Move Away from Suspect Hardware
    The vast majority of corruption problems are caused by faulty hardware.
    If there is a hardware fault or a suspect component then it is sensible
    to either repair the problem, or make disk space available on a
    separate disk sub-system prior to proceeding with a recovery option.

    IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING)
    OR You have OS level errors against the affected file
    OR The errors are transient and keep moving about
    then there is little point proceeding until the underlying problem
    has been addressed or space is available on alternative disks.
    Get your hardware vendor to check the system over and contact
    Oracle Support with details of all errors.

    You can move datafiles about using the following steps:

    1. Make sure the file to be relocated is either OFFLINE or
    the instance is in the MOUNT state (not open)

    2. Restore (or copy) the datafile to its new location
    eg: /newlocation/myfile.dbf

    3. Tell Oracle the new location of the file.
    eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf'
    TO '/newlocation/myfile.dbf';
    (Note that you cannot RENAME a TEMPFILE - TEMPFILEs should
    be dropped and recreated at the new location)



    (3) Which Objects are Affected ?
    It is best to determine which objects are affected BEFORE making any
    decisions about how to recover - this is because the corruption/s may be
    on object/s which can easily be re-created.
    Eg: For a corruption on a 5 row lookup table it may be far quicker to
    drop and recreate the table than to perform a recovery.

    For each corruption collect the information in the following table.
    The steps to do this are explained below.
    Information to Record for each Corruption Original
    Error Absolute
    File#
    Relative
    File#
    Block#

    Tablespace Segment
    Type Segment
    Owner.Name Related
    Objects Recovery
    Options





















    The notes below will help you fill in this table for each corruption.

    "Original Error"
    This is the error as initially reported.
    Eg: ORA-1578 / ORA-1110 , ORA-600 with all arguments etc..

    "Absolute File#", "Relative File#" and "Block#"
    The File# and Block# should have been given to you either by the
    error, by Oracle Support, or by the steps in an error article which
    directed you to this article.

    In Oracle7: Use the same file number for both the "Absolute File#"
    and the "Relative File#"

    In Oracle8: The absolute and relative file numbers are often the
    same but can differ (especially if the database has
    been migrated from Oracle7). It is important to get
    the correct numbers for and
    or you may end up salvaging the wrong object !!

    An ORA-1578 reports the RELATIVE file number, with the
    ABSOLUTE file number given in the accompanying ORA-1110
    error. For ORA-600 errors you should be told an absolute
    file number.

    The following query will show the absolute and relative
    file numbers for datafiles in the database:

    SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
    FROM dba_data_files;

    In Oracle8i: In addition to the notes above about Oracle8, Oracle8i
    can have TEMPFILES. The following query will show the
    absolute and relative file numbers for tempfiles in the
    database:

    SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
    FROM dba_temp_files, v$parameter
    WHERE name='db_files';


    "Segment Type", "Owner", "Name" and "Tablespace"
    The following query will tell you the object TYPE , OWNER and NAME of
    a segment given the absolute file number "" and block number "" of the
    corrupt block - the database must be open in order to use this query:

    SELECT tablespace_name, segment_type, owner, segment_name
    FROM dba_extents
    WHERE file_id =
    and between block_id AND block_id + blocks - 1
    ;

    If the block is in a TEMPFILE the above query will return no data.
    For TEMPFILES the "Segment Type" will be "TEMPORARY".


    "Related Objects" and Possible "Recovery Options" by SEGMENT_TYPE:
    The related objects and recovery options which can be used depend on the
    SEGMENT_TYPE. The additional queries and possible recovery options are
    listed below for each of the most common segment types.

    CACHE CLUSTER INDEX PARTITION
    INDEX ROLLBACK TABLE PARTITION
    TABLE TEMPORARY Some other Segment Type
    "no rows" from the query

    CACHE
    - If the segment type is CACHE recheck you have entered the SQL
    and parameters correctly.
    If you get the same result contact Oracle support with all
    information you have.

    Options:
    The database is likely to require recovery.

    {Continue} {Back to Segment List}

    CLUSTER
    - If the segment is a CLUSTER determine which tables it contains.
    Eg:
    SELECT owner, table_name
    FROM dba_tables
    WHERE owner=''
    AND cluster_name=''
    ;

    Options:
    If the OWNER is "SYS" then contact Oracle support with all details.
    The database is likely to require recovery.

    For non dictionary clusters possible options include:
    Database recovery
    OR Salvage data from all tables in the cluster
    THEN Recreate the cluster and all its tables

    As the cluster may contain a number of tables then it is best to
    collect information for each table in the cluster before making a
    decision.

    {Collect TABLE information} {Back to Segment List}

    INDEX PARTITION
    - If the segment is an INDEX PARTITION note the NAME and OWNER
    and then determine which partition is affected thus:

    SELECT partition_name
    FROM dba_extents
    WHERE file_id =
    AND BETWEEN block_id AND block_id + blocks - 1
    ;

    then continue below as if the segment was an INDEX segment.

    Options:
    Index partitions can be rebuilt using:
    ALTER INDEX xxx REBUILD PARTITION ppp;


    INDEX
    - If the segment is an INDEX then if the OWNER is "SYS" contact
    Oracle support with all details.

    For a non-dictionary INDEX or INDEX PARTITIONs find out which table
    the INDEX is on:
    Eg:
    SELECT table_owner, table_name
    FROM dba_indexes
    WHERE owner=''
    AND index_name=''
    ;

    and determine if the index supports a CONSTRAINT:

    Eg: SELECT owner, constraint_name, constraint_type, table_name
    FROM dba_constraints
    WHERE owner=''
    AND constraint_name=''
    ;

    Possible values for CONSTRAINT_TYPE are:

    P The index supports a primary key constraint.
    U The index supports a unique constraint.


    If the INDEX supports a PRIMARY KEY constraint (type "P") then
    check if the primary key is referenced by any foreign key constraints:
    Eg:
    SELECT owner, constraint_name, constraint_type, table_name
    FROM dba_constraints
    WHERE r_owner=''
    AND r_constraint_name=''
    ;

    Options:
    If the OWNER is "SYS" then contact Oracle support with all details.
    The database is likely to require recovery.

    For non dictionary indexes possible options include:
    Database recovery
    OR Recreate the index (with any associated constraint
    disables/enables)
    (take care with the REBUILD option as described in
    "Recreating Indexes" below)

    {Continue} {Back to Segment List}

    ROLLBACK
    - If the segment is a ROLLBACK segment contact Oracle support as
    rollback segment corruptions require special handling.

    Options:
    The database is likely to require recovery.
    {Continue} {Back to Segment List}

    TABLE PARTITION
    - If the segment is a TABLE PARTITION note the NAME and OWNER
    and then determine which partition is affected thus:

    SELECT partition_name
    FROM dba_extents
    WHERE file_id =
    AND BETWEEN block_id AND block_id + blocks - 1
    ;

    then continue below as if the segment was a TABLE segment.

    Options:
    If all corruptions are in the same partition then one option
    at this point is to EXCHANGE the corrupt partition with an
    empty TABLE - this can allow the application to continue (without
    access to the data in the corrupt partition) whilst any good
    data can then be extracted from the table.


    TABLE
    - If the OWNER is "SYS" then contact Oracle support with all details.
    The database is likely to require recovery.

    For a non-dictionary TABLE or TABLE PARTITIONs find out which
    INDEXES exist on the TABLE:
    Eg:
    SELECT owner, index_name, index_type
    FROM dba_indexes
    WHERE table_owner=''
    AND table_name=''
    ;

    and determine if there is any PRIMARY key on the table:

    Eg: SELECT owner, constraint_name, constraint_type, table_name
    FROM dba_constraints
    WHERE owner=''
    AND table_name=''
    AND constraint_type='P'
    ;

    If there is a primary key then check if this is referenced by any
    foreign key constraints:
    Eg:
    SELECT owner, constraint_name, constraint_type, table_name
    FROM dba_constraints
    WHERE r_owner=''
    AND r_constraint_name=''
    ;

    Options:
    If the OWNER is "SYS" then contact Oracle support with all details.
    The database is likely to require recovery.

    For non dictionary tables possible options include:
    Database recovery
    OR Salvage data from the table (or partition)
    THEN Recreate the table (or partition)

    {Continue} {Back to Segment List}

    TEMPORARY
    - If the segment type is TEMPORARY then the corruption does not
    affect a permanent object. Check if the tablespace where the
    problem occurred is being used as a TEMPORARY tablespace thus:

    SELECT count(*) FROM dba_users
    WHERE temporary_tablespace=''
    ;

    Options:
    If this is a TEMPORARY_TABLESPACE then it may be possible
    to create a NEW temporary tablespace and switch all users
    to that tablespace then DROP the problem tablespace.

    If this is not a temporary tablespace then the block should
    not be read again and should get re-formatted next time the
    block is used - the error should not repeat PROVIDED any
    underlying cause has been cured.

    No restore is normally required, although if the disk is
    suspect and the tablespace contains useful data then a
    database recovery of the affected file/s may be wise.

    {Continue} {Back to Segment List}

    Some other SEGMENT_TYPE
    - If the segment type returned is not covered above then contact
    Oracle support for advice with all information collected so far.

    {Continue} {Back to Segment List}

    "no rows returned"
    - If there appears to be no extent containing the corrupt block
    then first double check the figures used in the query. If you
    are sure the file and block are correct and do not appear as
    belonging to an object in DBA_EXTENTS then:

    - Double check if the file involved is a TEMPFILE.
    Note that TEMPFILE file numbers depend on the init.ora
    parameter DB_FILES so any changes to this parameter
    change the absolute file number reported in errors.

    - If the database you are now querying is from a different
    point in time to the datafile with the error then the
    problem object may have been dropped.

    - If the error you are investigating was reported by DBVERIFY
    then DBV checks all blocks regardless of whether they
    belong to an object or not.

    Options:
    An error on an UNUSED Oracle block can be ignored as Oracle will
    create a new block image should the block need to be used so any
    existing problem on the block will never get read.

    {Continue} {Back to Segment List}

    Evidence>>
    - For each corrupt block it is also a good idea to collect
    the following physical evidence if there is a need to try
    and identify the actual cause of the corruption:

    i) An operating system HEX dump of the bad block
    On UNIX:
    dd if= bs= skip= count=1 | od -x
    ^^^^^^^^ ^^^^^^^^^^^^^^ ^^^
    Eg:
    dd if=ts11.dbf bs=4k skip=1223 count=3 | od -x > dump.out

    On VMS:
    DUMP/BLOCKS=(start:XXXX,end:YYYY)/out=dump.out

    Where XXXX=Operating system block number (in 512 byte chunks)
    To calculate this multiply the block number reported by
    '/512'.

    ii) If you are in ARCHIVELOG mode make a safe copy of the archived
    log files around the time of the error, and preferably for a few
    hours before the error was reported. These may be required later.

    {Continue} {Back to Segment List}

    (4) Choosing a Recovery Option
    The best recovery option now depends on the objects affected. The notes
    in Section (3) above should have highlighted the main options available
    for each affected object. The actual recovery method chosen may include
    a mix or one or more methods thus:

    Is any Recovery Required ?
    If the error is in a TEMPORARY tablespace, or is in a block
    which is no longer part of any database object then no action
    is required, although it may be wise to relocate the problem
    tablespace to a different storage device.

    Is Complete Recovery an option ?

    In order for complete recovery to be an option the following
    must be true:
    - The database is in ARCHIVELOG mode
    (The "ARCHIVE LOG LIST" command shows Archivelog Mode)

    - You have a good backup of affected files

    - All ARCHIVELOGS are available from the time of the backup
    to the current point in time

    - The current online log/s are available and intact

    - The errors are NOT due to recovery through a NOLOGGING operation

    When the above criteria are satisfied then then complete recovery
    is usually the preferred option
    *BUT NOTE*
    (a) If the rollback of a transaction has seen a corrupt block on an
    object other than the rollback segment itself then UNDO may
    have been discarded. In this case you may need to rebuild
    indexes / check data integrity AFTER the recovery completes.
    (b) If the files to be recovered contain data from NOLOGGING
    operations performed since the last backup then those blocks
    will be marked corrupt.

    If database recovery has already been performed and the corruption
    is still there then either the backup contains a corruption, the
    underlying fault is still present or the problem is replaying
    through redo. In these cases you will need to choose some other
    recovery option.

    See "(4A) Complete Recovery" for complete recovery steps.

    Can the object be Dropped or Re-created without needing
    to extract any data from the object itself ?
    It may be possible to lose the object, or to recreate it from
    a script / recent export. Once an object is dropped then blocks
    in that object are marked as "free" and will be re-formatted when
    the block gets allocated to a new object. It is advisable to RENAME
    rather than DROP a table unless you are absolutely sure that you
    do not need any data in it.

    In the case of a table partition then only the affected partition
    needs to be dropped. eg: ALTER TABLE ... DROP PARTITION ...
    If the corruption affects the partition segment header, or the
    file containing the partition header is offline, then DROP
    PARTITION may fail. In this case it may still be possible to
    drop the partition by first exchanging it with a table of the
    same definition.
    eg: ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE ..;

    The most common object which can be re-created is an index.
    Always address TABLE corruptions before INDEX problems on a table.
    See "(4B) Recreating Indexes" for more details.

    Is it required to salvage data before recreating the object ?
    If the problem is on a critical application table which is
    regularly updated then it may be required to salvage as much
    data from the table as possible, then recreate the table.

    See "(4C) Salvaging Data from Tables" for more details.

    Last Options
    Are any of the following possible ?
    Recovery to an old point-in-time (via point in time recovery)
    OR Restore of a COLD backup from before the corruption
    OR Use of an existing export file
    See "(4D) Last Options" for more details.


    (4A) Complete Recovery
    If the database is in ARCHIVELOG mode and you have a good backup
    of the affected files then recovery is usually the preferred option.
    This is not GUARANTEED to clear a problem, but is effective for the
    majority of corruption issues. If recovery re-introduces the problem
    then return to the list of options above and choose another method.

    You can either perform datafile recovery (which can be done while the
    rest of the database is still up and running), or database recovery
    (which requires the database to be taken down).

    Datafile Recovery
    ~~~~~~~~~~~~~~~~~~
    Datafile recovery of a file involves the following steps. If there are
    several files repeat the steps for each file or see "Database Recovery"
    below. These steps can be used if the database is either OPEN or MOUNTED.

    OFFLINE the affected data file
    eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;

    Copy it to a safe location (in case the backup is bad)

    Restore the latest backup of the file onto a GOOD disk

    Check the restored file for obvious corruptions with DBVERIFY
    For details of using DBVERIFY see

    Assuming the restored file is OK the RENAME the datafile to the
    NEW location (if different from the old location)
    eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

    Recover the datafile
    eg: RECOVER DATAFILE 'name_of_file';

    Online the file/s
    eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

    {Continue}

    Database Recovery
    ~~~~~~~~~~~~~~~~~
    Database recovery generally involves the following steps:

    Shutdown Abort

    Copy the current copy of all files to be recovered to a safe location

    Restore the backup files to a GOOD disk location
    DO NOT RESTORE THE CONTROL FILES or REDO LOGS

    Check restored files with DBVERIFY
    For details of using DBVERIFY see

    Startup MOUNT

    Rename any relocated files
    eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

    Ensure all required files are online
    eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

    Recover the database
    eg: RECOVER DATABASE

    Open the database
    eg: ALTER DATABASE OPEN;


    After a Complete Recovery
    ~~~~~~~~~~~~~~~~~~~~~~~~~~
    Once a complete recovery has been performed it is advisable to check the
    database before allowing it to be used:

    - Run "ANALYZE VALIDATE STRUCTURE CASCADE"
    against each problem object to check for table/index mis-matches.
    If there has been any UNDO discarded this may show a mismatch
    requiring indexes to be re-created.

    - Check the logical integrity of data in the table at application level.



    (4B) Recreating Indexes
    If the corrupt object is a user INDEX you can simply drop and
    re-create it PROVIDED the underlying table is not also corrupt.
    If the underlying table is also corrupt it is advisable to sort out
    the TABLE before recreating any indexes.

    If the information collected shows that the index has dependent FOREIGN
    KEY constraints then you will need to do something like this:

    - ALTER TABLE DISABLE CONSTRAINT ;
    for each foreign key

    - Rebuild the primary key using
    ALTER TABLE DISABLE CONSTRAINT ;
    DROP INDEX ;
    CREATE INDEX .. with appropriate storage clause
    ALTER TABLE
    ENABLE CONSTRAINT ;

    - Enable the foreign key constraints
    ALTER TABLE ENABLE CONSTRAINT ;

    For an index partition you can:
    ALTER INDEX ... REBUILD PARTITION ...;

    Notes:
    (1) It is important not to REBUILD a non-partitioned corrupt index
    using an "ALTER INDEX .. REBUILD" command as this will usually
    try to build the new index from the existing index segment,
    which contains a corrupt block.
    "ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD
    PARTITION ..." do not build the new index from the old index
    segment and so can be used.

    (2) Create INDEX can use the data from an existing index
    if the new index is a sub-set of the columns in the
    existing index. Hence if you have 2 corrupt indexes drop
    them BOTH before re-creating them.

    (3) Be sure to use the correct storage details when recreating indexes.


    (4C) Salvaging Data from Tables
    If the corrupt object is a TABLE or CLUSTER it must be understood
    that the data within the corrupt block is lost. Some of the data
    may be salvageable from a HEX dump of the block, or from columns
    covered by indexes.

    Important:
    As it may be required to salvage data in the corrupt
    block from the indexes it is a good idea NOT to drop
    any existing index until any required data has been
    extracted.

    There are many ways to get data out of a table which contains a
    corrupt block. Choose the most appropriate method as detailed
    below. The aim of these methods is to extract as much data as
    possible from the table blocks which can be accessed. It is
    usually a good idea to RENAME the corrupt table so that the new
    object can be created with the correct name.
    Eg: RENAME TO ;

    Methods of extracting data from a corrupt table AROUND the corrupt block

    (1) From Oracle 7.2 onwards, including Oracle 8.0 and 8.1, it is
    possible to SKIP over corrupt blocks in a table.
    This is by far the simplest option to extract table data and is
    discussed in:

    Extracting data using SKIP_CORRUPT_BLOCKS or Event 10231

    Note that this event can only be used if the block "wrapper" is
    marked corrupt. Eg: If the block reports ORA-1578.


    (2) From Oracle 7.1 onwards you can use a ROWID range scan.
    The syntax for this is a little tricky but it is possible
    to select around a corrupt block using a ROWID hint.
    As the format of ROWIDs is different in Oracle7 and Oracle8
    there are 2 articles which discuss this:

    Using ROWID Range Scans to extract data in Oracle8
    Using ROWID Range Scans to extract data in Oracle7


    (3) 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 and is only normally needed
    for Oracle 7.0 releases. This method is described in
    (which also describes the ROWID range scans)

    (4) 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 besides an ORA-1578.
    As these methods typically require much hand-holding from
    support then some of these articles may not be visible to customers.

    These require Pro*C to be available and an understanding of how
    to build Pro*C executables:
    SALVAGE.PC for Oracle8.1
    SALVAGE.PC for Oracle7

    These requires manual interaction:
    SALVAGE.SQL for Oracle7/8
    SALVAGE.SQL for Oracle7/8

    This is only possible in Oracle8i.
    The aim is to mark the block as corrupt and then use the
    SKIP_CORRUPT table attribute to extract the table data:
    Use DBMS_REPAIR to mark the block corrupt


    Extracting data from the corrupt block itself

    As the corrupt block itself is "corrupt" then any data extracted
    from the block should be treated as suspect. The main methods
    of getting the rows from the corrupt block itself are:

    - Oracle Support can use a tool which attempts to interpret
    the block contents.
    - Use any existing indexes on the table to extract data for
    columns covered by the index where the ROWID falls inside the
    corrupt block. This is described towards the end of the ROWID
    range scan articles mentioned above:
    For Oracle8/8i see
    For Oracle7 see



    (4D) Last Options
    Whatever sort of block the problem occurred one possible option
    is to recover the whole database to a point in time BEFORE the
    corruption appeared. The difficulty with this option is that it is not
    always possible to know when the problem first appeared.
    DBVERIFY can be often be used to check a restored file for corruptions.
    For details of using DBVERIFY see

    This section outlines the absolute final options available for recovering
    a database. If you have come here then one or more of the following
    have happened:

    You have lost a "vital" datafile (or have a corruption on it)
    and have no useful backup of the problem file/s
    and are either not in ARCHIVELOG mode OR do not have all archivelogs
    since the file was first created

    Last chance:
    Please note if you have lost all copies of a datafile but DO still have
    the ARCHIVE logs from when the file was first created it is still possible
    to recover the file.
    Eg:
    ALTER DATABASE CREATE DATAFILE '....' [as '...'] ;
    RECOVER DATAFILE '....'
    ALTER DATABASE DATAFILE '....' ONLINE;

    If you are in this scenario try to recover the datafile using these
    steps before proceeding below.


    If you have reached this line there are no options left to recover to
    the current point in time. It is advisable to shutdown the instance and
    take a BACKUP of the current database NOW in order to provide a fall-back
    position if the chosen course of action fails. (Eg: if you find your backup
    is bad)

    The outline options available are:

    Revert to an old COLD backup
    - eg: If in NOARCHIVELOG mode

    Point in time recovery to an older point in time that is consistent
    - requires a good backup and any necessary archive logs
    - ALL files have to be restored and the whole DB rolled forward
    to a suitable point in time.

    Rebuild of DB from some logical export / copy
    - Requires there to already be a good logical backup of the database
    - NB: You have to RE-CREATE the database for this option.
    --------------------------------------------------------------------------------
    .

    sonofsita
    http://www.ordba.net

  3. #3
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184

  4. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width