ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

 ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

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.

-bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 2 22:49:25 2022

Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  281016480 bytes

Fixed Size                  8882336 bytes

Variable Size             218103808 bytes

Database Buffers           50331648 bytes

Redo Buffers                3698688 bytes

Database mounted.

ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

SQL> select status from v$instance;

STATUS

------------

MOUNTED

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

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID

---------- ------- ------- ------------------------------------------------------------ --- ----------

         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log                    NO           0

         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log                    NO           0

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------

         2          1        104   52428800        512          1 NO  CURRENT                 479386 02-JAN-22   9.2954E+18                    0

         1          1        103   52428800        512          1 NO  INACTIVE                479343 02-JAN-22       479386 02-JAN-22          0

3.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;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

4.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 479386 generated at 01/02/2022 00:06:42 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TESTDB/archivelog/2022_01_02/o1_mf_1_104_%u_.arc

ORA-00280: change 479386 for thread 1 is in sequence #104

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/testdb/redo02.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

-bash-4.4$ ps -ef | grep pmon

  oracle  1309     1   0 22:49:47 ?           0:00 ora_pmon_testdb

-bash-4.4$