If the performance of sequence replication is a concern, there are other alternatives to replication that support primary and standby use of the same sequence. These alternatives are currently suggested by Oracle and others interested in providing sequence coordination between multiple sites:
Assuming the sequence is being used to generate primary key values, the sequence at each site can be concatenated with something unique to the site. For example, use a sequence number concatenated with the database name, site name or something similar. This technique allows each site to maintain a unique range of sequence of numbers. Each site having a unique range would mean there would be no value in sending (replicating) changes of one site’s range to another site.
Similar to concatenating, each site could obtain a different range of numbers by having different starting points, or increment values, for the same sequence.As an example, the sequence at one site could start at one and increment by two to generate odd numbers (1, 3, 5) while the other site starts at two an generates even numbers (2, 4, 6). Again, each site would have a unique range and could avoid any need for replication.
A third option is available to standby solutions, where the standby site is for read-only, and does not access the sequence value until fail-over. Rather than continually replicate a sequence’s value, the value of the sequence at the standby site could be updated as part of the failover tasks. After failover and before the standby allows connection to client applications, a script or procedure could query the last used sequence value (based on the last table to use it for a primary key) and update or redefine the sequence one, based on that calculated value.
