Oracle Goldengate 19c Step by Step Replication -3 Real Time Replication Example using Golden Gate 19c
Replicate the following tables from Source to Target. Schema and table name will be changed on target as follows.
Source:
RAMESH.INFO_TAB1
RAMESH.INFO_TAB2
Target:
KUMAR.GG_TAB1
KUMAR.GG_TAB2
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-26 11:00: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-26 11:10:25 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-26: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-26:09:00:00
--ADD EXTTRAIL ./dirdat/prm/e1 EXTRACT E01MSD01, MEGABYTES 600
CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp
--Database login info
--SETENV (ORACLE_SID='PRIMEDB')
--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
RAMESH.INFO_TAB1;
RAMESH.INFO_TAB2;
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-26 11: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-26 09:00:00
SCN 0.0 (0)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 244> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11:40:38
SCN 3834.4014531880 (16470919144744)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 245> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11:41:10
SCN 3834.4015117393 (16470919730257)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 246> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11: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.
Firstly, Find the current SCN of Source database as follows.
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
16470829540670
RAMESH.INFO_TAB1,RAMESH.INFO_TAB2
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=RAMESH.INFO_TAB1,RAMESH.INFO_TAB2 dumpfile=RAM_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=RAM_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
RAMESH.INFO_TAB1;
RAMESH.INFO_TAB2;
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-26 11: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-26 11: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-26 11:44:07.000000 RBA 561436
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 261> !
info P01MSD01
EXTRACT P01MSD01 Last Started 2020-01-26 11: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-26 15: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_42" successfully completed at Thu Jan 26 10:45:10 2020 elapsed 0 00:07:47
scp RAM_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=RAM tables=RAMESH.INFO_TAB1,RAMESH.INFO_TAB2 dumpfile=RAM_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=RAM_22012020_TABLES.log remap_schema=RAMESH:KUMAR
Step – 6 Target Goldengate Replicat Process creation:
First we need to 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 RAMESH.IMFO_TAB1, TARGET KUMAR.GG_TAB1, colmap( usedefaults, GG_TRANS_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));
MAP RAMESH.INFO_TAB2, TARGET KUMAR.GG_TAB2, 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-26 15: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-26 15: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-26 15: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-26 15: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-26 16: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-26 16:45:03.051970 RBA 29335586
Replication is done, you can monitor Goldengate using the following scripts.
Displays the status of all Goldengate processes like following query
GGSCI (deveci01) 21> info all
You can monitor extract , pumper and replicat processes like following.
GGSCI (deveci01) 21> info extract EXDEV01
GGSCI (deveci01) 19> info extract EXDEV01
GGSCI (deveci01) 20> info replicat RXDEV01
GGSCI (deveci01) 20> info PROCESS_NAME
Displays the status of the Manager Process like following query
GGSCI (deveci01) 23> status manager
Manager is running (IP port deveci01.7809).
Show Extraction status
GGSCI (deveci01) 24> status extract EXDEV01
EXTRACT EXDEV01: RUNNING
Shows details of a specific extract process.
GGSCI (deveci01) 6> info extract EXDEV01, detail
EXTRACT EXDEV01 Last Started 2019-02-19 11:19 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2019-02-26 10:45:18 Seqno 786, RBA 44710400
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/oracle/software/goldengate/dirdat/lt 2 55644 10
Extract Source Begin End
/u02/oradata/apex/redo03.log 2019-02-19 11:13 2019-02-26 10:45
Current directory /u01/oracle/software/goldengate
Report file /u01/oracle/software/goldengate/dirrpt/EXDEV01.rpt
Parameter file /u01/oracle/software/goldengate/dirprm/EXDEV01.prm
Checkpoint file /u01/oracle/software/goldengate/dirchk/EXDEV01.cpe
Process file /u01/oracle/software/goldengate/dirpcs/EXDEV01.pce
Stdout file /u01/oracle/software/goldengate/dirout/EXDEV01.out
Error log /u01/oracle/software/goldengate/ggserr.log
Displays Extract status
GGSCI (deveci01) 35> send extract EXDEV01 status
Sending STATUS request to EXTRACT EXDEV01 ...
displays Extract Statistics
GGSCI (deveci01) 33> stats extract EXDEV01
Sending STATS request to EXTRACT EXDEV01 ...
View process rate ( ‘hr’,’min’ or ‘sec’ as a parameter for time )
Display the processing rates in hours or seconds of Goldengate processes
GGSCI (deveci01) 37> stats extract EXDEV02 reportrate hr
Sending STATS request to EXTRACT EXDEV02 ...
Display of latency between replicat and source
GGSCI (deveci01) 13> send extract EXDEV02, getlag
Sending GETLAG request to EXTRACT EXDEV02 ...
Last record lag: 3 seconds.
At EOF, no more records to process.
Detailed report of processes can be displayed like following.
GGSCI (deveci01) 2> view report EXDEV01
GGSCI (deveci01) 2> view report rep1
GGSCI (deveci01) 2> view report mgr
Child processes status like following.
GGSCI (deveci01) 8> send manager childstatus
Sending CHILDSTATUS request to MANAGER ...
Child Process Status - 6 Entries
ID Group Process Retry Retry Time Start Time
---- -------- ---------- ----- ------------------ -----------
0 EXDEV01 1925238 0 None 2019/02/19 11:07:54
1 DPUMP 2195496 0 None 2019/02/19 11:08:02
2 MSSQL1 422034 0 None 2019/02/22 13:54:59
4 MYREP 1302702 0 None 2019/02/23 09:08:34
6 DEVLOAD 1200242 0 None 2019/02/23 11:05:01
7 EXDEV02 2076844 0 None 2019/02/26 08:29:22
Reference: Oracle Internals
Source:
RAMESH.INFO_TAB1
RAMESH.INFO_TAB2
Target:
KUMAR.GG_TAB1
KUMAR.GG_TAB2
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-26 11:00: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-26 11:10:25 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-26: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-26:09:00:00
--ADD EXTTRAIL ./dirdat/prm/e1 EXTRACT E01MSD01, MEGABYTES 600
CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY ./dirtmp
--Database login info
--SETENV (ORACLE_SID='PRIMEDB')
--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
RAMESH.INFO_TAB1;
RAMESH.INFO_TAB2;
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-26 11: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-26 09:00:00
SCN 0.0 (0)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 244> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11:40:38
SCN 3834.4014531880 (16470919144744)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 245> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11:41:10
SCN 3834.4015117393 (16470919730257)
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 246> !
info E01MSD01
EXTRACT E01MSD01 Last Started 2020-01-26 11: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-26 11: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.
Firstly, Find the current SCN of Source database as follows.
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
16470829540670
RAMESH.INFO_TAB1,RAMESH.INFO_TAB2
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=RAMESH.INFO_TAB1,RAMESH.INFO_TAB2 dumpfile=RAM_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=RAM_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
RAMESH.INFO_TAB1;
RAMESH.INFO_TAB2;
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-26 11: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-26 11: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-26 11:44:07.000000 RBA 561436
GGSCI (SOURCE as GGS_ADMIN@SOURCEDB) 261> !
info P01MSD01
EXTRACT P01MSD01 Last Started 2020-01-26 11: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-26 15: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_42" successfully completed at Thu Jan 26 10:45:10 2020 elapsed 0 00:07:47
scp RAM_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=RAM tables=RAMESH.INFO_TAB1,RAMESH.INFO_TAB2 dumpfile=RAM_22012020_TABLES%U.dmp parallel=24 cluster=n logfile=RAM_22012020_TABLES.log remap_schema=RAMESH:KUMAR
Step – 6 Target Goldengate Replicat Process creation:
First we need to 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 RAMESH.IMFO_TAB1, TARGET KUMAR.GG_TAB1, colmap( usedefaults, GG_TRANS_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));
MAP RAMESH.INFO_TAB2, TARGET KUMAR.GG_TAB2, 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-26 15: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-26 15: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-26 15: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-26 15: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-26 16: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-26 16:45:03.051970 RBA 29335586
Replication is done, you can monitor Goldengate using the following scripts.
Displays the status of all Goldengate processes like following query
GGSCI (deveci01) 21> info all
You can monitor extract , pumper and replicat processes like following.
GGSCI (deveci01) 21> info extract EXDEV01
GGSCI (deveci01) 19> info extract EXDEV01
GGSCI (deveci01) 20> info replicat RXDEV01
GGSCI (deveci01) 20> info PROCESS_NAME
Displays the status of the Manager Process like following query
GGSCI (deveci01) 23> status manager
Manager is running (IP port deveci01.7809).
Show Extraction status
GGSCI (deveci01) 24> status extract EXDEV01
EXTRACT EXDEV01: RUNNING
Shows details of a specific extract process.
GGSCI (deveci01) 6> info extract EXDEV01, detail
EXTRACT EXDEV01 Last Started 2019-02-19 11:19 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2019-02-26 10:45:18 Seqno 786, RBA 44710400
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/oracle/software/goldengate/dirdat/lt 2 55644 10
Extract Source Begin End
/u02/oradata/apex/redo03.log 2019-02-19 11:13 2019-02-26 10:45
Current directory /u01/oracle/software/goldengate
Report file /u01/oracle/software/goldengate/dirrpt/EXDEV01.rpt
Parameter file /u01/oracle/software/goldengate/dirprm/EXDEV01.prm
Checkpoint file /u01/oracle/software/goldengate/dirchk/EXDEV01.cpe
Process file /u01/oracle/software/goldengate/dirpcs/EXDEV01.pce
Stdout file /u01/oracle/software/goldengate/dirout/EXDEV01.out
Error log /u01/oracle/software/goldengate/ggserr.log
Displays Extract status
GGSCI (deveci01) 35> send extract EXDEV01 status
Sending STATUS request to EXTRACT EXDEV01 ...
displays Extract Statistics
GGSCI (deveci01) 33> stats extract EXDEV01
Sending STATS request to EXTRACT EXDEV01 ...
View process rate ( ‘hr’,’min’ or ‘sec’ as a parameter for time )
Display the processing rates in hours or seconds of Goldengate processes
GGSCI (deveci01) 37> stats extract EXDEV02 reportrate hr
Sending STATS request to EXTRACT EXDEV02 ...
Display of latency between replicat and source
GGSCI (deveci01) 13> send extract EXDEV02, getlag
Sending GETLAG request to EXTRACT EXDEV02 ...
Last record lag: 3 seconds.
At EOF, no more records to process.
Detailed report of processes can be displayed like following.
GGSCI (deveci01) 2> view report EXDEV01
GGSCI (deveci01) 2> view report rep1
GGSCI (deveci01) 2> view report mgr
Child processes status like following.
GGSCI (deveci01) 8> send manager childstatus
Sending CHILDSTATUS request to MANAGER ...
Child Process Status - 6 Entries
ID Group Process Retry Retry Time Start Time
---- -------- ---------- ----- ------------------ -----------
0 EXDEV01 1925238 0 None 2019/02/19 11:07:54
1 DPUMP 2195496 0 None 2019/02/19 11:08:02
2 MSSQL1 422034 0 None 2019/02/22 13:54:59
4 MYREP 1302702 0 None 2019/02/23 09:08:34
6 DEVLOAD 1200242 0 None 2019/02/23 11:05:01
7 EXDEV02 2076844 0 None 2019/02/26 08:29:22
Reference: Oracle Internals
Post a Comment: