Our Standby Database stopped applying new logs and is lagging further and further behind. We’ve found these set of errors in the alert log:
Corrupt block seq: 166145 blocknum=795955.
Bad check value found during backing up archived log
Data in bad block - flag:1. format:34. bno:795955. seq:166145
beg:164 cks:9865
calculated check value: 8847
Reread of seq=166145, blocknum=795955, file=+FRA/PRODDR/ARCHIVELOG/2021_07_20/thread_1_seq_166145.1495.1078412405, found same corrupt data
Reread of seq=166145, blocknum=795955, file=+FRA/PRODDR/ARCHIVELOG/2021_07_20/thread_1_seq_166145.1495.1078412405, found same corrupt data
...
Fri Jul 23 15:21:13 2021
Errors in file /oracle/oraBase/oracle/diag/rdbms/proddr/PRODDR/trace/PRODDR_pr00_6164.trc:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 795955 change 29271160322 time 07/20/2021 14:52:49
ORA-00334: archived log: '+FRA/PRODDR/ARCHIVELOG/2021_07_20/thread_1_seq_166145.1495.1078412405'
The snippet in the DB alert log tells us that a block corruption in log sequence 166145 broke the Managed Recovery process. To resolve this issue in Oracle 12c and newer, we simply delete the corrupted log from the Standby using RMAN and Oracle automatically re-transport the log and started the applying the archiving logs on the Standby. If you delete the log manually outside of RMAN, you will need to run RMAN catalog command to update RMAN about the deleted log.
Connect to RMAN and list the log file to verify RMAN can see it.
RMAN> list archivelog sequence 166145;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PRODDR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
166576 1 166145 A 20-JUL-21
Name: +FRA/PRODDR/ARCHIVELOG/2021_07_23/thread_1_seq_166145.2571.1078673041
RMAN> delete force archivelog sequence 166145;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=379 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=503 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=627 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=751 device type=DISK
List of Archived Log Copies for database with db_unique_name PRODDR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
166576 1 166145 A 20-JUL-21
Name: +FRA/PRODDR/ARCHIVELOG/2021_07_23/thread_1_seq_166145.2571.1078673041
Delete the log file.
RMAN> delete force archivelog sequence 166145;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=379 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=503 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=627 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=751 device type=DISK
List of Archived Log Copies for database with db_unique_name PRODDR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
166576 1 166145 A 20-JUL-21
Name: +FRA/PRODDR/ARCHIVELOG/2021_07_23/thread_1_seq_166145.2571.1078673041
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=+FRA/PRODDR/ARCHIVELOG/2021_07_23/thread_1_seq_166145.2571.1078673041 RECID=166576 STAMP=1078673067
Deleted 1 objects
Alert log shows that Oracle automatically transport the log file and apply on the Standby Database.
Fri Jul 23 15:24:00 2021
Media Recovery Waiting for thread 1 sequence 166145 (in transit)
Fri Jul 23 15:24:01 2021
Archived Log entry 166575 added for thread 1 sequence 166662 ID 0x1813f9ed dest 1:
Fri Jul 23 15:24:27 2021
Archived Log entry 166576 added for thread 1 sequence 166145 rlc 983566170 ID 0x1813f9ed dest 2:
Fri Jul 23 15:24:28 2021
Media Recovery Log +FRA/PRODDR/ARCHIVELOG/2021_07_23/thread_1_seq_166145.2571.1078673041
Note: If Oracle does not automatically transfer the deleted log file (normally happens in older databases) after 30 minutes, you will need to manually copy the missing log over from the Primary Database.