DBA Hub

📋Steps in this guide1/11

Adding new table to a existing extract and replicat in goldengate DBACLASS

In this article steps for adding new table to a existing extract and replicat in goldengate. First we need to stop the extract,pump and replicat. Then updat

oracle replicationintermediate
by OracleDba
13 views
1

1.stop extract and pump process [ 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
18
19
20
21
22
GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:00
EXTRACT     RUNNING     EXT1P       00:00:00      00:00:00


GGSCI> stop EXT1

GGSCI> stop EXT1P



GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:00:00
EXTRACT     STOPPED     EXT1P       00:00:00      00:00:00
2

2. STOP replicat:[ TARGET ]

confirm that lag is zero i.e replicat consumed all the transactions. As LAG command reports that no more records to process (i.e lag is zero), we can stop the replicat also.

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
GGSCI  > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1       00:00:00      00:00:02

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

GGSCI (DBDvLBSS01) 3> stop REPR1

Sending STOP request to REPLICAT REPR1 ...
Request processed.
3

3. Enable supplemental logging for the tables, that need to be added [ SOURCE].

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
GGSCI > dblogin USERID ggate, PASSWORD ggate123
Successfully logged into database.

GGSCI > add trandata DBACLASS.SAL

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

4. Include the table in extract param file:[ SOURCE ]

Add the line TABLE DBACLASS.SAL; in parameter file.

Code/Command (click line numbers to comment):

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

EXTRACT EXT1
SETENV (ORACLE_SID="SRCDB")
SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ggate, PASSWORD ggate123
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;
TABLE DBACLASS.SAL;
5

5. Include the table in extract pump file: [ SOURCE]

Add the line TABLE DBACLASS.SAL; in pump parameter file.

Code/Command (click line numbers to comment):

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

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

6 . start the extract: [ SOURCE ]

Code/Command (click line numbers to comment):

1
2
3
4
GGSCI > start EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting
7

7. Start the extract pump: [ SOURCE ]

Code/Command (click line numbers to comment):

1
2
3
4
GGSCI > start EXT1P

Sending START request to MANAGER ...
EXTRACT EXT1P starting
8

8. Now do the initial loading:

Here for initial loading, take export dump of the table that we are adding and import in target database. Copy the dump to target host and import. scp [email protected]:/archive/dump/initload.dmp .

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
expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.SAL
.
.
.
Username: / as sysdba

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "DBACLASS"."SAL"                            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

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

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_DATA
. . imported "DBACLASS"."SAL"                            3.473 MB   90323 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
9

9. Update the replicat parameter file with handlecollisions parameter: [ TARGET]

Add the line MAP DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS; in param file

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
REPLICAT REPR1
setenv (ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/dbhome_1")
setenv (ORACLE_SID="TRGDB")
USERID ggate, PASSWORD ggate123
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
ASSUMETARGETDEFS
DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000
MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;
MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;
MAP  DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS;
10

10 . Start the replicat and wait for the lag to be cleared.[TARGET]

Wait till this lag is cleared( lag command will report as At EOF, no more records to process .)

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
ggsci> start repr1


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 >  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.

At EOF, no more records to process.
11

11. Remove the HANDLECOLLISIONS from the replicat parameter file and restart the replicat[TARGET]

As the lag is cleared, remove the handlecollision parameter and restart the replicat. With these steps, we have added a new table successfully to the existing extract and replicat.

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>stop REPR1

ggsci> edit replicat REPR1

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


GGSCI>start REPR1

Comments (0)

Please to add comments

No comments yet. Be the first to comment!