Home > Oracle > RMAN Block Recovery using Physical database backup

RMAN Block Recovery using Physical database backup


Many big databases using storage level mirror and split method to backup as a RMAN backup might take long time to complete.  I have seen the traditional OS level hot backup is very much active in many places, specailly on large databases.  In olden days, recovering a database block using physical OS level backup was not that easy.  The DBA had to replace the entire data file with a ‘Good copy’  of data file rather just the block.  RMAN introduced the block level recovery.    

So this post explains how to recover a corrupted block using a OS level hot backup.

To begin with I have taken an OS level hot backup of the affected tablespace.  

SQL> ALTER TABLESPACE TBS01  BEGIN BACKUP;

Tablespace altered.

$ cp /oradata/ctgr/tbs01.dbf /oradata/ctgr/tbs01.dbf.bkp

$ ls -ltr /oradata/ctgr/tbs01.dbf.bkp
-rw-r—–   1 oracle   dba      52436992 Oct 20 11:03 /oradata/ctgr/tbs01.dbf.bkp
$  ls -ltr /oradata/ctgr/tbs01.dbf
-rw-r—–   1 oracle   dba      52436992 Oct 20 11:03 /oradata/ctgr/tbs01.dbf

 As usuall, corrupted block ( See my previous posts, hot to corrupt a block)

SQL> host dd of=/oradata/ctgr/tbs01.dbf bs=8192 seek=20 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
SQL> alter system flush buffer_cache;

System altered.

SQL>  select count(*) from test;
 select count(*) from test
                      *
ERROR at line 1:
ORA-08103: object no longer exists

 Well, we have the corrupt block in tablespace.   Let us try to recover the block  using RMAN 

$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Wed Oct 20 13:35:11 2010

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

connected to target database: CTGR (DBID=1764603925)

RMAN> blockrecover datafile 16 block 20;

Starting blockrecover at 20-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=38 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 10/20/2010 13:35:24
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore

As there are no RMAN backup or copy, RMAN is not able to do the block recovery.  But we know, we have the OS level hot backups.  Let us catalog the hot backup to RMAN. 

RMAN> catalog datafilecopy ‘/oradata/ctgr/tbs01.dbf.bkp’;

cataloged datafile copy
datafile copy filename=/oradata/ctgr/tbs01.dbf.bkp recid=3 stamp=732893807

 Let us try the block recovery again.

RMAN>  blockrecover datafile 16 block 20;

Starting blockrecover at 20-OCT-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /oradata/ctgr/tbs01.dbf.bkp

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

Finished blockrecover at 20-OCT-10

Thats it, we are done with the recovery and the table is back.

SQL> select count(*) from test;

  COUNT(*)
———-
         2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: