Adding new table to an existing OGG configuration

1. On source DB server:

[oracle@source_host]$ ggsci

GGSCI (hostname) 3> dblogin userid ggate, password ggate

2. Add trandata for the new table

GGSCI (hostname) 3> add trandata user1.table1

3. Add the two tables in the Extract Parameter file

GGSCI (hostname) 5> edit params capt

...

TABLE user1.table1;

...

4. Restart the Extract capt

GGSCI (hostname) 2> stop capt

GGSCI (hostname) 3> start capt

5. [oracle@source_host]$ sqlplus / as sysdba

SQL> select to_char(current_scn) from v$database;

6. Export the table. Use of FLASHBACK_SCN.

[oracle@source_host]$ expdp system/oracle directory=dpdir dumpfile=expdp_new_tables.dmp flashback_scn=123456789 tables=user1.table1

7. Import on the target DB.

[oracle@target_host]$ impdp system/oracle directory=dpdir dumpfile=expdp_new_tables.dmp remap_schema=user1:user2

8. Edit the replicat’s parameter file:

GGSCI (hostname) 2> edit params rplct

...

MAP user1.table1, TARGET user2.table1 , FILTER ( @GETENV("TRANSACTION", "CSN") > 123456789);

...

9. Recycle the replicat.

GGSCI (hostname) 4> stop rplct

GGSCI (hostname) 5> start rplct

GGSCI (hostname) 6> info all

10. Check the replication on both systems.

Check lag on the replicat.

11. Remove the FILTER parameters from the replicat.

GGSCI (hostname) 2> edit params rplct

...

MAP user1.table1, TARGET user2.table1;

or

MAP user1.table1, TARGET user2.table1, KEYCOLS(ID); -- * if ID is your PK

...

12. Restart the replicat

GGSCI (hostname) 4> stop rplct

GGSCI (hostname) 5> start rplct

MOS Note: OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)

How to add tables to an Existing GoldenGate Configuration with Transaction Integrity? (Doc ID 1607591.1)