ORA-00742

ORA-00742 Log read detects lost write in thread 1 sequence 149 block 38233

In this post we will see how to solve

ORA-00742: Log read detects lost write-in

thread 1 sequence, You can encounter this error if your Oracle Database machine was suddenly stopped without proper shutdown command due to some unavoidable problem like Power Failure, Power cable issue, etc.

STEP1:

In this step, tried to login to the database, and below is an error.

Select status from v$instance;

STATUS

———–

MOUNTED

Alter database open;

ERROR at line 1:

ORA-00742: Log read detects lost write in thread 1 sequence 149 block 38233

ORA-00372: online log 2 thread 1: ‘/u01/oradata/test01/redo02.log’

STEP2:

Check the status of log file 2 in v$log & v$logfile and you can notice log group 2 is the current logfile.

SQL> Set lines 300

Set pages 3000

Col MEMBER for a60

Select * from v$logfile;

SQL> Select * from v$log;

STEP3:

Try to clear the current log file most likely it will fail as group log 2 is the current logfile.

Alter database clear unarchived logfile group 2;

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance test01 (thread 1)

ORA-00312: online log 2 thread 1: ‘/u01/oradata/test01/redo02.log’

STEP4:

So we will try to perform manual recovery on the database, Please note since Group log 2 is current it will be not archived, and the requested archive log sequence 149 is the current redo logfile, and hence I have provided manually redo log 2 file location.

SQL> recover database until cancel;

SQL> alter database open resetlogs;

ERROR at line 1 :

ORA-00600: internal error code

STEP 5:

SQL> !mv /u01/oradata/test01/redo02.log  /u01/oradata/test01/redo02.log

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

Hope It Helps !