Startup a RAC db fails with Logical corruption ORA-01172, ORA-01151

Startup error and alert.log

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

Alert.log : 
Hex dump of (file 5, block 2926) in trace file /u01/app/oracle/diag/rdbms/grac4/grac41/trace/grac41_ora_15971.trc
Reading datafile '+DATA/grac4/datafile/undotbs2.264.826111755' for corruption at rdba: 0x01400b6e (file 5, block 2926)
Read datafile mirror 'DATA_0002' (file 5, block 2926) found same corrupt data (logically corrupt)
Read datafile mirror 'DATA_0003' (file 5, block 2926) found valid data
Hex dump of (file 5, block 2926) in trace file /u01/app/oracle/diag/rdbms/grac4/grac41/trace/grac41_ora_15971.trc
Repaired corruption at (file 5, block 2926)
Hex dump of (file 2, block 315368) in trace file /u01/app/oracle/diag/rdbms/grac4/grac41/trace/grac41_ora_15971.trc
Reading datafile '+DATA/grac4/datafile/sysaux.257.826111425' for corruption at rdba: 0x0084cfe8 (file 2, block 315368)
Read datafile mirror 'DATA_0002' (file 2, block 315368) found same corrupt data (logically corrupt)
Read datafile mirror 'DATA_0003' (file 2, block 315368) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 2 STUCK AT BLOCK 315368 OF FILE 2
Abort recovery for domain 0
--> Logical corruption file 5, block 2926 fixed 
    Logical corruption  file 2, block 315368 --> logically corrupted on DATA_0002 and 'DATA_0003 --> Recovery aborted

Check datafile:
SQL> select FILE# , name from v$datafile where  FILE# =2;
     FILE# NAME
---------- --------------------------------------------------
     2 +DATA/grac4/datafile/sysaux.257.826111425
--> SYSAUX TS 

Validating datafile with RMAN for physical and logical corruptions

[oracle@grac42 ~]$ rman target=/
RMAN> backup validate datafile 2;
Starting backup at 29-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=grac42 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=00002 name=+DATA/grac4/datafile/sysaux.257.826111425
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
List of Datafiles
=================
tatus Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              44781        497923          88391584  
  File Name: +DATA/grac4/datafile/sysaux.257.826111425
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              207863          
  Index      0              212957          
  Other      0              32319           
Finished backup at 29-JUL-14
--> No physical corruption

Check for logical corruption
RMAN> backup check logical validate datafile 2 ;
Starting backup at 29-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/grac4/datafile/sysaux.257.826111425
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              44781        497923          88391584  
  File Name: +DATA/grac4/datafile/sysaux.257.826111425
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              207863          
  Index      0              212957          
  Other      0              32319           
Finished backup at 29-JUL-14
--> No logical corruption found 

Do we have a backup which we can restore and recover ?

RMAN> list backup of datafile 2;     <-- SYSASUX TS 
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65      Incr 0  3.08G      DISK        00:22:14     13-JUL-14      
        BP Key: 65   Status: AVAILABLE  Compressed: NO  Tag: TAG20140713T091718
        Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443
  List of Datafiles in backup set 65
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 43106516   13-JUL-14 +DATA/grac4/datafile/sysaux.257.826111425
--> looks good

Can we restore that backup ?
RMAN> restore datafile 2 preview;
Starting restore at 29-JUL-14
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65      Incr 0  3.08G      DISK        00:22:14     13-JUL-14      
        BP Key: 65   Status: AVAILABLE  Compressed: NO  Tag: TAG20140713T091718
        Piece Name: +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443
  List of Datafiles in backup set 65
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 43106516   13-JUL-14 +DATA/grac4/datafile/sysaux.257.826111425
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
67      671.00K    DISK        00:00:01     13-JUL-14      
        BP Key: 67   Status: AVAILABLE  Compressed: NO  Tag: TAG20140713T094233
        Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.304.852802955
  List of Archived Logs in backup set 67
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    23      43106179   13-JUL-14 43108607   13-JUL-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
68      412.00K    DISK        00:00:01     13-JUL-14      
        BP Key: 68   Status: AVAILABLE  Compressed: NO  Tag: TAG20140713T094233
        Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.296.852802955

  List of Archived Logs in backup set 68
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    53      43106182   13-JUL-14 43108603   13-JUL-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
69      206.50K    DISK        00:00:02     13-JUL-14      
        BP Key: 69   Status: AVAILABLE  Compressed: NO  Tag: TAG20140713T094233
        Piece Name: +FRA2/grac4/backupset/2014_07_13/annnf0_tag20140713t094233_0.289.852802955

  List of Archived Logs in backup set 69
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  3    25      43106185   13-JUL-14 43108600   13-JUL-14
List of Archived Log Copies for database with db_unique_name GRAC4
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
963     1    54      A 13-JUL-14  Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_54.301.852803207
966     1    55      A 13-JUL-14  Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_55.259.852803279
970     1    56      A 13-JUL-14  Name: +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_56.329.852806897
...
1135    3    75      A 28-JUL-14  Name: +FRA2/grac4/archivelog/2014_07_29/thread_3_seq_75.458.854179229

Media recovery start SCN is 43106516
Recovery must be done beyond SCN 43107799 to clear datafile fuzziness
Finished restore at 29-JUL-14
--> looks good!

Restore and recover datafile 2

RMAN> restore datafile 2;
Starting restore at 29-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DATA/grac4/datafile/sysaux.257.826111425
channel ORA_DISK_1: reading from backup piece +FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443
channel ORA_DISK_1: piece handle=+FRA2/grac4/backupset/2014_07_13/nnndn0_tag20140713t091718_0.334.852801443 tag=TAG20140713T091718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:05
Finished restore at 29-JUL-14

RMAN> recover datafile 2;
Starting recover at 29-JUL-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 54 is already on disk as file +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_54.301.852803207
archived log for thread 1 with sequence 55 is already on disk as file +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_55.259.852803279
archived log for thread 1 with sequence 56 is already on disk as file +FRA2/grac4/archivelog/2014_07_13/thread_1_seq_56.329.852806897
..
archived log file name=+FRA2/grac4/archivelog/2014_07_28/thread_2_seq_75.453.854143249 thread=2 sequence=75
archived log file name=+FRA2/grac4/archivelog/2014_07_28/thread_1_seq_121.454.854143743 thread=1 sequence=121
archived log file name=+FRA2/grac4/archivelog/2014_07_28/thread_2_seq_76.455.854144381 thread=2 sequence=76
media recovery complete, elapsed time: 01:29:46
Finished recover at 29-JUL-14

RMAN> alter database open;
database opened
--> database successfully opened 

Monitoring recovery process

SQL> select * from v$recovery_progress;
START_TIM TYPE                ITEM                 UNITS          SOFAR      TOTAL TIMESTAMP COMMENTS
--------- ------------------------- -------------------------------- --------------- ---------- ---------- --------- ------------------------------
29-JUL-14 Media Recovery        Log Files                 Files            113        179
29-JUL-14 Media Recovery        Active Apply Rate             KB/sec            541      0
29-JUL-14 Media Recovery        Average Apply Rate             KB/sec           1015      0
29-JUL-14 Media Recovery        Maximum Apply Rate             KB/sec           7419      0
29-JUL-14 Media Recovery        Redo Applied             Megabytes           2675      0
29-JUL-14 Media Recovery        Last Applied Redo             SCN+Time              0      0 21-JUL-14 SCN: 46557614
29-JUL-14 Media Recovery        Active Time              Seconds           2596      0
29-JUL-14 Media Recovery        Apply Time per Log             Seconds              6      0
29-JUL-14 Media Recovery        Checkpoint Time per Log         Seconds             16      0
29-JUL-14 Media Recovery        Elapsed Time             Seconds           2697      0

Leave a Reply

Your email address will not be published. Required fields are marked *