Oracle Blog | HariPrasath Rajaram

FEW BLOCKS IN DATAFILE ARE CORRUPT

There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.

For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.

As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUPTION ??

SYS@ram > SELECT header_block FROM dba_segments WHERE segment_name=’EMP’;

HEADER_BLOCK
————
146

$ dd of=/u01/oracle/DB11G/oradata/ram/users01.dbf bs=8192 conv=notrunc seek=147 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s

SYS@ram> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SYS@ram> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/u01/oracle/DB11G/oradata/ram/users01.dbf’

A) CHECK CORRUPTION USING RMAN

RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.

Using RMAN command:

RMAN> backup validate database archivelog all;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/ram/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 493
In Alert Log:

Wed JAN 18  1:53:28 2019
Hex dump of (file 4, block 147) in trace file /u01/oracle/DB11G/diag/rdbms/ram/ram/trace/ram_ora_6734.trc
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data
Reread of blocknum=147, file=/u01/oracle/DB11G/oradata/ram/users01.dbf. found same corrupt data

In V$DATABASE_BLOCK_CORRUPTION view:

RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.

SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———-   ———-   ———-       ——————           ———
4      147      1            0          CORRUPT

Using DBV:

dbv file=/u01/oracle/DB11G/oradata/ram/users01.dbf blocksize=8192

DBVERIFY: Release 12.2.0.2.0 – Production on Wed JAN 18 1:06:26 2019

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

DBVERIFY – Verification starting : FILE = /u01/oracle/DB11G/oradata/ram/users01.dbf
Page 147 is marked corrupt
Corrupt block relative dba: 0x01000093 (file 4, block 147)
Bad header found during dbv:
Data in bad block:
type: 99 format: 7 rdba: 0x69747075
last change scn: 0x7365.74206e6f seq: 0x74 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb843
block checksum disabled

DBVERIFY – Verification complete

Total Pages Examined : 640
Total Pages Processed (Data) : 90
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39
Total Pages Failing (Index): 0
Total Pages Processed (Other): 492
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1086086 (0.1086086)

B) CORRECT DATA BLOCK CORRUPTION

Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.

RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.

RMAN> recover datafile 4 block 147;

Starting recover at 18-JAN-2019 1:07:41
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/ram/backupset/2018_02_26/o1_mf_nnndf_TAG20190226T134738_9jwr7wj3_.bkp tag=TAG20190226T134738
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:01

Finished recover at 26-JAN-2019 1:07:45

C) VERIFY

SYS@ram > select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SYS@ram > select * from scott.emp;

EMPNO  ENAME  OB   MGR  HIREDATE  SAL  COMM   DEPTNO
———-   ———-   ———  ———-  ———--    ———-  ———-    ———-
7369  SMITH CLERK 7902 17-JUN-80  800    2       0

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba