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.