How to solve ORA-00354: corrupt redo log block header
To resolve ORA-00354 :
SQL>alter system checkpoint;
SQL>alter system switch logfile;
SQL>alter database backup controlfile to trace;
SQL>alter system drop logfile group n;
If the above command fail with some archiving issue then use the below mentioned command
SQL>alter database clear unarchived logfile group <n>;
SQL>alter database drop logfile group <n>;
SQL>alter database add logfile group n ('/dev/................') size nnnnM reuse;
To check the status
SQL>select * from v$logfile;
It should show unused as the status of the recently added log file
Now, switch log file 3-4 times and check the v$logfile again
SQL>alter system switch logfile;
SQL>select * from v$logfile;
C:\Windows\system32>set ORACLE_SID=orcl
ReplyDeleteC:\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...
This command in the 4th line above is incorrect:
ReplyDeleteSQL>alter system drop logfile group n;
should be:
SQL>alter DATABASE drop logfile group n;
(just like it is properly shown in the 6th command line above)
Otherwise, the steps above worked perfectly for me when one Redo Logfile was corrupted due to a SAN outage. Contact me for any Oracle consulting needs worldwide by remote-access: www.kmsdatasystems.com (25+ years experience with Oracle, SQL Server..., and lots of experience with PostgreSQL, MySQL too -- especially Performance Tuning accomplishments bringing long-running queries and batch jobs down to mere minutes or even seconds.