4 Replies Latest reply: Sep 9, 2011 1:26 AM by stevencallan RSS

    Golden Gate Sequence Value replication?

      Hi all,

      I have searched the web, and read the Golden Gate documentation but I've found mixed answers.

      I'm just trying to ascertain for certain whether it's possible to automatically replicate sequence values in a bi-directional configuration. Both databases will be identical and both will be 11gR2 with the latest version of GG.

      If it is not possible to automatically increment sequences in a bi-directional configuration, what are the best practices for maintaing them?

      One option is the alternate sequences on each database, one with even values (for example) and one with odd values, but this requires deployment of new sequences on both databases, something we were hoping would be taken care of by the replication. (yes the sequence creation can be taken care of but the value is not incremented on the target database).

      another way that we thought of off the top of our heads is to have an on insert trigger (we only use sequences to generate surrogate keys) which will select nextval from the target database via a db link, but this seems somewhat cumbersome.

      What is best practice?
        • 1. Re: Golden Gate Sequence Value replication?
          Hi ,

          Have you tried replicating the sequences using the sequence parameter in the extract side and having the same in target side.

          Extract side:

          sequence scott.seq1;

          Replicat side

          map scott.seq1, target scott.seq1;

          Creating a sequence with odd values on one and even on the other should be best solution according to me.

          • 2. Re: Golden Gate Sequence Value replication?
            Use sequence parameter... here is from Golden gate document:

            Valid for Extract
            Use the SEQUENCE parameter to extract sequence values from the transaction log forpropagation to a GoldenGate trail and delivery to another database. Currently, 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 GoldenGate DDL support environment. You can just use the SEQUENCE parameter.

            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 * source_RAC_nodes) <= target_highwater_value
            source_highwater_value + (source_cache_size * source_increment_size * source_RAC_nodes) >= 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 highwater value is 40, and CACHE is 20, and the source INCREMENTBY value is 1, and there are two source RAC nodes, the target highwater value should be at least 80:
            40 + (20*1*2) <80

            If the target highwater value is less than 80, GoldenGate updates the sequence to increase the highwater value, so that the target remains ahead of the source. To get the current highwater value, perform this query:
            SELECT last_number FROM all_sequences WHERE sequence_owner=upper('SEQUENCEOWNER') AND sequence_name=upper('SEQUENCENAME');
            • 3. Re: Golden Gate Sequence Value replication?
              thanks for that.

              you'll note on the next page of the documentation it actually explains that it's not available in a bi-directional configuration, which was my original question.

              • 4. Re: Golden Gate Sequence Value replication?
                The GoldenGate forum is here: