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
