source - LONDON
target - GERMANY
golden gate user - ggadmin

for replicating sequences we need to run @sequence.sql in target database as well

SYS @ GERMANY > @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
ggadmin
Setting schema name to GGADMIN

UPDATE_SEQUENCE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


GETSEQFLUSH

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


SEQTRACE

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


REPLICATE_SEQUENCE STATUS:

Line/pos
----------------------------------------
Error
-----------------------------------------------------------------
No errors
No errors


STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support

modify extract parameter file

add sequence claue in extract file. By using below we will replicat all sequences of us01 schema

GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 5> view params E_ICR01

Extract E_ICR01
userid ggadmin@LONDON password ggadmin
exttrail /home/oracle/EXTRACT/LONDON/EA
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE ALL
sequence us01.*;
table us01.*;

start extrcat

GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 21> start extract *

Sending START request to MANAGER ...
EXTRACT E_ICR01 starting

flush sequence in extract

GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 22> FLUSH SEQUENCE us01.*

2020-10-05 13:45:15  ERROR   OGG-15300  GLOBALS file GGSCHEMA parameter must be specified.

to resolve above error add ggschema in globals files

GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 23> edit params ./GLOBALS



GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 24> view params ./GLOBALS

GGSCHEMA ggadmin

exit and login again and then try flush

GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 26> exit
[oracle@oel6-node02 GG_LONDON]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (oel6-node02.localdomain) 1> dblogin userid ggadmin@LONDON password ggadmin
Successfully logged into database.

now flush sequence
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 2> FLUSH SEQUENCE us01.*

2020-10-05 13:47:21  INFO    OGG-15311  Successfully flushed 1 sequence(s) us01.*.

replicat parameter file

replicat parameter file should contain map statement. here we are using us01.* so it will cover all objects of us01 schema

Replicat R_ICJ01
SETENV(ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/db_1")
SETENV(ORACLE_SID = "GERMNAY")
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, append, megabytes 500
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
SOURCECHARSET PASSTHRU
UserID ggadmin@GERMANY, Password ggadmin
--showsyntax
REPERROR (1403, DISCARD)
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
map us01.*, target us01.*;

test sequence replication

create sequence on source side

US01 @ LONDON > CREATE SEQUENCE TEST_SEQ_01
START WITH 10
INCREMENT BY 1000
MINVALUE 10
CACHE 20
NOCYCLE
NOORDER;  2    3    4    5    6    7

Sequence created.


US01 @ LONDON > SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'TEST_SEQ_01';   2    3

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
        10         1000         20          10

do multiple iteration of

SELECT TEST_SEQ_01.NEXTVAL from dual;

now check current value on source

US01 @ LONDON >  SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'TEST_SEQ_01';     2    3

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
        10         1000         20       20010

lets check on target side value of sequence

US01 @ GERMANY >  SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'TEST_SEQ_01';     2    3

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
        10         1000         20       21010

we can see that sequence are replicated on target side.

so whenver you do a nextval and currval on target side you will get the value greater than or equal to of source side.

source side


US01 @ LONDON > SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'TEST_SEQ_01';   2    3

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
        10         1000         20       20010

US01 @ LONDON > SELECT TEST_SEQ_01.currval from dual;

   CURRVAL
----------
     13010

same time on target side

US01 @ GERMANY >  SELECT MIN_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
FROM user_sequences
WHERE sequence_name = 'TEST_SEQ_01';  2    3

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
        10         1000         20       21010

1 row selected.

US01 @ GERMANY > SELECT TEST_SEQ_01.NEXTVAL from dual;

   NEXTVAL
----------
     21010

1 row selected.

US01 @ GERMANY > SELECT TEST_SEQ_01.currval from dual;

   CURRVAL
----------
     21010

1 row selected.