Oracle Goldengate 19c Step by Step Replication -3 Real Time Replication Example using Goldengate 19c

Mehmet Salih Deveci January 23, 2020 6 Comments

Hi,

I will explain Real Time Replication Example using Goldengate in this article.

 

Oracle Goldengate Replication

Read the following article before this, if you don’t know the basic of Goldengate replication.

Oracle Goldengate Step by Step Replication -2

 

 

 

Goldengate 19c Real Time Replication Example

 

I will replicate the following tables from Source to Target. Schema and table name will be changed on target as follows.

Source

MSD.TEST_TABLE1
MSD.TEST_TABLE2

 

Target

DEVECI.GG_TABLES1
DEVECI.GG_TABLES2

 

 

 

 

Real Time Replication Example using Goldengate 19c

Steps of Real Time Replication Example using Goldengate are as follows.

  1. Source Goldengate Extract Process creation
  2. Initial load with Datapump-Export Start
  3. Source Goldengate Pump Process creation
  4. Transfer the Export dumps to the Target
  5. Initial load with Datapump-Import Start
  6. Target Goldengate Replicat Process creation
  7. Start Replication and monitoring

 

Step -1: Source Goldengate Extract Process creation

Firstly Add extract process to the Goldengate, then create the parameter file of extract process.

 

GGSCI (SOURCE as [email protected]) 224> REGISTER EXTRACT E01MSD01 DATABASE

2020-01-23 10:36:15  WARNING OGG-02064  Oracle compatibility version 11.2.0 has limited datatype support for integrated capture. Version 11.2.0.3 required for full support.

2020-01-23 10:37:08  INFO    OGG-02003  Extract E01MSD01 successfully registered with database at SCN 16470915871019.

GGSCI (SOURCE as [email protected]) 225> ADD EXTRACT E01MSD01,INTEGRATED TRANLOG, BEGIN 2020-01-23:09:00:00
EXTRACT (Integrated) added.


GGSCI (SOURCE as [email protected]) 226> ADD EXTTRAIL ./dirdat/prm/e1 EXTRACT E01MSD01, MEGABYTES 600
EXTTRAIL added.



-- Create the parameter file of Extract process as follows.


GGSCI (SOURCE as [email protected]) 6> edit params E01MSD01

EXTRACT E01MSD01

--REGISTER EXTRACT E01MSD01 DATABASE
--ADD EXTRACT E01MSD01,INTEGRATED TRANLOG, BEGIN 2020-01-23:09:00:00
--ADD EXTTRAIL ./dirdat/prm/e1 EXTRACT E01MSD01, MEGABYTES 600

CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp

--Database login info
--SETENV (ORACLE_SID='IMSD1')
--SETENV (NLS_LANG = 'AMERICAN_AMERICA.WE8ISO8859P9')
SETENV (ORACLE_HOME='/u01/app/oracle/product/11.2.0/dbhome_1')


TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 400, parallelism 1)

userid [email protected]_TNS, password AACAAAAAAAAAAAIAXETGCCSCYFRAAAOG, encryptkey default

--ABORTONFATALERROR
-- due to existence of unused columns


TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 4)

include ./dirprm/reporting.inc

-- Long Running Trx Infos

WARNLONGTRANS 4H, CHECKINTERVAL 30M
DBOPTIONS ALLOWUNUSEDCOLUMN

-- Bounded Recovery
--BR BROFF

--DDL REPLICATION

--- This parameter reports DDL being replicated.
--- This is very useful for troubleshooting.
--DDLOPTIONS ADDTRANDATA, REPORT
--- Capture DDL changes so that the target replicat can update metadata --DDLOPTIONS GETREPLICATES


GETTRUNCATES
NOCOMPRESSDELETES
NOCOMPRESSUPDATES

-- Just in case we can't process a record we'll dump info here
DISCARDFILE ./dirrpt/E01MSD01.dsc, APPEND, MEGABYTES 100
-- This is the Trail to where we output
EXTTRAIL ./dirdat/prm/e1, FORMAT RELEASE 12.1


-- This is the Heartbeat table
include ./dirprm/HB_Extract.inc


TABLE MSD.TEST_TABLE1;
TABLE MSD.TEST_TABLE2;



save this parameter file and start the process as follows.



GGSCI (SOURCE as [email protected]) 227> start E01MSD01

Sending START request to MANAGER ...
EXTRACT E01MSD01 starting


GGSCI (SOURCE as [email protected]) 228> info E01MSD01

EXTRACT    E01MSD01   Initialized   2020-01-23 10:37   Status STARTING
Checkpoint Lag       01:37:31 (updated 00:00:14 ago)
Process ID           47196
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2020-01-23 09:00:00
                     SCN 0.0 (0)


GGSCI (SOURCE as [email protected]) 244> !
info E01MSD01

EXTRACT    E01MSD01   Last Started 2020-01-23 10:39   Status RUNNING
Checkpoint Lag       00:00:02 (updated 00:00:04 ago)
Process ID           47196
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2020-01-23 10:40:38
                     SCN 3834.4014531880 (16470919144744)


GGSCI (SOURCE as [email protected]) 245> !
info E01MSD01

EXTRACT    E01MSD01   Last Started 2020-01-23 10:39   Status RUNNING
Checkpoint Lag       00:00:02 (updated 00:00:00 ago)
Process ID           47196
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2020-01-23 10:41:10
                     SCN 3834.4015117393 (16470919730257)


GGSCI (SOURCE as [email protected]) 246> !
info E01MSD01

EXTRACT    E01MSD01   Last Started 2020-01-23 10:39   Status RUNNING
Checkpoint Lag       00:00:03 (updated 00:00:03 ago)
Process ID           47196
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2020-01-23 10:42:12
                     SCN 3834.4015971746 (16470920584610)

 

Extract process and extraction have been started.

 

 

Step -2: Initial load with Datapump-Export Start

 

You can perform initial load with both Datapump and goldengate. I will use Datapump.

If you don’t know what is the Datapump, you can read the following articles.

https://ittutorial.org/oracle-data-pump-export-import-4/

 

Firstly, Find the current SCN of Source database as follows.

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
16470829540670

 

Start the Export of related tables using Flashback_scn parameter as follows. Our tables are very big, so I have used paralelism.

expdp "'/ as sysdba'" directory=DATA_PUMP_DIR tables=MSD.TEST_TABLE1,MSD.TEST_TABLE2 dumpfile=MSD_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=MSD_22012020_TABLES.log compression=all flashback_scn=16470829540670

 

Export process will take a long time, so you can start the pump process creation.

 

Step -3: Source Goldengate Pump Process creation

Firstly Add pump process to the Goldengate, then create the parameter file of pump process.

 

GGSCI (SOURCE as [email protected]) 247> ADD EXTRACT P01MSD01 exttrailsource ./dirdat/prm/e1 
EXTRACT added.


GGSCI (SOURCE as [email protected]) 248> ADD RMTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1 EXTRACT P01MSD01, MEGABYTES 1024
RMTTRAIL added.





GGSCI (SOURCE as [email protected]) 6> edit params P01MSD01


 EXTRACT P01MSD01

--ADD EXTRACT P01MSD01 exttrailsource ./dirdat/prm/e1 
--ADD RMTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/MSD/c1 EXTRACT P01MSD01, MEGABYTES 1024

-- Stats and Report information 

include ./dirprm/reporting.inc

CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp

-- Discard File information
DISCARDFILE ./dirrpt/P01MSD01.dsc, APPEND, MEGABYTES 100


userid [email protected]_TNS, password AACAAAAAAAAAAAIAXETGCCSCYFRAAAOG, encryptkey default

-- This is the Trail to where we output
RMTHOST 10.86.17.147, MGRPORT 7811, TCPBUFSIZE 10000000, TCPFLUSHBYTES 10000000, COMPRESS
--Format Release option added to align with Bigdata Goldengate version

RMTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/MSD/c1, FORMAT RELEASE 12.1

include ./dirprm/HB_pmp.inc

TABLE MSD.TEST_TABLE1;
TABLE MSD.TEST_TABLE2;
save this parameter file and start the process as follows.


GGSCI (SOURCE as [email protected]) 249> start P01MSD01

Sending START request to MANAGER ...
EXTRACT P01MSD01 starting


GGSCI (SOURCE as [email protected]) 250> info P01MSD01

EXTRACT P01MSD01 Last Started 2020-01-23 10:42 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:15 ago)
Process ID 56904
Log Read Checkpoint File ./dirdat/prm/e1000000000
First Record RBA 0

GGSCI (SOURCE as [email protected]) 257> !
info P01MSD01

EXTRACT P01MSD01 Last Started 2020-01-23 10:42 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 56904
Log Read Checkpoint File ./dirdat/prm/e1000000000
2020-01-23 10:44:07.000000 RBA 561436

GGSCI (SOURCE as [email protected]) 261> !
info P01MSD01

EXTRACT P01MSD01 Last Started 2020-01-23 10:42 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 56904
Log Read Checkpoint File ./dirdat/prm/e1000000000
2020-01-23 14:06:17.000000 RBA 18470944

 

 

Step – 4  Transfer the Export dumps to the Target

 

If export process are successfully completed, then you can transfer the dump files to the Target database.

Job "SYS"."SYS_EXPORT_TABLE_58" successfully completed at Thu Jan 23 09:15:08 2020 elapsed 0 00:07:47

 

scp MSD_22012020_TABLES*dmp   [email protected]_IP:/goldengate/export

 

Step – 5  Initial load with Datapump-Import Start

 

If Export dumps transfer are completed, you can start the import process as follows.

 

impdp "'/ as sysdba'" directory=MSD tables=MSD.TEST_TABLE1,MSD.TEST_TABLE2 dumpfile=MSD_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=MSD_22012020_TABLES.log remap_schema=MSD:DEVECI

 

Step – 6  Target Goldengate Replicat Process creation

Firstly Add Replicat process to the Goldengate, then create the parameter file of Replicate process.

 

[[email protected] 19.1.0.0.4]$ 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 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (TARGET as [email protected]) 6> dblogin userid [email protected] password "Goldengate.12345!"
Successfully logged into database.

GGSCI (TARGET as [email protected]) 6> ADD REPLICAT RMSD01 INTEGRATED EXTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/MSD/c1 CHECKPOINTTABLE GGS_ADMIN.GGSCHKPT

GGSCI (TARGET as [email protected]) 6> view params RMSD01

REPLICAT RMSD01

--ADD REPLICAT RMSD01 INTEGRATED EXTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/MSD/c1 CHECKPOINTTABLE GGS_ADMIN.GGSCHKPT
-- Database login info, source and target db's have different nls_lang params
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid [email protected]_TNS password "Goldengate.12345!"
--DBOPTIONS INTEGRATEDPARAMS(parallelism 12)

CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp
--BATCHSQL BATCHESPERQUEUE 400,BYTESPERQUEUE 40000000, OPSPERBATCH 2000, OPSPERQUEUE 15000

-- reset report statistcs for every send report command
STATOPTIONS, RESETREPORTSTATS

-- Record count every X minutes
REPORTCOUNT EVERY 10 MINUTES, rate
REPORT AT 00:00
REPORTROLLOVER ON SUNDAY

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

--DBOPTIONS DEFERREFCONST

-- Just in case we can't process a record we'll dump row data here
DiscardFile ./dirrpt/RMSD01.dsc, append, MEGABYTES 1000

AssumeTargetDefs

--CHARMAP ./dirprm/AL32UTF8_to_P9.map
--SOURCECHARSET AL32UTF8
--TARGETCHARSET WE8ISO8859P9
--\xC3A2\xE282AC\xE2809C \x2D
--CHARMAP \xC3A2\xE282AC\xE2809C \x2D

--dynamicresolution
--wildcardresolve dynamic
gettruncates
--eofdelay 2

APPLYNOOPUPDATES

-- REPERROR 1, DISCARD
-- REPERROR 1403, DISCARD

--grouptransops 10000
--maxtransops 10000
--batchsql BATCHTRANSOPS 5000 BYTESPERQUEUE 400000000
--HANDLECOLLISIONS
BATCHSQL BATCHESPERQUEUE 400,BYTESPERQUEUE 40000000, OPSPERBATCH 10000, OPSPERQUEUE 5000


-- DDL Operations
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP

--IGNOREUPDATEBEFORES
--GETUPDATEAFTERS
--NOCOMPRESSUPDATES

--insertmissingupdates
-- reperror (1403, discard)


-- Hearthbeat table
--include ./dirprm/HB_REP.inc

SQLEXEC "ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE";
--SQLEXEC "ALTER SESSION SET COMMIT_WRITE='NOWAIT'";
SQLEXEC "ALTER SESSION ENABLE PARALLEL DML";


MAP MSD.TEST_TABLE1, TARGET DEVECI.GG_TABLES1, colmap( usedefaults, GG_TRANS_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));
MAP MSD.TEST_TABLE2, TARGET DEVECI.GG_TABLES2, colmap( usedefaults, GG_TRANS_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));

 

 

Step – 7 Start Replication and monitoring

Replication process is added to the goldengate and created parameter file, now we can start goldengate replication and then monitor it.

 

GGSCI (TARGET as [email protected]) 6> start RMSD01

Sending START request to MANAGER ...
REPLICAT RMSD01 starting

GGSCI (TARGET as [email protected]) 6> info RMSD01

REPLICAT RMSD01 Last Started 2020-01-23 14:01 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:22 ago)
Process ID 101183
Log Read Checkpoint File /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1000000000
First Record RBA 0


GGSCI (TARGET as [email protected]) 8> !
info RMSD01

REPLICAT RMSD01 Last Started 2020-01-23 14:01 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 101183
Log Read Checkpoint File /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1000000000
First Record RBA 1449


GGSCI (TARGET as [email protected]) 9> !
info RMSD01

REPLICAT RMSD01 Last Started 2020-01-23 14:01 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 101183
Log Read Checkpoint File /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1000000000
First Record RBA 1449


GGSCI (TARGET as [email protected]) 10> !
info RMSD01

REPLICAT RMSD01 Last Started 2020-01-23 14:01 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 101183
Log Read Checkpoint File /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1000000000
First Record RBA 1449

GGSCI (TARGET as [email protected]) 104> !
info RMSD01

REPLICAT RMSD01 Last Started 2020-01-23 15:08 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:10 (updated 00:00:06 ago)
Process ID 141927
Log Read Checkpoint File /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1000000000
2020-01-23 15:53:03.051970 RBA 29335586

 

 

Replication is done, you can monitor Goldengate using the following article.

 

Goldengate Lag Monitor and Replication Lag Scripts

 

 

 

Do you want to learn Oracle Goldengate, then read the following articles.

Oracle Goldengate Tutorials for Beginners

 3,395 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

6 comments

  1. Malesh
    April 22, 2021 at 11:28 am

    At step 5 do you renamed the tables ? To make these mapping work

    MAP MSD.TEST_TABLE1, TARGET DEVECI.GG_TABLES1

    MAP MSD.TEST_TABLE2, TARGET DEVECI.GG_TABLES2

    Reply
  2. OGG New User
    June 20, 2021 at 4:24 pm

    can extract process will be at same with replicate at targets

    Reply
  3. Meka sakthi
    August 26, 2022 at 4:18 am

    Hi

    Thanks for the detailed document. the last line mapping with committmestamp. please explain in detail.

    AP MSD.TEST_TABLE1, TARGET DEVECI.GG_TABLES1, colmap( usedefaults, GG_TRANS_DATE = @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’));
    MAP MSD.TEST_TABLE2, TARGET DEVECI.GG_TABLES2, colmap( usedefaults, GG_TRANS_DATE = @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’));

    Thanks
    Meka

    Reply
    • Mehmet Salih Deveci
      August 26, 2022 at 7:41 am

      GG_TRANS_DATE means Goldengate Transaction date, and Some of BI or ETL Teams need this column and they may request this Column. Then you can generate this column using @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’)) user Tokens of Goldengate.

      Reply

Leave a Reply

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

Powered by IT Tutorial | Designed by SysDBASoft IT
IT Tutorial © Copyright 2023, All Rights Reserved