Identify the block Corruption using RMAN advisor

Identify the block Corruption using RMAN advisor

Rman validate command is used to identified the logical and physical corruption in the oracle database. You can also check ora-01578 error in alert log file. If any of your database block is corrupted you get message in alert log file.

Check the alert log file it will give you the error as follows:

ORA-01578: ORACLE data block corrupted (file # 9, block # 62904)
ORA-01110: data file 9: 'E:\ORACLE\ORADATA\IC\ICTABLES05.DBF'

For validate that any other block is corrupted except mentioned in the alert log file. You need to run the following command from the RMAN prompt. It will scan all datafiles and give you result in V$DATABASE_BLOCK_CORRUPTION view of Oracle.

Recovery Manager: Release - Production on Thu Apr 2 21:13:35 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys
target database Password:
connected to target database: ORCL12C (DBID=704940817)

This command start checking all the datafiles in oracle database and give you the result in log file as well as you can check at last V$DATABASE_BLOCK_CORRUPTION view from SQL PLUS prompt.

---   ----------   -------  ------------------ ---------
4     491669       1           0           CORRUPT

Step 3:
Get file# and block# corruption from above command. you can get the object name and object_type which is having issue with following commands

select substrb(dbe.owner,1,15) DB_OWNER,
substrb(dbe.segment_name,1,30 ) OBJ_NAME,
substrb(dbe.partition_name,1,20 ) PART_NAME,
substrb(dbe.segment_type,1,9) TYPE_object,
substrb(dbe.tablespace_name,1,15) TS_NAME
from dba_extents dbe
where dbe.file_id = 4
and 491669 between dbe.block_id and dbe.block_id+dbe.blocks-1

--------   ---------------  ---------   -------- ---------
MANA       TAXINQUIRY_LOG               INDEX          USERS

If you got object_type as INDEX. You can get the DDL of the index and drop it and create it again will solve the problem.

alter index index_name rebuild;

If Object_type as table then you try to recover with RMAN command and you can try to create copy of table to save data.

If no row return from upper query then follow the following link:

create table tablename_new as select * from table_name;

RMAN Advisory

You can check failure with RMAN Advisory, it will give you clear picture that you have available backup which can help to repair that block. Following command present in Oracle 11g.

1. List failure
2. Advise failure
For More Detail:

List Failure: command show you the corrupted block information
Advise Failure: command give you the suggestion for recovery if any backup is available.

Example for RMAN Advisory commands:

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
18822 HIGH OPEN 16-FEB-16 Datafile 2: 'E:\ORACLE\ORADATA\IC\SY
SAUX01.DBF' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
18822 HIGH OPEN 16-FEB-16 Datafile 2: 'E:\ORACLE\ORADATA\IC\SY
SAUX01.DBF' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
1. No backup of block 77810 in file 2 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
2. Contact Oracle Support Services if the preceding recommendations cannot be us
ed, or if they do not fix the failures selected for repair

Optional Manual Actions
no manual actions available

Automated Repair Options
no automatic repair options available


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.