Golden Gate 12c and DIY Sequence Replication with PL/SQL

Recently, while migrating AIX 11gR2 Databases to Oracle Linux 12cR1 on an ODA X5-2, our setup of Sequence Replication by Oracle Golden Gate appeared to be faulty. The target side sequences were not automatically incremented.

The problem came to light during the migration of acceptance databases, and under some time pressure it was devised to generate drop + create statements ( start with = DBA_SEQUENCES.LAST_NUMBER + DBA_SEQUENCES.INCREMENT_BY ) of all sequences in the Source, and to run these statements on the Target. Although this eventually resulted in the desired result, there were 2 side effects:

With a total of 1270 sequences, the operation as a whole took more than an hour.
Packages and triggers referencing these sequences became invalid.

Further research revealed that the Golden Gate Sequence Replication of Production suffered the same problem and I wondered if I could find a better solution with now a bit more time at hand. Well, I discovered that to set any desired sequence “currval” value, a one-time temporary adjustment of the increment and subsequent call to the sequence “nextval” pseudo column is sufficient. What follows is the output of a quick test, but check out what happens with “USER_SEQUENCES.LAST_NUMBER”, and what it really means in combination with the cache.

Create a test sequence

CREATE SEQUENCE TEST_SEQ_01 
START WITH 10 
INCREMENT BY 1000 
MINVALUE 10 
CACHE 20 
NOCYCLE 
NOORDER;
 
-- the sequence returns no current value yet
SELECT TEST_SEQ_01.CURRVAL from dual;
  ORA-08002: sequence TEST_SEQ_01.CURRVAL is not yet defined in this session.

-- check out last_number... it equals nextval because the cache doesn't exist yet
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01'; 
  MIN_VALUE	INCREMENT_BY CACHE_SIZE	LAST_NUMBER
  10	      1000	       20	        10

-- generate the first number and create the cache
SELECT TEST_SEQ_01.NEXTVAL from dual;
  NEXTVAL
  10

-- last_number is updated as the highest possible number of the cache
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01';
  MIN_VALUE	INCREMENT_BY CACHE_SIZE LAST_NUMBER
  10	      1000	       20	        20010

-- and now a current value is returned
SELECT TEST_SEQ_01.CURRVAL from dual;
  CURRVAL
  10

Set the current sequence value = 20000 without recreating the sequence

-- adjust the increment
ALTER SEQUENCE TEST_SEQ_01 INCREMENT BY 19990;

-- last_number equals the sequence next value
-- the last "alter sequence" command must have flushed the cache
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01';
  MIN_VALUE	INCREMENT_BY CACHE_SIZE LAST_NUMBER
  10	      19990	       20	        20000

-- generate the next value and create a new cache
SELECT TEST_SEQ_01.NEXTVAL from dual
  NEXTVAL
  20000

-- last_number is updated as the highest possible number of the cache
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01';
  MIN_VALUE	INCREMENT_BY CACHE_SIZE LAST_NUMBER
  10	      19990	       20	        419800

-- the sequence has the desired current value
SELECT TEST_SEQ_01.CURRVAL from dual
  CURRVAL
  20000

Reset the increment

-- set the increment_by value back to original
ALTER SEQUENCE TEST_SEQ_01 INCREMENT BY 1000;

-- again, the cache is flushed and last_number equals the next value
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01';
  MIN_VALUE	INCREMENT_BY CACHE_SIZE LAST_NUMBER
  10	      1000	       20	        21000

-- generate the next value and create a new cache
SELECT TEST_SEQ_01.NEXTVAL from dual
  NEXTVAL
  21000

-- last_number is updated as the highest possible number of the cache 
SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER 
FROM user_sequences 
WHERE sequence_name = 'TEST_SEQ_01';
  MIN_VALUE	INCREMENT_BY CACHE_SIZE LAST_NUMBER
  10	      1000	       20	        41000

-- the increment is back to 1000
SELECT TEST_SEQ_01.NEXTVAL from dual
  NEXTVAL
  22000

This test shows that “USER_SEQUENCES.LAST_NUMBER”:

I decided to use “DBA_SEQUENCES.LAST_NUMBER” instead of the “currval” pseudo column to compare sequences in Source and Target. The reason is that “currval” is only ( and by definition ) the value returned by my sessions last call to “nextval”. If my session has not called “nextval” yet, “currval” is undefined. So I would have to “nextval” 1270 sequences in Source and also in Target before I could even start with the comparison, while last_numbers are already there to compare with. Also, this activity is unwanted during the short inactive Source and inactive Target migration stage and would take too much time. Last but not least, an exact match of sequence “currval” values is not really necessary… a guarantee of higher sequence “currval” values in Target compared to those in Source is quite enough.

The next short piece of code is what I eventually came up with and used in the Production migration. It took less than 3 minutes processing time, did not render any Oracle object invalid, and contributed highly to a very limited migration inactivity time.

-- Code assumes:
--   1. "nocycle" sequences with positive "increment_by" values
--   2. identical number of sequences and sequence DDL in Source and Target Database 
-- Grant 'alter any sequence' and 'select any sequence' to the owner
-- Replace the database link and schema names with your own
-- Run the code from Target
declare
  v_ret PLS_INTEGER := 0;
  v_dummy VARCHAR2(100);
  v_ln number := 0;
  v_ib number := 0;
  v_cz number := 0;
  v_incr number := 0;
begin
  for i in ( select sequence_owner  so
                  , sequence_name   sn
                  , last_number     ln
                  , increment_by    ib
                  , cache_size      cz 
             from dba_sequences@<DBLINK_FROM_SOURCE2TARGET>
             where sequence_owner in ('<SCHEMA01>','<SCHEMA02>','<SCHEMA03>','<SCHEMA04>') )
  loop
      select last_number
           , increment_by 
           , cache_size
        into v_ln
           , v_ib
           , v_cz 
      from dba_sequences
      where sequence_owner = i.so
        and sequence_name = i.sn;

-- set the difference in last_numbers as increment if target.last_number < source.last_number
      if v_ln < i.ln then
        v_incr := i.ln - v_ln;
-- set the cache as increment if last_numbers match  
      elsif v_ln = i.ln then
        v_incr := v_ib * v_cz;
      end if;
      
      if v_ln <= i.ln then    
        execute immediate 'alter sequence '||i.so||'.'||i.sn||' increment by '||v_incr;
        execute immediate 'select '||i.so||'.'||i.sn||'.nextval from dual' into v_dummy;
        execute immediate 'alter sequence '||i.so||'.'||i.sn||' increment by '||v_ib;
        v_ret := v_ret +1;
      end if;
  end loop;
  dbms_output.put_line('Nr. sequences adjusted: '||v_ret);
end;
/