Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos
  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.
2 Comments
Labels in this area