Recently I got a problem with one of the ORLs; like the title is suggesting, one of the ORL got corrupted (don’t ask how!) and below is what I did:
ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 8741986 change 136264748341 time 09/06/2016 12:47:59 ORA-00312: online log 4 thread 1: '/u2000/oradata/DB/redo4a.log'
What I had in mind was to bounce the database in mount state and to drop the redo04a.log file and to re-create it. Something like this:
SQL> startup mount ORACLE instance started. Total System Global Area 3.2068E+10 bytes Fixed Size 2269072 bytes Variable Size 5905580144 bytes Database Buffers 2.6105E+10 bytes Redo Buffers 55242752 bytes Database mounted. SQL> alter database drop logfile group 4; alter database drop logfile group 4 * ERROR at line 1: ORA-00350: log 4 of instance DB (thread 1) needs to be archived ORA-00312: online log 4 thread 1: '/u2000/oradata/DB/redo4a.log'
…and the decision had to be taken:
SQL> alter database clear unarchived logfile group 4; Database altered. SQL> alter database drop logfile group 4; Database altered. SQL> alter database add logfile group 4 '/u2000/oradata/DB/redo4a.log' size 4G reuse; Database altered. SQL> alter database open; Database altered.
Another Oracle blog 
C:\Windows\system32>set ORACLE_SID=orcl
C:\Windows\system32>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 3 17:00:11 2017
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT name,open_mode FROM V$DATABASE;
NAME OPEN_MODE
——— ——————–
ORCL MOUNTED
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘D:\APP\ADMIN\ORADATA\ORCL\SYSTEM01.DBF’
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 26984 change 3032511481418 time 07/31/2017
18:24:39
ORA-00312: online log 1 thread 1: ‘D:\APP\ADMIN\ORADATA\ORCL\REDO01.LOG’
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: ‘D:\APP\ADMIN\ORADATA\ORCL\REDO01.LOG’
Please Help me…
LikeLike
Hi,
Did you try to recover the “database until cancel”? If that’s successful, you can open your database with RESETLOGS option (this is going to clear your online redo logs, but it’s going also to change the database to a new incarnation!!!).
Cheers,
Marius
LikeLike