ORACLE Instance (pid = ) - Error 1578 encountered while recovering transaction (, ) on : ORA-01578: ORACLE data block corrupted (file # , block # )Block corruption after crashed database recovered

There are many cases when automatic crash recovery fails and Oracle database has to be salvaged from manual recovery [without restore and without archive log ] but DB incurrs Data block corruption(fractured blocks) which could not be fixed by just applying current or active redo logs manually. This happens due abrupt outage of all instances of databases during heavy DML, generally due power failure In below case DB had same scenario when crash recovery failed and I had to manually give recover command. I first tried sqlplus but then aborted it as it would require supply different redo log file names at recovery prompt so I switch to RMAN. See bottom of post for alert log snippet and block corruption error. You can notice ORA-01578 appearing every minute while this error is not detected unless these blocks are accessed and in this case since Oracle was trying to rollback block changes of failed (terminated session due outage) transaction and blocks is fractured ORA-01578 is popping even though no user session connected.
  
RMAN> recover database ;

Starting recover at 18-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 instance=RAC1 devtype=DISK

starting media recovery

archive log thread 1 sequence 874 is already on disk as file +DATA4/redo08.log
archive log thread 2 sequence 1628 is already on disk as file +DATA4/redo14.log
archive log thread 2 sequence 1629 is already on disk as file +DATA4/redo11.log
archive log thread 2 sequence 1630 is already on disk as file +DATA4/redo12.log
archive log thread 3 sequence 368 is already on disk as file +DATA4/redo18.log
archive log thread 3 sequence 369 is already on disk as file +DATA4/redo15.log
archive log thread 3 sequence 370 is already on disk as file +DATA4/redo16.log
archive log filename=+DATA4/redo14.log thread=2 sequence=1628
archive log filename=+DATA4/redo08.log thread=1 sequence=874
archive log filename=+DATA4/redo18.log thread=3 sequence=368
archive log filename=+DATA4/redo15.log thread=3 sequence=369
archive log filename=+DATA4/redo11.log thread=2 sequence=1629
archive log filename=+DATA4/redo16.log thread=3 sequence=370
archive log filename=+DATA4/redo12.log thread=2 sequence=1630
media recovery complete, elapsed time: 00:03:14
Finished recover at 18-JUN-14


RMAN> open resetlogs database ;

database opened

RMAN> exit

[oracle@node1 bdump]$ tail -200f alert_RAC1.log
Wed Jun 18 01:52:17 IST 2014
ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo13.log'
Wed Jun 18 01:52:17 IST 2014
Media Recovery Log +DATA4/redo13.log
Wed Jun 18 01:53:45 IST 2014
Hex dump of (file 7, block 65672) in trace file /u01/app/oracle/admin/RAC/udump/rac1_ora_21662.trc
Corrupt block relative dba: 0x01c10088 (file 7, block 65672)
Fractured block found during media recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x01c10088
 last change scn: 0x0000.01aea031 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9c190601
 check value in block header: 0x30e7
 computed block checksum: 0x3c28
Reread of rdba: 0x01c10088 (file 7, block 65672) found same corrupted data
ORA-279 signalled during: ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo13.log'  ...
Wed Jun 18 01:53:52 IST 2014
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Jun 18 01:53:52 IST 2014
Media Recovery Log /u01/app/oracle/product/10.2.0/db_1/dbs/arch2_1628_850308178.dbf
Errors with log /u01/app/oracle/product/10.2.0/db_1/dbs/arch2_1628_850308178.dbf
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Jun 18 01:53:52 IST 2014
ALTER DATABASE RECOVER CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Jun 18 01:53:57 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA3 was dismounted
SUCCESS: diskgroup DATA4 was dismounted
Wed Jun 18 01:54:22 IST 2014
ALTER DATABASE RECOVER  database until cancel using backup controlfile
Wed Jun 18 01:54:22 IST 2014
Media Recovery Start
Wed Jun 18 01:54:23 IST 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA3 was mounted
Wed Jun 18 01:54:23 IST 2014
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile   ...
Wed Jun 18 01:54:36 IST 2014
ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo14.log'
Wed Jun 18 01:54:36 IST 2014
Media Recovery Log +DATA4/redo14.log
SUCCESS: diskgroup DATA4 was mounted
ORA-279 signalled during: ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo14.log'  ...
Wed Jun 18 01:54:44 IST 2014
ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo08.log'
Wed Jun 18 01:54:44 IST 2014
Media Recovery Log +DATA4/redo08.log
ORA-279 signalled during: ALTER DATABASE RECOVER    LOGFILE '+DATA4/redo08.log'  ...
Wed Jun 18 02:00:26 IST 2014
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Jun 18 02:00:26 IST 2014
Media Recovery Log /u01/app/oracle/product/10.2.0/db_1/dbs/arch3_368_850308178.dbf
Errors with log /u01/app/oracle/product/10.2.0/db_1/dbs/arch3_368_850308178.dbf
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Jun 18 02:00:26 IST 2014
ALTER DATABASE RECOVER CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Jun 18 02:00:35 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA3 was dismounted
SUCCESS: diskgroup DATA4 was dismounted
Wed Jun 18 02:00:41 IST 2014
ALTER DATABASE RECOVER  database until cancel using backup controlfile
Wed Jun 18 02:00:41 IST 2014
Media Recovery Start
Wed Jun 18 02:00:41 IST 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA3 was mounted
Wed Jun 18 02:00:41 IST 2014
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel using backup controlfile   ...
Wed Jun 18 02:00:57 IST 2014
ALTER DATABASE RECOVER    CANCEL
Wed Jun 18 02:00:57 IST 2014
ORA-1547 signalled during: ALTER DATABASE RECOVER    CANCEL  ...
Wed Jun 18 02:00:57 IST 2014
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Jun 18 02:01:02 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA3 was dismounted
Wed Jun 18 02:01:13 IST 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA3 was mounted
Wed Jun 18 02:01:14 IST 2014
SUCCESS: diskgroup DATA4 was mounted
Wed Jun 18 02:01:14 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA3 was dismounted
Wed Jun 18 02:01:14 IST 2014
SUCCESS: diskgroup DATA2 was mounted
Wed Jun 18 02:01:14 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA4 was dismounted
Wed Jun 18 02:01:14 IST 2014
SUCCESS: diskgroup DATA3 was mounted
SUCCESS: diskgroup DATA2 was mounted
Wed Jun 18 02:01:15 IST 2014
alter database recover datafile list clear
Wed Jun 18 02:01:15 IST 2014
Completed: alter database recover datafile list clear
Wed Jun 18 02:01:15 IST 2014
alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8
Wed Jun 18 02:01:15 IST 2014
alter database recover if needed
 start until cancel using backup controlfile
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: alter database recover if needed
 start until cancel using backup controlfile
...
SUCCESS: diskgroup DATA4 was mounted
Wed Jun 18 02:01:15 IST 2014
alter database recover logfile '+DATA4/redo14.log'
Wed Jun 18 02:01:15 IST 2014
Media Recovery Log +DATA4/redo14.log
ORA-279 signalled during: alter database recover logfile '+DATA4/redo14.log'...
Wed Jun 18 02:01:15 IST 2014
alter database recover logfile '+DATA4/redo08.log'
Wed Jun 18 02:01:15 IST 2014
Media Recovery Log +DATA4/redo08.log
ORA-279 signalled during: alter database recover logfile '+DATA4/redo08.log'...
Wed Jun 18 02:01:16 IST 2014
alter database recover logfile '+DATA4/redo18.log'
Wed Jun 18 02:01:16 IST 2014
Media Recovery Log +DATA4/redo18.log
Wed Jun 18 02:03:19 IST 2014
ORA-279 signalled during: alter database recover logfile '+DATA4/redo18.log'...
Wed Jun 18 02:03:19 IST 2014
alter database recover logfile '+DATA4/redo15.log'
Wed Jun 18 02:03:19 IST 2014
Media Recovery Log +DATA4/redo15.log
ORA-279 signalled during: alter database recover logfile '+DATA4/redo15.log'...
Wed Jun 18 02:03:27 IST 2014
alter database recover logfile '+DATA4/redo11.log'
Wed Jun 18 02:03:27 IST 2014
Media Recovery Log +DATA4/redo11.log
Wed Jun 18 02:03:51 IST 2014
ORA-279 signalled during: alter database recover logfile '+DATA4/redo11.log'...
Wed Jun 18 02:03:51 IST 2014
alter database recover logfile '+DATA4/redo16.log'
Wed Jun 18 02:03:51 IST 2014
Media Recovery Log +DATA4/redo16.log
Wed Jun 18 02:04:03 IST 2014
ORA-279 signalled during: alter database recover logfile '+DATA4/redo16.log'...
Wed Jun 18 02:04:03 IST 2014
alter database recover logfile '+DATA4/redo12.log'
Wed Jun 18 02:04:03 IST 2014
Media Recovery Log +DATA4/redo12.log
Wed Jun 18 02:04:29 IST 2014
Incomplete recovery applied all redo ever generated.
Recovery completed through change 28235622
Wed Jun 18 02:04:29 IST 2014
Media Recovery Complete (RAC1)
Completed: alter database recover logfile '+DATA4/redo12.log'
Wed Jun 18 02:04:34 IST 2014
SUCCESS: diskgroup DATA3 was dismounted
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA4 was dismounted
Wed Jun 18 02:04:38 IST 2014
alter database open
Wed Jun 18 02:04:38 IST 2014
This instance was first to open
Wed Jun 18 02:04:38 IST 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA3 was mounted
Wed Jun 18 02:04:38 IST 2014
SUCCESS: diskgroup DATA2 was dismounted
SUCCESS: diskgroup DATA3 was dismounted
Wed Jun 18 02:04:38 IST 2014
ORA-1589 signalled during: alter database open...
Wed Jun 18 02:04:46 IST 2014
alter database open resetlogs
Wed Jun 18 02:04:46 IST 2014
SUCCESS: diskgroup DATA2 was mounted
SUCCESS: diskgroup DATA3 was mounted
Wed Jun 18 02:04:46 IST 2014
RESETLOGS after complete recovery through change 28235622
Resetting resetlogs activation ID 2438300125 (0x915581dd)
SUCCESS: diskgroup DATA4 was mounted
Wed Jun 18 02:05:13 IST 2014
Setting recovery target incarnation to 3
Wed Jun 18 02:05:13 IST 2014
This instance was first to open
Wed Jun 18 02:05:13 IST 2014
SUCCESS: diskgroup DATA4 was dismounted
Wed Jun 18 02:05:13 IST 2014
Picked broadcast on commit scheme to generate SCNs
Wed Jun 18 02:05:13 IST 2014
Assigning activation ID 2438538385 (0x91592491)
SUCCESS: diskgroup DATA4 was mounted
Thread 1 opened at log sequence 1
  Current log# 7 seq# 1 mem# 0: +DATA4/redo07.log
Successful open of redo thread 1
Wed Jun 18 02:05:14 IST 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jun 18 02:05:14 IST 2014
SMON: enabling cache recovery
Wed Jun 18 02:05:16 IST 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Wed Jun 18 02:05:16 IST 2014
SMON: enabling tx recovery
Wed Jun 18 02:05:16 IST 2014
Database Characterset is WE8ISO8859P1
Wed Jun 18 02:05:16 IST 2014
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Wed Jun 18 02:05:17 IST 2014
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=27, OS id=30068
Wed Jun 18 02:05:21 IST 2014
LOGSTDBY: Validating controlfile with logical metadata
Wed Jun 18 02:05:22 IST 2014
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Wed Jun 18 02:05:37 IST 2014
SMON: Restarting fast_start parallel rollback
SMON: ignoring slave err,downgrading to serial rollback
ORACLE Instance RAC1 (pid = 12) - Error 1578 encountered while recovering transaction (13, 35) on object 58984.
Wed Jun 18 02:05:37 IST 2014
Errors in file /u01/app/oracle/admin/RAC/bdump/rac1_smon_21583.trc:
ORA-01578: ORACLE data block corrupted (file # 7, block # 65672)
ORA-01110: data file 7: '+DATA/data01.dbf'
Wed Jun 18 02:05:38 IST 2014
Trace dumping is performing id=[cdmp_20140618020538]
Wed Jun 18 02:05:41 IST 2014
ORACLE Instance RAC1 (pid = 12) - Error 1578 encountered while recovering transaction (13, 35) on object 58984.
Wed Jun 18 02:05:41 IST 2014
Errors in file /u01/app/oracle/admin/RAC/bdump/rac1_smon_21583.trc:
ORA-01578: ORACLE data block corrupted (file # 7, block # 65672)
ORA-01110: data file 7: '+DATA/data01.dbf'Wed Jun 18 02:05:42 IST 2014
Trace dumping is performing id=[cdmp_20140618020542]

In my case as DB was in norachive log mode and it was test database I dropped entire tablespace containing this corrupted block. Another option would be to drop this segment only if corrupted block is not segment header block

ORA-01172: recovery of thread 2 stuck at block 224 of file 4

Error ORA-01172 should not come in ideal database It comes when database crashes(all RAC instances crashes) and automatic database crash recovery fails. Oracle first tries to recover with parallels slaves [ as default parallel recovery option is low, if it also fails then it tries serial recovery and it it also fails then gives below message. Instance is not terminated at the end of failed recovery. ]Remedy is to apply recover command manually so that oracle can apply ACTIVE and CURRENT redo logs of all threads. In below case simple recover command did job. [it did not prompt for any redo log file to apply] If you are unlucky (your database is not in archivelog mode and you do not have older but recent archive logs) then recover database or recover database until cancel command won't help you as oracle falsely wants older log sequence to be applied which is not visible in v$log.  Below is lucky one. Unlucky case will be posted sooner. 


SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 2 stuck at block 224 of file 4
ORA-01151: use media recovery to recover block, restore backup if needed


SQL>  select * from v$recover_file;

no rows selected




SQL> recover database ;
Media recovery complete.
SQL> alter database open ;

Database altered.


From alert log , Thu May 29 21:44:40 2014
Hex dump of (file 4, block 224) in trace file /home/oracle/trace/diag/rdbms/orcl/ORCL1/trace/ORCL1_p002_4479.trc
Reading datafile '+DATA/orcl/datafile/undotbs2.274.847998769' for corruption at rdba: 0x010000e0 (file 4, block 224)
Reread (file 4, block 224) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 2 STUCK AT BLOCK 224 OF FILE 4
Slave exiting with ORA-1172 exception
Errors in file /home/oracle/trace/diag/rdbms/orcl/ORCL1/trace/ORCL1_p002_4479.trc:
ORA-01172: recovery of thread 2 stuck at block 224 of file 4
ORA-01151: use media recovery to recover block, restore backup if neededThu May 29 21:44:49 2014
Abort recovery for domain 0
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 2 threads
Started redo scan
kcrfr_rnenq: use log nab 201057
kcrfr_rnenq: use log nab 43466
Completed redo scan
 read 351699 KB redo, 13118 data blocks need recovery
Thu May 29 21:45:07 2014
Started redo application at
 Thread 1: logseq 189, block 23667
 Thread 2: logseq 102, block 208354
Recovery of Online Redo Log: Thread 1 Group 5 Seq 189 Reading mem 0
  Mem# 0: +DATA2/orcl/redo05.log
Recovery of Online Redo Log: Thread 2 Group 8 Seq 102 Reading mem 0
  Mem# 0: +DATA2/orcl/redo08.log
Recovery of Online Redo Log: Thread 2 Group 7 Seq 103 Reading mem 0
  Mem# 0: +DATA2/orcl/redo07.log
Hex dump of (file 4, block 224) in trace file /home/oracle/trace/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_4415.trc
Reading datafile '+DATA/orcl/datafile/undotbs2.274.847998769' for corruption at rdba: 0x010000e0 (file 4, block 224)
Reread (file 4, block 224) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 2 STUCK AT BLOCK 224 OF FILE 4Abort recovery for domain 0
Aborting crash recovery due to error 1172
Errors in file /home/oracle/trace/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_4415.trc:
ORA-01172: recovery of thread 2 stuck at block 224 of file 4
ORA-01151: use media recovery to recover block, restore backup if needed
Abort recovery for domain 0
Errors in file /home/oracle/trace/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_4415.trc:
ORA-01172: recovery of thread 2 stuck at block 224 of file 4
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:56922:2} */... ]
----------------end alert log snippet ---------



-----------------------------------
[oracle@11gRAC1 trace]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 29 21:51:38 2014

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

connected to target database: ORCL (DBID=1375779469, not open)

RMAN> backup check logical validate database ;

Starting backup at 29-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=196 instance=ORCL1 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=00007 name=+DATA/orcl/data01.dbf
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.270.847998745
input datafile file number=00005 name=+DATA/orcl/datafile/users.263.847998771
input datafile file number=00004 name=+DATA/orcl/datafile/undotbs2.274.847998769
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA3/orcl/indexes01.dbf
input datafile file number=00001 name=+DATA/orcl/datafile/system.269.847998737
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.261.847998757
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:34
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              12879        76876           30261376
  File Name: +DATA/orcl/datafile/sysaux.270.847998745
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              21416
  Index      0              17867
  Other      0              24638

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              1            38401           30253503
  File Name: +DATA/orcl/datafile/undotbs2.274.847998769
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              38399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1            641             19721
  File Name: +DATA/orcl/datafile/users.263.847998771
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              639

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1            131072          30266334
  File Name: +DATA/orcl/data01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              97510
  Index      0              31833
  Other      0              1728

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
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              1128
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:22
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12551        89606           30266212
  File Name: +DATA/orcl/datafile/system.269.847998737
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              47836
  Index      0              9749
  Other      0              19464

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            48641           30248852
  File Name: +DATA/orcl/datafile/undotbs1.261.847998757
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              48639

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              438          98561           30265514
  File Name: +DATA3/orcl/indexes01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              89706
  Other      0              8416

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Finished backup at 29-MAY-14

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

block corruption using RMAN in oracle,also simulate block corruption

Check and repair physical and logical block corruption :

Block corruption errors are not detected in oralce until you run dbv,RMAN,analyze or export command etc. As a DBA you can proactively monitor the block corruptions in Oracle using RMAN. Further you can recover these block corruptions using RMAN with little efforts using healthy RMAN or user managed backup. If you are using user managed backup then this will need to be cataloged in oracle using catalog datafilecopy 'datafile_path_name'

When you take RMAN backup using backupset or backup image copy or run RMAN validate command it automatically checks for physical block corruptions(media blcok corruptions, hard corruption) and RMAN aborts at the first block corruption it encounters. You may want RMAN to check completely, in this case you need to allow RMAN explicitly for N number of block corruptions it encounters.

RUN { set maxcorrupt for datafile 1,2,3 ,4 to 200;
                  backup validate database;}

Since oracle 9i and onward we always have a default channel so there is no need to allocate one.

check physical as well as logical block corruption(software corruption):

RUN { set maxcorrupt for datafile 1,2,3 ,4 to 200;
          backup validate check logical database;}

Above commands mark the blocks as corrupt similar to dbv and populated v$database_block_corruption.
V$database_block_corruption gives you the file number of the datafiles that contain the corruption, and will provide the block number at which it starts and the number of blocks corrupted. You can find the object(s) affected by this corruption in the dba_segments view. using the provided values for file_id & block_id. Don't forget to check the indexes or constraints for any affected objects.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 756652 1 5.5157E+12 CORRUPT

select segment_name, segment_type, owner

from dba_extents
where file_id = 4
and 756652 between block_id
and block_id + blocks -1;

no rows selected

Question is is corrupted block the free block ?

 [ create table block_corr as select file#,block# from V$DATABASE_BLOCK_CORRUPTION where 1=0;

create unique index bcorr_pk on block_corr(file#,block#);
insert into block_corr select file#,block# from V$DATABASE_BLOCK_CORRUPTION order by file#,block#;

create table corr_objects as
SELECT de.segment_type, de.owner, de.segment_name, de.PARTITION_NAME
FROM dba_extents de, block_corr bc
WHERE de.file_id = bc.file#
and bc.block# between block_id AND block_id + blocks - 1
group by de.segment_type, de.owner, de.segment_name, de.PARTITION_NAME;
 ]

Next run this statement to identify the objects associated with corruption:

SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id = c.block#;

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE#
---------- -------------------- -------------------- --------------- ----------
CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED D
----------------- --------------- ---------------- -

TEST INDEX XXXXXXXXXXXX 62
ME_IDX
292779 292779 1

If the block does not belong to any object there is nothing to do, nothing to care, the block will be reformatted when it will be reallocated.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

OWNER SEGMENT_TYPE SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
 PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ---------- ----------------- --------------- ---------------- --------------

4 756652 756652 1 Free Block

Note: DBMS_REPAIR does not recover the blocks , rather it marks the corrupted blocks as fixed so these blocks are skipped in read/write operation. Data in these blocks are lost forever.

Other examples:

# Check for physical and logical corruption of a tablespace.
RMAN { VALIDATE CHECK LOGICAL TABLESPACE USERS;}

# Check for physical corruption of all archived redo logs files.
VALIDATE ARCHIVELOG ALL;

# Check for physical and logical corruption of the controlfile.
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;

# Check for physical and logical corruption of a specific backupset.
VALIDATE CHECK LOGICAL BACKUPSET 3;

# Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

# Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

# Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;

Repair block corruption: RMAN can be used to recover corrupted blocks online.Without block media recovery, if even a single block is corrupt, then you must take the datafile offline and restore a backup of the datafile. You must apply all redo generated for the datafile after the backup was created. The entire file is unavailable until media recovery completes. With block media recovery, only the blocks actually being recovered are unavailable during the recovery.

Repairing All Block Corruption in the Database: Follwing runs a backup validation to populate V$DATABASE_BLOCK_CORRUPTION, then repairs any corrupt blocks recorded in the view:

RUN {
BACKUP VALIDATE CHECK LOGICAL DATABASE;
BLOCKRECOVER CORRUPTION LIST;}

OR

RECOVER CORRUPTION LIST;

Note: RMAN block media recovery required enterprise edition. 

Alternate 1 : following repairs all physically corrupted blocks recorded in the view:
 BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE - 7';

Other examples #1 Recovering a Group of Corrupt Blocks: This recovers corrupt blocks in three datafiles:

BLOCKRECOVER DATAFILE 2 BLOCK 12, 13 DATAFILE 3 BLOCK 5, 98, 99 DATAFILE 4 BLOCK 19;

#2 Limiting Block Media Recovery by Type of Restore:
following  recovers a series of blocks and restores only from datafile copies:

RUN {
BLOCKRECOVER DATAFILE 3 BLOCK 2,3,4,5 TABLESPACE sales DBA 4194405, 4194409, FROM DATAFILECOPY; }

#3 Limiting Block Media Recovery by Backup Tag:
This recovers blocks and restores only from the backup with the tag weekly_backup:

BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 FROM TAG "weekly_backup";

#4 Limiting Block Media Recovery by Time: Following  recovers two blocks in the SYSTEM tablespace. It restores only from backups that could be used to recover the database to a point two days ago:

BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 RESTORE UNTIL TIME 'SYSDATE-2';

References: 1
                  2
                  3
                  4
 finding corrupted blocks in detail

It was all about physical and logical block corruption but But what about when the data itself is "corrupted" but the block is fine? I'm not talking about a logical or application oriented corruption rather a corruption that happens when there is a "Lost Write." In versions 11.1 (Oracle says it is now corrected for 11.2 onwards) and earlier it was possible for dbwr process to get a write acknowledgement of the write when in fact it did not happen. Of course this isn't Oracle's fault, but try explaining that to your manager/director weeks or months later when a data corruption is encountered and you don't know where it came from! Check it out in the "Backup and Recovery User's Guide" as well as the parameter DB_LOST_WRITE_PROTECT.  The best protection is using Data Guard.

reference: db lost write
asl tom about db lost write & protection

Simulate block corruption in oracle:
select segment_name , header_file , header_block
from dba_segments
where segment_name = 'table_to_be_corrupted' and owner = 'user_name';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK
---------------------------- ----------- ------------
table1                              8                          19

Lets corrupt block 20 using the dd command in Linux. For windows use ultra edit editor in hexa mode

DISCLAIMER: The dd command given below is just for learning purposes and should only be used on testing systems. I will not take any responsibility of any consequences or loss of data caused by this command.

$dd of=ts_corrupt01.dbf bs=8192 conv=notrunc seek=20 << EOF
Make it Corrupt.
EOF

11g Active Standby Database Automatic Block Corruption Repair(reference: Gavin Soorma)


In addition to the real time query capability of the 11g Active Data Guard feature, we can also add to our high availability capability by using the Automatic Block Media Repair feature whereby data block corruptions on the Primary database can be repaired by obtaining those blocks from the standby site – all performed by a background process (ABMR) transparent to the application. The same functionality can be used to repair block corruptions on the Active Standby site by applying blocks which are conversely now received from the Primary site.

Short Circuit=>Power Down=>SAN Down=>DB crashed=>DBstarted fine=>but BLAB BLAB BLAB ...SAP Application not working properly

Short Circuit=>Power Down=>SAN Down=>DB crashed=>DB Started fine=>but BLAB BLAB BLAB ...SAP Application not working properly

User complained some modules had issues

Database Alert Log recorded ORA-600 internal error with different first actual code argument. search into Meta link told DB had problem in executing DMLs on some tables. As some of indices on these tables had logical corruption. So its obvious that when DB crashed DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occured as key-rowid information was not consistent.

automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption in logs nor in any datafile block. So when Db started after power SAN was made up again after restoring power supply it was opened successfully. But it had logical corruption hidden inside bad leaf blocks of indices. and crash recovery was not able to fix index logical corruption. And its obvious behavivour. We can't expect so hight from Database technology.

solution :

1. identify such indices which are in bad shape and
a) drop and recreated OR b) rebuild them online

2. restore from backup and recover


later was not feasible as DB had size 1TB and prod database was on RAID 5. It would take 3 days to restore!!!

also server did not have enough space to store all archive logs at same time.

corrupt indexes key-rowid value mismatch

Short Circuit caused power Down=>SAN Down=>All databases servers using this storage  Crashed=>Oracle 9i DB started fine=>but…OOPS! SAP Application not working properly

Once a User complained about some modules having issues :
There was a Database Alert Log recorded ORA-600 internal error with different first actual code argument. A search into Meta link showed that DB had problems in executing DMLs on some tables, as some of  the indices on these tables had logical corruption. So it was thought, when DB crashed, DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occurred as key-row information was not consistent. So  queries and DMLs which would involve these indexes were failing.
Restart of the database, automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption shown in logs nor in any datafile block.  So when DB started,  SAN was made up again. After restoring power supply, it was opened successfully. But it had a logical corruption hidden inside bad leaf blocks of indices.  Automatic crash recovery was not able to fix index logical corruption. This is obvious behavior. Some  won’t expect such issue and blame RDBMS some won't. I won't.


Solution:


1. Identify such indices which are in bad shape and fix problematic indexes
a) drop and recreate such indexes  OR
b) rebuild them  online so that it read key values/rowed from table (normal rebuild  reads existing index entries so corruption does not go away)


2. Restore from backup and recover
The latter  was not feasible as DB had size 1TB and prod database was on RAID 5. It would  have taken more than 2 days to restore it!!!

Another case:
I seen some people killing their application had corrupt indexes. It means when they killed they had some running DMLs on tables.

Oracle full of features but BUGs too ORA-00600: internal error code, arguments: [kcratr1_lostwrt],

SETUP : two node 9i rac 9204 ON ocfs ON WIN 2K

first instance crahed due OCFS bug
now second rtried instance recovery of crashed first instance and it quite successfully recovered the first instance(appareent fro malert log of seconds instance)
but sooner secondnstance crashes too and throws ora-600 internal error.

ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [], [], [], [], [], []

I tried to manually start the database but oracle throwing same error and complaining about corrupted logs the lost update. I used first argument of ora-600 here [kcratr1_lostwrt] - searched in metalink using ora-600 lookup tool broadly suggested either that was dangerous situation - required media recovery or that could be BUG with other OS like HP Unix and oracle software versions 9205 onwards. which was not our case.Oracle was spuriously trying to recover in second case and metalink told it can be recovered easily in second case.

I preferred to choose second solution path first over giving recover database command (with out restore from backup/after restore). So I set undocument parameter two_pass=false and opened the database successfully from first instance.then I removed this parameter and opened from first instance and second instance,respectively.