Let
us look at a test case where we corrupt from blocks in a particular datafile
and then use the RMAN blockrecover
command to recover the corrupted data blocks.
To
simulate a block corruption scenario, we will do the following:
- Create a table in
tablespace users
- Identify the blocks
belonging to that table
- Corrupt all or some of
those blocks using the Unix dd command.
- Flush the buffer cache
to ensure we read blocks from disk and not from memory(buffer cache)
- Verify block corruptions
from V$DATABASE_BLOCK_CORRUPTION
SQL>
create table mytab
2
tablespace users
3 as
select * from tab;
Table
created.
SQL>
select count(*) from mytab;
COUNT(*)
----------
183
SQL>
select * from
(select
distinct dbms_rowid.rowid_block_number(rowid)
2
3 from
mytab)
4
where rownum < 6;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
1027
sun01:/export/home/oracle
$ dd of=/u03/oradata/leventwo/users01.dbf bs=8192 seek=1027 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
sun01:/export/home/oracle
$ sqlplus system/manager
SQL*Plus:
Release 10.2.0.4.0 - Production on Fri Mar 18 09:34:53 2011
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
select count(*) from mytab;
COUNT(*)
----------
183
SQL>
alter system flush buffer_cache;
System
altered.
SQL>
/
System
altered.
SQL>
/
System
altered.
SQL>
select count(*) from mytab;
select
count(*) from mytab
*
ERROR
at line 1:
ORA-01578:
ORACLE data block corrupted (file # 4, block # 1027)
ORA-01110:
data file 4: '/u03/oradata/leventwo/users01.dbf'
SQL>
select * from v$database_block_corruption;
FILE#
BLOCK# BLOCKS
CORRUPTION_CHANGE# CORRUPTIO
----------
---------- ---------- ------------------ ---------
4
1027 1 0 ALL ZERO
We
can either now recover the corrupted blocks using the command
BLOCKRECOVER
DATAFILE 4, BLOCK 1027
Or,
if there are a number of data blocks which are corrupted, we can issue a single
command
BLOCKRECOVER CORRUPTION LIST
sun01:/export/home/oracle
$ rman target /
Recovery
Manager: Release 11.2.0.2.0 - Production on Fri Mar 18 09:36:51 2011
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: LEVENTWO (DBID=2678523375)
RMAN>
blockrecover corruption list;
Starting
recover at 18-MAR-11
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=214 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 /u02/oraback/leventwo/rman/1am7fiir_1_1
channel
ORA_DISK_1: piece handle=/u02/oraback/leventwo/rman/1am7fiir_1_1 tag=TAG20110317T193450
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-MAR-11
RMAN>
quit
Recovery
Manager complete.
sun01:/export/home/oracle
$ sqlplus system/manager
SQL*Plus:
Release 10.2.0.4.0 - Production on Fri Mar 18 09:37:36 2011
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$database_block_corruption;
no
rows selected
SQL>
select count(*) from mytab;
COUNT(*)
----------
183
Source: Internet
:)
ReplyDelete