Skip to Content
author's profile photo Former Member
Former Member

How to correct table block corruption on EDI40 table ?

Hi,

I have discovered that there is table block corruption on the EDI40 table. Following OSS 365481 I have found the Idoc number that the corruption is specific to.

I can't see what options are available to me to correct the situation :-

WE11 short-dumps due to the corruption when attempting to delete the Idoc.

Manual 'delete from sapd1p.edi40 where docnum='idocnumber'' also fais due to the corruption.

Reorg is not possible as the EDI40 table contains a LONG field.

Trying to create a dummy table using Oracle 'create table ... as select from' doesn't work due to the LONG field.

DBMS_REPAIR package use seems to be not supported by SAP.

Oracle EXP/IMP based export will fail on impot due to the corruption.

Can anyone suggest what options could still be used to enable me to either delete the specific DOCNUM records from the EDI40 table or be able to export then re-import the EDI40 table data ?.

BR0301E SQL error -1578 at location stats_tab_validate-2, SQL statement:

'ANALYZE TABLE "SAPD1P"."EDI40" VALIDATE STRUCTURE CASCADE ONLINE'

ORA-01578: ORACLE data block corrupted (file # 62, block # 2163100)

ORA-01110: data file 62: '/oracle/D7R/sapdata6/d1p_49/d1p.data49'

BR0893E Validating structure failed for table/cluster SAPD1P.EDI40

I had hoped the corruption was index related but based on the contents of the dba_extents table for the block # and file # , the corruption is related to table data.

SQL> select segment_name, partition_name, segment_type, block_id, blocks from dba_extents where (2163100 between block_id and (block_id + blocks - 1)) and file_id = 62 and rownum < 2;

SEGMENT_NAME

-


PARTITION_NAME SEGMENT_TYPE BLOCK_ID BLOCKS

-


-


-


-


EDI40

TABLE 2162185 3072

SQL>

Regards,

Brian.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jan 18, 2012 at 12:54 PM

    Hello Brian,

    as your corrupted block is related to a table (and it seems like a used block) - a reorg would not help here anyway.

    What kind of backup do you perform? If you do backups with RMAN - you are lucky, because of you can perform a block recovery (online) - if you are using "flat file" based backup - you need to restore the data file and recover it.

    Regards

    Stefan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Brian,

      ok that's bad (for the future you really should think about RMAN or dbverify checks).

      There are some hidden parameters for handling block corruptions, but i will not mention / suggest them here. In your case the official way is to open a SR for that issue (sapnote #1116190).

      Regards

      Stefan

  • author's profile photo Former Member
    Former Member
    Posted on Jan 19, 2012 at 03:06 PM

    Hi Stefan,

    I have been able to resolve the problem, but it meant that the corrupt Idoc (which was massive - over 750,000 segments) was lost from the EDI* tables.

    After my analysis using OSS 365481,

    I did the following :-

    u2022 Took database backup

    u2022 Shutdown SAP instance

    u2022 Created ddl statements for the EDI40 table/index

    u2022 Exported Table using brtools

    u2022 Renamed EDI40 table and it's index to EDI40_OLD & EDI40_OLD~0 respectively

    u2022 Imported EDI40 table contents and checked table contents (Idoc with corruption wasn't present, export didn't export the corrupt table block). Ran DB statistics on the imported EDI40 table.

    u2022 Started SAP instance and checked the contents of the Idoc tables to confim only the affected Idoc was missing in EDI40.

    u2022 Traced WE11 Idoc deletion to find out which other Idoc tables needed to be cleaned up for the problem Idoc. Based on the trace, removed the table entries for the problem Idoc in EDIDC,EDIDS & EDIDOC

    u2022 Ran a brtools structural check on the sapsr3.edi40 table - no corrupt blocks were flagged now.

    u2022 Dropped the EDI40_OLD table & EDI40_OLD~0 index.

    In this case the recovery didn't involve data being "missing" from the BW data tables as the problematic Idoc had posted successfully before corruption, so the data was in BW.

    Regards,

    Brian.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hehehe...hi Stefan 😊

      Yes, it's not all super-fully-automatic, but most often users don't even consider this option and instead go for the long and usually unpleasant path of manual corruption handling.

      That's why I sometimes like to push the block recovery feature a bit 😊

      best regards,

      Lars

  • author's profile photo Former Member
    Former Member
    Posted on Jan 23, 2012 at 12:08 PM

    Hi Brian,

    You can schedule DBVERIFY and Validate Structure Jobs at least weekly on your database as a best practice to be more prepared for any block corruption.This way you will immediately come to know of any block corruption in the DB.

    BTW,I had followed same steps as Lars suggested when I encountered a block corruption in one of the tables in the database,while I backup using Legato too,using RMAN was really more convenient.

    Regards,

    KB

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.