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