cancel
Showing results for 
Search instead for 
Did you mean: 

Block Corruption (BR0398E DBVERIFY detected corrupted blocks in /oracle/TS2

Former Member
0 Kudos

Hello Gurus

I am facing Data Block corruption error for single datafile....

BR0278W Command output of '/oracle/TS2/102_64/bin/dbv file=/oracle/TS2/sapdata3/ts2_73/ts2.data73 blocksize=8192':

DBVERIFY: Release 10.2.0.2.0 - Production on Thu Jul 17 23:31:25 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/TS2/sapdata3/ts2_73/ts2.data73

Block Checking: DBA = 528925394, Block Type = KTB-managed data block

        • row 4: key out of order

end index block validation

Page 443090 failed with check code 6401

DBVERIFY - Verification complete

Total Pages Examined : 1280000

Total Pages Processed (Data) : 248379

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 180541

Total Pages Failing (Index): 1

Total Pages Processed (Other): 13272

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 837808

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 65006255 (0.65006255)

BR0398E DBVERIFY detected corrupted blocks in /oracle/TS2/sapdata3/ts2_73/ts2.data73

appriciated help please..

Regards

Giridhar.

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hello Giridhar,

one of your indexes got corrupted, because the index keys are not sorted completely.

Now you have 2 options:

  • DROP the index and CREATE it again

  • REBUILD the index ONLINE (with the ONLINE clause the table is used as reference)

Identify the block and datafile .. and then check the index that is corrupted

>SQL> SELECT dbms_utility.data_block_address_block(528925394) "BLOCK",

> 2 dbms_utility.data_block_address_file(528925394) "FILE" FROM dual;

> BLOCK FILE

> 443090 126

>

>SQL> alter system dump datafile 126 block 443090;

>SQL> exit

>shell> cd /oracle/TS2/saptrace/usertrace

>shell> show the last trace file

> Identify the object id in the trace file and search for the keyword objn

>SQL> SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE DATA_OBJECT_ID = <objn>;

>SQL> ALTER INDEX <OBJECT_NAME> REBUILD ONLINE;

After that check it again with DBVERIFY. If you are not fimiliar with corruption and recovery - open a SAP call and they will help you. My experience with the SAP database support is very good .. so let they help you.

Regards

Stefan

Answers (4)

Answers (4)

Former Member
0 Kudos

Hey Lars ,

update on the issue is , now for DBV it is pointing another datafile as courrption. and earlier datafile seems to you verified successfully.

>>DBVERIFY - Verification starting : FILE = /oracle/TS2/sapdata5/ts2_75/ts2.data75

>>Block Checking: DBA = 537312850, Block Type = KTB-managed data block

>>**** row 96: key out of order

>>---- end index block validation

>>Page 441938 failed with check code 6401

DBVERIFY - Verification complete

Total Pages Examined : 1280000

Total Pages Processed (Data) : 254453

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 168678

Total Pages Failing (Index): 1

Total Pages Processed (Other): 13126

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 843743

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 81193602 (0.81193602)

BR0398E DBVERIFY detected corrupted blocks in /oracle/TS2/sapdata5/ts2_75/ts2.data75

Where are earlier problem was with "/oracle/TS2/sapdata3/ts2_73/ts2.data73 "

which now sees as

BR0395I Verifying /oracle/TS2/sapdata3/ts2_73/ts2.data73 using DBVERIFY...

BR0396I Verification of /oracle/TS2/sapdata2/ts2700_38/ts2700.data38 using DBVERIFY successful

BR0280I BRBACKUP time stamp: 2008-07-24 23.57.29

BR0063I 96 of 136 files processed - 787848.750 MB of 1133849.062 MB done

BR0204I Percentage done: 69.48%, estimated end time: 1:15

BR0001I ***********************************_______________

lbreddemann
Active Contributor
0 Kudos

Hello,

did you actually rebuild the affected index?

I would highly recommend to open a support message for this so that we can investigate what is causing this corruptions.

Usually corruptions do not 'wander' around.

I remember one problem with a very early patchlevel of oracle 10.2.0.2 when the new sort mechanism was active during the index creation... do you have installed all the required patches? If not - do that first (patchset 10.2.0.4 is available now...).

Ah, yes ... and make sure that db_block_checksum is either left to the default value or explicitely set to TRUE - otherwise DBVerify or RMAN don't have good chances to discover corruptions.

regards,

Lars

Former Member
0 Kudos

Hi!

Many thanks for the reply. My Oracle version is 10.2.0.2

From DB13 DBVerify log file I found:

BR0395I Verifying /oracle/SM2/sapdata5/sr3700_6/sr3700.data6 using DBVERIFY...

BR0278W Command output of '/oracle/SM2/102_32/bin/dbv file=/oracle/SM2/sapdata4/sr3_8/sr3.data8 blocksize=8192':

DBVERIFY: Release 10.2.0.2.0 - Production on Fri Nov 6 16:34:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/SM2/sapdata4/sr3_8/sr3.data8

Page 286214 is marked corrupt

Corrupt block relative dba: 0x02c45e06 (file 11, block 286214)

Completely zero block found during dbv:

Page 286215 is marked corrupt

Corrupt block relative dba: 0x02c45e07 (file 11, block 286215)

Completely zero block found during dbv:

Page 286216 is marked corrupt

Corrupt block relative dba: 0x02c45e08 (file 11, block 286216)

Completely zero block found during dbv:

Page 286217 is marked corrupt

Corrupt block relative dba: 0x02c45e09 (file 11, block 286217)

Completely zero block found during dbv:

Page 286218 is marked corrupt

Corrupt block relative dba: 0x02c45e0a (file 11, block 286218)

Completely zero block found during dbv:

Page 286219 is marked corrupt

...........

Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined : 394240

Total Pages Processed (Data) : 221693

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 99375

Total Pages Failing (Index): 0

Total Pages Processed (Other): 27915

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 44769

Total Pages Marked Corrupt : 488

Total Pages Influx : 0

Highest block SCN : 448786918 (0.448786918)

BR0398E DBVERIFY detected corrupted blocks in /oracle/SM2/sapdata4/sr3_8/sr3.data8

Question:

Does is these errors harmless and mean that this data file contains empty blocks?

How should I do to solve my problems here?

lbreddemann
Active Contributor
0 Kudos

> Many thanks for the reply. My Oracle version is 10.2.0.2

Change that!

10.2.0.4 should be used meanwhile!

> From DB13 DBVerify log file I found:

> BR0395I Verifying /oracle/SM2/sapdata5/sr3700_6/sr3700.data6 using DBVERIFY...

> BR0278W Command output of '/oracle/SM2/102_32/bin/dbv file=/oracle/SM2/sapdata4/sr3_8/sr3.data8 blocksize=8192':

> DBVERIFY: Release 10.2.0.2.0 - Production on Fri Nov 6 16:34:40 2009

> Copyright (c) 1982, 2005, Oracle. All rights reserved.

> DBVERIFY - Verification starting : FILE = /oracle/SM2/sapdata4/sr3_8/sr3.data8

> Page 286214 is marked corrupt

> Corrupt block relative dba: 0x02c45e06 (file 11, block 286214)

> Completely zero block found during dbv:

> Page 286215 is marked corrupt

> Corrupt block relative dba: 0x02c45e07 (file 11, block 286215)

> Completely zero block found during dbv:

> Page 286216 is marked corrupt

> Corrupt block relative dba: 0x02c45e08 (file 11, block 286216)

> Completely zero block found during dbv:

> Page 286217 is marked corrupt

> Corrupt block relative dba: 0x02c45e09 (file 11, block 286217)

> Completely zero block found during dbv:

> Page 286218 is marked corrupt

> Corrupt block relative dba: 0x02c45e0a (file 11, block 286218)

> Completely zero block found during dbv:

> Page 286219 is marked corrupt

> Question:

> Does is these errors harmless and mean that this data file contains empty blocks?

Well, harmless... hmm...

First of all: if these blocks aren't supposed to belong to any segment, then these corruptions won't hurt you.

In that sense they are harmless.

However, it's rather odd to find a whole range of blocks beeing completely zero - that means not even containing a block structure...

> How should I do to solve my problems here?

If you can live with the corruption warnings - leave them alone.

As soon as the blocks should be reused they will be reformatted anyhow.

If you want to get rid of the warnings. then a reorg is the only sure way to archieve this.

regards,

Lars

Former Member
0 Kudos

Dump file /oracle/TS2/saptrace/usertrace/ts2_ora_23103.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

ORACLE_HOME = /oracle/TS2/102_64

System name: SunOS

Node name: sassad25

Release: 5.10

Version: Generic_120011-14

Machine: sun4u

Instance name: TS2

Redo thread mounted by this instance: 1

Oracle process number: 53

Unix process pid: 23103, image: oracle@sassad25 (TNS V1-V3)

      • 2008-07-18 13:48:40.486

      • SERVICE NAME:(SYS$USERS) 2008-07-18 13:48:40.484

      • SESSION ID:(925.20292) 2008-07-18 13:48:40.484

Block Checking: DBA = 528925394, Block Type = KTB-managed data block

        • row 4: key out of order

-


end index block validation

-


for block 0x1f86c2d2

Block header dump: 0x1f86c2d2

Object id on Block? Y

seg/obj: 0x2c6f0 csc: 0x00.3f418d9 itc: 2 flg: E typ: 2 - INDEX

brn: 0 bdba: 0x1f86c00b ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0000.000.00000000 0x00000000.0000.00 -


0 fsc 0x0000.00000000

0x02 0x0002.008.00002cb6 0x02475283.0359.19 --U- 2 fsc 0x0000.03f418ee

Leaf block dump

===============

header address 17494483044=0x412c0a064

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 7

kdxcosdc 0

kdxconro 174

kdxcofbo 384=0x180

kdxcofeo 967=0x3c7

kdxcoavs 583

kdxlespl 0

kdxlenxt 528925395=0x1f86c2d3

kdxleprv 528925393=0x1f86c2d1

kdxledsz 6

kdxlebksz 8032

row#0[7990] flag: -


, lock: 0, len=42, data:(6): 1b ce 75 c6 00 15

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 31 31 30 30 69

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#1[7952] flag: -


, lock: 0, len=38, data:(6): 1c 46 88 34 00 0e

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 5; (5): 48 50 4c 4a 34

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#2[7913] flag: -


, lock: 0, len=39, data:(6): 1b 8f 2b bd 00 03

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 6; (6): 48 50 4c 4a 34 30

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#3[7871] flag: -


, lock: 0, len=42, data:(6): 20 03 18 b1 00 0a

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 00

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#4[7830] flag: -


, lock: 0, len=41, data:(6): 1b 4f 19 ef 00 0b

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 8; (8): 48 50 4c 4a 34 30 30 30

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#5[7788] flag: -


, lock: 0, len=42, data:(6): 21 03 15 12 00 02

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 31

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#6[7746] flag: -


, lock: 0, len=42, data:(6): 1c 86 83 6a 00 0c

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 37

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#7[7704] flag: -


, lock: 0, len=42, data:(6): 1b 4f 19 0f 00 02

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 44

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#8[7662] flag: -


, lock: 0, len=42, data:(6): 1f 03 50 f5 00 03

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 44

col 4; len 3; (3): 37 30 30

col 5; len 1; (1): 44

col 6; len 1; (1): 80

row#9[7619] flag: -


, lock: 0, len=43, data:(6): 1f 03 50 f5 00 04

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 44

col 4; len 3; (3): 37 30 30

col 5; len 1; (1): 44

col 6; len 2; (2): c1 02

row#10[7577] flag: -


, lock: 0, len=42, data:(6): 1f 43 21 d1 00 09

col 0; len 3; (3): 30 31 35

col 1; len 2; (2): 58 58

col 2; len 8; (8): 46 4f 4e 54 52 45 50 4c

col 3; len 9; (9): 48 50 4c 4a 34 30 30 30 45

col 4; len 3; (3): 34 36 43

col 5; len 1; (1): 44

lbreddemann
Active Contributor
0 Kudos

Hi there,

so you decided to go the hard way... well, ok.

But with the RMAN verification and the SQL statements from my blog you would have already fixed the index by now.

Let's see what we have here:


Block header dump:  0x1f86c2d2
 Object id on Block? Y
 seg/obj: 0x2c6f0  csc: 0x00.3f418d9  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1f86c00b ver: 0x01 opc: 0
     inc: 0  exflg: 0

Ok, we see again, that it is a INDEX segment block and we see that it belongs to object id 0x2c6f0 which is hexadecimal for 182000.

Now you run the following qery:

select owner, object_name, subobject_name from dba_objects

where object_id =182000 or data_object_id =182000;

As a result you should know which index or indexpartition is affected.

regards,

Lars

Former Member
0 Kudos

Thank you Sirs for your precious reply...

and sorry for the late response...

I have tried the sql script given by you and when I tired to find " OBJN " in the trace file , I don't find anything called OBJN in trace file..

please guide me accordingly

Thanks

Giridhar

stefan_koehler
Active Contributor
0 Kudos

Hello Giridhar,

please post the content of the tracefile here... i think that is the easiest way.

Regards

Stefan

lbreddemann
Active Contributor
0 Kudos

> DBVERIFY - Verification complete

> Total Pages Examined : 1280000

> Total Pages Processed (Data) : 248379

> Total Pages Failing (Data) : 0

> Total Pages Processed (Index): 180541

> Total Pages Failing (Index): 1

> Total Pages Processed (Other): 13272

> Total Pages Processed (Seg) : 0

> Total Pages Failing (Seg) : 0

> Total Pages Empty : 837808

> Total Pages Marked Corrupt : 0

> Total Pages Influx : 0

> Highest block SCN : 65006255 (0.65006255)

>

Hi there,

Stefan is right with his comment here (and thanks to Stefan for the accolade to the SAP Oracle Support ). A index block is the corrupted.

Perhabs my blog [Some hints to make handling of oracle datablock corruptions more efficient|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/9285] [original link is broken] [original link is broken] [original link is broken]; has some useful information for you to handle this problem.

best regards,

Lars