Please check What is a sequence? to learn more about the sequence in Oracle Database.
For a sequence, you can ONLY replicat in one-way replications. This means
- You can't replicat a sequence in an two-way or multi-way replication.
-
You can ONLY replicat in an active-passive HA (high availability) not an active-active HA setup. For active-active replications, the databases need to stay independent in their sequence management. You need to turn off the sequence replication by:
- Excluding sequence capture from the capture (extract) using TABLEEXCLUDE.
- Disabling triggers that process sequences with DBOPTIONS SUPRESSTRIGGERS in the delivery (replicat).
2. How the Sequence Replication Works
During a replication, Oracle GoldenGate captures the sequence updates and makes sure the target sequence value is equal or higher than the source sequence number.
- If the NOCACHE option is specified in the sequence, an data entry will show in the GoldenGate trail every time the sequence is updated.
- If the CACHE option is specified for the sequence, an data entry will show in the GoldenGate trail every time the high water mark is updated.
A (source) B (target) - Extract runs on the source database 1000 1000 1001 1001 ... 1006 1006
A (source) B (target) - Extract runs on the source database (cache 4) 1000 1005 1001 1002 1003 1004 1009 1005 ... 1008 1013
If you do need to replicat sequence objects, you then need to consider the followings:
- Use the SEQUENCE parameter in the extract to capture sequence values from the transaction log.
- Use the SEQUENCE parameter in the pump to send the sequence replication to the target.
- Make sure the database sequence cache size and the increment intervals on the source and target database are identical [1].
- Use FLUSH SEQUENCE in GGSCI immediately after you start an Extract for the first time during an initial synchronization or a re-synchronization [2].
- Use DDL replication when you need to replicate CREATE, DROP, RENAME of the sequence.
Extract/Pump: DDL INCLUDE ALL, EXCLUDE OPTPE ALTER OBJTYPE SEQUENCE SEQUENCE awsuser.member_id_seq; Replicat: MAP awsuser.member_id_seq, TARGET pmdemo.member_id_seq;
This is an DR use case where a sequence is replicat from the primary database to the standby. Both DDL and Sequence replication are enables. The replication from the standby to the primary database is also enabled but the application ensures that there is no transaction on the standby.
When the primary database fails, the database failover to the standby. The new primary will start accepting transactions until the GoldenGate replicat applied all the captured changes from the primary database. Oracle GoldenGate will start replication in the other direction. The same process applies when failing over back to the old primary database.
- Oracle Database 11.2 Documentation, CREATE SEQUENCE
- Weicheng Zhong, Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate — 3
- Gleb Otochkin, Truncate and Sequences replication in Oracle GoldenGate, July 15, 2016