Thursday, December 5, 2013

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)

No comments :

Post a Comment