Performing Block Media Recovery in Oracle

In most cases, the database marks a block as media corrupt and then writes it to disk when the corruption is first encountered. No subsequent read of the block will be successful until the block is recovered. You can only perform block recovery on blocks that are marked corrupt or fail a corruption check.

Create Tablespace, User and Table:

SQL> CREATE TABLESPACE tbs_hr DATAFILE '/u02/oradata/NONCDB/hr01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

Tablespace created.

SQL>

SQL> CREATE USER samad IDENTIFIED BY Oracle
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE tbs_hr
  QUOTA UNLIMITED ON tbs_hr;  

User created.

SQL> GRANT DBA TO SAMAD;

Grant succeeded.

SQL> CONN SAMAD/Oracle
Connected.


SQL> CREATE TABLE TBL_TEST (ID NUMBER, NAME CHAR(2000)) TABLESPACE TBS_HR;

Table created.

SQL> INSERT INTO TBL_TEST VALUES (1001, 'SAMAD');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1002, 'ALL DBA');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1003, 'ALL DBA1');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1004, 'ALL DBA2');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1005, 'ALL DBA3');

1 row created.

SQL> INSERT INTO TBL_TEST VALUES (1006, 'ALL DBA3');

1 row created.

SQL> COMMIT;

Commit complete.

Object Details:

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME =  'TBL_TEST';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93574      93574


SQL> SELECT ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
                DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
                FROM SAMAD.TBL_TEST;

        ID      FILE#     BLOCK#
---------- ---------- ----------
      1001         12        133
      1002         12        133
      1003         12        133
      1004         12        134
      1005         12        134
	
SQL>
SQL> COL NAME FOR A70
SQL> SET LINESIZE 100
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u02/oradata/NONCDB/datafile/o1_mf_system_hn33p6nh_.dbf
         2 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm37yg_.dbf
         3 /u02/oradata/NONCDB/datafile/o1_mf_sysaux_hn33ofkb_.dbf
         4 /u02/oradata/NONCDB/datafile/o1_mf_undotbs1_hn33qbtl_.dbf
         5 /u02/oradata/NONCDB/datafile/tbs_test_01.dbf
         6 /u02/oradata/NONCDB/datafile/o1_mf_users_hn33q9r9_.dbf
         7 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm3823_.dbf
         8 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm39m5_.dbf
         9 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_hormf0ly_.dbf
        10 /u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horoo9m9_.dbf
        11 /u02/oradata/cat/rccat01.dbf

     FILE# NAME
---------- ----------------------------------------------------------------------
        12 /u02/oradata/NONCDB/hr01.dbf

12 rows selected.


SQL> SELECT header_block FROM dba_segments WHERE segment_name='TBL_TEST';

HEADER_BLOCK
------------
         130

Database Backup:

We can restore corrupted block from backup either from full backup or datafile backup.

[oracle@ocm12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 7 01:34:43 2020

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

connected to target database: NONCDB (DBID=1867026507)

RMAN> backup database;

Starting backup at 07-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 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=00004 name=/u02/oradata/NONCDB/datafile/o1_mf_undotbs1_hn33qbtl_.dbf
input datafile file number=00006 name=/u02/oradata/NONCDB/datafile/o1_mf_users_hn33q9r9_.dbf
input datafile file number=00001 name=/u02/oradata/NONCDB/datafile/o1_mf_system_hn33p6nh_.dbf
input datafile file number=00003 name=/u02/oradata/NONCDB/datafile/o1_mf_sysaux_hn33ofkb_.dbf
input datafile file number=00005 name=/u02/oradata/NONCDB/datafile/tbs_test_01.dbf
input datafile file number=00011 name=/u02/oradata/cat/rccat01.dbf
input datafile file number=00012 name=/u02/oradata/NONCDB/hr01.dbf
input datafile file number=00002 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm37yg_.dbf
input datafile file number=00007 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm3823_.dbf
input datafile file number=00008 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horm39m5_.dbf
input datafile file number=00009 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_hormf0ly_.dbf
input datafile file number=00010 name=/u02/oradata/NONCDB/datafile/o1_mf_tbs_orde_horoo9m9_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T013448_htdj08fq_.bkp tag=TAG20201107T013448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-NOV-20
channel ORA_DISK_1: finished piece 1 at 07-NOV-20
piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_ncsnf_TAG20201107T013448_htdj1ph9_.bkp tag=TAG20201107T013448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-NOV-20

RMAN> exit


Recovery Manager complete.

Block Corruption:

We will not able to run any query with full table scan. Once query will return values if corrupted block do not access. Even we will able to perform DML for all other block for this object.

For demonstration, I corrupted a block manually but don’t try this on your database.

[oracle@ocm12c ~]$ dd of=/u02/oradata/NONCDB/hr01.dbf bs=8192 conv=notrunc seek=133 << EOF
> FILE CORRUPTED
> EOF
0+1 records in
0+1 records out
15 bytes (15 B) copied, 0.000271132 s, 55.3 kB/s

[oracle@ocm12c ~]$
[oracle@ocm12c ~]$
[oracle@ocm12c ~]$ exit
exit

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT * FROM TBL_TEST;
SELECT * FROM TBL_TEST
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'


SQL> INSERT INTO TBL_TEST VALUES (1007, 'ALL DBA4');

1 row created.

SQL> commit;

Commit complete.

SQL> COL NAME FOR A20
SQL> SET LINESIZE 100
SQL> SELECT * FROM TBL_TEST;
SELECT * FROM TBL_TEST
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'

Corruption details in alter log file:

In real life, you will get notification either from OEM or monitoring tools about the error.

Corrupt block relative dba: 0x03000085 (file 12, block 133)
Bad header found during multiblock buffer read
Data in bad block:
 type: 70 format: 1 rdba: 0x524f4320
 last change scn: 0x4445.54505552 seq: 0xa flg: 0x06
 spare1: 0x4c spare2: 0x45 spare3: 0x0
 consistency value in tail: 0xede10601
 check value in block header: 0xa33f
 computed block checksum: 0x1770

Reading datafile '/u02/oradata/NONCDB/hr01.dbf' for corruption at rdba: 0x03000085 (file 12, block 133)
Reread (file 12, block 133) found same corrupt data (no logical check)
Sat Nov 07 01:37:10 2020
Corrupt Block Found
         CONT = 0, TSN = 13, TSNAME = TBS_HR
         RFN = 12, BLK = 133, RDBA = 50331781
         OBJN = 93575, OBJD = 93575, OBJECT = TBL_TEST, SUBOBJECT =
         SEGMENT OWNER = SAMAD, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/noncdb/noncdb/trace/noncdb_ora_20085.trc  (incident=67206):
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Errors in file /u01/app/oracle/diag/rdbms/noncdb/noncdb/trace/noncdb_ora_20085.trc  (incident=67207):
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Sat Nov 07 01:37:10 2020
Sweep [inc][67207]: completed
Sweep [inc][67206]: completed
Errors in file /u01/app/oracle/diag/rdbms/noncdb/noncdb/trace/noncdb_ora_20085.trc  (incident=67208):
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Sat Nov 07 01:39:25 2020
Sweep [inc][67208]: completed

From Trace File:

Corrupt block relative dba: 0x03000085 (file 12, block 133)
Bad header found during multiblock buffer read
Data in bad block:
 type: 70 format: 1 rdba: 0x524f4320
 last change scn: 0x4445.54505552 seq: 0xa flg: 0x06
 spare1: 0x4c spare2: 0x45 spare3: 0x0
 consistency value in tail: 0xede10601
 check value in block header: 0xa33f
 computed block checksum: 0x1770
TRCMIR:kcf_reread     :start:    133:0:/u02/oradata/NONCDB/hr01.dbf
Reading datafile '/u02/oradata/NONCDB/hr01.dbf' for corruption at rdba: 0x03000085 (file 12, block 133)
Reread (file 12, block 133) found same corrupt data (no logical check)
TRCMIR:kcf_reread     :done :    133:0:/u02/oradata/NONCDB/hr01.dbf
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
Byte offset to file# 12 block# 133 is 1089536
DDE: Problem Key 'ORA 1578' was flood controlled (0x2) (incident: 67206)
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Action (ID=34340903) was flood controlled by a FC Qualifier
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Byte offset to file# 12 block# 133 is 1089536
DDE: Problem Key 'ORA 1578' was flood controlled (0x2) (incident: 67207)
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Action (ID=34340903) was flood controlled by a FC Qualifier

*** 2020-11-07 01:39:22.923
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
Byte offset to file# 12 block# 133 is 1089536
DDE: Problem Key 'ORA 1578' was flood controlled (0x2) (incident: 67208)
ORA-01578: ORACLE data block corrupted (file # 12, block # 133)
ORA-01110: data file 12: '/u02/oradata/NONCDB/hr01.dbf'
Action (ID=34340903) was flood controlled by a FC Qualifier

Recover corrupted blocks from backup using RMAN:

Syntax:

#For Single Block Recovery:
RMAN> RECOVER DATAFILE 8 BLOCK 13;

#For Multiple Block Recovery:
RMAN> RECOVER DATAFILE 8 BLOCK 13
  DATAFILE 2 BLOCK 19;
[oracle@ocm12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 7 01:41:29 2020

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

connected to target database: NONCDB (DBID=1867026507)

RMAN> RECOVER DATAFILE 12 BLOCK 133;

Starting recover at 07-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00012
channel ORA_DISK_1: reading from backup piece /u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T013448_htdj08fq_.bkp
channel ORA_DISK_1: piece handle=/u02/oradata/fast_recovery_area/NONCDB/backupset/2020_11_07/o1_mf_nnndf_TAG20201107T013448_htdj08fq_.bkp tag=TAG20201107T013448
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 07-NOV-20

Access the table or corrupted block’s data after recovered:

SQL> COL NAME FOR A10
SQL> SELECT * FROM SAMAD.TBL_TEST;

        ID NAME
---------- ----------
      1001 SAMAD
      1002 ALL DBA
      1003 ALL DBA1
      1004 ALL DBA2
      1005 ALL DBA3
      1006 ALL DBA3
      1007 ALL DBA4

Leave a Reply