How to create an Oracle GoldenGate EXTRACT in Multitenant

Create an EXTRACT process into container database has some specificity :

From the CDB$ROOT, create a common user and configure the database to be ready to extract data via GoldenGate:

SQL> create user c##gg_admin identified by "*****" default tablespace goldengate temporary tablespace temp;

User created.

SQL>

SQL> alter user c##gg_admin quota unlimited on goldengate;

User altered.

SQL>


SQL> grant create session, connect,resource,alter system, select any dictionary, flashback any table to c##gg_admin container=all;

Grant succeeded.

SQL>

SQL> exec dbms_goldengate_auth.grant_admin_privilege(grantee => 'c##gg_admin',container=>'all');

PL/SQL procedure successfully completed.

SQL> alter user c##gg_admin set container_data=all container=current;

User altered.

SQL>

SQL> grant alter any table to c##gg_admin container=ALL;

Grant succeeded.

SQL>

alter system set enable_goldengate_replication=true scope=both;


SQL> alter database force logging;


SQL> alter pluggable database add supplemental log data;

Pluggable database altered.

SQL>

Add the schematrandata for the schema concerned:

GGSCI (vmld-01726 as c##gg_admin@MYCDB) 3> add schematrandata schema_source

2022-04-13 18:06:55  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "schema_source".

2022-04-13 18:06:55  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "schema_source".

2022-04-13 18:06:55  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "schema_source".

2022-04-13 18:07:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table schema_source.ZZ_DUMMY *****
Oracle Goldengate support native capture on table schema_source.ZZ_DUMMY.
Oracle Goldengate marked following column as key columns on table schema_source.ZZ_DUMMY: SCN, D, COMMENT_TXT
No unique key is defined for table schema_source.ZZ_DUMMY.

2022-04-13 18:07:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table schema_source.ZZ_DUMMY2 *****
Oracle Goldengate support native capture on table schema_source.ZZ_DUMMY2.
Oracle Goldengate marked following column as key columns on table schema_source.ZZ_DUMMY2: SCN, D, COMMENT_TXT
No unique key is defined for table schema_source.ZZ_DUMMY2.

2022-04-13 18:07:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table schema_source.ZZ_SURVEILLANCE *****
Oracle Goldengate support native capture on table schema_source.ZZ_SURVEILLANCE.
Oracle Goldengate marked following column as key columns on table schema_source.ZZ_SURVEILLANCE: I.

2022-04-13 18:07:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table schema_source.ZZ_SURVEILLANCE_COPY *****
Oracle Goldengate support native capture on table schema_source.ZZ_SURVEILLANCE_COPY.
Oracle Goldengate marked following column as key columns on table schema_source.ZZ_SURVEILLANCE_COPY: I, SURV_DATE, ELLAPSED_1, ELLAPSED_2, CLIENT_HOST, CLIENT_TERMINAL, OS_USER, CLIENT_PROGRAM, INFO
No unique key is defined for table schema_source.ZZ_SURVEILLANCE_COPY.

GGSCI (vmld-01726 as c##gg_admin@MYCDB/CDB$ROOT) 3> dblogin userid c##gg_admin@MYPDB password xxxx
Successfully logged into database.

GGSCI (vmld-01726 as c##gg_admin@MYCDB) 4> info schematrandata schema_source

2022-04-13 18:32:43  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "schema_source".

2022-04-13 18:32:43  INFO    OGG-01980  Schema level supplemental logging is enabled on schema "schema_source" for all scheduling columns.

2022-04-13 18:32:43  INFO    OGG-10462  Schema "schema_source" have 4 prepared tables for instantiation.

GGSCI (vmld-01726 as c##gg_admin@MYCDB) 5>

Create a new alias connection to the container database and register the extract, the extract must be registered into the root container (CDB$ROOT) even the data to capture are from the PDB:

GGSCI (myserver) 10> alter credentialstore add user c##gg_admin@MYCDB_X1 alias ggadmin_exacc
Password:

Credential store altered.

GGSCI (myserver) 11> dblogin useridalias ggadmin
Successfully logged into database CDB$ROOT.

GGSCI (myserver as c##gg_admin@MYCDB/CDB$ROOT) 2>

GGSCI (myserver as c##gg_admin@MYCDB/CDB$ROOT) 2> register extract E3 database container (MYPDB)

2022-04-13 18:31:19  INFO    OGG-02003  Extract E3 successfully registered with database at SCN 3386436450080


GGSCI (myserver as c##gg_admin@MYCDB/CDB$ROOT) 3>

Save the SCN –> 3386436450080

Create the EXTRACT, connected on the CDB:

[oracle@myserver:/u01/app/oracle/product/19.1.0.0.4/gg_1]$ mkdir -p /u01/gs_x/ogg/

GGSCI (myserver) 7> add extract E3, integrated tranlog, begin now
EXTRACT (Integrated) added.


GGSCI (myserver) 8> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E3		    00:00:00      00:00:06


GGSCI (myserver) 9>


GGSCI (myserver) 9> add exttrail /u01/gs_x/ogg/gz, extract E3
EXTTRAIL added.


GGSCI (myserver) 2> edit param E3


GGSCI (myserver as c##gg_admin@MYCDB/CDB$ROOT) 13> edit param E3
Extract E3
useridalias ggadmin
Exttrail /u01/gs_x/ogg/gz
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL  &
INCLUDE MAPPED OBJNAME MYPDB.SCHEMA.*
Sequence MYPDB.SCHEMA.*;
Table MYPDB.SCHEMA.* ;

The parameter Table must be prefixed by the Pdb Name

Start the Extract always from the CDB$ROOT:

GGSCI (myserver as c##gg_admin@MY_CDB/CDB$ROOT) 12> START EXTRACT E3 atcsn 3386436450080

Sending START request to MANAGER ...
EXTRACT E3 starting


GGSCI (myserver as c##gg_admin@MYCDB/CDB$ROOT) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E3      00:00:05      00:00:03

Check the extract is running.
Now you are ready to create the Pump process, do the Initial Load and create the replicat process on the target.