ORA-00742: Log read detects lost write in thread 1 sequence YYY block XXXXX

We had this error while trying to open a DB:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread 1 sequence 635 block 58093
ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/LIMS/redo02.log'
SQL>

Digging a bit on the enviroment, we noticed server had a power cut and DB did not stop gracefully.

This is how we fixed:

1:- Start DB in mount and list log files:

SQL> startup mount


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

    GROUP# STATUS     TYPE    MEMBER                                                       IS_     CON_ID
---------- ---------- ------- ------------------------------------------------------------ --- ----------
         3            ONLINE  /u02/app/oracle/oradata/LIMS/redo03.log                      NO           0
         2            ONLINE  /u02/app/oracle/oradata/LIMS/redo02.log                      NO           0
         1            ONLINE  /u02/app/oracle/oradata/LIMS/redo01.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
---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ----------
         1          1        634  104857600        512          1 YES INACTIVE        25136197 04-MAR-22     25136223 04-MAR-22          0
         3          1        633  104857600        512          1 YES INACTIVE        25076876 03-MAR-22     25136197 04-MAR-22          0
         2          1        635  104857600        512          1 NO  CURRENT         25136223 04-MAR-22   9.2954E+18                    0

2:- Recover Database: (we need to input the failed log file):

SQL> recover database until cancel;
ORA-00279: change 25136223 generated at 03/04/2022 20:00:23 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/fra/LIMS/archivelog/2022_03_14/o1_mf_1_635_%u_.arc
ORA-00280: change 25136223 for thread 1 is in sequence #635


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/app/oracle/oradata/LIMS/redo02.log
Log applied.
Media recovery complete.
SQL>

3:- Try to open reset logs, but it might fail:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krsi_al_hdr_update.invalid_nab_1], [4294967295], [], [], [], [], [], [], [], [], [], []
SQL>

4:- To fix the ORA-00600[krsi_al_hdr_update.invalid_nab_1]

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

SQL>  !mv /u02/app/oracle/oradata/LIMS/redo02.log /u02/app/oracle/oradata/LIMS/redo02.log.old
SQL>

5:- Recover and reset logs:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 25154543 generated at 03/04/2022 22:30:09 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/fra/LIMS/archivelog/2022_03_14/o1_mf_1_635_%u_.arc
ORA-00280: change 25154543 for thread 1 is in sequence #635


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

Now we have an open DB.