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.
|
SQL>
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 |
No comments:
Post a Comment