There is always something new to learn every day. For me, today was one of those days, where I learnt a new way to add new tables to an existing Extract, Pump, and Replicat setup.
The current configuration consists of a single Extract and Replicat running OGG 11.2.1.0.13 on OEL 6.4 64bit. Both databases are on the same host.
From the source database, we capture DDL for all tables from the DAY and NIGHT schema and replicate them to the RIVER and WATER schemas on the target respectively.
If you noticed, I’m not using a Pump. The two databases reside on the same server, therefore there’s no need for one.
Onward!
The goal here is to add two more tables STONE and GREYSTREET to the Extract and Pump while GoldenGate is replicating data.
1. Log into the source database via GGSCI
[oracle@blue ggate]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.13 17435036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131002.1206_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 3 2013 02:39:46
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (blue.color) 2> dblogin userid ggate, password ggate
Successfully logged into database.
2. Add trandata for the new table(s).
GGSCI (blue.color) 3> add trandata song.stone
2013-12-05 15:17:07 WARNING OGG-00869 No unique key is defined for table 'STONE'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SONG.STONE.
GGSCI (blue.color) 4> add trandata song.greystreet
2013-12-05 15:17:15 WARNING OGG-00869 No unique key is defined for table 'GREYSTREET'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SONG.GREYSTREET.
3. Add the two tables in the Extract Parameter file
GGSCI (blue.color) 5> edit params e_gg
...
TABLE song.stone;
TABLE song.greystreet;
...
4. Restart the Extract e_gg
GGSCI (blue.color) 2> stop e_gg
Sending STOP request to EXTRACT E_GG ...
Request processed.
GGSCI (blue.color) 3> start e_gg
Sending START request to MANAGER ...
EXTRACT E_GG starting
5. At this point, capture the Current SCN from the database. Do not lose this number, we need it later!
[oracle@blue ggate]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 15:28:31 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1113396
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
6. Take a Data Pump export of the two tables. Please note the use of FLASHBACK_SCN.
[oracle@blue ggate]$ expdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet
Export: Release 11.2.0.3.0 - Production on Thu Dec 5 15:32:04 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SONG"."GREYSTREET" 5.882 KB 100 rows
. . exported "SONG"."STONE" 5.679 KB 100 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/sky/dpdump/additional_tables.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:14
7. Run the import on the Target database.
[oracle@blue ggate]$ impdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river
Import: Release 11.2.0.3.0 - Production on Thu Dec 5 15:45:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RIVER"."GREYSTREET" 5.882 KB 100 rows
. . imported "RIVER"."STONE" 5.679 KB 100 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:45:31
8. Here’s the important step, you need to edit the replicat’s parameter file and include the following string in the map statement.
GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone , FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
MAP song.greystreet, TARGET river.greystreet, FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
...
9. Recycle the replicat.
GGSCI (blue.color) 4> stop r_gg
Sending STOP request to REPLICAT R_GG ...
start Request processed.
GGSCI (blue.color) 5> start r_gg
Sending START request to MANAGER ...
REPLICAT R_GG starting
GGSCI (blue.color) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_GG 00:00:00 00:00:09
REPLICAT RUNNING R_GG 00:00:00 00:00:03
10.Check the counts on both systems.
10.1. On the Source
SQL> select name from v$database;
NAME
---------
SKY
SQL> select count(1) from song.stone;
COUNT(1)
----------
100
10.2. On the Target
SQL> select name from v$database;
NAME
---------
WATER
SQL> select count(1) from river.stone;
COUNT(1)
----------
100
10.3. Insert data on the source.
SQL> INSERT INTO song.stone
SELECT dbms_random.string('A', 2) RNDMSTR
FROM all_objects
WHERE ROWNUM;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from song.stone;
COUNT(1)
----------
200
10.4. Check the counts on the target table.
SQL> select count(1) from river.stone;
COUNT(1)
----------
200
10.5. Check lag on the replicat.
GGSCI (blue.color) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_GG 00:00:00 00:00:02
REPLICAT RUNNING R_GG 00:00:00 00:00:08
11. Once the table is caught up, remove the FILTER parameters from the replicat.
GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone;
MAP song.greystreet, TARGET river.greystreet;
...
12. Restart the replicat and we’re done!
GGSCI (blue.color) 4> stop r_gg
Sending STOP request to REPLICAT R_GG ...
start Request processed.
GGSCI (blue.color) 5> start r_gg
Sending START request to MANAGER ...
REPLICAT R_GG starting
GGSCI (blue.color) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_GG 00:00:00 00:00:06
REPLICAT RUNNING R_GG 00:00:00 00:00:02
According to the MOS note I’ve found to accomplish this task, this only works when DDL Replication is enabled. I’ve yet to try the steps without the former enabled, and on databases other than Oracle.
MOS Note: OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)
Update Dec 15, 2013: I had shared this note with an Engineer @ Oracle who actually converted this into a MOS Note! I’m excited!!
How to add tables to an Existing GoldenGate Configuration with Transaction Integrity? (Doc ID 1607591.1)
Maaz Anjum's Blog 
I don’t think this is adequate. Changes to GREYSTONE and STREET that are > SCN 1113396, and SCN less than the replicat position CSN at the time of replicat restart will not be applied to the target database. This is because the replicat is already checkpointed beyond that point in the trail at the time you restart the replicat.
I followed the steps . but after step 6 i.e expdp i added one more row to that table . and follow all steps. but after start replicator group i am not getting that row which i added after expdp on to target db . . Would you pl suggest what i missing
Naresh – Unfortunately I don’t have access any more to a GoldenGate environment and its been years since I last touched it 🙂
You could try asking in Oracle-L or Oracle Forums. Alternatively, try asking on dbasolved.com.
Good luck!
Cheers,
Maaz
Naresh, Did you sort this out? Is it working fine (adding one row after expdp) ?
Maaz, Hope you are doing well!!! I am doing the exact same thing right now and found your post. Great post!!!
Craig,
Great to hear from you 🙂 Glad this helped you out!
Cheers,
Maaz