Issue: I have the ORA-01172 error in my alert log, and I cannot start my database:
ORA-01172: recovery of thread 1 stuck at block 25 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed.
The error utility shows this for the ORA-01172 error:
ORA-01172: recovery of thread string stuck at block string of file string
Cause: Crash recovery or instance recovery could not apply a change to a block because it was not the next change. This can happen if the block was corrupted and then repaired during recovery.
Action: Do a RECOVER DATAFILE for the file containing the block. If this does not resolve the problem then restore the file from a backup and recover it.
This can be caused by several factors:
1 – A transient error
2 – Redo_log_corruption
First, perform a full backup of what you have now, before you proceed further.
Next, assume that you have a transient error and attempt to start/recover the database:
Step 1. Shutdown the database and expect the ORA-01109 error:
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Step 2. Mount the database:
SQL> startup mount;
ORACLE instance started.
Database mounted.
Step 3. Recover the database with “recover database”
SQL> recover database;
Media recovery complete.
Step 4. Open the database with “alter database open”
SQL> alter database open;
Database altered.
