Infrastructure at your Service

Marc Wagner

Applying archive log on a standby is failing with error ORA-00756: recovery detected a lost write of a data block

During one last ODA project, I was deploying dbvisit software version 9.0.10 on Oracle database SE Edition version 18.7. From time to time I was getting lost write of a data block with type KTU UNDO on the standby. Through this blog, I would like to share my investigation and experience on this subject.

Problem description

Applying archive log on a standby database will generate following output :
PID:80969
TRACEFILE:80969_dbvctl_DBTEST_202005141045.trc
SERVER:ODA01
ERROR_CODE:2001
ORA-00756: recovery detected a lost write of a data block

The full dbvisit output is the following one :
[email protected]:/u01/app/dbvisit/standby/ [rdbms18000_1] ./dbvctl -d DBTEST
=============================================================
Dbvisit Standby Database Technology (9.0.10_0_g064b53e) (pid 80969)
dbvctl started on ODA01: Thu May 14 10:45:24 2020
=============================================================
 
 
>>> Applying Log file(s) from ODA02 to TESTDB on ODA01:
 
thread 1 sequence 8258 (1_8258_1033287237.arc)... done
thread 1 sequence 8259 (1_8259_1033287237.arc)... done
...
...
...
 
<<<>>>
PID:80969
TRACEFILE:80969_dbvctl_SALESPRD_202005141045.trc
SERVER:SEERP1SOP011-replica
ERROR_CODE:2001
ORA-00756: Recovery hat einen verlorenen Schreibvorgang eines Datenblockes ermittelt
 
 
>>>> Dbvisit Standby terminated <<<<

    Note that this problem could also be faced :

  1. during Data Guard MRP Recovery process
  2. doing a restore/recover of a database with RMAN

The consequence was that no more archive log could be applied on the standby.

Troubleshooting

Alert log and trace file

In the alert log following errors could be found :
Additional information: 7
ORA-10567: Redo is inconsistent with data block (file# 7, block# 3483690, file offset is 2768584704 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: Datendatei 7: '/u02/app/oracle/oradata/DBTEST_DC13/DBTEST_DC13/datafile/o1_mf_undotbs1_h59nykn5_.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
2020-05-14T10:56:46.756001+02:00
ERROR: ORA-00756 detected lost write of a data block
Recovery interrupted!

Following errors was displayed in the trace file :
[email protected]:/u01/app/oracle/diag/rdbms/dbtest_dc13/DBTEST/trace/ [DBTEST] grep "KCOX_FUTURE" *
DBTEST_ora_10502.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_20942.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_22282.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_22525.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_37658.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_4482.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_50411.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_56399.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_64093.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_67930.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_78658.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_80717.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_91154.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_9180.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
DBTEST _ora_95242.trc:KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK

Checking corruption

    Checking corruption, we can see that :

  1. There is no block corruption
  2. The corruption is only raised on UNDO blocks

[email protected]:/home/oracle/ [DBTEST] rmanh
 
Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 12 16:28:28 2020
Version 18.7.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
 
RMAN> connect target /
 
connected to target database: DBTEST (DBID=3596833858, not open)
 
RMAN> validate check logical datafile 7;
 
Starting validate at 12-MAY-2020 16:28:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u02/app/oracle/oradata/DBTEST_DC13/DBTEST_DC13/datafile/o1_mf_undotbs1_h59nykn5_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 1 3932160 818750393
File Name: /u02/app/oracle/oradata/DBTEST_DC13/DBTEST_DC13/datafile/o1_mf_undotbs1_h59nykn5_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 3932159
 
Finished validate at 12-MAY-2020 16:29:00
 
RMAN> select * from V$DATABASE_BLOCK_CORRUPTION;
 
no rows selected
 
RMAN>

Root cause

This is a known 11.2.0.4 bug that affects 18.7 as well: Bug 21629064 – ORA-600 [3020] KCOX_FUTURE by RECOVERY for KTU UNDO BLOCK SEQ:254 sometime after RMAN Restore of UNDO datafile in Source Database (Doc ID 21629064.8)

Workaround

On both primary and standby databases set _undo_block_compression hidden parameter to false.
SQL> alter system set "_undo_block_compression"=FALSE scope=both;
 
System wurde geandert.
 
SQL>

Knowing this is a hidden parameter, I would recommend you to open an Oracle SR before setting it to your database. Neither the author (that’s me 🙂 ) nor dbi services 😉 would be responsible for any issue or consequence following commands described in this blog. This would be your own responsability. 😉

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Marc Wagner
Marc Wagner

Senior Consultant