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
-------------------------------------------------
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
-------------------------------------------------
Posts
