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#;