APPLY process starts hunderds of plogs back

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

APPLY process does not remove old PLOGS from the filesystem ,there by filling up the filesystem. Moreover when APPLY is restarted it starts reading from the OLDEST PLOG available in the filesystem.

Problem Details

Problem Statement

PLOGS are not removed from the APPLY and fills up filesystem.

Applicable Error Code

dbvrep>
shutdown apply Upon restart APPLY will go back to plog 8120, going back 1001 plog.

Affected Versions

Replicate 2.7.x+

Affected Platforms

Platform Independent

Description

The reason the above happens might be due to a long running transaction because the 
APPLY process needs to cover all uncommitted transaction, therefore won't delete any needed plog. 
Consequentially a restart of the APPLY process will take lots of time to catch up the MINE process, since it starts reading again from the PLOG where the transaction started

Scenarios
There are two scenarios ,this might happen

  • APPLY process is far Behind the MINE process.

  • APPLY process is up to date with MINE process.

If the source database has a long running transaction , either you can wait for the transaction to complete (Application commits or rollbacks the transaction) , and wait for the APPLY to catch up with MINE or You can either commit or rollback the transactions from the command console. This can be achieved by running one of the commands provided in the below link

https://dbvisit.atlassian.net/wiki/display/ugd8/Uncommitted+transactions

But if the transactions that are long running are related to the Replicate tables it could lead to data inconsistency. It means conflicts could occur, within subsequent PLOG processing on the APPLY process,
the replication will modify its start of the APPLY process to the next longest running transaction.

 

To find out the long running transaction in the source (MINE) process. run the below command,

dbvrep> list obsolete redo

It will show you how far MINE has to go back if restarted. To check and determine the transactions which require the oldest redo, use the below link.

https://support.dbvisit.com/hc/en-us/articles/215913398-Oldest-redo-log-sequence-required-by-MINE. 

Please not that it doesn't matter if a transaction is executed within not related schemas to replication. The MINE process has to process the entire redo to see which transactions are required by the replicated and processed. We need to make sure that it satisfies ACID properties(Atomicity, Consistency, Isolation, Durability). Check the below link for more on ACID

https://en.wikipedia.org/wiki/ACID

To check the long running transactions on the APPLY process. run the below command.

dbvrep> list transactions

We have to note that, if we decide to commit or rollback the transactions , Dbvisit replicate will mark the transaction complete only in the next log switch. If a transaction is unknown to anyone and persists in the target database for a long time without a conflict it's better to rollback it.