GoldenGate – Cyclic Sequence Replication

GoldenGate sequence replication allows you to ensure that your sequence values on the target database are never lower than the values on the source. This is critical if you’re using GoldenGate as a near-zero downtime migration tool to avoid using the same value twice. However, if you use cyclic sequences within your schema (created with the CYCLE option) then you may hit a problem with replicat appearing to hang.

What happens with sequence replication is basically as follows –

Highest cached value on source = 10;
Highest value on target = 7;
On target: select sequence.nextval from dual;
On target: select sequence.nextval from dual;
On target: select sequence.nextval from dual;
On target: select sequence.nextval from dual;

This is fine most of the time, but when using a cyclic sequence if the target sequence manages to get ahead of the source sequence then it will actually see it as being a whole cycle behind and it will start incrementing one value at a time. For a sequence with a maximum value of billions, this is going to consume a whole CPU for a very very long time –

 8244 oracle    20   0 1825m  96m  91m R 97.7  2.4   9:45.51 oraclegg2db (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

If you take a look at what this process is actually doing, you’ll see the following statements repeating over and over –

BEGIN  "GG_DDL" .replicateSequence (TO_NUMBER(5000000040), TO_NUMBER(0

Try it with the following test case –

-- On the source, create the sequence -

create sequence test_sequence start with 1 cycle maxvalue 9999999999;

-- On the target, create (dropping first if you are using DDL replication) the sequence but with a lower value -

create sequence test_sequence start with 50 cycle maxvalue 9999999999;

-- Then on the source -

select test_sequence.nextval from dual;

Straight away, you’ll see replicat hang, your lag time increasing and high CPU on a server process.

It does make sense – as it is a cyclic sequence, there is no reason why a higher numeric value can’t represent an earlier sequence value. This is what Oracle sees, and it tries to bridge that gap one value at a time.

Oracle have obviously acknowledged this isn’t always desirable, and talk about it in MOS article 1331998.1. Basically, they have provided a hidden parameter named _MAXSEQUENCEDISTANCE. Set it in your replicat parameter file as follows –


This will ensure that if the target is more than 10 values ahead of the source, replicat will simply allow the values to catch up naturally before it starts keeping them in sync again. Much much easier on the CPU cycles!

The problem is that once you hit this problem, it isn’t sufficient to simply change this parameter. You won’t be able to stop the replicat process (it won’t respond to GGSCI) and if you try to kill it, the underlying session continues within the database. Once you’re in this situation, you’ll need to identify the GoldenGate session within the database and kill it. You can then put the hidden parameter in place, (optionally recreate the sequence on the target with a lower value than on the source), and then start up replicat again.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: