Skip to Content
  1. Identify corrupt data file by running DBVERIFY

Sample Output:

DBVERIFY: Release 9.2.0.3.0 – Production on Thu Aug 25 11:15:54 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
DBVERIFY – Verification starting : FILE = E:\xxxx\test.ORA
Page 48740 is marked corrupt    ***
Corrupt block relative dba: 0x01c0be64 (file 7, block 48740)
Bad check value found during dbv:
Data in bad block –
type: 0 format: 2 rdba: 0x0000be64
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
consistency value in tail: 0x00000001
check value in block header: 0xb964, computed block checksum: 0x2a5a
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY – Verification complete
Total Pages Examined         : 64000
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1751
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 45
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 62203
Total Pages Marked Corrupt   : 1

Note that Block 48740 is reported as corrupt in datafile 7.

  1. Verify by cross checking whether the corrupted block belongs to any segment

SQL> select segment_name, segment_type, owner

from dba_extents

where file_id = <Absolute file number>

and <corrupted block number> between block_id

and block_id + blocks -1;

  1. Check  whether the datafile is AUTOENTEND OFF, if not, please turn it OFF.
  2. Create dummy table other than SYS user.

  SQL> create table dbsnmp.s (

       n number,

       c varchar2(4000)

     ) nologging tablespace <tablespace name having the corrupt block>  ;

                                              

  1. Check whether the new table ‘S’ exists in the required tablespace

SQL> select segment_name,tablespace_name from user_segments

      where segment_name=’S’ ;

  1. create trigger as SYS to throw exception when the corrupted block is reused.

SQL>CREATE OR REPLACE TRIGGER corrupt_trigger

  AFTER INSERT ON scott.s

  REFERENCING OLD AS p_old NEW AS new_p

  FOR EACH ROW

DECLARE

  corrupt EXCEPTION;

BEGIN

  IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)

and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN

     RAISE corrupt;

  END IF;

EXCEPTION

  WHEN corrupt THEN

RAISE_APPLICATION_ERROR(-20000, ‘Corrupt block has been formatted’);

END;

/

  1. Check the size of corrupted block

SQL> Select BYTES from dba_free_space where file_id= <file_id> and <corrupt_block_number> between block_id and block_id + blocks -1;

     BYTES

———-

2415853568

  1. Convert the size of corrupted block in KB

BEGIN

for i in 1..7000000 loop

EXECUTE IMMEDIATE ‘alter table dbsnmp.s allocate extent (DATAFILE ‘||’datafilename.dbf ‘||’SIZE 65536 K) ‘;

end loop;

end ;

/

  1. Populate the table

BEGIN

FOR i IN 1..1000000000 LOOP

INSERT /*+ APPEND */ INTO dbsnmp.s select i, lpad(‘REFORMAT’,3092, ‘R’) from dual;

commit ;

END LOOP;

END;

/

  1. Drop Table & run dbv again.
  2. You will not see the error of block corrupted because they do not belong to any segment.
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Förster

    Your approach means data loss. Depending on the affected SAP table such a data loss could be anywhere between acceptable and a catastrophe. SAP’s recommendation on corrupt blocks is to fix the issue. You forgot to mention block media recovery as a really good approach (if you detect the corruption early enough).

    (0) 

Leave a Reply