Flush Sequence for Multitenant Container Database

You can only use the FLUSH SEQUENCE command within Oracle GoldenGate, if the sequence.sql script applies the database procedures into the GoldenGate Admin schema of the database.

Use the FLUSH SEQUENCE command immediately after you start Extract for the first time during an initial synchronization or a re-synchronization. This command updates an Oracle sequence, so that initial redo records are available at the time that Extract starts to capture transaction data. Normally, redo is not generated until the current cache is exhausted. The flush gives Replicat an initial start point with which to synchronize to the correct sequence value on the target system. From then on, Extract can use the redo that is associated with the usual cache reservation of sequence values.

The following Oracle procedures are used by FLUSH SEQUENCE:

Database	Procedure	User and Privileges
Source	`updateSequence`	Grants `EXECUTE` to the owner of the Oracle GoldenGate DDL objects, or other selected user if not using DDL support.
Target	`replicateSequence`	Grants `EXECUTE` to the Oracle GoldenGate Replicat user.

The sequence.sql script installs these procedures. Normally, this script is run as part of the Oracle GoldenGate installation process, but make certain that was done before using FLUSH SEQUENCE. If sequence.sql was not run, the flush fails and an error message similar to the following is generated:

Cannot flush sequence {0}. Refer to the Oracle GoldenGate for Oracle 
documentation for instructions on how to set up and run the sequence.sql 
script. Error {1}.

The GLOBALS file must contain a GGSCHEMA parameter that specifies the schema in which the procedures are installed. This user must have CONNECT, RESOURCE, and DBA privileges.
Before using FLUSH SEQUENCE, issue the DBLOGIN command as the database user that has EXECUTE privilege on the updateSequence procedure. If logging into a multitenant container database, log into the pluggable database that contains the sequence that is to be flushed.

FLUSH SEQUENCE must be issued at the PDB level, to create an Oracle GoldenGate user in each PDB for which the sequence replication is required. Use DBLOGIN to log into that PDB, and run the FLUSH SEQUENCE command.

It is recommended that you use the same schema in each PDB, so that it works with the GGSCHEMA GLOBALS parameter file.

In the following example, the environment setup is for Oracle 21c to Oracle 21c Replication, with integrated Extract, parallel Replicat using Oracle GoldenGate 21c (21.3.0).

The following table lists the names of source and target CDB, PDBs, and their corresponding user credentials for connecting to the database.

Source CDB Target CDB

Source CDB	Target CDB
`NORTH`	`SOUTH`
PDB Name: `DBEAST`	PDB Name: `DBWEST`
Common user: `c##ggadmin` PDB user for sequences: `ggate`	PDB User: `ggadmin`

NORTH

SOUTH

PDB Name: DBEAST

PDB Name: DBWEST

Common user: c##ggadmin

PDB user for sequences: ggate

PDB User: ggadmin

sqlplus / as sysdba 
ALTER SESSION SESSION SET CONTAINER=CERTMISSN;
CREATE USER ggate IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS CONTAINER=CURRENT;

Run @sequence.sql

sqlplus / as sysdba
ALTER SESSION SET CONTAINER=DBEAST;
@sequence.sql

When prompted enter the following:

GGADMIN GLOBALS
GGSCHEMA GGADMIN

Run the FLUSH SEQUENCE command:

DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate
FLUSH SEQUENCE DBEAST.HR.*

Target Oracle GoldenGate Configuration:

sqlplus / as sysdba
ALTER SESSION SET CONTAINER =PDBWEST;
@sequence.sql

When prompted, enter the PDB user name ggadmin.

This also applies to the @sequence.sql script, which you must also run on each PDB from where you are going to capture.