收到logical standby数据库告警 LSP0: can't recover from rollback of multi-chunk txn, aborting

末蓝、 2022-08-13 03:29 68阅读 0赞

收到数据库告警物理备库应用停止

LOGSTDBY status: ORA-16246: User initiated abort apply successfully completed  
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action

分析这不是问题，只是回滚事物，然后在启动恢复应用进程，如下是support上的文档

In this Document

Symptoms  
  Cause  
  Solution  
  References  
  
APPLIES TO:  
  
Oracle Server - Enterprise Edition - Version: 9.2.0 to 11.1.0  
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).