[oracle@rac201 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 17 10:47:58 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2415917856 bytes
Fixed Size 8928032 bytes
Variable Size 1006632960 bytes
Database Buffers 1392508928 bytes
Redo Buffers 7847936 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 170 block 72959
ORA-00312: online log 1 thread 1: '/data1/ORCL/redo01.log'
ORA-00742: Log read detects lost write in thread 1 sequence 170 block 72959
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 the database, and below is an error.
SQL> startup;
ORACLE instance started.
Total System Global Area 2415917856 bytes
Fixed Size 8928032 bytes
Variable Size 1006632960 bytes
Database Buffers 1392508928 bytes
Redo Buffers 7847936 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 170 block 72959
ORA-00312: online log 1 thread 1: '/data1/ORCL/redo01.log'
STEP2: Check the status of log file 1 in v$log & v$logfile and you can notice log group 1 is the current logfile.
set lines 300
set pages 3000
col MEMBER for a60
select * from v$Logfile;SQL> SQL> SQL>
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
3 ONLINE /data1/ORCL/redo03.log NO 0
2 ONLINE /data1/ORCL/redo02.log NO 0
1 ONLINE /data1/ORCL/redo01.log NO 0
11 STANDBY /data1/ORCL/stb_redo11.log NO 0
12 STANDBY /data1/ORCL/stb_redo12.log NO 0
13 STANDBY /data1/ORCL/stb_redo13.log NO 0
14 STANDBY /data1/ORCL/stb_redo14.log NO 0
7 rows selected.
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 170 209715200 512 1 NO CURRENT 4974163 17-DEC-24 9.2954E+18 0
3 1 169 209715200 512 1 YES INACTIVE 4939898 14-DEC-24 4974163 17-DEC-24 0
2 1 168 209715200 512 1 YES INACTIVE 4859292 08-DEC-24 4939898 14-DEC-24 0
STEP3: Try to clear the current log file most likely it will fail as group log 1 is the current logfile.
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/data1/ORCL/redo01.log'
STEP4: So we will try to perform manual recovery on the database, Please note since Group log 1 is current it will be not archived, and the requested archive log sequence 170 is the current redo logfile, and hence I have provided manually redo log 1 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 4974164 generated at 12/17/2024 10:08:15 needed for thread 1
ORA-00289: suggestion : /data1/ORCL/ARCH/1_170_1178543939.arc
ORA-00280: change 4974164 for thread 1 is in sequence #170
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data1/ORCL/redo01.log
Log applied.
Media recovery complete.
SQL>
SQL>
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>
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], [], [], [], [], [], [], [], [], [], []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 the steps mention in MOS Note and again attempted fake recovery with the CANCEL option and was able to open the database.
At the OS level move the current online redologs:
SQL> !mv /data1/ORCL/redo01.log /data1/ORCL/redo01_old.log SQL> SQL> recover database using backup controlfile until cancel; ORA-00279: change 4977857 generated at 12/17/2024 10:23:53 needed for thread 1 ORA-00289: suggestion : /data1/ORCL/ARCH/1_170_1178543939.arc ORA-00280: change 4977857 for thread 1 is in sequence #170 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. SQL> SQL> SQL> SQL> SQL> alter database open resetlogs; Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>
SQL> set lines 300
set pages 3000
col MEMBER for a60
select * from v$Logfile;SQL> SQL> SQL>
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
3 ONLINE /data1/ORCL/redo03.log NO 0
2 ONLINE /data1/ORCL/redo02.log NO 0
1 ONLINE /data1/ORCL/redo01.log NO 0
11 STANDBY /data1/ORCL/stb_redo11.log NO 0
12 STANDBY /data1/ORCL/stb_redo12.log NO 0
13 STANDBY /data1/ORCL/stb_redo13.log NO 0
14 STANDBY /data1/ORCL/stb_redo14.log NO 0
7 rows selected.
No comments:
Post a Comment