Issue:- We had a situation for a customer where his server was shutdown abruptly due to power cut at datacenter. After resuming the power, when he tried to start his database he encountered following error“ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653”
Environment :- Windows 2019
DB Version:- 12.2.0.1
Solution: –
Login to database and shutdown cleanly
| D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:26:27 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. |
Lets try to open the database
| D:\oracle\product\12.2.0\dbhome\bin>sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 11 10:28:29 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1.2885E+10 bytes Fixed Size 8761232 bytes Variable Size 2885681264 bytes Database Buffers 9965666304 bytes Redo Buffers 24793088 bytes Database mounted. ORA-00742: Log read detects lost write in thread 1 sequence 5789 block 805653 ORA-00312: online log 1 thread 1: ‘D:\ORACLE\ORADATA\ORCL\REDO01.LOG’ |
As we can see database is getting mounted, but it couldn’t open, and above error was thrown.
Th above error is generally observed due to a relog log corruption. In this case corruption seems to have happened on relog group 1.
| QL> select * from v$logfile; SQL> col member for a35 SQL> / GROUP# STATUS TYPE MEMBER IS_ CON_ID ———- ——- ——- ———————————– — ———- 4 ONLINE D:\ORACLE\ORADATA\ORCL\REDO04.LOG NO 0 3 ONLINE D:\ORACLE\ORADATA\ORCL\REDO03.LOG NO 0 2 ONLINE D:\ORACLE\ORADATA\ORCL\REDO02.LOG NO 0 1 ONLINE D:\ORACLE\ORADATA\ORCL\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 5789 536870912 512 1 NO CURRENT 342324096 11-MAR-24 1.8447E+19 0 4 1 5788 536870912 512 1 YES INACTIVE 342320572 11-MAR-24 342324096 11-MAR-24 0 3 1 5787 536870912 512 1 YES INACTIVE 342313670 11-MAR-24 342320572 11-MAR-24 0 2 1 5786 536870912 512 1 YES INACTIVE 342313238 11-MAR-24 342313670 11-MAR-24 0 |
Lets try do an incomplete recovery of the database
| SQL> recover database until cancel; ORA-00279: change 342324096 generated at 03/11/2024 05:04:10 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC ORA-00280: change 342324096 for thread 1 is in sequence #5789 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} D:\ORACLE\ORADATA\ORCL\REDO01.LOG Log applied. Media recovery complete Here we need to provide the redolog group which was corrupted. |
Now lets try to open the database using resetlogs
| SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00600: internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], [] |
As per Doc ID 2026541.1 , to fix the above error we need to rename the online corrupted redolog and initial resetlogs again
We renamed D:\ORACLE\ORADATA\ORCL\REDO01.LOG as D:\ORACLE\ORADATA\ORCL\REDO01_old.LOG and tried to do recovery again
| SQL> recover database using backup controlfile until cancel; ORA-00279: change 342379894 generated at 03/11/2024 08:46:09 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\FAST_RECOVERY_AREA\ORCL\ORCL\ARCHIVELOG\2024_03_11\O1_MF_1_5789_%U_.ARC ORA-00280: change 342379894 for thread 1 is in sequence #5789 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database alerted. SQL> select open_mode from v$database; OPEN_MODE ——————– READ WRITE |
Leave a Reply