Similar to the support for table and procedure replication, Oracle Sequences are individually marked for replication using the new Replication Agent pdb_setrepseq command. For a description of the pdb_setrepseq command, refer to the Mirror Replication Agent Reference Guide.
Individual sequence changes are not logged in the Oracle database log file; however, changes to Oracle Sequences do impact (update) the Oracle sys.seq$ table. These changes do not occur with each new sequence value generated. Instead, the sys.seq$ table is updated periodically, based on sequence caching refresh activity or other system changes. The value stored in the sys.seq$ table for a sequence is the “next” value to be assigned “after” the existing cache of values has been exhausted.
For example, a newly created sequence starts with a value of 1, increments by 1, and has a cache value of 20. (These are all default values and can be customized.) The value stored in the sys.seq$ record for this new sequence is 21. This indicates that the “next” value to be used by the sequence, after the existing cache of 20 numbers is used, is 21. The record in sys.seq$ does not change until the sequence value hits 21. At that time, Oracle will cache the next 20 values for the sequence, and the sys.seq$ record will be updated to 41. It is this value (41), recorded in change to the sequences sys.seq$ record, that will be used for replication. The key point is to recognize that not every individual sequence update is recorded in the log and therefore is not available for replication.
When a sequence is marked for replication, changes to that sequence against sys.seq$ are captured and sent to Replication Server in the form of parameters passed to a procedure. The procedure (rs_update_sequence) must be installed at the standby site as part of system setup, as well as a function replication definition for that procedure. At the standby site, an implementation of rs_update_sequence will increment a same-named sequence until its value is equal to the value at the primary site. Scripts are provided with installation to create the rs_update_sequence stored procedure and function replication definition and are located as follows:
$SYBASE/RAX-15_0/scripts/ oracle_create_replicate_sequence_proc.sql $SYBASE/RAX-15_0/scripts/ oracle_create_rs_sequence_repdef.sql
Compared to the performance of incrementing a sequence at the primary database, particularly where sequence values are cached, the effort to increment the same sequence at the standby site may be less efficient. The stored procedure must dynamically determine the sequence to increment and must loop internally, incrementing the sequence until the primary value has been reached. The loop is required because there is no way to assign a specific value to a sequence.
Because the name of the sequence is passed as a parameter, Oracle cannot pre-compile the procedure for efficiency. With the addition of the looping activity required to properly increment the sequence, the performance of the solution may impact some environments where a large number of highly used sequences is the norm.
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 that 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 reason 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, by having a unique range, each site 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.
