Table of Contents
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