SAP_INFOCUBE_INDEXES_REPAIR

[Former Member]

Hi All,

I have created a new sandbox BW system from a copy our BW Production. I used the Oracle database restore method for this.

Normally, before starting the backup on Production, I do " alter database force logging" and then use that backup for restore.

This time I forgot this and after all system copy steps were completed, I ran dbverify and it reported lots and lots of corrupt blocks due to corrupt indexes. This is a known problem and SAP note 547464 deals with it.

I am currently running report SAP_INFOCUBE_INDEXES_REPAIR to recreate/repair all indexes.

It has been running for a long time.

My concern is this - this report may be able to fix the indexes, but will dbverify still report corrupt blocks.

I get errors like:

BV-00200: Block, dba 76030411, already marked corrupted

Does anyone have experince with this and will this also be fixed with SAP_INFOCUBE_INDEXES_REPAIR?

Any suggestions, please help.

Cyrus

[Former Member]

You can do one more thing. Run collect database statics by this u know the missing statics then update these statics. after this ur index problem can be solved. and infocube index u can rebuild indexes sepratly by brtools table wise.

Thanks

presu

[Former Member]

Hi,

DBV-00200 errors indicate corrupted blocks on database.

Did you confirm if the backup restored is reliable, free of corrupted data

Did you perform a hardware check in this target server ?

Many case of corrupted blocks can appear due hardware fails

Could you please execute the consistency check using the

analyze command ? See question 7 of note 365481.

Additionaly you can find more details in the note 23345

Also Check SAP Notes 547464 and 442763.

We came across same issue during our system refresh. Report SAP_INFOCUBE_INDEXES_REPAIR didnt worked for us. What we did is we dropped & recreated indexes for those particular cubes & ran DB verify job. corrupt block error didn't come again.

can you check whether any index creation/drop job ran during the backup time which you have restored.

This worked for us.

The cause of the problem might be that indexes on objects which trigger

the error were created with NOLOGGING option.

As discussed in note 442763, only the indexes are created this way,and they can be created again with the reported mentioned.

we have resolved this issue by taking backup at that time where no process chain runs.We restored same backup.

after executing DB verify after the system refresh block corruption warnings didn't come.

Additionally see :

863417 : FAQ: Database Archive modes and redo logs

23070 : Backup and Recovery: Basic Concepts (it is very old one but

provides the basic concepts)

You can use BR*Tools/BRBACKUP for database backup. See the URL :

http://help.sap.com/saphelp_nwpi71/helpdata/en/0b/5daf09b03344ad97338f838e09b9ee/frameset.htm

Hope this helps

Thanks,

Sushil

Edited by: Sushil Suryawanshi on May 20, 2009 12:02 AM

[Former Member]

check, these will help and blog from Lars

365481 Block corruptions

354293 DBVerify reports corrupt block in freespace area

23345 Consistency check of ORACLE database

540463 FAQ: Consistency Checks + Block Corruptions

http://tknight.org/sdn/show/9285

Edited by: Bhudev on May 19, 2009 5:55 AM

[Former Member]

Thanks Lars.

The rapair job has been running for about 10 hours now and it seems to be working.

I ran a dbverify for one of the datafile that was reporting several corruptions and this file doesnt show any now.

I havent ran dbverify for the whole database which I will do after the job finishes. And yes, I will use rman this time.

Thanks

Cyrus

[Former Member]

After the Index repair finished, I have started the dbverify with RMAN for entire database.

It is still running but I get corrupt blocks information for some datafiles like this:

BR0398E RMAN detected corrupted blocks in '<datafile path>'

BR0548I Please check Oracle alert log '<path>' for further information about this error

But no errors are reported in alert log log or usertrace.

As I understand from Lars earlier, these blocks will be reformatted next time Oracle needs them?

I dont have to worry too much for this right?

Cyrus

Edited by: cyrus brocha on May 19, 2009 4:05 PM

[Former Member]

Hi,

You can repair Corrupted block using DBMS_REPAIR if not possible to repair you can fix it see below link

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/repair.htm

[lbreddemann]

> This time I forgot this and after all system copy steps were completed, I ran dbverify and it reported lots and lots of corrupt blocks due to corrupt indexes. This is a known problem and SAP note 547464 deals with it.

>

> I am currently running report SAP_INFOCUBE_INDEXES_REPAIR to recreate/repair all indexes.

> It has been running for a long time.

As you've read the notes, you do know that the brtools will handle those indexes automatically when you restore&recover the database with them, do you?

> My concern is this - this report may be able to fix the indexes, but will dbverify still report corrupt blocks.

> I get errors like:

> BV-00200: Block, dba 76030411, already marked corrupted

Yeah, so what?

The blocks are marked as corrupt and will be reformatted the next time Oracle wants to write to them.

As long as all the NOLOGGING indexes that have been created after you took the data file backup have been rebuilt you're done here.

The DBV-200 messages are then only warnings.

> Does anyone have experince with this and will this also be fixed with SAP_INFOCUBE_INDEXES_REPAIR?

Nope, that one does only recreate missing indexes or indexes that are unusable.

The whole rebuild the soft-corrupt NOLOGGING index procedure is NOT focussed on getting rid of the soft-corrupt blocks. Instead it's focussed on getting the indexes back to work.

If you really want to get rid of all those soft-corrupt blocks, then a full tablespace reorg is the only 100% guaranteed way.

BTW: DBV is an offline tool that does not know about how blocks are used within a segment.

Use RMAN instead. You've much better analysis options with RMAN and RMAN won't give you 1000 warnings for corruptions that don't do any harm.

regards,

Lars