If the performance of sequence replication is a concern, other alternatives to replication are available 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. If each site has a unique range, there is no value in sending (replicating) changes of one site's range to another site.
Similar to concatenating, each site can obtain a different range of numbers by having different starting points, or increment values, for the same sequence. For example, the sequence at one site can start at 1 and increment by 2 to generate odd numbers (1, 3, 5), while the other site starts at 2 and generates even numbers (2, 4, 6). Again, each site would have a unique range and would 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 failover. Rather than continually replicating a sequence's value, the value of the sequence at the standby site can be updated as part of the failover tasks. After failover and before the standby allows connection to client applications, a script or procedure can query the last-used sequence value (based on the last table to use it for a primary key) and update or redefine the sequence once, based on that calculated value.
