Oracle DBA,RAC and APPS DBA Blog: repair failure

Data Recovery Advisor in RMAN

Connect to sqlplus and create any table.

Sql> create table myobjects tablespace users as select * from all_objects;

Using DBMS_ROWID, we determine the blocks which this table occupies (if you like, just restrict the query to the first 5 blocks in case the table contains many blocks)

SQL> select * from
2 (select distinct dbms_rowid.rowid_block_number(rowid)
3 from myobjects)
4 where rownum < 6;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
12
13
14
15
16

We can then corrupt any one of these blocks (in our case block 13) to simulate a block corruption - don't do this in production!

dd of=/u01/app/oracle9/oradata/SATISH/datafile / o1_mf_users_9gdfxf2t_.dbf bs=8192 seek=13 conv=notrunc count=1 if=/dev/zero

Now try to take backup of tablespace users. RMAN will validate corrupted blocks and it will not take backup.

[oracle9@fed01 datafile]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 18 12:09:23 2014

connected to target database: SATISH (DBID=3984685639)

RMAN> backup tablespace users;

Starting backup at 18-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=190 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=/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

channel ORA_DISK_1: starting piece 1 at 18-FEB-14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/18/2014 12:09:46

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

RMAN is unable to take backup of tablespace users as a block was corrupted .

Using Data Recovery Advisor we will recover corrupted block.

RMAN> list failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

562 HIGH OPEN 18-FEB-14 Datafile 4: '/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

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

Failure ID Priority Status Time Detected Summary

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

562 HIGH OPEN 18-FEB-14 Datafile 4: '/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

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 221 in file 4

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

Repair script: /u01/app/oracle9/diag/rdbms/satish/SATISH/hm/reco_1937462406.hm

RMAN> repair failure;

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

Repair script: /u01/app/oracle9/diag/rdbms/satish/SATISH/hm/reco_1937462406.hm

contents of repair script:

# block media recovery

recover datafile 4 block 221;

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

executing repair script

Starting recover at 18-FEB-14

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 /u01/app/oracle9/product/11.2.0/dbhome_1/dbs/07ovuei2_1_1

channel ORA_DISK_1: piece handle=/u01/app/oracle9/product/11.2.0/dbhome_1/dbs/07ovuei2_1_1 tag=TAG20140206T104938

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:07

Finished recover at 18-FEB-14

repair failure complete

RMAN> backup tablespace users;

Starting backup at 18-FEB-14

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=/u01/app/oracle9/oradata/SATISH/datafile/o1_mf_users_9gdfxf2t_.dbf

channel ORA_DISK_1: starting piece 1 at 18-FEB-14

channel ORA_DISK_1: finished piece 1 at 18-FEB-14

piece handle=/u01/app/oracle9/product/11.2.0/dbhome_1/dbs/0ep0u7sd_1_1 tag=TAG20140218T121157 comment=NONE

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

Finished backup at 18-FEB-14

Now RMAN successfully taken backup of tablespace users.

Monday, 17 February 2014

Data Recovery Advisor in RMAN