Solving ORA-00742 Log read detects lost write in Oracle

Solving 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-00312: 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.

set lines 300 
set pages 3000
col MEMBER for a60
select * from v$Logfile;

    GROUP# STATUS  TYPE    MEMBER							IS_	CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
	 3	   ONLINE  /u01/oradata/test01/redo03.log				NO	     0
	 2	   ONLINE  /u01/oradata/test01/redo02.log				NO	     0
	 1	   ONLINE  /u01/oradata/test01/redo01.log				NO	     0

select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME	  CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
	 1	    1	     148   52428800	   512		1 YES INACTIVE		     2955910 03-DEC-20	    3009896 04-DEC-20	       0
	 3	    1	     147   52428800	   512		1 YES INACTIVE		     2901560 03-DEC-20	    2955910 03-DEC-20	       0
	 2	    1	     149   52428800	   512		1 NO  CURRENT		     3009896 04-DEC-20	    1.8447E+19		       0

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

SQL> 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. But even after “Media recovery complete” While trying to open DB, I encounter an ORA-00600 error.

SQL> recover database until cancel;
ORA-00279: change 3009896 generated at 12/04/2020 04:00:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST01/archivelog/2020_12_19/o1_mf_1_149_%u_.arc
ORA-00280: change 3009896 for thread 1 is in sequence #149


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/test01/redo02.log
Log applied.
Media recovery complete.
 
SQL> alter database open resetlogs;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krsi_al_hdr_update.invalid_nab_1], [4294967295], [], [], [], [], [], [], [], [], [], []

At this point, I tried to search in MOS and fortunately found below MOS note for the above issue

Alter Database Open Resetlogs returns error ORA-00600: [krsi_al_hdr_update.15], (Doc ID 2026541.1)

STEP 5: We followed the steps mention in MOS Note and again attempted fake recovery with the CANCEL option and was able to open the database. At the OS level move the current online redologs:

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

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3043326 generated at 12/04/2020 07:51:33 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST01/archivelog/2020_12_19/o1_mf_1_149_%u_.arc
ORA-00280: change 3043326 for thread 1 is in sequence #149


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

SQL> alter database open resetlogs;


Database altered.


SQL> select status from v$instance;

STATUS
------------
OPEN

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

DATABASE, ORA-ERRORS, ORACLE

DBsGuru