Coruupted Blocks in BW - real or cosmetic?

[Former Member]

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?

[Former Member]

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

[lbreddemann]

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

[stefan_koehler]

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

[Former Member]

I looked in the trace file and .... is there something wrong with the temp file?

more dwp_ora_22498.trc

Dump file /oracle/DWP/saptrace/usertrace/dwp_ora_22498.trc

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

ORACLE_HOME = /oracle/DWP/920_64

System name: HP-UX

Node name: sdbwdb1

Release: B.11.11

Version: U

Machine: 9000/800

Instance name: DWP

Redo thread mounted by this instance: 1

Oracle process number: 34

Unix process pid: 22498, image: oracle@sdbwdb1 (TNS V1-V3)

• SESSION ID:(139.19381) 2008-01-16 09:10:12.346

alter system dump datafile/tempfile: block range beyond file maximum size

[stefan_koehler]

Hello William,

maybe your file information was incorrect.

> SQL> select FILE# from V$DATAFILE where NAME = '/oracle/DWP/sapdata17/dwp_119/dwp.data119';

Is this the file 147?

Regards

Stefan

[Former Member]

Yes

select FILE_ID, FILE_NAME from dba_data_files where FILE_NAME = '/oracle/DWP/sapdata17/dwp_119/dwp.data119';

FILE_ID

-

---

FILE_NAME

-

---

147

/oracle/DWP/sapdata17/dwp_119/dwp.data119

[stefan_koehler]

Hello William,

now the only explanation that i have.

Is this datafile in a permanent tablespace which is used as "temporary tablespace" or is it a new datafile which already exists some time ago under the same name?

Regards

Stefan

[Former Member]

It's a permanent tablespace PSAPDWP

[stefan_koehler]

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