DBA Hub

📋Steps in this guide1/14

Setting up Table replication in oracle goldengate DBACLASS

In this below tutorial, we will setup one-way goldengate replication for below two tables from database SRCDB to TRGDB. DBACLASS.EMP; DBACLASS.DEPT; SEE – Oracle goldengate Fundamentals NOTE – Here we are replicating only DML transactions PREREQUISITE: Make sure the goldengate installation is completed and manager process is running on both source and target hosts. REFER: Installation […]

oracle replicationintermediate
by OracleDba
15 views
1

PREREQUISITE:

Make sure the goldengate installation is completed and manager process is running on both source and target hosts. REFER : Installation and basic configuration of goldengate Once the installation is done, proceed with below steps.
2

1. Enable supplemental logging for those tables.[SOURCE]

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
GGSCI > dblogin USERID ggate_user, PASSWORD ggate_user

Successfully logged into database.

GGSCI > add trandata DBACLASS.EMP

Logging of supplemental redo data enabled for table DBACLASS.EMP.
TRANDATA for scheduling columns has been added on table 'DBACLASS.EMP'.
TRANDATA for instantiation CSN has been added on table 'DBACLASS.EMP'.
GGSCI >


GGSCI > add trandata DBACLASS.DEPT

Logging of supplemental redo data enabled for table DBACLASS.DEPT.
TRANDATA for scheduling columns has been added on table 'DBACLASS.DEPT'.
TRANDATA for instantiation CSN has been added on table 'DBACLASS.DEPT'.
3

2. Prepare extract parameter file[ SOURCE ]

Here – /ggate/app/oracle/product/11.2.1.0.3/TST is the location on LOCAL(SOURCE HOST) where the extract trail files will be created with prefix E5*

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
GGSCI> edit params EXT1

EXTRACT EXT1
SETENV (ORACLE_SID="SRCDB")
SETENV (ORACLE_HOME = "/home/oracle/app/oracle/product/12.1.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ggate_user, PASSWORD ggate_user
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /ggate/app/oracle/product/11.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100
EXTTRAIL /ggate/app/oracle/product/11.2.1.0.3/TST/E5
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE DBACLASS.EMP;
TABLE DBACLASS.DEPT;
4

3. Prepare the extract pump parameter file [SOURCE]

RMTTRAIL – Here /ggate/oradata/datacapture/TRG in the location on TARGET HOST where the extract trails from source will be pushed and the prefix will be P1*.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
GGSCI> EDIT PARAMS EXT1P

EXTRACT EXT1P
SETENV (ORACLE_HOME = "/home/oracle/app/oracle/product/12.1.0/dbhome_1")
SETENV (ORACLE_SID = "SRCDB")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID ggate_user, PASSWORD ggate_user
RMTHOST 172.20.21.56, MGRPORT 7809
RMTTRAIL /ggate/oradata/datacapture/TRG/P1
TABLE DBACLASS.EMP;
TABLE DBACLASS.DEPT;
5

4. Add extract process [SOURCE]

Code/Command (click line numbers to comment):

1
2
3
add extract EXT1,tranlog,begin now 

EXTRACT added.
6

5. Add the extract trail [SOURCE]

Code/Command (click line numbers to comment):

1
2
GGSCI > add exttrail /ggate/app/oracle/product/11.2.1.0.3/TST/E5,extract EXT1
EXTTRAIL added.
7

6. Add the extract pump process [SOURCE]

Code/Command (click line numbers to comment):

1
2
GGSCI > add extract EXT1P, exttrailsource /ggate/app/oracle/product/11.2.1.0.3/TST/E5
EXTRACT added.
8

7. Add remote trail [SOURCE]

Now we will configure replicat on target server:

Code/Command (click line numbers to comment):

1
2
GGSCI > add rmttrail /ggate/oradata/datacapture/TRG/P1,extract EXT1P
RMTTRAIL added.
9

8. Prepare replicat parameter 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. As per Oracle DOC: > Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
ggsci> edit params REPR1

REPLICAT REPR1
setenv (ORACLE_HOME="/home/oracle/app/oracle/product/12.1.0/dbhome_1")
setenv (ORACLE_SID="TRGDB")
USERID ggate_user, PASSWORD ggate_user
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
ASSUMETARGETDEFS
HANDLECOLLISIONS
DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000
MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;
MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;
10

9. add the replicat process [ TARGET ]

Now complete setup is done. Next step is start the extract process and do the initial loading . There are multiple methods to do initial loading, But here We will use the simple expdp impdp method.

Code/Command (click line numbers to comment):

1
2
GGSCI > add replicat REPR1, exttrail /ggate/oradata/datacapture/TRG/P1
REPLICAT added.
11

10. Start the extract [ SOURCE ]

Now as the extract is running, we will proceed with initial loading (datapump method). i.e it will start capturing the transactions.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
GGSCI > start EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03 ----- >>> RUNNING 
EXTRACT     STOPPED     EXT1P       00:00:00      00:02:34
12

10. INITIAL LOADING:

EXPORT FROM SOURCE: COPY THE DUMP TO TARGET: scp oracle@***********:/archive/dump/ initload.dmp . IMPORT IN TARGET DB: Initial loading is completed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.EMP,DBACLASS.DEPT

E

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 tables=DBACLASS.EMP,DBACLASS.DEPT
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/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "DBACLASS"."EMP"                            3.473 MB   90323 rows
. . exported "DBACLASS"."DEPT"                           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
Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at Sun Jul 23 11:56:50 2017 elapsed 0 00:00:13

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 "DBACLASS"."EMP"                            3.473 MB   90323 rows
. . imported "DBACLASS"."DEPT"                           3.473 MB   90323 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
13

11. Start the datapump extract [ SOURCE ]

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
GGSCI > start EXT1P

Sending START request to MANAGER ...
EXTRACT EXT1P starting


GGSCI (dm-relay-dev-Test) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03
EXTRACT     RUNNING     EXT1P       00:00:00      00:00:01
14

12. Start the replicat [ TARGET ]

13. Remove HANDLECOLLSION after lag is cleared and restart replicat. As lag is zero now, Remove the handlescollision parameter and restart the replicat. Let’s add some record in source and see whether it is replicating to target or not: Check the replicat status: We can see the extract captures one insert and replicat received one insert. Lets do the row_count on both dbs . ROW COUNT ON SOURCE: SQL> select count(*) from “DBACLASS”.”EMP” ; COUNT(*) ———- 90324 ROW COUNT ON TARGET: Both row_count are matching. Our table level replication setup  in goldengate  is completed. It will now apply all the transactions happening on the source to target db.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
GGSCI (DBDvLBSS01) 17> start REPR1

Sending START request to MANAGER ...
REPLICAT REPR1 starting

GGSCI >  lag replicat REPR1
 
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 > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1      00:43:54         00:00:06       --- >>>> Lag at checkpoint is not  zero yet.

 
GGSCI >  !
 
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.
 
At EOF, no more records to process. -------- >>>>>>>>> This indicates lag is zero.


GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1      00:00:00          00:00:06 -- > Lag at checkpoint is zero, i.e lag is zero now.

REPLICAT REPR1
setenv (ORACLE_HOME="/home/oracle/app/oracle/product/12.1.0/dbhome_1")
setenv (ORACLE_SID="TRGDB")
USERID ggate_user, PASSWORD ggate_user
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
ASSUMETARGETDEFS
---HANDLECOLLISIONS
DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000
MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;
MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;

stop REPR1


start REPR1

SQL> insert into "DBACLASS"."EMP" values ('TEST','DB','DB2',384302938);

1 row created.

SQL> COMMIT;

Commit complete.


GGSCI > stats EXT1

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-23 12:07:45.

Output to /ggate/app/oracle/product/11.2.1.0.3/TST/E5:

Extracting from DBACLASS.EMP to DBACLASS.EMP:

*** Total statistics since 2017-07-23 12:04:16 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

GGSCI > stats EXT1

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-23 12:07:45.

Output to /ggate/app/oracle/product/11.2.1.0.3/TST/E5:

Extracting from DBACLASS.EMP to DBACLASS.EMP:

*** Total statistics since 2017-07-23 12:04:16 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

GGSCI > stats REPR1

Sending STATS request to REPLICAT REPR1 ...

Start of Statistics at 2017-07-23 12:06:58.

Replicating from DBACLASS.EMP to DBACLASS.DEPT:

*** Total statistics since 2017-07-23 12:06:58 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Comments (0)

Please to add comments

No comments yet. Be the first to comment!