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 database and below is 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 requested archive log sequence 149 is 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 steps mention in MOS Note and again attempted fake recovery with the CANCEL option and was able to open the database.

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

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.

 754 total views,  1 views today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *

11 + five =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru