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-00333: redo log read error block 9167 count 1073

If database is not able to start after failed crash automatic recovery with error you receive ORA-00333: redo log read error block xxxx count xxxx then you should try manually recover the database. 

[oracle@11gRAC1 11g]$ srvctl start database -d ORCL
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action "ora.orcl.db start" encountered the following erro
ORA-00333: redo log read error block 9167 count 8184
. For details refer to "(:CLSN00107:)" in "/u01/11.2.0/grid/log/11grac1/agent/cr

CRS-2674: Start of 'ora.orcl.db' on '11grac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on th
CRS-5017: The resource action "ora.orcl.db start" encountered the following erro
ORA-00333: redo log read error block 9167 count 8184
. For details refer to "(:CLSN00107:)" in "/u01/11.2.0/grid/log/11grac2/agent/cr

CRS-2674: Start of 'ora.orcl.db' on '11grac2' failed
[oracle@11gRAC1 11g]$ sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        195  241172480        512          1 NO  CURRENT               64028116 18-MAY-14   2.8147E+14
         2          1        194  241172480        512          1 NO  INACTIVE              63987815 16-MAY-14     64028116 18-MAY-14
         3          2        151  241172480        512          1 NO  CURRENT               64007840 16-MAY-14   2.8147E+14
         4          2        150  241172480        512          1 NO  INACTIVE              63941096 16-MAY-14     64007840 16-MAY-14

         SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  +DATA2/orcl/redo01.log                             NO
         2         ONLINE  +DATA2/orcl/redo02.log                             NO
         3         ONLINE  +DATA2/orcl/redo03.log                             NO
         4         ONLINE  +DATA2/orcl/redo04.log                             NO

SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00333: redo log read error block 8192 count 2048
SQL> select name,state from v$asm_diskgroup ;

NAME                           STATE
------------------------------ -----------
DATA2                          CONNECTED
DATA3                          CONNECTED
DATA                           CONNECTED

SQL> select FILE#,STATUS,ERROR,RECOVER,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header;

Disk group DATA2 ON which redo log resides is mounted and DB has successfully connected to it

     FILE# STATUS  ERROR                                                             REC FUZ CHECKPOINT_CHANGE#
---------- ------- ----------------------------------------------------------------- --- --- ------------------
         1 ONLINE                                                                    NO  YES           63941096
         2 ONLINE                                                                    NO  YES           63941096
         3 ONLINE                                                                    NO  YES           63941096
         4 ONLINE                                                                    NO  YES           63941096
         5 ONLINE                                                                    NO  YES           63941096
         6 ONLINE                                                                    NO  YES           63941096
         7 ONLINE                                                                    NO  YES           63941096

7 rows selected.


 #rebooted  crs but database did not start and both its instances terminated after giving error

SQL> startup mount
ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00333: redo log read error block 9167 count 1073

This time block number is different and so is count

SQL> shutdown immediate
startup ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  534519808 bytes
Fixed Size                  2229944 bytes
Variable Size             314575176 bytes
Database Buffers          209715200 bytes
Redo Buffers                7999488 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile ;ORA-00279: change 64028117 generated at 05/18/2014 11:34:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_195_847218868.dbf
ORA-00280: change 64028117 for thread 1 is in sequence #195

Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA2/orcl/redo01.log
ORA-00279: change 64028117 generated at 05/16/2014 13:06:28 needed for thread 2
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch2_151_847218868.dbf
ORA-00280: change 64028117 for thread 2 is in sequence #151

Specify log: {=suggested | filename | AUTO | CANCEL}
+DATA2/orcl/redo03.log
Log applied.
Media recovery complete.



SQL> select distinct FUZZY from v$datafile_header;

FUZ
----
NO

SQL> alter database open resetlogs ;
Database altered.


[ DB is opened and you are done. You may have some corruption in undo tablespaces , data files or index tablespaces- always check alert log even after crash recovery or instance recovery but DB is opened and you can tackle other error errors with available backup or  dropping/recreating undo [if it does not have active and if undo tablespace has active SMU then by marking them to be dropped by hidden parameter, post about this later on ]
-------------------------------------------------------------------------------------------------------
 
####### From alert log

Incomplete read from log member '+DATA2/orcl/redo03.log'. Trying next member.
Abort recovery for domain 0
Aborting crash recovery due to error 333
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc:
ORA-00333: redo log read error block 9167 count 8184
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc:
ORA-00333: redo log read error block 9167 count 8184
ORA-333 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:22317:292} */...
Mon May 19 11:32:05 2014
Shutting down instance (abort)

snippet of trace file : /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_5083.trc

Successfully allocated 3 recovery slaves
Using 45 overflow buffers per recovery slave
Thread 2 checkpoint: logseq 151, block 2, scn 64033559
  cache-low rba: logseq 151, block 9159
    on-disk rba: logseq 151, block 9190, scn 64078136
  start recovery at logseq 151, block 9159, scn 0
Thread 1 checkpoint: logseq 195, block 2, scn 64028117
  cache-low rba: logseq 195, block 75202
    on-disk rba: logseq 195, block 75256, scn 64077704
  start recovery at logseq 195, block 75202, scn 0
Incomplete read from log member '+DATA2/orcl/redo03.log'. Trying next member.
2014-05-19 11:32:05.362112 : Abort recovery for domain 0, flags = 0x4
2014-05-19 11:32:05.362192 : kjb_abort_recovery: abort recovery for domain 0 @ inc 3 (0)
2014-05-19 11:32:05.362209 : kjb_abort_recovery: domain flags=0x0, valid=0
ORA-00333: redo log read error block 9167 count 8184
2014-05-19 11:32:05.391695 : Abort recovery for domain 0, flags = 0x4
2014-05-19 11:32:05.391717 : aborting recovery of 0 (0) with cluster inc 3 (0) recovery 1
2014-05-19 11:32:05.391730 : kjb_abort_recovery: domain flags=0x0, valid=0
ORA-00333: redo log read error block 9167 count 8184


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

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

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

BACKUP , RECOVERY and archive log handling in RAC

Here are few basic points to consider about safe backup and recovery in RAC environment :

• If DB running in archive log mode then archive logs should be placed on a single location(say single ASM directory) in shared disk so that if you ever needs to recover then you do not need to copy all archive logs to a single location or supply archive log file names manually for each log sequence#. Configuring Flash recovery area is so good option for RAC databases.
• If you have less storage available or you want faster archiving only then you should consider archiving logs to local disks of nodes.                                                                                           
• In case all instances crashes(power failure in data centre) then you should bring all nodes one by one. As this way it is not only cluster ware will have safe recovery but also  Database crash recovery will also be safe. 
• Configure RMAN control file auto back-up feature also to shared disks (say OCFS file system,if applicable) as this will allow control file to be restored from any node and hence so recovery also.

• As RAC instances has more shared pool available than single instance you can increase large pool size to increase RMAN buffers for faster backup so you may multiplex more files in backupset and parallelize more if enough CPUs available but remember higher multiplexing can potentially slow down restore.

• To benefit from more CPUs available in RAC than single instance database backup channels can be opened across all RAC nodes this when combines with benefits of compressed backup sets speeds up backup   

ORA-00314: log 3274 of thread 1, expected sequence# 3276 doesn't match 3276

I had power failure while there was heavy DMLs running on my database and after

restating machine I my database(running in noarchive log mode) did not start due 

failed automatic instance recovery due error:  "ORA-00314: log %s of thread %s, 

expected sequence# %s doesn't match %s". 

Strange part was lower log sequence number mentioned in error message was already 

applied fully to database and it was even not appearing in v$log as inactive,it was 

so old though there was another log group active so it means recovery was vital

So,I tried to recover using first broader recovery command preferred to start with as safe practice "recover

database" but it did not succeed then I tried same using RMAN and then using backup 

control file syntax. Recovery prompted for couple of log sequences which I supplied 

after joing and v$ and v$logfile for corresponding thread# and sequence# but recovery 

terminated saying "datafile 1 may need recovery" It just mean I was unlucky as in most

of cases this error is solved by recover database until cancel using backup controlfile

. Then I tried with recreating controlfile and then recovery and then I tried with 

clearing logfiles but it also did not give me any luck. Finally I had to restore from

cold backup. Luckily it was just a test database. 

oerr reasons: 

ORA-00314: log %s of thread %s, expected sequence# %s doesn't match %s
 *Cause:  The online log is corrupted or is an old version.
 *Action: Find and install correct version of log or reset logs.

ORA-16038: log 1 sequence# 2901 cannot be archived - ORA-19809: limit exceeded for recovery files logfileswitch(archiving needed)

ORA-16038: log 1 sequence# 36792 cannot be archived.
ORA-19809: limit exceeded for recovery files

Your DML statements ,even, session logon hangs and you get above error besides getting below error in alert log file as well. Further you see wait event logfileswitch(archiving needed) which implies sesion is waiting for log file switch to succeed which in turn is waiting for older logfiles to be archived.

SID,SEQ#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,WAIT_TIME_MICRO,TIME_REMAINING_MICRO,TIME_SINCE_LAST_WAIT_MICRO

49,47571,logfileswitch(archivingneeded),,0,00,,0,00,,0,00,3290255840,2,Configuration,0,84,WAITING,84329776,-1,0

If this wait event is seen continuously then it means archiver process is not able to write archive redo logfiles due below reasons

1. mount point or directory pointed by log_archive_destination_n is full in disk space

2. There is flash recovery area being used for archiving, there may be enough space in directory/mount point on which flash recovery area lies but flash recovery area is filled upto space limit defined by instance init parameter db_recovery_file_dest_size   

 select *  from v$recovery_file_dest

NAME,           SPACE_LIMIT,SPACE_USED,   SPACE_RECLAIMABLE,NUMBER_OF_FILES

C:\flash_recovery_area,107374182400,107216960512,1081344,1147

107374182400=100GB

Here you can see flash recovery area has only free space=SPACE_LIMIT-SPACE_USED=149MB while the redo log file size is bigger(200MB) than this free space 149MB so redo log could not be archived.

Remedy 1:

 SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
db_recovery_file_dest                string      C:\flash_recovery_area
db_recovery_file_dest_size           big integer 100G

If there is enough free space in flash reocvery area location pointed by flash recovery area db_recovery_file_dest  then increase space limit(db_recovery_file_dest) which is set to 100GB in above: 

 

alter systemset db_recovery_file_dest_size=400g [

Remedy 2:

-delete older archive logs which are not needed according to backup policy

RMAN> delete archivelog until time 'sysdate-7'

-delete expired archive logs (archive log fiels deleted physically from OS command and not from RMAN.First, mark such archived logs to be expired in RMAN.

RMAN> crosscheck archivelog all
OR
RMAN>change archive log all validate;

then delete marked expired archive logs

RMAN>delete expired archivelog all;

Now archiving will succeed.

RMAN salient feature introduced from 10g incrementally updated image copy backup-- which is mostly ignored

Incrementally updated image copy backup:

It is faster to restore from RMAN image (copy) backup still   image copy backups were not considered very good practice because it takes longer to take rman copy(image)
backup as they backup whole database block to block backup and also incremental image copy backup was  not possible.

Oracle 10g has introduced incrementally updated image copy backup feature. In this feature first
copy backup was taken and then incremntal backup set is taken and image copy backup is updated with  this incremental backupset. So basically this is three steps task.

first step: update(recover) the image copy backup
second step : take image copy backup if this does not exist before
and then take incremental level 1 backupset(changed block from image copy backup)
RUN {
  RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}

Note the backuo tags must be same in both rman commands.
Note 1: When above commands are executed for first time. RMAN gives message
"no copy of datafile #n found to recover" as there are no existing image copy backup with given tag
and then take the image copy backup of database.
It does not take the incremental backupset.
Note 2: when above commands are executed for second time, then RMAN again gives message
"no copy of datafile #n found to recover" as there are no existing incremental backupset to apply(recover)
the image copy backup of database with the given tag. and takes the incremental level 1 backupset.

Note3: It performs  two steps: First recover the image copy backup of database with incremental backupset.
and then it takes incremental level 1 backup set(backup of changed blocks since image copy backup of database
was taken with given tag
First run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSTEM_FNO-1_5DO179AR tag=INCR_BACKUP recid=85 stamp=806593907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSAUX_FNO-3_5EO179BK tag=INCR_BACKUP recid=86 stamp=806593922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-EXAMPLE_FNO-5_5FO179C3 tag=INCR_BACKUP recid=87 stamp=806593929
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-UNDOTBS1_FNO-2_5GO179CB tag=INCR_BACKUP recid=88 stamp=806593934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-USERS_FNO-4_5HO179CI tag=INCR_BACKUP recid=89 stamp=806593938
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806593939_8K1J9WD4_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13
Second run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-FEB-13
channel ORA_DISK_1: finished piece 1 at 05-FEB-13
piece handle=D:\ORATEMP\DEF_LOC_5JO179D2_1_1 tag=TAG20130205T135234 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806593979_8K1JC4OG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13
Third run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSTEM_FNO-1_5DO179AR
recovering datafile copy fno=00002 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-UNDOTBS1_FNO-2_5GO179CB
recovering datafile copy fno=00003 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSAUX_FNO-3_5EO179BK
recovering datafile copy fno=00004 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-USERS_FNO-4_5HO179CI
recovering datafile copy fno=00005 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-EXAMPLE_FNO-5_5FO179C3
channel ORA_DISK_1: reading from backup piece D:\ORATEMP\DEF_LOC_5JO179D2_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORATEMP\DEF_LOC_5JO179D2_1_1 tag=TAG20130205T135234
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-FEB-13
channel ORA_DISK_1: finished piece 1 at 05-FEB-13
piece handle=D:\ORATEMP\DEF_LOC_5LO179EJ_1_1 tag=TAG20130205T135323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806594029_8K1JDP83_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13


Recoverability Window:

If you want to recover to three days ago you can not do from using above backup strategy
so you can alter RMAN commands to as below:

RMAN> RUN {
2> RECOVER COPY OF DATABASE WITH TAG 'incr_backup'  until time 'sysdate-3';
3> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }

Now if you want to recover to 27 hours ago you can recover as below:

RMAN { set until time "to_date('03-feb-2013 03:00:00','dd-mon-yy hh:mi:ss am')";
restore database;
recover database;
}
 

put rac database in archivelog mode

Putting Oracle clustered(RAC) database in archivelog is very easy,only step that differs from single instance is set cluster_database=false while running statement alter database archivelog

1. set init parameter cluster_database =false
2. shutdown the database
3. startup mount
4. alter database archivelog;
5. set init parameter cluster_database =true
6. satrtup open
 

DB lost write and standby database

Recovering From Lost-Write Errors on a Primary Database

During media recovery in a Data Guard configuration, a physical standby database can be used to detect lost-write data corruption errors on the primary database. This is done by comparing SCNs of blocks stored in the redo log on the primary database to SCNs of blocks on the physical standby database. If the SCN of the block on the primary database is lower than the SCN on the standby database, then there was a lost-write error on the primary database.

Note: Because lost-write errors are detected only when a block is read into the cache by a primary and the corresponding redo is later compared to the block on the standby, there may be undetected stale blocks on both the primary and the standby that have not yet been read and verified. These stale blocks do not affect operation of the current database because until those blocks are read, all blocks that have been used up to the SCN of the currently applied redo on the standby to do queries or updates were verified by the standby.

When a primary lost-write error is detected on the standby, one or more block error messages similar to the following for each stale block are printed in the alert file of the standby database:

Tue Dec 12 19:09:48 2006
STANDBY REDO APPLICATION HAS DETECTED THAT THE PRIMARY DATABASE
LOST A DISK WRITE OF BLOCK 26, FILE 7
NO REDO AT OR AFTER SCN 389667 CAN BE USED FOR RECOVERY.
.
.
.
The alert file then shows that an ORA-00752 error is raised on the standby database and the managed recovery is cancelled:
Slave exiting with ORA-752 exception
Errors in file /oracle/log/diag/rdbms/dgstwrite2/stwrite2/trace/stwrite2_pr00_23532.trc:
ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 7, block# 26)
ORA-10564: tablespace TBS_2
ORA-01110: data file 7: '/oracle/dbs/btbs_21.f'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 57503
.
.
.
The standby database is then recovered to a consistent state, without any corruption to its datafiles caused by this error, at the SCN printed in the alert file:

Recovery interrupted!
Recovered data files to a consistent state at change 389569

This last message may appear significantly later in the alert file and it may have a lower SCN than the block error messages. Also, the primary database may operate without visible errors even though its datafiles may already be corrupted.

The recommended procedure to recover from such errors is a failover to the physical standby, as described in the following steps.

Steps to Failover to a Physical Standby After Lost-Writes Are Detected on the Primary

1.Shut down the primary database. All data at or after SCN printed in the block error messages will be lost.
2.Issue the following SQL statement on the standby database to convert it to a primary:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

compressed archivelog backup set in 11g

Oracle 10g brought compressed backupset feature which is wonderful especially when backing up large data warehouse databases. This not only saves the disk space but also the backup time is reduced. This comes at only little price of CPU. But archivelogs could not be compressed in 10g. From 11g onwards archive logs also can be compressed as backup set.

RMAN> backup as compressed backupset archive log from sequence=2000

RMAN> backup as compressed backupset archive log from sequence=2000 until time sysdate-10*1/1440

If any of archive logs is physicall deleted then archive log backup can fail. In this case
RMAN> change archivelog all validate or RMAN>crosscheck archivelog all comes handy.

If you are in oracle 10g you can backup archive logs using below shell script

#!/bin/bash
# change directory to archive log location
cd /apps/oracle/archive_logs
# it is assumed log_archive_format has .arc in last
ls -t *.arc >file_list
# sed is used to leave latest two archive logs from compression as they might be stilll being generated
cat file_list|sed '1,2d'>file_list_to_compress
for file in `cat file_list_to_compress`
do
gzip $file
done

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.