ORA-00742: Log read detects Lost write

ORA-00742: Log read detects Lost write

Oracle database is not opening, while trying to open the database it’s crashed. 

DB can be opened till mount state after that it throw the following error :

We tried to clean the archive redo log with following commands:

alter database clear unarchived logfile group 2;

But that didn't solved our problem so we tried a different solution.

SOLUTION THAT WORKED FOR ME:

1. Open the database in Mount mode, as follows

2. Try to find the redo log status, as follows

we can see in the above output that Redo log group 2 is in Current State and this is the same redo log that is diagnosed with log read detects lost write. 

3. Then we try to recover the database, as per below command

Here we gave the path of the redo log which was shown up in the error.

4. Now we will open the database with resetlogs, as follows

Database was successfully altered.

5. Check the status of the open database, as follows

6. check the status of the redo logs, as follows

SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

Note : Please switch the logfile with the following command;

alter system switch logfile;

NOTE: Please take the cold backup before performing the activity.

I hope it will help you :)