Sequences are replicated in a High Availability replication environment to facilitate a quick failover in planned or unplanned situations. Anytime after failover the secondary is guaranteed to have a value higher than the one that was last selected on primary. There will be some gaps between the source and target sequences but the uniqueness is guaranteed. All this happens because of the way Shareplex replicates sequences (see solution SOL16434 for details). At times the integrity of sequences on secondary database server may be lost. The reasons could be direct user activity, data loss due to queue corruption, among others. At that point if one had to failover to secondary, there is a good chance that the sequences on secondary may not be in sync with the primary. They are supposed to be in synchronization with primary if they return a higher value anytime a SELECT is issued on them.
The sequences on secondary are not in sync with primary.
The following procedure will ensure that the sequences in secondary are restored to a value as required by Shareplex sequence replication in a High Availability environment. This procedure entails downtime on the source sequences as otherwise it is bound to fail. The magnitude of the downtime will depend on the number of sequences to be corrected. Here are the steps involved:
Quiet the primary database (as sequences cannot be changing at this step)
Issue flush from primary to secondary
Make a copy of the currently active config file on primary and comment out the sequences from the copy
Activate the copy of the primary config file (without the sequences)
Once the Post stops due to flush on secondary, remove sequences from the copy of the secondary configuration file
Activate the copy of the secondary config file (without the sequences)
Drop sequences on the secondary
Recreate sequences on the secondary with a start value equal to the last_number of the corresponding sequence on the primary.
Select the nextval from each sequence created on the secondary
Activate the original config file (with the sequences) on the primary
Activate the original config file (with the sequences) on the secondary.
Allow users back into the primary system
Start Post on secondary
