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
