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;