Block corruption

Backup and Recovery

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