How to Fix ORA-00743-Log Read detects lost write in thread

So far, I am pretty satisfied with my Oracle 19c installation on my CentOS 8. Everything went very well until today I got an ORA-00743 error. I cannot set my container database open in read write mode. I can mount it but cannot open in read write mode. This tutorial will show you how to fix How to Fix ORA-00743-Log error on Oracle Database 19c.

Here is the complete error message I got:

ERROR at line 1:
 ORA-00742: Log read detects lost write in thread 1 sequence 14 block 317816
 ORA-00312: online log 2 thread 1:
 '/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_2_h16h2q62_.log'
 ORA-00312: online log 2 thread 1:
 '/u02/oradata/CDB1/onlinelog/o1_mf_2_h16h2o6z_.log'

Possible Cause

I am not exactly sure why this happen but recently my CentOS 8 server went off several times due to power outage and I don’t have time to shut down it properly.

How to fix this?

I tried the following method to solve it. It will only take few minutes.

First, login to the Oracle Database via sqlplus.

sqlplus / as sysdba

Once in the sqlplus, open the database in mount mode.

SQL>startup mount

Find the redo logs

SQL>select * from v$log;

As you can see from the result above, there is a redo log which is in CURRENT state in group 2.

Recover the database

SQL> recover database until cancel;

We need to specify the log file path.

Next try to open the database with this command

SQL> alter database open resetlogs;

Now check the database status

SQL> select name, open_mode, database_role from v$database;

Now check the log again

SQL>select * from v$log;

Done. Thanks for reading this article How to fix ORA-00743-Log Read detects lost write in thread error. Big thanks to the writer of this article.