How to deal with sequences during migration? (4300153)

If migrating the database using Shareplex, one should ensure that the sequences on target are in sync with the ones on source, or at least have values higher than the ones selected on the source the last time.

Migration of database requires special considerations for sequences.

There are two ways to handle sequences when doing migration of database using Shareplex.

a. Do not replicate sequences and prior to opening the target database for users, manually create the sequences and sync them to the last value selected on source (using "SELECT sequence.currval from DUAL" to get the last value selected on source and "CREATE SEQUENCE <name> START WITH clause" to create the sequence while maintaining other fields such as CACHE, etc, to match with those of source or change as appropriate). If you have numerous sequences to deal with, this can become a bit of an exercise.

b. Replicate the sequences and once you fail over to the new server in a planned manner (after migration), selecting them on the new server is guaranteed to return a value that will be higher than the last value selected on the source Shareplex. But there will be gaps in the value of sequences selected in old vis-a-vis new server. So if you can live with such gaps in sequence values, then this approach will not require any manual intervention and is, therefore, not prone to error.

See solution SOL16434 for more information regarding how shareplex replicates sequences.