Logical apply aborts with 'CAN'T RECOVER FROM ROLLBACK OF MULTI-CHUNK TXN, AB...

郑全

Applies to:  

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Database Exadata Express Cloud Service - Version N/A and later

Oracle Database Exadata Cloud Machine - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Information in this document applies to any platform.

This problem can occur on any platform.

Symptoms

You face Logical Apply suddenly aborting. Logical Standby ALERT.LOG shows:

LSP0: rolling back apply server 4
LSP0: apply server 4 rolled back
LSP0: can't recover from rollback of multi-chunk txn, aborting..
LOGSTDBY Apply process P004 pid=25 OS id=176 stopped
LOGSTDBY Apply process P005 pid=26 OS id=179 stopped
LOGSTDBY Apply process P007 pid=27 OS id=183 stopped
LOGSTDBY Analyzer process P003 pid=24 OS id=174 stopped
LOGSTDBY Apply process P008 pid=28 OS id=185 stopped
LOGSTDBY Apply process P006 pid=12 OS id=181 stopped

Cause

In order to reduce Memory Consumption on the Logical Standby Database, large Transactions to be applied are splitted into smaller Pieces (Chunks). Those Chunks can be applied to the Logical Standby Database before the Commit/Rollback has been reached by the LogMiner in the Redo. Once a Chunk is applied, the LCR's belonging to this Chunk get deleted and so the Memory is freed up.
However, sometimes it is possible that such a Transaction is blocking another Transaction. So in this Case, the splitted large Transaction is rolled back in order to let the other Transaction apply.
After that we are missing the LCR's from the already applied, then rolled back Chunks. In order to make the LogMiner being able to read those again, the Logical Apply needs to be aborted and restarted so that the LogMiner can read the large Transaction again (typically starting from the READ_SCN).

Solution

This is not a Bug or Problem. It's just as per the Design of the Logical Apply (see description above).

References