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

Coruupted Blocks in BW - real or cosmetic?

In SAP Note 849485 there is this statement:

"Note that DBV can still return DBV-00200 messages after a restructuring of all indexes. If a block that has been marked as corrupt is returned to the freespace area by dropping an index, and then allocated by another index as part of an extent, the "corruption" remains until data is written to the

block. This is entirely a cosmetic problem with DBV."

If DB13-DBVerify show a corrupted block"

dbv file=/oracle/DWP/sapdata17/dwp_119/dwp.data119 blocksize=8192

DBV-00200: Block, dba 617370019, already marked corrupted

I then run this select statement and it shows there is no object there?

SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER FROM SYS.DBA_EXTENTS WHERE FILE_ID = 147 AND 617370019 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

no rows selected

How can I tell if a corrupted block is real or cosmetic in my BW system?

And if they are cosmetic, how can I get rid of these errors in DB13-DBVerify?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jan 16, 2008 at 03:54 PM

    Hello William,

    the easiest way to check if the corrupted block is in use is the following.

    > SQL> alter system dump datafile 147 block 617370019

    > cd /oracle/DBWP/saptrace/usertrace

    and check the latest trace file - and take a look at the seg/obj and convert the hex value to decimal.

    After that query the dba_objects one:

    > SQL> select * from dba_objects where DATA_OBJECT_ID = <your_value>;

    You can mark the blocks as corrupt using the package DBMS_REPAIR (but read carefully what DBMS_REPAIR do!!):

    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#i1006090

    Regards

    Stefan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello William,

      sorry i haven't seen that this was the raw "dba" - my mistake.

      Please check metalinknote #113005.1 (TFTS: Converting DBA's (Database Addresses) to File # and Block #)

      The reported dba is not a block number - you have to convert it first and then you can do a block dump (or run your query from above)

      Or you can try the pl/sql procedure DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK / DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE for that:

      http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm

      > select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(617370019) from dual;

      > select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(617370019) from dual;

      Regards

      Stefan

  • Posted on Jan 18, 2008 at 01:36 AM

    Hi William,

    the easiest way would be to check the several SAP Support notes. Just like this one:

    #849485 - Reconstruction of the NOLOGGING indexes after recovery

    or this one

    #724972 - ORA-00200: Block, dba

    (Just put in the note number into the search field in the upper left and search for SAP Support notes!)

    It's quite likely that the corrupt blocks are the result of NOLOGGING data operations (like index builds), as discribed in

    #547464 - Nologging Option when creating indexes.

    The BRTOOLS have support for handling these cases build-in.

    Nevertheless, these blocks are corrupt! If you leave the indexes that had been stored in them as they are - you cannot use the indexes. That is not something you want to see in your BW...

    In any way: don't use the DBMS_REPAIR package on your SAP Oracle instance. Basically it's a convinient way to get rid of the corrupt data by deleting it...

    If you really do have a corruption that you cannot handle with the notes you'll find once you search for "oracle block corruption" then: open a support message.

    And never forget: make backups, check backups.

    Ah before I forget... have a look here: ['I can repair your corrupt database!'|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/6120] [original link is broken] [original link is broken] [original link is broken]; 😊

    KR Lars

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 21, 2008 at 05:15 PM

    The corrupted block are not really corruputed in the BW system.

    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.