Database corrupt block recovery using RMAN

1 Introduction

The next document will describe the steps to recover corrupt block using RMAN

 

2. Recover block if datafile header is corrupted

 

2.1. Please check the physical corruption

 

[oracle@cdv1pradmdbv01 datafile]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 12:55:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/26/2017 12:55:10
RMAN-06056: could not access datafile 5

 

2.2. Restore and recover the particular tablespace (For example test1)

 

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> restore tablespace test1;
5> recover tablespace test1;
6> alter tablespace test1 online;
7> }

released channel: ORA_DISK_1
allocated channel: dev_0
channel dev_0: SID=28 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 26-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

 

 

2.3. Now please validate after restore and recover (For example test1)

 

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 12673 12800 1510201
File Name: /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

Finished backup at 26-JUN-17

 

 

3.  Recover corrupt block (Logical corruption)

 

3.1  Detect the logical corruption

 

 RMAN> backup database;

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/23/2017 11:27:36
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/EAMDB01/DATAFILE/system.266.926328081

$ dbv file=+DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY: Release 12.1.0.2.0 – Production on Fri Jun 23 11:33:22 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = +DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY – Verification complete

Total Pages Examined : 262144
Total Pages Processed (Data) : 70470
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15803
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6573
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 169298
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

 

3.2 Validate using RMAN

 

 

  • Validate the datafile using RMAN:RMAN> backup validate check logical datafile 1;2) After completion of RMAN command, provide output from Sqlplus on connection AS SYSDBA:select * from v$database_block_corruption ;set pagesize 2000
    set linesize 280
    SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
    , greatest(e.block_id, c.block#) corr_start_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
    – greatest(e.block_id, c.block#) + 1 blocks_corrupted
    , corruption_type description
    FROM dba_extents e, v$database_block_corruption c
    WHERE e.file_id = c.file#
    AND e.block_id <= c.block# + c.blocks – 1
    AND e.block_id + e.blocks – 1 >= c.block#
    UNION
    SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
    , header_block corr_start_block#
    , header_block corr_end_block#
    , 1 blocks_corrupted
    , corruption_type||’ Segment Header’ description
    FROM dba_segments s, v$database_block_corruption c
    WHERE s.header_file = c.file#
    AND s.header_block between c.block# and c.block# + c.blocks – 1
    UNION
    SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
    , greatest(f.block_id, c.block#) corr_start_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
    – greatest(f.block_id, c.block#) + 1 blocks_corrupted
    , ‘Free Block’ description
    FROM dba_free_space f, v$database_block_corruption c
    WHERE f.file_id = c.file#
    AND f.block_id <= c.block# + c.blocks – 1
    AND f.block_id + f.blocks – 1 >= c.block#
    order by file#, corr_start_block#;

 

 

SQL> select * from v$database_block_corruption ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
———- ———- ———- —————— ——— ———-
5 341508 1 0 CORRUPT 0
3 117889 1 0 FRACTURED 0
4 456056 1 0 FRACTURED 0

OWNER SEGMENT_TYPE SEGMENT_NAME
——————————————————————————————————————————– —————— ——————————————————————————————————————————–
PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
——————————————————————————————————————————– ———- —————– ————— —————- ————————
SYS INDEX PARTITION WRH$_SYSSTAT_PK
WRH$_SYSSTA_2631080415_9515 3 117889 117889 1 FRACTURED
4 456056 456056 1 Free Block
5 341508 341508 1 Free Block

 

 

3.3 Repair corrupt block using RMAN

 

ACTION PLAN
============

1) Repair the affected blocks using RMAN:

RMAN> blockrecover corruption list;

2) Validate again to repopulate view v$database_block_corruption

RMAN> backup validate check logical datafile1;

3) Verify the output again from sqlplus to make sure all is clear:

select * from v$database_block_corruption ;

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||’ Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA

1 thought on “Database corrupt block recovery using RMAN

    • Author gravatar

      i read this block very useful and let we discuss about the wallet in 12c database security. my mail id. svkoracledba@gmail.com.

      need access for google drive.

    • Author gravatar

      Thanks for the thoughts you share through your blog. In addition, several young women exactly who become pregnant don’t even seek to get health insurance because they dread they won’t qualify. Although many states currently require that insurers give coverage in spite of the pre-existing conditions. Prices on these types of guaranteed programs are usually greater, but when taking into consideration the high cost of medical treatment it may be the safer way to go to protect one’s financial potential.

      Your comment is awaiting moderation.

Leave a Reply

Your email address will not be published. Required fields are marked *