Home » RDBMS Server » Backup & Recovery » Datafile recovery not repairing corrupted block (oracle 11g, OS-Linux)
Datafile recovery not repairing corrupted block [message #460507] Fri, 11 June 2010 09:57 Go to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi,
Two of the datafiles are experiencing data block corruption and recovery of the files not repairing the block corruption.

sql 'alter database datafile 2, 8 offline';
   restore datafile 2, 8;
   recover datafile 2, 8;
   sql 'alter database datafile 2, 8 online';



The media recovery finishes succesfully with no warnings.
But, after recovery when I try :

validate check logical datafile 8,2;



it shows again the block corruptions. I've tried recovery several times, and the same appears when I query V$database_block_corruption.
I also tried the same with
RMAN > Repair Failure
and the result is still the same.

I tried RMAN incremental and full after this and both of them failed

ORA-00600: internal error code, arguments: [17114], [0x2B58288E0BB8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17147], [0x2B58288E0BB8], [], [], [], [], [], []




It seems the datafiles are not recovered and the block corruption exists after restore and recovery.

One of these datafile is from sysaux. and the DB version is 11g standard edition.

BANNER
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production




Thanks

[Updated on: Fri, 11 June 2010 09:59]

Report message to a moderator

Re: Datafile recovery not repairing corrupted block [message #460508 is a reply to message #460507] Fri, 11 June 2010 10:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Talk to oracle support. This is a known bug.
Re: Datafile recovery not repairing corrupted block [message #460516 is a reply to message #460508] Fri, 11 June 2010 10:44 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi Mahesh,
Thanks for the info.

As an workaround; as RMAN backup is not possible, is it possible to build another DB will full expdp?
if yes, can you please shade some light on the steps can be followed to do the activity?

Thanks
Re: Datafile recovery not repairing corrupted block [message #460517 is a reply to message #460516] Fri, 11 June 2010 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which OS filename contains corrupt blocks?
Re: Datafile recovery not repairing corrupted block [message #460518 is a reply to message #460516] Fri, 11 June 2010 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> is it possible to build another DB will full expdp?
Would be.
Nothing special here.
Just export and import (exp or expdp) like any other database ( hopefully there are no issues exporting it).
Just google for examples.
http://wiki.oracle.com/page/Data+Pump+Export+%28expdp%29+and+Data+Pump+Import%28impdp%29
Re: Datafile recovery not repairing corrupted block [message #460520 is a reply to message #460517] Fri, 11 June 2010 11:05 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi,

One of is SYSAUX and another one is non system datafile.

Thanks
Re: Datafile recovery not repairing corrupted block [message #460523 is a reply to message #460520] Fri, 11 June 2010 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>One of is SYSAUX and another one is non system datafile.
what is fully qualified pathname of the files with corrupt data?
Re: Datafile recovery not repairing corrupted block [message #460524 is a reply to message #460518] Fri, 11 June 2010 11:10 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi Mahesh,

How about if I
1. Take SYSAUX and other corrupted datafile TBS export backup;
then
2. Drop the TBS
3. and import them on place.

will that help removing the block corruption?

Thanks.
Re: Datafile recovery not repairing corrupted block [message #460525 is a reply to message #460523] Fri, 11 June 2010 11:13 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi Blackswan,

Below is the path.

/u02/oradata/database/datafiles/o1_mf_sysaux_3ylo749k_.dbf
/u02/oradata/database/datafiles/schema1_DATA1.dbf


Thanks
Re: Datafile recovery not repairing corrupted block [message #460526 is a reply to message #460525] Fri, 11 June 2010 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
run dbv against "corrupt" file as shown below

bcm@bcm-laptop:~$ dbv USERID=dbadmin/admindb file='/u01/app/oracle/oradata/v112/sysaux01.dbf'

DBVERIFY: Release 11.2.0.1.0 - Production on Fri Jun 11 09:23:25 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/v112/sysaux01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 128000
Total Pages Processed (Data) : 34636
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 39996
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 9558
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 16206
Total Pages Processed (Seg)  : 4181
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23423
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 7286348 (0.7286348)
bcm@bcm-laptop:~$ dbv -help

DBVERIFY: Release 11.2.0.1.0 - Production on Fri Jun 11 09:23:55 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)           


a couple of points

1) DBV can identify the specific bad blocks within the datafile.
2) if you have the block number of a bad block you can determine which object, if any, uses that block
3) After knowing the object name, owner & type, in some cases that single object can be reconstructed manually.

4) AFAIK with V11 the SYSAUX tablespace is integral to the DBs operation.
5) do not believe you can remove all objects from it, drop it, & then rebuild SYSAUX

[Updated on: Fri, 11 June 2010 12:25]

Report message to a moderator

Re: Datafile recovery not repairing corrupted block [message #460529 is a reply to message #460526] Fri, 11 June 2010 12:35 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Block Checking: DBA = 8429678, Block Type = KTB-managed data block                                  
**** row 0: row length 8031 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 41070 failed with check code 6401                                                              
Block Checking: DBA = 8429679, Block Type = KTB-managed data block                                  
**** row 0: row length 32 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 41071 failed with check code 6401                                                              
Block Checking: DBA = 8429680, Block Type = KTB-managed data block                                  
**** row 0: row length 32 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 41072 failed with check code 6401                                                              
Block Checking: DBA = 8434585, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 45977 failed with check code 6401                                                              
Block Checking: DBA = 8434972, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46364 failed with check code 6401                                                              
Block Checking: DBA = 8435409, Block Type = KTB-managed data block                                  
**** row 0: row length 22 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46801 failed with check code 6401                                                              
Block Checking: DBA = 8435410, Block Type = KTB-managed data block                                  
**** row 0: row length 34 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46802 failed with check code 6401                                                              
Block Checking: DBA = 8435411, Block Type = KTB-managed data block                                  
**** row 0: row length 8790 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46803 failed with check code 6401                                                              
Block Checking: DBA = 8435412, Block Type = KTB-managed data block                                  
**** row 0: row length 654 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46804 failed with check code 6401                                                              
Block Checking: DBA = 8435415, Block Type = KTB-managed data block                                  
**** row 0: row length 4808 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46807 failed with check code 6401                                                              
Block Checking: DBA = 8435416, Block Type = KTB-managed data block                                  
**** row 0: row length 601 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 46808 failed with check code 6401                                                              
Block Checking: DBA = 8435871, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47263 failed with check code 6401                                                              
Block Checking: DBA = 8435917, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47309 failed with check code 6401                                                              
Block Checking: DBA = 8435919, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47311 failed with check code 6401                                                              
Block Checking: DBA = 8435943, Block Type = KTB-managed data block                                  
**** row 0: row length 26 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47335 failed with check code 6401                                                              
Block Checking: DBA = 8435945, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47337 failed with check code 6401                                                              
Block Checking: DBA = 8435959, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47351 failed with check code 6401                                                              
Block Checking: DBA = 8435962, Block Type = KTB-managed data block                                  
**** row 0: row length 1606 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47354 failed with check code 6401                                                              
Block Checking: DBA = 8435963, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47355 failed with check code 6401                                                              
Block Checking: DBA = 8435969, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47361 failed with check code 6401                                                              
Block Checking: DBA = 8435970, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47362 failed with check code 6401                                                              
Block Checking: DBA = 8435971, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47363 failed with check code 6401                                                              
Block Checking: DBA = 8435975, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47367 failed with check code 6401                                                              
Block Checking: DBA = 8436554, Block Type = KTB-managed data block                                  
**** row 0: row length 658 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47946 failed with check code 6401                                                              
Block Checking: DBA = 8436566, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47958 failed with check code 6401                                                              
Block Checking: DBA = 8436576, Block Type = KTB-managed data block                                  
**** row 0: row length 481 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47968 failed with check code 6401                                                              
Block Checking: DBA = 8436597, Block Type = KTB-managed data block                                  
**** row 0: row length 732 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 47989 failed with check code 6401                                                              
Block Checking: DBA = 8436613, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48005 failed with check code 6401                                                              
Block Checking: DBA = 8436616, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48008 failed with check code 6401                                                              
Block Checking: DBA = 8436625, Block Type = KTB-managed data block                                  
**** row 0: row length 391 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48017 failed with check code 6401                                                              
Block Checking: DBA = 8436629, Block Type = KTB-managed data block                                  
**** row 0: row length 66 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48021 failed with check code 6401                                                              
Block Checking: DBA = 8436633, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48025 failed with check code 6401                                                              
Block Checking: DBA = 8436636, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48028 failed with check code 6401                                                              
Block Checking: DBA = 8436640, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48032 failed with check code 6401                                                              
Block Checking: DBA = 8436644, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48036 failed with check code 6401                                                              
Block Checking: DBA = 8436648, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48040 failed with check code 6401                                                              
Block Checking: DBA = 8436655, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48047 failed with check code 6401                                                              
Block Checking: DBA = 8436656, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48048 failed with check code 6401                                                              
Block Checking: DBA = 8436660, Block Type = KTB-managed data block                                  
**** row 0: row length 390 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48052 failed with check code 6401                                                              
Block Checking: DBA = 8436664, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48056 failed with check code 6401                                                              
Block Checking: DBA = 8436675, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48067 failed with check code 6401                                                              
Block Checking: DBA = 8436676, Block Type = KTB-managed data block                                  
**** row 0: row length 811 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48068 failed with check code 6401                                                              
Block Checking: DBA = 8436680, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48072 failed with check code 6401                                                              
Block Checking: DBA = 8436682, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48074 failed with check code 6401                                                              
Block Checking: DBA = 8436683, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48075 failed with check code 6401                                                              
Block Checking: DBA = 8436684, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48076 failed with check code 6401                                                              
Block Checking: DBA = 8436686, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48078 failed with check code 6401                                                              
Block Checking: DBA = 8436687, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48079 failed with check code 6401                                                              
Block Checking: DBA = 8436691, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48083 failed with check code 6401                                                              
Block Checking: DBA = 8436693, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48085 failed with check code 6401                                                              
Block Checking: DBA = 8436695, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48087 failed with check code 6401                                                              
Block Checking: DBA = 8436701, Block Type = KTB-managed data block                                  
**** row 0: row length 736 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48093 failed with check code 6401                                                              
Block Checking: DBA = 8436702, Block Type = KTB-managed data block                                  
**** row 0: row length 368 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48094 failed with check code 6401                                                              
Block Checking: DBA = 8436705, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48097 failed with check code 6401                                                              
Block Checking: DBA = 8436706, Block Type = KTB-managed data block                                  
**** row 0: row length 66 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48098 failed with check code 6401                                                              
Block Checking: DBA = 8436707, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48099 failed with check code 6401                                                              
Block Checking: DBA = 8436708, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48100 failed with check code 6401                                                              
Block Checking: DBA = 8436709, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48101 failed with check code 6401                                                              
Block Checking: DBA = 8436711, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48103 failed with check code 6401                                                              
Block Checking: DBA = 8436712, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48104 failed with check code 6401                                                              
Block Checking: DBA = 8436713, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48105 failed with check code 6401                                                              
Block Checking: DBA = 8436715, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48107 failed with check code 6401                                                              
Block Checking: DBA = 8436716, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48108 failed with check code 6401                                                              
Block Checking: DBA = 8436717, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48109 failed with check code 6401                                                              
Block Checking: DBA = 8436719, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48111 failed with check code 6401                                                              
Block Checking: DBA = 8436725, Block Type = KTB-managed data block                                  
**** row 0: row length 95 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48117 failed with check code 6401                                                              
Block Checking: DBA = 8436727, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48119 failed with check code 6401                                                              
Block Checking: DBA = 8436729, Block Type = KTB-managed data block                                  
**** row 0: row length 520 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48121 failed with check code 6401                                                              
Block Checking: DBA = 8436733, Block Type = KTB-managed data block                                  
**** row 0: row length 66 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48125 failed with check code 6401                                                              
Block Checking: DBA = 8436737, Block Type = KTB-managed data block                                  
**** row 0: row length 12 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48129 failed with check code 6401                                                              
Block Checking: DBA = 8436741, Block Type = KTB-managed data block                                  
**** row 0: row length 605 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 48133 failed with check code 6401                                                              
Block Checking: DBA = 8476061, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 87453 failed with check code 6401                                                              
Block Checking: DBA = 8476062, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 87454 failed with check code 6401                                                              
Block Checking: DBA = 8476064, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 87456 failed with check code 6401                                                              
Block Checking: DBA = 8476421, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 87813 failed with check code 6401                                                              
Block Checking: DBA = 8476422, Block Type = KTB-managed data block                                  
**** row 0: row length 632 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 87814 failed with check code 6401                                                              
Block Checking: DBA = 8476926, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 88318 failed with check code 6401                                                              
Block Checking: DBA = 8477109, Block Type = KTB-managed data block                                  
**** row 0: row length 19 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 88501 failed with check code 6401                                                              
Block Checking: DBA = 8477111, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 88503 failed with check code 6401                                                              
Block Checking: DBA = 8489060, Block Type = KTB-managed data block                                  
**** row 0: row length 21 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 100452 failed with check code 6401                                                             
Block Checking: DBA = 8489061, Block Type = KTB-managed data block                                  
**** row 0: row length 7924 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 100453 failed with check code 6401                                                             
Block Checking: DBA = 8489062, Block Type = KTB-managed data block                                  
**** row 0: row length 4182 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 100454 failed with check code 6401                                                             
Block Checking: DBA = 8489063, Block Type = KTB-managed data block                                  
**** row 0: row length 34 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 100455 failed with check code 6401                                                             
Block Checking: DBA = 8489064, Block Type = KTB-managed data block                                  
**** row 0: row length 196 past end of block                                                        
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 100456 failed with check code 6401                                                             
Block Checking: DBA = 8495743, Block Type = KTB-managed data block                                  
**** row 0: row length 14 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 107135 failed with check code 6401                                                             
Block Checking: DBA = 8496689, Block Type = KTB-managed data block                                  
**** row 0: row length 39 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108081 failed with check code 6401                                                             
Block Checking: DBA = 8496690, Block Type = KTB-managed data block                                  
**** row 0: row length 28 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108082 failed with check code 6401                                                             
Block Checking: DBA = 8496691, Block Type = KTB-managed data block                                  
**** row 0: row length 39 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108083 failed with check code 6401                                                             
Block Checking: DBA = 8496692, Block Type = KTB-managed data block                                  
**** row 0: row length 39 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108084 failed with check code 6401                                                             
Block Checking: DBA = 8496693, Block Type = KTB-managed data block                                  
**** row 0: row length 29 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108085 failed with check code 6401                                                             
Block Checking: DBA = 8496694, Block Type = KTB-managed data block                                  
**** row 0: row length 41 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108086 failed with check code 6401                                                             
Block Checking: DBA = 8496695, Block Type = KTB-managed data block                                  
**** row 0: row length 39 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108087 failed with check code 6401                                                             
Block Checking: DBA = 8496696, Block Type = KTB-managed data block                                  
**** row 0: row length 29 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 108088 failed with check code 6401                                                             
Block Checking: DBA = 8512092, Block Type = KTB-managed data block                                  
**** row 0: row length 29 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 123484 failed with check code 6401                                                             
Block Checking: DBA = 8512093, Block Type = KTB-managed data block                                  
**** row 0: row length 34 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 123485 failed with check code 6401                                                             
Block Checking: DBA = 8512094, Block Type = KTB-managed data block                                  
**** row 0: row length 39 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 123486 failed with check code 6401                                                             
Block Checking: DBA = 8512095, Block Type = KTB-managed data block                                  
**** row 0: row length 8565 past end of block                                                       
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 123487 failed with check code 6401                                                             
Block Checking: DBA = 8512096, Block Type = KTB-managed data block                                  
**** row 0: row length 41 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 123488 failed with check code 6401                                                             
Block Checking: DBA = 8518146, Block Type = KTB-managed data block                                  
**** row 0: row length 29 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 129538 failed with check code 6401                                                             
Block Checking: DBA = 8518147, Block Type = KTB-managed data block                                  
**** row 0: row length 41 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 129539 failed with check code 6401                                                             
Block Checking: DBA = 8518148, Block Type = KTB-managed data block                                  
**** row 0: row length 28 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 129540 failed with check code 6401                                                             
Block Checking: DBA = 8518149, Block Type = KTB-managed data block                                  
**** row 0: row length 41 past end of block                                                         
**** row 0: row skipped so other stats may be wrong                                                 
---- end index block validation                                                                     
Page 129541 failed with check code 6401                                                             
Block Checking: DBA = 8518150, Block Type = KTB-managed data block
**** row 0: row length 39 past end of block
**** row 0: row skipped so other stats may be wrong
---- end index block validation
Page 129542 failed with check code 6401
Block Checking: DBA = 8518151, Block Type = KTB-managed data block
**** row 0: row length 39 past end of block
**** row 0: row skipped so other stats may be wrong
---- end index block validation
Page 129543 failed with check code 6401
Block Checking: DBA = 8518152, Block Type = KTB-managed data block
**** row 0: row length 29 past end of block
**** row 0: row skipped so other stats may be wrong
---- end index block validation
Page 129544 failed with check code 6401


DBVERIFY - Verification complete

Total Pages Examined         : 256000
Total Pages Processed (Data) : 57850
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 68218
Total Pages Failing   (Index): 105
Total Pages Processed (Lob)  : 2864
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 100967
Total Pages Processed (Seg)  : 5865
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 20236
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2181152757 (2.2181152757)
Re: Datafile recovery not repairing corrupted block [message #460530 is a reply to message #460529] Fri, 11 June 2010 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
check this metalink note: 28814.1 - Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Re: Datafile recovery not repairing corrupted block [message #460531 is a reply to message #460530] Fri, 11 June 2010 13:13 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi BlackSwan,

I followed the steps mentioned in the metalink. This is what I mentioned. After recovery without any warnings; it still shows that the blocks are corrupted. And thus rman inc/full cannot be performed as well.
Re: Datafile recovery not repairing corrupted block [message #460533 is a reply to message #460531] Fri, 11 June 2010 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE TABLE BAD_BLOCK (ID NUMBER);
INSERT INTO BAD_BLOCK VALUES(8429678);
INSERT INTO BAD_BLOCK VALUES(8429679);
INSERT INTO BAD_BLOCK VALUES(8429680);
INSERT INTO BAD_BLOCK VALUES(8434585);
INSERT INTO BAD_BLOCK VALUES(8434972);
INSERT INTO BAD_BLOCK VALUES(8435409);
INSERT INTO BAD_BLOCK VALUES(8435410);
INSERT INTO BAD_BLOCK VALUES(8435411);
INSERT INTO BAD_BLOCK VALUES(8435412);
INSERT INTO BAD_BLOCK VALUES(8435415);
INSERT INTO BAD_BLOCK VALUES(8435416);
INSERT INTO BAD_BLOCK VALUES(8435871);
INSERT INTO BAD_BLOCK VALUES(8435917);
INSERT INTO BAD_BLOCK VALUES(8435919);
INSERT INTO BAD_BLOCK VALUES(8435943);
INSERT INTO BAD_BLOCK VALUES(8435945);
INSERT INTO BAD_BLOCK VALUES(8435959);
INSERT INTO BAD_BLOCK VALUES(8435962);
INSERT INTO BAD_BLOCK VALUES(8435963);
INSERT INTO BAD_BLOCK VALUES(8435969);
INSERT INTO BAD_BLOCK VALUES(8435970);
INSERT INTO BAD_BLOCK VALUES(8435971);
INSERT INTO BAD_BLOCK VALUES(8435975);
INSERT INTO BAD_BLOCK VALUES(8436554);
INSERT INTO BAD_BLOCK VALUES(8436566);
INSERT INTO BAD_BLOCK VALUES(8436576);
INSERT INTO BAD_BLOCK VALUES(8436597);
INSERT INTO BAD_BLOCK VALUES(8436613);
INSERT INTO BAD_BLOCK VALUES(8436616);
INSERT INTO BAD_BLOCK VALUES(8436625);
INSERT INTO BAD_BLOCK VALUES(8436629);
INSERT INTO BAD_BLOCK VALUES(8436633);
INSERT INTO BAD_BLOCK VALUES(8436636);
INSERT INTO BAD_BLOCK VALUES(8436640);
INSERT INTO BAD_BLOCK VALUES(8436644);
INSERT INTO BAD_BLOCK VALUES(8436648);
INSERT INTO BAD_BLOCK VALUES(8436655);
INSERT INTO BAD_BLOCK VALUES(8436656);
INSERT INTO BAD_BLOCK VALUES(8436660);
INSERT INTO BAD_BLOCK VALUES(8436664);
INSERT INTO BAD_BLOCK VALUES(8436675);
INSERT INTO BAD_BLOCK VALUES(8436676);
INSERT INTO BAD_BLOCK VALUES(8436680);
INSERT INTO BAD_BLOCK VALUES(8436682);
INSERT INTO BAD_BLOCK VALUES(8436683);
INSERT INTO BAD_BLOCK VALUES(8436684);
INSERT INTO BAD_BLOCK VALUES(8436686);
INSERT INTO BAD_BLOCK VALUES(8436687);
INSERT INTO BAD_BLOCK VALUES(8436691);
INSERT INTO BAD_BLOCK VALUES(8436693);
INSERT INTO BAD_BLOCK VALUES(8436695);
INSERT INTO BAD_BLOCK VALUES(8436701);
INSERT INTO BAD_BLOCK VALUES(8436702);
INSERT INTO BAD_BLOCK VALUES(8436705);
INSERT INTO BAD_BLOCK VALUES(8436706);
INSERT INTO BAD_BLOCK VALUES(8436707);
INSERT INTO BAD_BLOCK VALUES(8436708);
INSERT INTO BAD_BLOCK VALUES(8436709);
INSERT INTO BAD_BLOCK VALUES(8436711);
INSERT INTO BAD_BLOCK VALUES(8436712);
INSERT INTO BAD_BLOCK VALUES(8436713);
INSERT INTO BAD_BLOCK VALUES(8436715);
INSERT INTO BAD_BLOCK VALUES(8436716);
INSERT INTO BAD_BLOCK VALUES(8436717);
INSERT INTO BAD_BLOCK VALUES(8436719);
INSERT INTO BAD_BLOCK VALUES(8436725);
INSERT INTO BAD_BLOCK VALUES(8436727);
INSERT INTO BAD_BLOCK VALUES(8436729);
INSERT INTO BAD_BLOCK VALUES(8436733);
INSERT INTO BAD_BLOCK VALUES(8436737);
INSERT INTO BAD_BLOCK VALUES(8436741);
INSERT INTO BAD_BLOCK VALUES(8476061);
INSERT INTO BAD_BLOCK VALUES(8476062);
INSERT INTO BAD_BLOCK VALUES(8476064);
INSERT INTO BAD_BLOCK VALUES(8476421);
INSERT INTO BAD_BLOCK VALUES(8476422);
INSERT INTO BAD_BLOCK VALUES(8476926);
INSERT INTO BAD_BLOCK VALUES(8477109);
INSERT INTO BAD_BLOCK VALUES(8477111);
INSERT INTO BAD_BLOCK VALUES(8489060);
INSERT INTO BAD_BLOCK VALUES(8489061);
INSERT INTO BAD_BLOCK VALUES(8489062);
INSERT INTO BAD_BLOCK VALUES(8489063);
INSERT INTO BAD_BLOCK VALUES(8489064);
INSERT INTO BAD_BLOCK VALUES(8495743);
INSERT INTO BAD_BLOCK VALUES(8496689);
INSERT INTO BAD_BLOCK VALUES(8496690);
INSERT INTO BAD_BLOCK VALUES(8496691);
INSERT INTO BAD_BLOCK VALUES(8496692);
INSERT INTO BAD_BLOCK VALUES(8496693);
INSERT INTO BAD_BLOCK VALUES(8496694);
INSERT INTO BAD_BLOCK VALUES(8496695);
INSERT INTO BAD_BLOCK VALUES(8496696);
INSERT INTO BAD_BLOCK VALUES(8512092);
INSERT INTO BAD_BLOCK VALUES(8512093);
INSERT INTO BAD_BLOCK VALUES(8512094);
INSERT INTO BAD_BLOCK VALUES(8512095);
INSERT INTO BAD_BLOCK VALUES(8512096);
INSERT INTO BAD_BLOCK VALUES(8518146);
INSERT INTO BAD_BLOCK VALUES(8518147);
INSERT INTO BAD_BLOCK VALUES(8518148);
INSERT INTO BAD_BLOCK VALUES(8518149);
INSERT INTO BAD_BLOCK VALUES(8518150);
INSERT INTO BAD_BLOCK VALUES(8518151);
INSERT INTO BAD_BLOCK VALUES(8518152);
INSERT INTO BAD_BLOCK VALUES(8429678);
INSERT INTO BAD_BLOCK VALUES(8429679);
INSERT INTO BAD_BLOCK VALUES(8429680);
INSERT INTO BAD_BLOCK VALUES(8434585);
INSERT INTO BAD_BLOCK VALUES(8434972);
INSERT INTO BAD_BLOCK VALUES(8435409);
INSERT INTO BAD_BLOCK VALUES(8435410);
INSERT INTO BAD_BLOCK VALUES(8435411);
INSERT INTO BAD_BLOCK VALUES(8435412);
INSERT INTO BAD_BLOCK VALUES(8435415);
INSERT INTO BAD_BLOCK VALUES(8435416);
INSERT INTO BAD_BLOCK VALUES(8435871);
INSERT INTO BAD_BLOCK VALUES(8435917);
INSERT INTO BAD_BLOCK VALUES(8435919);
INSERT INTO BAD_BLOCK VALUES(8435943);
INSERT INTO BAD_BLOCK VALUES(8435945);
INSERT INTO BAD_BLOCK VALUES(8435959);
INSERT INTO BAD_BLOCK VALUES(8435962);
INSERT INTO BAD_BLOCK VALUES(8435963);
INSERT INTO BAD_BLOCK VALUES(8435969);
INSERT INTO BAD_BLOCK VALUES(8435970);
INSERT INTO BAD_BLOCK VALUES(8435971);
INSERT INTO BAD_BLOCK VALUES(8435975);
INSERT INTO BAD_BLOCK VALUES(8436554);
INSERT INTO BAD_BLOCK VALUES(8436566);
INSERT INTO BAD_BLOCK VALUES(8436576);
INSERT INTO BAD_BLOCK VALUES(8436597);
INSERT INTO BAD_BLOCK VALUES(8436613);
INSERT INTO BAD_BLOCK VALUES(8436616);
INSERT INTO BAD_BLOCK VALUES(8436625);
INSERT INTO BAD_BLOCK VALUES(8436629);
INSERT INTO BAD_BLOCK VALUES(8436633);
INSERT INTO BAD_BLOCK VALUES(8436636);
INSERT INTO BAD_BLOCK VALUES(8436640);
INSERT INTO BAD_BLOCK VALUES(8436644);
INSERT INTO BAD_BLOCK VALUES(8436648);
INSERT INTO BAD_BLOCK VALUES(8436655);
INSERT INTO BAD_BLOCK VALUES(8436656);
INSERT INTO BAD_BLOCK VALUES(8436660);
INSERT INTO BAD_BLOCK VALUES(8436664);
INSERT INTO BAD_BLOCK VALUES(8436675);
INSERT INTO BAD_BLOCK VALUES(8436676);
INSERT INTO BAD_BLOCK VALUES(8436680);
INSERT INTO BAD_BLOCK VALUES(8436682);
INSERT INTO BAD_BLOCK VALUES(8436683);
INSERT INTO BAD_BLOCK VALUES(8436684);
INSERT INTO BAD_BLOCK VALUES(8436686);
INSERT INTO BAD_BLOCK VALUES(8436687);
INSERT INTO BAD_BLOCK VALUES(8436691);
INSERT INTO BAD_BLOCK VALUES(8436693);
INSERT INTO BAD_BLOCK VALUES(8436695);
INSERT INTO BAD_BLOCK VALUES(8436701);
INSERT INTO BAD_BLOCK VALUES(8436702);
INSERT INTO BAD_BLOCK VALUES(8436705);
INSERT INTO BAD_BLOCK VALUES(8436706);
INSERT INTO BAD_BLOCK VALUES(8436707);
INSERT INTO BAD_BLOCK VALUES(8436708);
INSERT INTO BAD_BLOCK VALUES(8436709);
INSERT INTO BAD_BLOCK VALUES(8436711);
INSERT INTO BAD_BLOCK VALUES(8436712);
INSERT INTO BAD_BLOCK VALUES(8436713);
INSERT INTO BAD_BLOCK VALUES(8436715);
INSERT INTO BAD_BLOCK VALUES(8436716);
INSERT INTO BAD_BLOCK VALUES(8436717);
INSERT INTO BAD_BLOCK VALUES(8436719);
INSERT INTO BAD_BLOCK VALUES(8436725);
INSERT INTO BAD_BLOCK VALUES(8436727);
INSERT INTO BAD_BLOCK VALUES(8436729);
INSERT INTO BAD_BLOCK VALUES(8436733);
INSERT INTO BAD_BLOCK VALUES(8436737);
INSERT INTO BAD_BLOCK VALUES(8436741);
INSERT INTO BAD_BLOCK VALUES(8476061);
INSERT INTO BAD_BLOCK VALUES(8476062);
INSERT INTO BAD_BLOCK VALUES(8476064);
INSERT INTO BAD_BLOCK VALUES(8476421);
INSERT INTO BAD_BLOCK VALUES(8476422);
INSERT INTO BAD_BLOCK VALUES(8476926);
INSERT INTO BAD_BLOCK VALUES(8477109);
INSERT INTO BAD_BLOCK VALUES(8477111);
INSERT INTO BAD_BLOCK VALUES(8489060);
INSERT INTO BAD_BLOCK VALUES(8489061);
INSERT INTO BAD_BLOCK VALUES(8489062);
INSERT INTO BAD_BLOCK VALUES(8489063);
INSERT INTO BAD_BLOCK VALUES(8489064);
INSERT INTO BAD_BLOCK VALUES(8495743);
INSERT INTO BAD_BLOCK VALUES(8496689);
INSERT INTO BAD_BLOCK VALUES(8496690);
INSERT INTO BAD_BLOCK VALUES(8496691);
INSERT INTO BAD_BLOCK VALUES(8496692);
INSERT INTO BAD_BLOCK VALUES(8496693);
INSERT INTO BAD_BLOCK VALUES(8496694);
INSERT INTO BAD_BLOCK VALUES(8496695);
INSERT INTO BAD_BLOCK VALUES(8496696);
INSERT INTO BAD_BLOCK VALUES(8512092);
INSERT INTO BAD_BLOCK VALUES(8512093);
INSERT INTO BAD_BLOCK VALUES(8512094);
INSERT INTO BAD_BLOCK VALUES(8512095);
INSERT INTO BAD_BLOCK VALUES(8512096);
INSERT INTO BAD_BLOCK VALUES(8518146);
INSERT INTO BAD_BLOCK VALUES(8518147);
INSERT INTO BAD_BLOCK VALUES(8518148);
INSERT INTO BAD_BLOCK VALUES(8518149);
INSERT INTO BAD_BLOCK VALUES(8518150);
INSERT INTO BAD_BLOCK VALUES(8518151);
INSERT INTO BAD_BLOCK VALUES(8518152);
COMMIT;
SELECT DISTINCT owner, segment_name, segment_type
from dba_extents A
where file_id = 2
and block_id IN (select block_id from dba_extents B, bad_block where id between B.block_id and B.block_id+B.blocks)
/

post results from SELECT above
Re: Datafile recovery not repairing corrupted block [message #460765 is a reply to message #460533] Mon, 14 June 2010 10:41 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi BlackSwan,

I tried it but returned no rows. So I tried the following:

declare
nrows number;
badsegs number;
begin
badsegs:=0;
nrows:=0;
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from 
dba_extents where file_id=i.file# and i.block# between block_id and block_id + 
blocks -1;
end;
nrows:=nrows+1;
end loop;
commit;
if nrows>0 then
dbms_output.put_line('List of segments affected by corruption');
end if;
for i in (select distinct owner, segment_name, segment_type from 
segment_corrupted) loop
dbms_output.put_line(i.segment_type||' '||i.owner||'.'||i.segment_name);
badsegs:=badsegs+1;
end loop;
dbms_output.put_line('Total blocks corrupted: '||to_char(nrows)||'. Total 
segments affected: '||to_char(badsegs));
end;
/

select * from segment_corrupted


And below is the output:

OWNER	SEGMENT_NAME	SEGMENT_TYPE
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_ECM_OS_REGISTERED_SW_COMP	TABLE
SYSMAN	MGMT_DB_INIT_PARAMS_ECM_PK	INDEX
SYSMAN	MGMT_DB_INIT_PARAMS_ECM_PK	INDEX
SYSMAN	MGMT_METRICS_RAW_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_DB_INIT_PARAMS_ECM_PK	INDEX
SYSMAN	MGMT_DB_INIT_PARAMS_ECM_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_DB_INIT_PARAMS_ECM_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX
SYSMAN	PK_MGMT_CRED_SETS	INDEX
SYSMAN	PK_MGMT_CRED_SETS	INDEX
SYSMAN	SYS_IOT_OVER_232291	TABLE
SYSMAN	SYS_IOT_OVER_232297	TABLE
SYSMAN	PK_MGMT_CONTAINER_CREDS	INDEX
SYSMAN	PK_MGMT_CONTAINER_CREDS	INDEX
SYSMAN	MGMT_BUG_AVAILABLE_PATCH	TABLE
SYSMAN	MGMT_METRICS_1HOUR_PK	INDEX


All are IOT and indexes.
Re: Datafile recovery not repairing corrupted block [message #461362 is a reply to message #460533] Thu, 17 June 2010 09:19 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi Blackswan,

Any suggession on the corruption list?

Thanks
Re: Datafile recovery not repairing corrupted block [message #461363 is a reply to message #461362] Thu, 17 June 2010 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any suggession on the corruption list?

ALTER INDEX REBUILD
Re: Datafile recovery not repairing corrupted block [message #461393 is a reply to message #461363] Thu, 17 June 2010 11:57 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi Blackswan,

I attempted this earlier, but the problem is IOT.
it throws the following error:

ORA-28650: Primary index on an IOT cannot be rebuilt 


Thanks
Re: Datafile recovery not repairing corrupted block [message #461394 is a reply to message #461393] Thu, 17 June 2010 12:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml

URL above provides steps to remove & re-create SYSMAN schema.
I suspect this system has hardware or OS flaws & I would not trust it to run any Production DB
Re: Datafile recovery not repairing corrupted block [message #461409 is a reply to message #461394] Thu, 17 June 2010 14:22 Go to previous message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member


Thanks for the nice link. I'll try that on test and also
I agree with not executing the same in production.

Thanks for your continuous help.

Previous Topic: cold backup recovery fails for database in noarchivelog mode
Next Topic: Archivelogs needed for Incomplete Recovery
Goto Forum:
  


Current Time: Fri Mar 29 10:58:15 CDT 2024