Golden Gate - how to add table to existing replication

Somtimes we have a task to add table(s) to existing replication environment. I'd like to demonstrate it clear. In my test example - classic and easy model:
Source DB host - extract + pump
Target DB host - replicat 
But extract, pump not necessarily on the same host, so and replicat may resides not on the source DB host.


1.On source - add supplementall logging for table:

GGSCI (source_host) 1> DBLOGIN, USERID ****, PASSWORD ****

GGSCI (source_host) 2> add trandata SOURCE_TABLE

2.Add table to extract proccess EXTRACT_PRC:

GGSCI (source_host) 3> stop EXTRACT_PRC

GGSCI (source_host) 4> edit param PUMP_PROC

GGSCI (source_host) 5> table SOURCE_TABLE;
GGSCI (source_host) 6> start EXTRACT_PRC

3.Add table to pump proccess PUMP_PRC:

GGSCI (source_host) 7> stop PUMP_PRC

GGSCI (source_host) 8> DBLOGIN, USERID ****, PASSWORD ****

GGSCI (source_host) 10> edit param PUMP_PROC

GGSCI (source_host) 11> table  SOURCE_TABLE;

4. Start  PUMP_PROC , check extract statistics:

GGSCI (source_host) 12> start PUMP_PRC

GGSCI (source_host) 13> stats PUMP_PROC , table  SOURCE_TABLE;

5. Add table to replicat process REPL_PRC (and dont't start it yet):

GGSCI (target_host) 1> stop REPL_PRC
GGSCI (target_host) 2> edit param REPL_PRC

GGSCI (target_host) 3> map  SOURCE_TABLE target TARGET_TABLE;

6. Create empty table on target DB. I have not big table so it's easier to "insert into as select". If U have huge table - it's better use datapump (expdp\impdp) with FLASHBACK SCN option. On the target DB: 


SQL> create table...TARGET_TABLE;

/* to get table metadata U may use dbms_metadata.get_ddl on the source table */


7. Get SCN of replicat stopped time:

GGSCI (target_host) 4> info REPL_PRC

REPLICAT    REPL_PRC     Last Started 2012-08-07 16:34   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:05:04 ago)

Log Read Checkpoint  File ./dirdat/qm000053

                     2012-08-07 16:30:29.880223  RBA 17561526
GGSCI (target_host) 5> exit

[oracle@target_host gg]$ logdump

Oracle GoldenGate Log File Dump Utility

Version 11.1.1.1.2_05 13609969 OGGCORE_11.1.1.1.3_PLATFORMS_120127.1840

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Logdump 1 >open ./dirdat/qm000053

Current LogTrail is /gg/dirdat/qm000053

Logdump 2 >ggstoken detail

Logdump 3 >detail on

Logdump 4 >pos 17561526

Reading forward from RBA 17561526

Logdump 5 >n

2012/07/11 11:56:29.880.595 FieldComp            Len    45 RBA 17561526

Name: SOURCE_TABLE

After  Image:                                             Partition 4   G  s

 0000 000d 0000 0009 3230 3437 3136 3834 3500 0400 | ........204716845...

 0600 0000 0235 3200 0600 0e00 0000 0a33 3036 3538 | .....52........30658

 3631 3630 38                                      | 61608

Column     0 (x0000), Len    13 (x000d)

Column     4 (x0004), Len     6 (x0006)

Column     6 (x0006), Len    14 (x000e)

GGS tokens:

TokenID x52 'R' ORAROWID         Info x00  Length   20

 4141 4172 6d69 4141 4141 4174 702b 7a41 4151 0001 | AAArmiAAAAAtp+zAAQ..

TokenID x4c 'L' LOGCSN           Info x00  Length   12

 3837 3739 3436 3234 3830 3332                     | 877946248032

TokenID x36 '6' TRANID           Info x00  Length   12

 3638 2e33 332e 3335 3832 3634                     | 68.33.358264

8. Fill target table. As you can see - we inserts data at the SCN=SCN_WHEN_REPLICAT_STOPED-1. And of course you need to create database link to source DB.

SQL> insert into TARGET_TABLE select * from

SOURCE_TABLE@DB_LINK AS OF SCN  877946248031;
SQL> commit;

9. Start replicat and check stats, make sure that Golden Gate replicates data:

GGSCI (target_host) 4> start REPL_PRC

GGSCI (target_host) 6> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING      REPL_PRC       00:00:00      00:00:01

GGSCI (target_host) 6> stats  REPL_PRC, table TARGET_TABLE

Sending STATS request to REPLICAT  REPL_PRC  ...

Start of Statistics at 2012-08-07 16:35:38.

Replicating from  SOURCE_TABLE   to  TARGET_TABLE :

*** Total statistics since 2012-08-02 10:08:28 ***

        Total inserts                                2.00

        Total updates                                2.00

        Total deletes                                0.00

        Total discards                               0.00

        Total operations                             4.00

*** Daily statistics since 2012-08-07 00:00:00 ***

        Total inserts                                2.00

        Total updates                                2.00

        Total deletes                                0.00

        Total discards                               0.00

        Total operations                             4.00

*** Hourly statistics since 2012-08-07 16:00:00 ***

        Total inserts                                2.00

        Total updates                                2.00

        Total deletes                                0.00

        Total discards                               0.00

        Total operations                             4.00

*** Latest statistics since 2012-08-02 10:08:28 ***

        Total inserts                                2.00

        Total updates                                2.00

        Total deletes                                2.00

        Total discards                               0.00

        Total operations                             4.00

End of Statistics.

10. Optionaly, you may add PK, indexes (not triggers) and so on to the target table.

PS: if DDL of source and target tables are different you should use DEFGEN utility.