SEQUENCE

Valid For

Extract

Description

Use the SEQUENCE parameter to capture sequence values from the transaction log. Currently, Oracle GoldenGate supports sequences for the Oracle database.

Note:

DDL support for sequences (CREATE, ALTER, DROP, RENAME) is compatible with, but not required for, replicating sequence values. To replicate just sequence values, you do not need to install the Oracle GoldenGate DDL support environment. You can just use the SEQUENCE parameter.

Oracle GoldenGate ensures that the values of a target sequence are:

higher than the source values if the increment interval is positive
lower than the source values if the increment interval is negative

Depending on the increment direction, Replicat applies one of the following formulas as a test when it performs an insert:

source_highwater_value + (source_cache_size * source_increment_size) = target_highwater_value

Or...

source_highwater_value + (source_cache_size * source_increment_size) >= target_highwater_value

If the formula evaluates to FALSE, the target sequence is updated to be higher than the source value (if sequences are incremented) or lower than the source value (if sequences are decremented). The target must always be ahead of, or equal to, the expression in the parentheses in the formula. For example, if the source high water value is 40, and CACHE is 20, the target high water value should be at least 60:

40 + (20*1) <60

If the target high water value is less than 80, Oracle GoldenGate updates the sequence to increase the high water value, so that the target remains ahead of the source. To get the current high water value, perform this query:

SELECT last_number FROM all_sequences WHERE sequence_owner=upper('SEQUENCEOWNER') AND sequence_name=upper('SEQUENCENAME');

Supported Processing Modes

The processing modes that support the capture of sequences are as follows:

Oracle GoldenGate supports sequences in an active-passive high-availability configuration. Oracle GoldenGate does not support the replication of sequence values in an active-active configuration. An active-passive configuration includes a primary Extract, a data pump, and a Replicat on both servers, but the processes are active in only one direction. The Extract process on the failover server must be inactive, which includes not capturing sequences. See the Administering Oracle GoldenGate for more information about how to configure Oracle GoldenGate for high-availability.
If using SEQUENCE for a primary Extract that writes to a data pump, you must also use an identical SEQUENCE parameter in the data pump.
Oracle GoldenGate initial load methods that contain the SOURCEISTABLE parameter, either as an Extract parameter or within ADD EXTRACT, do not support the replication of sequence values.

Guidelines for Using SEQUENCE

The cache size and the increment interval of the source and target sequences must be identical.
The cache can be any size, including 0 (NOCACHE).
The sequence can be set to cycle or not cycle, but the source and target databases must be set the same way.
To add SEQUENCE to a configuration in which DDL support is enabled, you must re-install the Oracle GoldenGate DDL objects in INITIALSETUP mode.

Error Handling

If Extract cannot resolve a sequence name, it ignores the operation.
To enable Replicat error handling for sequences, use the REPERROR parameter. This parameter is available as an option in the MAP parameter and also as a standalone parameter. REPERROR can detect if a sequence has been dropped on the target and can be used to retry a sequence operation until the sequence is recreated.
REPERROR does not handle missing objects on startup. Use DDLERROR with IGNOREMISSINGTABLES.

Other Important Information

Gaps are possible in the values of the sequences that Oracle GoldenGate replicates because gaps are inherent, and expected, in the way that sequences are maintained by the database. However, the target values will always be greater than those of the source.
If Extract is running in single-threaded mode on a RAC system, and if sequences are updated on a node that has lag, it might take more time to capture a sequence. This is normal behavior.
In a failover, any problem that causes the loss or corruption of data in a transaction log or Oracle GoldenGate trail file will cause the loss of the replicated sequence updates.
The statistics shown by SEND EXTRACT and SEND REPLICAT when used with the REPORT option will show the sequence operation as an UPDATE.

Default

None

Syntax

SEQUENCE [container.]schema.sequence;

[container.]schema.sequence: Specifies the fully qualified name of the source sequence. Include the name of the pluggable database if the source is an Oracle container database. To specify object names and wildcards correctly, see Administering Oracle GoldenGate.
;: Terminates the SEQUENCE parameter statement.

Example

SEQUENCE hr.employees_seq;