Block Recovery using RMAN with No Valid backup during Block Corruption

In this demo, we will see how to recover a block when we do not have a valid backup for the tablespace.

1      1. Create a new tablespace.

SQL> create tablespace newtest datafile '/data1/noasmdb/datafile/newtest01.dbf' size 10M autoextend on next 5M;

Tablespace created.

2. Create a table with tablespace to NEWTEST tablespace

SQL> create table testnobkp tablespace newtest as (select * from rki_details);

Table created.

SQL> select count(1) from testnobkp;

  COUNT(1)

----------

    100000

3. We do not have backup for this tablespace in RMAN

SQL> select file#,blocks,name from v$datafile where name like '%newtest%';

     FILE#     BLOCKS NAME

---------- ---------- ---------------------------------------------

         6       1280 /data1/noasmdb/datafile/newtest01.dbf

RMAN> list backup of datafile 6;

specification does not match any backup in the repository

 4. Using dbverify utility to check the block corruption

[oracle@vm1 trace]$ dbv file=/data1/noasmdb/datafile/newtest01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Tue Feb 11 14:24:36 2014

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

DBVERIFY - Verification starting : FILE = /data1/noasmdb/datafile/newtest01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 1280

Total Pages Processed (Data) : 372

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 143

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 765

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1153567 (0.1153567)

5. Now, we will corrupt the block .

SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||

       to_number(S.HEADER_BLOCK + 1) || ' << EOF',

       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',

       'EOF'

  FROM DBA_SEGMENTS S, dba_data_files f

 WHERE f.tablespace_name = 'NEWTEST'

   and S.SEGMENT_NAME = 'TESTNOBKP';

  

[oracle@vm1 trace]$ dd of=/data1/noasmdb/datafile/newtest01.dbf bs=8192 conv=notrunc seek=131 << EOF

> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt  EOF

> EOF

0+1 records in

0+1 records out

117 bytes (117 B) copied, 0.000249 seconds, 470 kB/s

SQL> alter system flush buffer_cache;

System altered.

SQL>  select count(1) from testnobkp;

 select count(1) from testnobkp

                      *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6: '/data1/noasmdb/datafile/newtest01.dbf'

Alert log message

Corrupt Block Found

         TSN = 8, TSNAME = NEWTEST

         RFN = 6, BLK = 131, RDBA = 25165955

         OBJN = 75643, OBJD = 75643, OBJECT = TESTNOBKP, SUBOBJECT =

         SEGMENT OWNER = TEST1, SEGMENT TYPE = Table Segment

Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_ora_10185.trc  (incident=19481):

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6: '/data1/noasmdb/datafile/newtest01.dbf'

Incident details in: /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/incident/incdir_19481/noasmdb_ora_10185_i19481.trc

6. Query the view v$database_block_corruption

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

         6        131          1                  0 CORRUPT

7. To check and have a completed list of corrupt blocks, you should issue a backup validate command

RMAN> backup validate tablespace newtest;

Starting backup at 11-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/data1/noasmdb/datafile/newtest01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

6    FAILED 0              765          1280            1153567

  File Name: /data1/noasmdb/datafile/newtest01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              371

  Index      0              0

  Other      1              144

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_ora_11248.trc for details

Finished backup at 11-FEB-14

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

2722       HIGH     OPEN      11-FEB-14     Datafile 6: '/data1/noasmdb/datafile/newtest01.dbf' contains one or more corrupt blocks

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

---------- -------- --------- ------------- -------

2722       HIGH     OPEN      11-FEB-14     Datafile 6: '/data1/noasmdb/datafile/newtest01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

------ ------------------

1      Perform block media recovery of block 131 in file 6

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/hm/reco_4233699349.hm

   8. Recover a datafile .

RMAN> recover datafile 6 block 131;

Starting recover at 11-FEB-14

using channel ORA_DISK_1

searching flashback logs for block images

finished flashback log search, restored 1 blocks

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 11-FEB-14

9. Check now whether everything is fine now.

SQL>  select count(1) from testnobkp;

  COUNT(1)

----------

    100000

Conclusion:

Without even a valid backup, we were able to recover the block corrupted.  In the above steps, we had issued “validate database” in RMAN  and hence the view v$database_block_corruption was populated.