It may take long time for updateable secondary (HDR or RSS) to become available for SQL execution
During restart an Informix updateable secondary server (either HDR or RSS) may spend a significant amount of time in the 'fast recovery' mode before it becomes available for SQL execution. In this scenario the data replication subsystem generates following message in the message log:
'Started processing open transactions on secondary during startup'.
Question is why does it happen?
The locks on secondary server are created by recovery threads while replaying logical log records. A secondary server does not able to create the locks when it restarted and at the same time there are open transactions being processed on the primary server, unless it replayed the log records prior to the initial checkpoint.
The updateable secondary server must be kept in the recovery mode until all such prior open transactions are completed in order to maintain the ‘committed read last committed’ isolation level.
You can find open transactions in 'onstat -x' output, which prevent secondary server from going out of 'fast recovery' mode. Following is an example of 'onstat -x' output:
address flags userthread locks begin_logpos current logpos isol rb_time retrys coord
700000030591028 A---- 70000003054f028 0 - - COMMIT - 0
700000030591358 A---- 70000003054f850 0 - - COMMIT - 0
700000030591688 A---- 700000030550078 0 - - COMMIT - 0
700000030595978 A---- 70000003055abc0 0 - - DIRTY - 0
700000030595ca8 A-B-- 70000003055bc10 0 124:0x0 124:0x2ef018 DIRTY 0:00 0
In the above output the 'begin_logpos' value for the highlighted transaction is '124:0x0' (where 124 is the logical log number, and 0x0 represents offset into the logical log). The simpler reason of offset '0x0' is secondary server does not know the transaction begin address since it restarted. Therefore, the secondary server will remain in 'fast recovery' mode (however the data replication is continue working) until that transaction is committed or rolled back on the primary server.
On the primary server you can find the open transaction by running onstat -x and grep for the current logpos shown in the secondary:
onstat -x | grep 124:0x2ef018
On the primary you can find the user and session id by running onstat -u and grep for associated userthread obtained from previous onstat -x output:
onstat -u | grep userthread
After some investigation of the session ( onstat -g ses sid ) you might determine it is OK to kill this session so the secondary can complete fast recovery:
onmode -z sessionid
More support for:
Software version: 11.5, 11.7, 12.1
Operating system(s): AIX, HP-UX, Linux, OS X, Solaris, Windows
Software edition: Enterprise, Growth, Ultimate, Workgroup
Reference #: 1626164
Modified date: 05 November 2014