FIXING BLOCK CORRUPTION (RMAN) on 11G

Assume: BLOCK CORRUPTION and DATABASE ARCHIVELOG MODE.

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME

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

/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

$ dd if=/dev/zero of=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf bs=8k conv=notrunc seek=10 count=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.0631573 seconds, 130 kB/s

1. Using Data Recovery Advisor (by RMAN command)

Reference: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/backrec.htm#CNCPT1438

• list failure - to list any failures.

• advise failure - to display repair options for the specified failures.

• repair failure - to repair database failures identified.

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

1881       HIGH     OPEN      14-APR-10     Datafile 4: '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

1881       HIGH     OPEN      14-APR-10     Datafile 4: '/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=131 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

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

1      Perform block media recovery of block 10 in file 4

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /oracle/diag/rdbms/orcl/orcl/hm/reco_1500755688.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /oracle/diag/rdbms/orcl/orcl/hm/reco_1500755688.hm

contents of repair script:

   # block media recovery

   recover datafile 4 block 10;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script

Starting recover at 14-APR-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T085114_5wb7ws6f_.bkp

channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T085114_5wb7ws6f_.bkp tag=TAG20100414T085114

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

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

starting media recovery

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

Finished recover at 14-APR-10

repair failure complete

2. Using NONE Data Recovery Advisor

VERIFY:

- Using DB VERIFY

$ dbv  -help

DBVERIFY: Release 11.1.0.6.0 - Production on Wed Apr 14 04:25:48 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Keyword     Description                    (Default)

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

FILE        File to Verify                 (NONE)

START       Start Block                    (First Block of File)

END         End Block                      (Last Block of File)

BLOCKSIZE   Logical Block Size             (8192)

LOGFILE     Output Log                     (NONE)

FEEDBACK    Display Progress               (0)

PARFILE     Parameter File                 (NONE)

USERID      Username/Password              (NONE)

SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)

HIGH_SCN    Highest Block SCN To Verify    (NONE)

            (scn_wrap.scn_base OR scn)

$ dbv FILE=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf BLOCKSIZE=8192

DBVERIFY: Release 11.1.0.6.0 - Production on Wed Apr 14 04:26:36 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

Page 10 is marked corrupt

Corrupt block relative dba: 0x0100000a (file 4, block 10)

Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 35304

Total Pages Processed (Data) : 29789

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 2182

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 2254

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1078

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 909266 (0.909266)

- Using RMAN ***Assume: Using ARCHIVELOG Mode***

RMAN> backup validate tablespace USERS;

Starting backup at 14-APR-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4    FAILED 0              1078         35304           909266

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              29789

  Index      0              2182

  Other      1              2255

validate found one or more corrupt blocks

See trace file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7505.trc for details

Finished backup at 14-APR-10

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

         4         10          1                  0 ALL ZERO

FIX: ***Assume: Using ARCHIVELOG Mode***

RMAN> recover datafile 4 block 10;

Starting recover at 14-APR-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 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 00004

channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T041711_5w9qtxn2_.bkp

channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T041711_5w9qtxn2_.bkp tag=TAG20100414T041711

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

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

starting media recovery

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

Finished recover at 14-APR-10

RMAN> backup validate tablespace USERS;

Starting backup at 14-APR-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4    OK     0              1078         35304           909266

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              29789

  Index      0              2182

  Other      0              2255

Finished backup at 14-APR-10

Or Verify and then Fix

Command:

run{

BACKUP VALIDATE DATABASE;

BLOCKRECOVER CORRUPTION LIST;

}

Example:

$ dd if=/dev/zero of=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf bs=8k conv=notrunc seek=10 count=1

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 0.000707258 seconds, 11.6 MB/s

RMAN> run{

BACKUP VALIDATE DATABASE;

BLOCKRECOVER CORRUPTION LIST;

}

Starting backup at 14-APR-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf

input datafile file number=00001 name=/oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf

input datafile file number=00004 name=/oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

input datafile file number=00003 name=/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5twzp19d_.dbf

input datafile file number=00006 name=/oracle/oradata/ORCL/datafile/o1_mf_flow_1_5w23bgcm_.dbf

input datafile file number=00005 name=/oracle/oradata/ORCL/datafile/o1_mf_tbs_data_5v51k05o_.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

1    OK     0              13219        103680          1162051

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_system_5twzp126_.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              75573

  Index      0              12161

  Other      0              2727

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

2    OK     0              34420        117536          1162049

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_sysaux_5twzp15k_.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              21077

  Index      0              14799

  Other      0              47240

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

3    OK     0              112          33280           1162051

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5twzp19d_.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              0

  Index      0              0

  Other      0              33168

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

4    FAILED 0              1078         35304           909266

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_users_5twzp1c3_new.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              29789

  Index      0              2182

  Other      1              2255

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

5    OK     0              5903         12800           1107868

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_tbs_data_5v51k05o_.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              3585

  Index      0              3138

  Other      0              174

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

6    OK     0              12265        12808           1112803

  File Name: /oracle/oradata/ORCL/datafile/o1_mf_flow_1_5w23bgcm_.dbf

  Block Type Blocks Failing Blocks Processed

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

  Data       0              159

  Index      0              97

  Other      0              287

validate found one or more corrupt blocks

See trace file /oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7572.trc for details

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00

List of Control File and SPFILE

===============================

File Type    Status Blocks Failing Blocks Examined

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

SPFILE       OK     0              2

Control File OK     0              600

Finished backup at 14-APR-10

Starting recover at 14-APR-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T041711_5w9qtxn2_.bkp

channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/ORCL/backupset/2010_04_14/o1_mf_nnndf_TAG20100414T041711_5w9qtxn2_.bkp tag=TAG20100414T041711

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

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

starting media recovery

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

Finished recover at 14-APR-10

SQL> select * from v$database_block_corruption;

no rows selected

By: Surachart