Oracle GoldenGate 21c -- Enable Replication of Sequences for an Oracle Database

Problem

You want to enable replication of sequences for an Oracle Database using Oracle GoldenGate 21c version

Solution

Oracle GoldenGate 21c online documentation has a flaw for the chapter on how to setup the sequence replication, and following the documentation instruction do not work

Follow the below steps to enable the Oracle sequences for GoldenGate 21c:

On the target:

1) Create a new schema or use the existing schema used for the Replicat or DDL Support, and ensure the required privileges granted:

CREATE USER <schema> IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE, DBA TO <schema>;

2) Ensure the GLOBALS parameter file of the GolgenGate instance contains the setting:

GGSCHEMA <schema>

3) As SYS user, execute the script:

<ogg_home>\lib\sql\legacy\sequence.sql 

At prompt enter the schema from step 1) above

4) In case the user from step 1) and the user used by the Replicat are different, grant the privilege:

GRANT EXECUTE on <schema>.replicateSequence TO <replicat_user>;

On the source:

1) Create a new schema or use the existing schema used for the Extract or DDL Support, and ensure the required privileges granted:

CREATE USER <schema> IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE, DBA TO <schema>;

2) Ensure the GLOBALS parameter file of the GolgenGate instance contains the setting:

GGSCHEMA <schema>

3) As SYS user, execute the script:

<ogg_home>\lib\sql\legacy\sequence.sql 

At prompt enter the schema from step 1) above

4) In case the user from step 1) and the user used by the Extract are different, grant the privilege:

GRANT EXECUTE on <schema>.updateSequence TO <extract_user>;

5) As user SYS, execute:

ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

6) Restart Extract, using adminclient, log into the database and run FLUSH SEQUENCE command against the sequences in the subject:

OGG (not connected) 1> connect https://localhost:34030 as sys !
Password for 'sys' at 'https://localhost:34030/gg_inst1':

OGG (https://localhost:34030 gg_inst1) 2> DBLOGIN USERIDALIAS DB0117SR
Successfully logged into database.

OGG (https://localhost:34030 gg_inst1 as DB0117SR@DB0117SR) 3> FLUSH SEQUENCE owner2.*
2023-01-22T21:53:28Z  INFO    OGG-15311  Successfully flushed 2 sequence(s) owner2.*.