Installing Support for Oracle Sequences

To support Oracle sequences, you must install some database procedures.

To Install Oracle Sequence Objects

  1. In SQL*Plus, connect to the source and target Oracle systems as SYSDBA.

  2. If you already assigned a database user to support the Oracle GoldenGate DDL replication feature, you can skip this step. Otherwise, in SQL*Plus on both systems create a database user that can also be the DDL user.
    CREATE USER DDLuser IDENTIFIED BY password; 
GRANT CONNECT, RESOURCE, DBA TO DDLuser;

  3. From the Oracle GoldenGate installation directory on each system, run GGSCI.

  4. In GGSCI, issue the following command on each system.

    EDIT PARAMS ./GLOBALS

  5. In each GLOBALS file, enter the GGSCHEMA parameter and specify the schema of the DDL user that you created earlier in this procedure. 

    GGSCHEMA schema

  6. Save and close the files.

  7. In SQL*Plus on the source system, issue the following statement in SQL*Plus.

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

To capture the sequence from a multitenant database

  1. Create an Oracle GoldenGate user in each PDB that you need to capture sequences from.

  2. Add the user to the GLOBALS parameter file. It is easier if you use the same user for each PDB, if you don't then you need to change the GLOBALS file each time you do step 3.

  3. Log into Admin Client or GGSCI.

  4. Connect to the root container on the source using DBLOGIN.

  5. Issue the FLUSH SEQUENCE command for each PDB.

If you don't want to keep these database accounts, you can drop the user or deactivate the account.

Here is an example of the entire process:

Environment information
            OGG 19.1 Oracle 12c to Oracle 12c Replication, Integrated 
            Extract, Parallel Replicat
            Source: CDB GOLD, PDB CERTMISSN 
            Target: CDB PLAT, PDB CERTDSQ 
            Source Oracle GoldenGate Configuration
            Container User: C##GGADMIN 
            PDB User for Sequences: GGATE

When prompted, enter GGATE 

GLOBALS 
            GGSCHEMA GGATE 
         Flush Sequence 
            GGSCI> DBLOGIN USERIDALIAS GGADMIN DOMAIN GOLD_QC_CDB$ROOT 
            GGSCI> FLUSH SEQUENCE CERTMISSN.SRCSCHEMA1.
Target OGG Configuration 
         PDB User: GGATE 
         Run @sequence 
                sqlplus / as sysdba 
                SQL> alter session set container=CERTDSQ;
                SQL> @sequence

When prompted enter GGATE.

Replicating sequences is required for High Availability (HA) and DR scenarios:

  • For migrations, you need rebuild the sequences on the target during the switchover, or increase them to a higher value just prior to the switchover.

  • Make sure you place the sequences into their own Replicat.