Adding Tables to an Existing GoldenGate Configuration with Transaction Integrity

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)

Advertisements

3 comments

  1. Ryan Young · · Reply

    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.

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Musings

Things I see and learn!

Thoughts from James H. Lui

If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.

bdt's oracle blog

Sharing experience (by Bertrand Drouvot)

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Vishal desai's Oracle Blog

Just another WordPress.com weblog

%d bloggers like this: