Resolve ORA-01172 and ORA-01151 Errors in Oracle Databases

In Oracle database management, encountering errors such as ORA-01172 and ORA-01151 can disrupt operations. Understanding these errors and implementing effective recovery strategies is essential for maintaining database integrity. in this article we will learn how to Resolve ORA-01172 and ORA-01151 in oracle database.

Understanding ORA-01172 and ORA-01151

ORA-01172: This error indicates that crash or instance recovery is unable to apply a change to a block because it is not the next change. This situation can arise if a block becomes corrupted and is subsequently repaired during recovery.

ORA-01151: This error suggests that media recovery is required to recover a block, and restoring a backup may be necessary.

Common Causes

Today there was power failure in my office due to that servers were shutdown suddenly. when the team tries to connect with database server then found error message

“ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0“

I tried to make database up by restarting ORACLE service but got same error message.

These errors basically say:

ORA-01172 – recovery of thread % stuck at block % of file %
ORA-01151 – use media recovery to recover block, restore backup if needed

Step-by-Step Recovery Process

There are several steps you can take to Resolve ORA-01172 and ORA-01151 error:

1. Check the alert log for any additional error messages related to the recovery process. This may provide more information on the cause of the problem.
2. Check the status of the redo logs and control files to ensure they are available and accessible.
3. Try to recover the database using the “RECOVER DATABASE” command. If this command is unsuccessful, you may need to perform a manual recovery using the “RECOVER DATABASE UNTIL CANCEL” command.
4. If the issue is related to the redo logs, try to recover the database using a different set of redo logs.
5. If the above steps do not resolve the issue, you may need to restore a backup of the database and perform a recovery.
6. If none of the above steps resolve the issue, it may be necessary to contact Oracle support for further assistance.

It’s important to make sure you have a recent backup of your database before trying any recovery or restore procedures to minimize data loss.

So how do I recover from this. The solution is simple, I just needed to perform the following steps:

1. Shutdown the database

SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

2. Mount the database

Start the instance and mount the database without opening it:

SQL> startup mount;
  ORACLE instance started.
  Total System Global Area 1.0033E+10 bytes
  Fixed Size 2934696 bytes
  Variable Size 1677723736 bytes
  Database Buffers 8321499136 bytes
  Redo Buffers 30617600 bytes
  Database mounted.

3. Recover the database

Apply media recovery to the affected datafile:

SQL> recover database;
  Media recovery complete.

4. Open the database with “alter database”

SQL> alter database open;
  Database altered.

5- Open the Database

Once recovery is complete, open the database:

ALTER DATABASE OPEN;

How to Resolve INS-08101 Unexpected Error on Linux/OL 8: A Comprehensive Guide