Blog

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 !

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.