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;
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
------------------------------------
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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.
No comments:
Post a Comment