Block corruption

     1)     Create a  tablespace

SQL> create tablespace abc datafile 'C:\APP\USER\ORADATA\MYDATABASE\abc.dbf' size 200k;

2) Connect the user create a table in above tablespaces

SQL> conn scott/tiger

Connected.

SQL> create table abc (name varchar(10)) tablespace abc;

Table created.

SQL> insert into abc values('currpt blk');

1 row created.

SQL> commit;

commit complete.

3) Take tablespace offline

SQL> conn / as sysdba

Connected.

SQL> alter tablespace abc offline;

Tablespace altered.

4) Open the datafile belongs to above tablespace and edit the file change 'currpt blk' to block

5)Change status of tablespace  online

SQL> alter tablespace abc online;

Tablespace altered.

6) Now  access the data from the table

SQL> conn scott/tiger

Connected.

SQL> select * from abc;

select * from abc

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: 'C:\APP\USER\ORADATA\MYDATABASE\ABC.DBF'

7)  Run the DBV utility find the corrupted block

\BIN>dbv   file =C:\APP\USER\ORADATA\MYDATABASE\abc.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Aug 10 17:41:13 2011

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

DBVERIFY - Verification starting : FILE = C:\APP\USER\ORADATA\MYDATABASE\ABC.DBF

Page 11 is marked corrupt

Corrupt block relative dba: 0x0140000b (file 5, block 11)

Bad check value found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x0140000b

 last change scn: 0x0000.001096fc seq: 0x1 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x96fc0601

 check value in block header: 0xe8f3

 computed block checksum: 0x1a00

DBVERIFY - Verification complete

Total Pages Examined         : 25

Total Pages Processed (Data) : 4

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 10

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 10

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1087227 (0.1087227)

8) Connect the Rman and recover the block using clockrecover command

C:\app\user\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 10 17:41:50 2011

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

connected to target database: MYDATABA (DBID=1269509988)

RMAN> blockrecover datafile 5 block 11;

Starting recover at 10-AUG-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=142 device type=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: reading from backup piece C:\APP\USER\FLASH_RECOVERY_AREA\MYDATABASE\BACKUPSET\2

011_08_10\O1_MF_NNNDF_TAG20110810T173411_744WXXQG_.BKP

channel ORA_DISK_1: piece handle=C:\APP\USER\FLASH_RECOVERY_AREA\MYDATABASE\BACKUPSET\2011_08_10\O1_

MF_NNNDF_TAG20110810T173411_744WXXQG_.BKP tag=TAG20110810T173411

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 10-AUG-11

RMAN>

You can find the  corrupt block for particular  object as given below

select tablespace_id,HEADER_FILE,header_block,segment_type from  SYS_dba_SEGS where segment_na

me like 'ABC'

SQL> /

TABLESPACE_ID HEADER_FILE HEADER_BLOCK SEGMENT_TYPE

------------- ----------- ------------ ------------------

            6           5         1547 TABLE

C:\oracle\product\10.2.0\db_1\BIN>dbv userid=scott/tiger segment_id=6.5.1547