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
