• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group

By admin

The Problem

Following errors are reported in the alert log file:

ORA-354: corrupt redo log block header
ORA-353: log corruption near block %s change %s time %s
ORA-312: online log %s thread %s: '%s'
ORA-312: online log %s thread %s: '%s'

ORA-312 reports all the redo log file names for the same redo log group number. Example for redo log group number 3 which has two redo log file members:

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 878787 time 12/05/2014 13:17:10
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_A.dbf'
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_B.dbf'

Both online redo log members are reported in the error. Alert log may also contain the next message if all the redo members are indeed corrupt and the database is in archivelog mode:

ARCn: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread %d sequence %d log %d at block %d. Arch found corrupt blocks

Example:

ARC1: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread 1 sequence 28 log 3 at block 1892904. Arch found corrupt blocks

This indicates a possible corruption in Online Redo Log File Members in a Redo Log Group. If Oracle detects corruption when archiving a log member, it tries to read the same redo block from the second member of the group. If the block is found corrupt in all the members of the redo log group, archiving does not proceed. The cause is normally due to an overwrite/missed due to faulty OS or bad Hardware.

Validate what Redo Log File Members are corrupt

Execute the next statement to confirm that the redo log member is corrupt:

$ sqlplus / as sysdba

alter session set tracefile_identifier='VALIDATEREDO';

alter system dump logfile '&name' VALIDATE;

oradebug setmypid
oradebug tracefile_name

This is to confirm that the redo log member is corrupt; if the redo log file is corrupt it should produce an error. Example:

SQL> alter system dump logfile '/oracle/dbs/log3_A.dbf' validate;
alter system dump logfile '/oracle/dbs/log3_A.dbf' validate
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 869569 time 12/05/2014 13:17:10
ORA-00334: archived log: '/oracle/dbs/log3_A.dbf'

Repeat the same for all the redo log files in the same redo log group number. The next query can be executed to identify the members:

select member
from v$logfile
where  group# = &group_number;
Note: Before applying any of the solutions below, please take a backup of the corrupt redo log file in case it is needed for further root cause analysis.

Solution 1

If one of the redo log members is not corrupt then execute the next command several times until the respective redo log group number is in status=CURRENT in view v$log.

alter system switch logfile;

select status
from   v$log
where  group#=&log_group_number;

Optionally wait until the redo log gets reused and the block will be repaired.

Solution 2

If all redo log members are corrupt, follow the Oracle documentation to “clear [unarchived] logfile group”. After clearing the redo log group, alert Log may be updated with the next message:

WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
    BEFORE [Timestamp] (CHANGE [scn]) CANNOT BE USED FOR RECOVERY.

Make sure to take a new backup after clearing the redo log group. Also note that if an individual member of a redo log group number cannot be cleared; error ORA-1514 is produced:

SQL> alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf';
alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: '/oracle/dbs/log3_A.dbf'

This is the expected behavior.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle database – How to create pfile or spfile using the current parameters
  2. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above
  3. How to Recover A Dropped Tablespace Using TSPITR
  4. How to Trace Dynamic Registration from PMON or LREG (Oracle 12c)
  5. How to add and drop online redo log members and groups in Oracle
  6. How to Identify Different File types and space used in Flash Recovery Area
  7. How to Modify an Existing ASM Spfile in a RAC Environment
  8. How to move ASM spfile to a different disk group
  9. How to Convert STANDARD ASM to FLEX ASM in 12C
  10. What are different Oracle Database Vault Roles

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary