DML replication from source GG to Target GG on pluggable database 19c
Container source database : sourcegg1
source pdb name : sgg1
Container Target database : targetgg1
target pdb name : tgg1
Golden gate version : 19c linux on both source and target
oracle database version : 19c on both source and target
source container schema name : c##ggsource
target container schema name : c##ggtarget
Source ggci scapture process exint and datapump process is dpint
Target ggsci replicate process repint
Source hostname: srv6.example.com
Target hostname : srv7.example.com
- Enable replication parameter on source database sourcegg1
SQL> alter system set enable_goldengate_replication=TRUE;
- Create common user on source contianer database
System altered.
SQL> create user c##ggsource identified by ggsource default tablespace users;
User created.
- Add supplemental log data columns to capture information from redo log
SQL> alter database add supplemental log data (all) columns;
- Grant dba privilege to all pdb or we can provide to specific pdb as well
Database altered.
SQL> grant dba to c##ggsource container=all;
Grant succeeded.
- Enable archive log on both source and target database in our case it is enabled
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
- Connect PDB database
SQL> alter session set container=SGG1;
Session altered.
SQL> sho pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SGG1 READ WRITE NO
- Create table supplier for dml replication as an example and insert value later we will do further insert that will reflect on target database
SQL> CREATE TABLE hr.supplier(supplier_id numeric(10) not null,supplier_name varchar2(50) not null,contact_name varchar2(50),CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));
SQL> INSERT INTO hr.supplier values (1,'ABCD1','ABCD1');
1 row created.
SQL> INSERT INTO hr.supplier values (2,'ABCD2','ABCD2');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.supplier;
SUPPLIER_ID SUPPLIER_NAME
----------- --------------------------------------------------
CONTACT_NAME
--------------------------------------------------
1 ABCD1
ABCD1
2 ABCD2
ABCD2
- Target container database which is targetgg1 and pdb is TGG1
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TGG1 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Pluggable database altered.
- Enable golden gate replication parameter true on container database
SQL> alter system set enable_goldengate_replication=TRUE;
System altered.
- Create common user and grant dba privilege to all pdb or we can give privilege to specific pdb as well
SQL> grant dba to c##ggtarget container=all;
Grant succeeded.
- Connect to source GG and create extract and datapump values for replication
[oracle@srv6 ~]$ cd $GG_HOME
[oracle@srv6 sourcegg1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (srv6.example.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
- Start manager process
GGSCI (srv6.example.com) 2> start mgr
Manager started.
- Connect pdb with common user
GGSCI (srv6.example.com) 1> dblogin userid c##ggsource@sgg1,password ggsource
Successfully logged into database SGG1.
- Add trandata for table replication or mention schema * in terms of al table
GGSCI (srv6.example.com as c##ggsource@sourcegg1/SGG1) 3> add trandata sgg1.hr.supplier
2021-11-19 22:29:01 INFO OGG-15132 Logging of supplemental redo data enabled for table SGG1.HR.SUPPLIER.
2021-11-19 22:29:01 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SGG1.HR.SUPPLIER.
2021-11-19 22:29:01 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SGG1.HR.SUPPLIER.
2021-11-19 22:29:02 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.SUPPLIER *****
Oracle Goldengate support native capture on table HR.SUPPLIER.
Oracle Goldengate marked following column as key columns on table HR.SUPPLIER: SUPPLIER_ID.
- Login with container db common user
GGSCI (srv6.example.com as c##ggsource@sourcegg1/SGG1) 4> dblogin userid c##ggsource,password ggsource
Successfully logged into database CDB$ROOT.
- edit manager with port values 7810 information
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 5> edit param mgr
- Add extract details for capturing commit dml details
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 6> ADD EXTRACT extint, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
- Register capture extract with pdb ssg1
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 7> REGISTER EXTRACT EXTINT DATABASE CONTAINER (sgg1);
2021-11-19 22:33:35 INFO OGG-02003 Extract EXTINT successfully registered with database at SCN 2313961.
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTINT 00:00:00 00:03:23
- Connect contianer db add trail location connecting with capture extint process
[oracle@srv6 sourcegg1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (srv6.example.com) 1> dblogin userid c##ggsource,password ggsource
Successfully logged into database CDB$ROOT.
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 2> ADD EXTTRAIL /u01/app/oracle/product/sourcegg1/dirdat/lt, EXTRACT extint
EXTTRAIL added.
- Add datapump process to transfer trail file to remote trail file location
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 3> ADD EXTRACT dpint, EXTTRAILSOURCE /u01/app/oracle/product/sourcegg1/dirdat/lt
EXTRACT added.
- Add and view extint parameter process
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 4> EDIT PARAMS extint
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 9> view param extint
EXTRACT extint
USERID c##ggsource, PASSWORD ggsource --connection to CDB$ROOT database
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 100)
EXTTRAIL /u01/app/oracle/product/sourcegg1/dirdat/lt
TABLE sgg1.hr.supplier;
- Add and view dpint parameter process
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 6> EDIT PARAMS dpint
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 8> view param dpint
EXTRACT dpint
USERID c##ggsource, PASSWORD ggsource
RMTHOST 192.168.1.130, MGRPORT 7810
RMTTRAIL /u01/app/oracle/product/targetgg1/dirdat/rt
PASSTHRU
TABLE sgg1.hr.supplier;
- Add remote trail location using container db login
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 5> ADD RMTTRAIL /u01/app/oracle/product/targetgg1/dirdat/rt, EXTRACT dpint
RMTTRAIL added.
- Start capture and datapump process extint and dpint
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 11> start EXTINT
Sending START request to MANAGER ...
EXTRACT EXTINT starting
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 12> start DPINT
Sending START request to MANAGER ...
EXTRACT DPINT starting
- View status of process in ggsci console
GGSCI (srv6.example.com as c##ggsource@sourcegg1/CDB$ROOT) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPINT 00:00:00 00:12:27
EXTRACT RUNNING EXTINT 00:00:00 00:23:07
- Connect to target ggsci console
[oracle@srv7 targetgg1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (srv7.example.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (srv7.example.com) 2> start mgr
Manager started.
GGSCI (srv7.example.com) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
- Add port on manager process
GGSCI (srv7.example.com) 24> view param mgr
PORT 7810
GGSCI (srv7.example.com) 25>
- Add replication process on target ggsci console
GGSCI (srv7.example.com) 4> ADD REPLICAT repint,integrated EXTTRAIL /u01/app/oracle/product/targetgg1/dirdat/rt
REPLICAT (Integrated) added.
- Edit and view replicate parameters
GGSCI (srv7.example.com) 5> EDIT PARAMS repint
GGSCI (srv7.example.com) 8> view param repint
REPLICAT repint
USERID c##ggtarget@TGG1, PASSWORD ggtarget –Connection to pluggable
DBOPTIONS INTEGRATEDPARAMS(parallelism 4)
ASSUMETARGETDEFS
MAP sgg1.hr.supplier, TARGET tgg1.hr.supplier;
- Connect to source pluggable database SGG1 and insert the values , It should reflect on target database TGG1
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SGG1 READ WRITE NO
SQL>
SQL> INSERT INTO hr.supplier values (5,'ABCD5','ABCD5');
1 row created.
SQL> commit;
Commit complete.
ggserr.log
2021-11-20T12:32:32.763+0530 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2021-11-20T13:50:24.420+0530 INFO OGG-06507 Oracle GoldenGate Capture for Oracle, extint.prm: MAP (TABLE) resolved (entry sgg1.hr.supplier): TABLE "SGG1"."HR"."SUPPLIER".
2021-11-20T13:50:24.574+0530 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, extint.prm: Using the following key columns for source table SGG1.HR.SUPPLIER: SUPPLIER_ID.
2021-11-20T13:50:24.708+0530 INFO OGG-06507 Oracle GoldenGate Capture for Oracle, extint.prm: MAP (TABLE) resolved (entry sgg1.hr.supplier): TABLE "SGG1"."HR"."SUPPLIER".
2021-11-20T13:50:24.708+0530 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, extint.prm: Using the following key columns for source table SGG1.HR.SUPPLIER: SUPPLIER_ID.
2021-11-20T13:50:26.217+0530 INFO OGG-02263 Oracle GoldenGate Capture for Oracle, dpint.prm: Passthru MAP (TABLE) resolved (entry sgg1.hr.supplier): TABLE "SGG1"."HR"."SUPPLIER".
- After inserting table values reflected on target pluggable database TGG1
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TGG1 READ WRITE NO
SQL> select * from hr.supplier order by SUPPLIER_ID asc;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- ---------------------------------------- --------------------------------------------------
1 ABCD1 ABCD1
2 ABCD2 ABCD2
3 ABCD3 ABCD3
4 ABCD4 ABCD4
5 ABCD5 ABCD5
ggserr.log
2021-11-20T12:32:46.523+0530 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2021-11-20T13:50:29.073+0530 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, repint.prm: MAP resolved (entry sgg1.hr.supplier): MAP "SGG1"."HR"."SUPPLIER", TARGET tgg1.hr.supplier.
2021-11-20T13:50:35.176+0530 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, repint.prm: The definition for table SGG1.HR.SUPPLIER is obtained from the trail file.
2021-11-20T13:50:35.176+0530 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, repint.prm: Using following columns in default map by name: SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME.
2021-11-20T13:50:35.177+0530 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, repint.prm: Using the following key columns for target table TGG1.HR.SUPPLIER: SUPPLIER_ID.
