Exporting table with corrupted block
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Exporting table with corrupted block

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226
    I have exported a table with a corrupted block and get the following:

    EXP-00056: ORACLE error 1578 encountered
    ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
    ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'
    Export terminated successfully with warnings.

    If I drop the corrupted table, and try to import the table back from the exported dump file which gave the above error during export, would I get my rows back minus what was in the corrupted block??? What are the consequences??? Please - your input highly awaited!!
    roukie-dba

  2. #2
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226

    Can someone help with my problem please???

    Can a moderator help with my problem please???
    roukie-dba

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, AFAIK you will not get any rows of this table from this export. Export performs full table scan on this table and like normal select it fails without returning any rows when hitting the first corrupted block.

    You have sveral options of how to pull out the table data (other than rows in the corrupted blocks).

    *** If you are on 8i you can simply isue the following:

    execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('','');

    and then export the table. This will enable exp to skip over the corrupted blocks. After you are done you can reset the skip flag back to normal with:

    execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('', '', flags=>dbms_repair.noskip_flag);

    *** If you are on pre-8i or if you can't use DBMS_REPAIR, you can set special event in your init.ora:

    event="10231 trace name context forever, level 10"

    This event like the procedure above enable the database to skip over the corrupted blocks when performing full table scan. Start the database with this modified init.ora, export the table, shutdown the database, delete the above event from your init.ora and start the database again.

    ***
    If you can't or wouldn't use any of the above options (for example you don't have the appropriate privileges on the database), you could find out the ROWID "boundaries" of the corrupted block and use that in your WHERE clause of the export. You should find the LAST rowid that resides in the block prior the offended block and the FIRST one that resides in the block following the corrupted one. Then you can simply use something like QUERY="where ROWID <= rid_LAST or ROWID >= rid_FIRST" in your exp, replacing rid_LAST and rid_FIRST with your actual ROWIDs found in the previous step. Note that QUERY parameter of exp is available only in 8i.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226
    Dear Jmodic

    A very big thank you for very detailed answer. I am running on 8.0.5.2. I do have the following questions:

    1) The following recommendation to adjust in my init.ora

    event="10231 trace name context forever, level 10"

    Please explain what the number 10231 and level 10 mean, and is this what we have to put in the init.ora regardless of corrupted block?

    roukie-dba

  5. #5
    Join Date
    Sep 2000
    Location
    Singapore
    Posts
    32

    event="10231 trace name context forever, level 10"

    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.
    Felix
    DBA

  6. #6
    Join Date
    Jun 2001
    Location
    NY
    Posts
    226
    Thanks very much for the answers.

    Please explain the following to me:

    I do a select * from schema.table and obtained the following:

    SQL> select count(*) from sphdb.fwhdblot;
    select count(*) from sphdb.fwhdblot
    *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
    ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'


    Then I exported the table from the schema and obtained the following:

    About to export specified tables via Conventional Path ...
    Current user changed to SPHDB
    . . exporting table FWHDBLOT
    EXP-00056: ORACLE error 1578 encountered
    ORA-01578: ORACLE data block corrupted (file # 5, block # 112676)
    ORA-01110: data file 5: '/u12/oradata/SPFHD1/fh_datal01_001.dbf'
    Export terminated successfully with warnings.
    $


    Then imported the table into another schema and obtained the following:

    Connected to: Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production
    With the Partitioning option
    PL/SQL Release 8.0.5.2.0 - Production

    Export file created by EXPORT:V08.00.05 via conventional path

    Warning: the objects were exported by SYSTEM, not by you

    . . importing table "FWHDBLOT" 14068 rows imported
    Import terminated successfully without warnings.

    Then I repeat a SELECT * from schema.table (in schema I just imported table into) and get the following:
    SQL> select count(*) from martin.fwhdblot;

    COUNT(*)
    ----------
    14068

    1) Why did the preceding query not work in the schema containing the corrupt table?
    2) a SELECT * FROM table is a full table scan and works in both schemas...I am confused!!!

    Please help me understand!


    roukie-dba

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