Translate

Search This Blog

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