Interpration of DBVerify zero block errors

[Former Member]

Hello Oracle-experts,

after DBVerify check on my Oracle DB 10.2.0.2 i get the several following errors:

Completely zero block found during dbv:

Page 286685 is marked corrupt

After these warnings I get the error:

BR0398E DBVERIFY detected corrupted blocks in /oracle/<sid>/sapdata4/sr3_8/sr3.data8

Question:

1) How should these zero block errors be interpreted? Is my database therefore corrupt or can these errors be neglected? If not, what is the approach to solve these errors?

The problem is that I have a lot of these errors and do not have a valid backup.

Any helpful information information will be very appreciated!

[lbreddemann]

Hi Holger,

didn't you like the replies of your other threads?

Honestly, handling corruptions is really not that difficult.

You figure out what kind of data is supposed to be stored in the corrupted blocks and take your actions accordingly.

So if you can regenerate the content (like you can do that by rebuilding indexes), you do that.

If you cannot regenerate the content, you need a backup.

That's it. It's all lengthly described in the SAP notes on corruptions. Maybe you should read them ...

> Question:

> 1) How should these zero block errors be interpreted? Is my database therefore corrupt or can these errors be neglected? If not, what is the approach to solve these errors?

> The problem is that I have a lot of these errors and do not have a valid backup.

Ok, having no backups always puts you in dire straits. It's the single one error no DBA is allowed to make.

However, what you should do is to perform a FULL consistency check.

That is:

• run ANALYZE TABLE VALIDATE STRUCTURE CASCADE on ALL tables

• run a FULL db export to /dev/nul

• run DBVerify/RMAN validate on the complete database (looks like you've already done that)

Don't skip a check, don't shortcut - just do them.

Maybe the "completely zero filled blocks* don't belong to any object and you've no data loss.

Then you may (as I already told you in the other thread) reorganize the tablespace to get rid of the warnings.

If there was some data in these blocks that you cannot regenerate then you have to face it: you lost that data.

It's gone and won't come back.

regards,

Lars

[Former Member]

Hello Lars,

many thaks for your repy.

> run ANALYZE TABLE VALIDATE STRUCTURE CASCADE on ALL tables

> run a FULL db export to /dev/nul

> run DBVerify/RMAN validate on the complete database (looks like you've already done that)

> Don't skip a check, don't shortcut - just do them.

> Mayb the "completely zero filled blocks* don't belong to any object and you've no data loss.

> Then you may (as I already told you in the other thread) reorganize the tablespace to get rid of the warnings.

I alredy executed ValidStruct job from DB13. This job runs without any errors.

After this I executed for several times DBVerify job from SAP system (online) and via BRTOOLS (offline).

This job ended with the same result: I get a lot of errors about zero blocks.

When I execute the SQL commando for some of the errors (SELECT dbms_utility.data_block_address_block ...) I can see that the block and data file belong to a existing SAP table.

Question

Is there a SQL commando to rebuild the indexes of a comleted data file?

Thank you

[lbreddemann]

> I alredy executed ValidStruct job from DB13. This job runs without any errors.

Well, this is good news - maybe none of your table data is affected by the corruption.

What about the full export?

> After this I executed for several times DBVerify job from SAP system (online) and via BRTOOLS (offline).

Any reason for doing this twice? DB13 just calls the BRTOOLS which in turn call DBV to perform the verification.

It really doesn't make any difference where you trigger this.

> This job ended with the same result: I get a lot of errors about zero blocks.

> When I execute the SQL commando for some of the errors (SELECT dbms_utility.data_block_address_block ...) I can see that the block and data file belong to a existing SAP table.

With the command you mentioned above, all you can do it to reformat a (R)DBA (block address) to a file and a block address.

You cannot know which object belongs to this block with the statement.

Have you read the sap notes yet?

> Question

> Is there a SQL commando to rebuild the indexes of a comleted data file?

Counter-Question: You assume that a table is affected - what should it be useful for to rebuild indexes then?

But to answer that question: no there is no SQL command for batch DDL.

Instead you may use brspace to reorganize the tablespace.

See, a single data file is not a unit of space management for segments - data files just build up the tablespace where you put the segment into. So if you want to get rid of a data file you'd have to reorganize the tablespace.

regards,

Lars

[lbreddemann]

> I alredy executed ValidStruct job from DB13. This job runs without any errors.

Well, this is good news - maybe none of your table data is affected by the corruption.

What about the full export?

> After this I executed for several times DBVerify job from SAP system (online) and via BRTOOLS (offline).

Any reason for doing this twice? DB13 just calls the BRTOOLS which in turn call DBV to perform the verification.

It really doesn't make any difference where you trigger this.

> This job ended with the same result: I get a lot of errors about zero blocks.

> When I execute the SQL commando for some of the errors (SELECT dbms_utility.data_block_address_block ...) I can see that the block and data file belong to a existing SAP table.

With the command you mentioned above, all you can do it to reformat a (R)DBA (block address) to a file and a block address.

You cannot know which object belongs to this block with the statement.

Have you read the sap notes yet?

> Question

> Is there a SQL commando to rebuild the indexes of a comleted data file?

Counter-Question: You assume that a table is affected - what should it be useful for to rebuild indexes then?

But to answer that question: no there is no SQL command for batch DDL.

Instead you may use brspace to reorganize the tablespace.

See, a single data file is not a unit of space management for segments - data files just build up the tablespace where you put the segment into. So if you want to get rid of a data file you'd have to reorganize the tablespace.

regards,

Lars