Block Media Recovery using RMAN ( Oracle 10g)

ORA-01578: ORACLE data block corrupted (file # 19, block # 461509)
ORA-01110: data file 19: ‘E:\ORACLE\ORADATA\PLACID\CARA_NS.ORA’

You are getting the above error in Application. This means a block has been corrupted on the disk. In this scenario we had last nights RMAn backup. IF you do not have RMAN configured. Please make sure RMAN backups are properly configured. RMAN is one of the most powerful backup and recovery tool for ORacle database. And every DBA should be familiar with RMAN.

First we will check for the objects which has been corrupted using the below quiery. In our case it was an Index.

LOgin to SQL*PLUS as sysdba

sqlplus sys/****** as sysdba

+ Identify the object holding the corrupted block using the following query:

SQL> SELECT tablespace_name, segment_type, owner,
    segment_name
    FROM  dba_extents
    WHERE file_id = 19
    and 461509 between block_id AND block_id +  blocks – 1;

+ If the block belonged to an index segment, drop and recreate the index.

+ If the block belonged to a data segment either use Rman Blockrecover or restore and recover the complete datafile to fix the corruption.

TABLESPACE_NAME                SEGMENT_TYPE       OWNER
—————————— —————— ——————————

SEGMENT_NAME
——————————————————————————–

CAIND_TS                       INDEX              PLACID
CAOUT_L_ITEM_I

SQL>SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

no rows selected.

So now we will logon to RMAN on the server itself( No recovery catalog is used. The information is stored in control file itself) Another silver bullet is that always tag your RMAN backups. It is very helpful in recovery.
eg : – backup incremental level 0 TAG ‘DAILY_FULL_CARA’ database filesperset 4;

rman target sys/****

RMAN> BLOCKRECOVER DATAFILE 19 BLOCK 461509;

Starting blockrecover at 16-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=59 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 08/16/2011 09:40:54
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 19 found to restore

RMAN is not able to find the backup. So we will use the tag name and blockrecover for the entire corrupted blocks. To find out the tag name you can use list backup summary command;

RMAN> list backup summary;

RMAN> blockrecover corruption list from tag “DAILY_FULL_CARA”;

Starting blockrecover at 16-AUG-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed ti

Finished blockrecover at 16-AUG-11

RMAN> exit;

/*
We need to flush the buffer_cache because if the block 461509 is
in the buffer_cache already it will not be read from the data file.
*/

 SQL> alter system flush buffer_cache;

NOw restart the application. You would have succesfully restored the corrupted block and everything should be ok.

Advertisements

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