How to correct table block corruption on EDI40 table ?

[Former Member]

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.

[stefan_koehler · Accepted solution]

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

[Former Member · Accepted solution]

Hi Stefan,

many thanks for your swift reply.

Unfortunately we don't perform RMAN backups and only backup to legato using the brbackup of the datafiles.

The corruption has been here for a long time - all our restorable backup images contain the corrupt table block for EDI40.

So it's looking like some manual process is needed to correct the corruption.

Regards,

Brian.

[former_member188883 · Accepted solution]

Hi Brian,

To remove this block corruption you can think of performing table export and then import.

You can use brtools to perform export and import . Or

You can use Oracle exp / imp tools to perform export or import.

Regards,

Deepak Kori

[stefan_koehler · Accepted solution]

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

[Former Member]

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

[Former Member]

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.

[lbreddemann]

Hi Brian,

congrats for rescuing your data.

However, two comments I'd like to make here:

• it's definitively not a export feature to automatically skip corrupt blocks! I've no idea why the export didn't abort when it hit the corrupt block, but usually it would. And in that case you would not only loose the corrupt block itself but also all subsequent blocks.

Thus - make double sure you got all your rows in place!

• whenever you face block corruptions on only a few blocks, you should try to use the RMAN blockrecovery function to recover the block from a backup.

For this it's not necessary to have taken the backup with RMAN before - all you need is a copy of the datafile (or a RMAN backup of it) and the subsequent redolog backups. It's really simple to use and quite effective - this can even be done without downtime!

But again: glad to hear you managed to resolve the issue in this case.

regards,

Lars

[stefan_koehler]

Hey Lars,

what a honour to see you here again ) I wonder about that export behaviour too.

For this it's not necessary to have taken the backup with RMAN before

Well technically not of course, but Brian is using Legato Networker (with BRTools) as backup. So in this case he need to restore that specific data file to a different location and register it in Oracle. After that he need to restore all the corresponding archive log files for recovery (without automatic deletion of already applied archive logs) in a different or the same location. If they are restored in the same location the control file still got the information about it (because of BRTools don't delete that by default) - if not he need to register all the archive log files again. The needed disk space for this procedure is huge in productive systems ... not soooo simple )

Regards

Stefan

[lbreddemann]

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