Being a DBA we are must aware of replication in DATA field. In this tutorial, we will explain how to do schema replication in goldengate. Schema replication means, replicating all the tables of a schema. This table explains about the HOST IP, DB_NAME, Goldengate Process, SCHEMA To Replicate. All steps mention the way to replication in goldengate.
| SOURCE | TERGET | |
| HOST IP | 192.168.0.1 | 192.168.0.2 |
| DB_NAME | ORCLSRC | ORCLTRG |
| GG Process | EXT_ORCL (extract)
EXT_ORCLP((extract pump) |
REP_ORCL(replicat) |
| SCHEMA TO REPULICAT | SCOTT.* | SCOTT.* |
1. In the first step add supplemental for all tables of a schema on the source side
add trandata <schema_name>.*
GGSCI > dblogin userid ggate_user, password ggate_user Successfully logged into database.
GGSCI > ADD TRANDATA SCOTT.* Logging of supplemental redo data enabled for table SCOTT.TAB1. TRANDATA for scheduling columns has been added on table 'SCOTT.TAB1'. TRANDATA for instantiation CSN has been added on table 'SCOTT.TAB1'. Logging of supplemental redo data enabled for table SCOTT.TAB2. TRANDATA for scheduling columns has been added on table 'SCOTT.TAB2'. TRANDATA for instantiation CSN has been added on table 'SCOTT.TAB2'. Logging of supplemental redo data enabled for table SCOTT.TAB3. TRANDATA for scheduling columns has been added on table 'SCOTT.TAB3'. TRANDATA for instantiation CSN has been added on table 'SCOTT.TAB3'.
2. Prepare extract file for source
ggsci> edit params EXT_ORCL EXTRACT EXT_ORCL SETENV (ORACLE_SID="ORCLSRC) SETENV (ORACLE_HOME="/oracle/app/oracle/product/12.1.0.2/dbhome_1") userid ggate_user, password ggate_user FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION DBOPTIONS ALLOWUNUSEDCOLUMN CACHEMGR CACHESIZE 500M DISCARDFILE /ggate_user/DISCARD/ORCL_B2B1.dsc, APPEND, MEGABYTES 100 EXTTRAIL /ggate_user/TRAILFILE/S2 WARNLONGTRANS 6H, CHECKINTERVAL 1H TABLE SCOTT.*;
3. Prepare extract pump parameter file [ SOURCE ]
ggsci>edit params EX_C1P EXTRACT EX_C1P userid ggate_user, password ggate_user SETENV (ORACLE_HOME="/oracle/app/oracle/product/12.1.0.2/dbhome_1") RMTHOST 172.41.32.42, MGRPORT 7809 RMTTRAIL /ggate/install/datacapture/T2 DBOPTIONS ALLOWUNUSEDCOLUMN TABLE SCOTT.*;
4. Add extract process [SOURCE]
add extract EX_C1,tranlog,begin now EXTRACT added.
5. Add the extract trail [SOURCE]
GGSCI > add exttrail /ggate_user/TRAILFILE/S2,extract EX_C1 EXTTRAIL added.
6. Add the extract pump process [SOURCE]
GGSCI > add extract EX_C1P, exttrailsource /ggate_user/TRAILFILE/S2 EXTRACT added.
7. Add remote trail [SOURCE]
GGSCI > add rmttrail /ggate/install/datacapture/T2,extract EX_C1P RMTTRAIL added.
8. Prepare replicat file [ TARGET ]
Here we have to add HANDLECOLLISION parameter to resolve conflicts for the transactions during initial loading. We will remove this parameter once the lag is cleared after initial loading.
ggsci> edit params REP_S1 REPLICAT REP_S1 SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.1.0/db_1") SETENV (ORACLE_SID = "TRGDB") USERID ggate_user, PASSWORD ggate_user DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST ASSUMETARGETDEFS HANDLECOLLISIONS DISCARDFILE /ggate/install/datacapture/discardcrm.dsc,append MAP SCOTT.*,TARGET SCOTT.*;
9. add the replicat process [ TARGET ]
GGSCI > add replicat REPR1, exttrail /ggate/install/datacapture/T2 REPLICAT added.
10.Start the extract and pump process [ SOURCE ]
START EX_C1 START EX_C1P GGSCI > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EX_C1 00:00:00 00:00:03 EXTRACT RUNNING EX_C1P 00:00:00 00:02:34
11. Initial loading:
Now export this schema using expdp in the source database and import in the target database.
— SOURCE DB
expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT schemas=SCOTT Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_02": /******** AS SYSDBA dumpfile=initload.dmp logfile=initload.log directory=DIRT schemas=SCOTT Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 16 MB Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS . . exported "SCOTT"."TAB1" 3.473 MB 90323 rows . . exported "SCOTT"."TAB2" 3.473 MB 90323 rows . . exported "SCOTT"."TAB3" 3.473 MB 90323 rows Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_02 is: /archive/dump/initload.dmp
— TRANSFER THE DUMP TO TARGET SERVER:
scp oracle@**********:/archive/dump/initload.dmp .
— -IMPORT IN TARGET DATABASE:
impdp dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH Import: Release 12.1.0.2.0 - Production on Sun Jul 23 12:01:58 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_04" successfully loaded/unloaded import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in AL32UTF8 character set and UTF8 NCHAR character set WARNING: possible data loss in character set conversions Starting "SYS"."SYS_IMPORT_FULL_04": /******** AS SYSDBA dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."TAB1" 3.473 MB 90323 rows . . imported "SCOTT"."TAB2" 3.473 MB 90323 rows . . imported "SCOTT"."TAB3" 3.473 MB 90323 rows Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
12. Start the replicat: [ SOURCE ]
GGSCI > start REP_S1 Sending START request to MANAGER ... REPLICAT REP_S1 starting
Use LAG command to monitor the lag at replicat.
GGSCI > lag replicat REP_S1 2017-07-24 15:33:12 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN. Sending GETLAG request to REPLICAT REPR1 ... Last record lag 6 seconds. GGSCI > ! 2017-07-24 15:33:12 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN. Sending GETLAG request to REPLICAT REP_S1 ... Last record lag 6 seconds. At EOF, no more records to process.
As lag is zero now, Remove the handles collision parameter and restart the rreplicate
13. Remove handlecollison parameter and restart replicat[ TARGET ]
REPLICAT REP_S1 SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.1.0/db_1") SETENV (ORACLE_SID = "TRGDB") USERID ggate_user, PASSWORD ggate_user DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST ASSUMETARGETDEFS --HANDLECOLLISIONS DISCARDFILE /ggate/install/datacapture/discardcrm.dsc,append MAP SCOTT.*,TARGET SCOTT.*; ggsci>stop REP_S1 ggsci>start REP_S1
goldengate Schema replication has been completed.
Pingback: Schema replication using oracle goldengate - SSWUG.ORG
It is better to take the export of the source database with the flashback scn and start the replicat with aftercsn to avoid duplication on the target server which was a part of the import. This will eliminate the use of handlecollisions in the replicat parameter file to handle duplicates.