Skip to content

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.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *