IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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.
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.
- Source Goldengate Extract Process creation
- Initial load with Datapump-Export Start
- Source Goldengate Pump Process creation
- Transfer the Export dumps to the Target
- Initial load with Datapump-Import Start
- Target Goldengate Replicat Process creation
- 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 GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 225> ADD EXTRACT E01MSD01,INTEGRATED TRANLOG, BEGIN 2020-01-23:09:00:00
EXTRACT (Integrated) added.
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 226> ADD EXTTRAIL ./dirdat/prm/e1 EXTRACT E01MSD01, MEGABYTES 600
EXTTRAIL added.
-- Create the parameter file of Extract process as follows.
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCE_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 GGS_ADMIN@SOURCEDB) 227> start E01MSD01
Sending START request to MANAGER ...
EXTRACT E01MSD01 starting
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 247> ADD EXTRACT P01MSD01 exttrailsource ./dirdat/prm/e1 EXTRACT added. GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 248> ADD RMTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/ccb/c1 EXTRACT P01MSD01, MEGABYTES 1024 RMTTRAIL added.
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCE_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 GGS_ADMIN@SOURCEDB) 249> start P01MSD01 Sending START request to MANAGER ... EXTRACT P01MSD01 starting GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 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 GGS_ADMIN@SOURCEDB) 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 oracle@TARGET_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.
[goldengate@TARGET 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 GGS_ADMIN@TARGETDB) 6> dblogin userid GGS_ADMIN@TARGETDB password "Goldengate.12345!"
Successfully logged into database.
GGSCI (TARGET as GGS_ADMIN@TARGETDB) 6> ADD REPLICAT RMSD01 INTEGRATED EXTTRAIL /ggateb01/goldengate/product/19.1.0.0.4/dirdat/MSD/c1 CHECKPOINTTABLE GGS_ADMIN.GGSCHKPT
GGSCI (TARGET as GGS_ADMIN@TARGETDB) 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 GGS_ADMIN@TARGET_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 GGS_ADMIN@TARGETDB) 6> start RMSD01 Sending START request to MANAGER ... REPLICAT RMSD01 starting GGSCI (TARGET as GGS_ADMIN@TARGETDB) 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 GGS_ADMIN@TARGETDB) 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 GGS_ADMIN@TARGETDB) 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 GGS_ADMIN@TARGETDB) 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 GGS_ADMIN@TARGETDB) 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.
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
not rename table, I did remap_schema to change schema.
can extract process will be at same with replicate at targets
Could you give me some details about your question ? ( same with replicate at targets ) ?
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
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.