Sunday 13 April 2014

GoldenGate - Add new table to existing Replication setup

###########################################################
Source -  SOURCE.TABLE                          Target -  TARGET.TABLE
###########################################################

STEP 1 (TARGET): Create table on Target using dblink or export import or DDL and DML statements. (Initial Load)

SQL> create table TARGET.TABLE as select * from SOURCE.TABLE@dblink_to_src;

Table created.

SQL> select count(*) from TARGET.TABLE;

  COUNT(*)
----------
        24

SQL> select count(*) from SOURCE.TABLE@dblink_to_src;

  COUNT(*)
----------
        24

STEP 2 (SOURCE): Add Trandata for table which needs to be replicated

GGSCI (SOURCE) 2> dblogin userid system, password tiger
Successfully logged into database.

GGSCI (SOURCE) 4> add trandata SOURCE.TABLE

Logging of supplemental redo data enabled for table SOURCE.TABLE.

GGSCI (SOURCE) 5> info trandata SOURCE.TABLE

Logging of supplemental redo log data is enabled for table SOURCE.TABLE


STEP 3 (SOURCE): Take a backup of parameter file

-rw-r--r--   1 oracle     oinstall      4813 Jan  2 14:58 extsrc.prm_bkp
-rw-r--r--   1 oracle     oinstall      4854 Apr  9 09:20 extsrc.prm


STEP 4 (SOURCE): Added table name to parameter file and check the difference between the backup and current param files.

oracle@SOURCE:/u02/GG/source/dirprm$ diff extsrc.prm_bkp extsrc.prm

> TABLE SOURCE.TABLE;


STEP 5 (SOURCE): Create a new definition file by adding a new table to the defgen param file.

oracle@SOURCE:/u02/GG/source/dirdef$ mv proddefgen.def proddefgen.def_bkp   --- rename the existing .def file to avoid file exist error.
oracle@SOURCE:/u02/GG/source$ cp -pr proddefgen.prm proddefgen.prm_bkp

added table entry to defgen param file

oracle@SOURCE:/u02/GG/source$ diff proddefgen.prm proddefgen.prm_bkp

< TABLE SOURCE.TABLE;


STEP 6 (SOURCE) : create a new defnition file using defgen utility

oracle@SOURCE:/u02/GG/source$ ./defgen paramfile proddefgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
     Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
  HP/UX, IA64, 64bit (optimized), Oracle 10g on Oct  5 2011 00:39:52

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


                    Starting at 2014-04-09 09:34:41
***********************************************************************
.
.
.
.
.
.
TABLE SOURCE.TABLE;
Retrieving definition for SOURCE.TABLE


STEP 7 (SOURCE): Check whether the definition got generated for new table.

oracle@SOURCE:/u02/GG/source$ diff proddefgen.def_bkp proddefgen.def

< * Definitions created/modified  2014-01-09 22:39
---
> * Definitions created/modified  2014-04-09 09:34
> *
> Definition for table SOURCE.TABLE
> Record length: 56
> Syskey: 0
> Columns: 3
> EVENT_ID     134     11        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 3
> PKUSERID     134     11       12  0  0 1 0      8      8      8 0 0 0 0 1    0 0 3
> PKTIMESTAMP  192     29       24  0  0 1 0     29     29     29 0 6 0 0 1    0 0 0


STEP 8 (SOURCE): Copy the new definition file to TARGET location.

oracle@SOURCE:/u02/GG/source$ scp proddefgen.def TARGET:/u02/GG/target/dirdef
Password:
proddefgen.def                           100%  154KB 153.5KB/s   00:00


STEP 9 (TARGET): Add Mapping to the Replicat Param file.

oracle@TARGET:/u02/GG/target/dirprm$ diff reptgt.prm reptgt.prm_bkp

< MAP SOURCE.TABLE, TARGET TARGET.TABLE;

oracle@TARGET:/u02/GG/target/dirprm$


STEP 10 (SOURCE) : STOP EXTRACT <EXTRACT NAME>
                                          : START EXTRACT <EXTRACT NAME>

STEP 11 (TARGET) : STOP REPLICAT <REPLICAT NAME>
                                          : START REPLICAT <REPLICAT NAME>


STEP 12 (SOURCE) : If pump process is configured for the extract, dont forget to add the table name to pump process param file.



If you do not have primary/Unique key constraints on any of the columns you may receive this error during update statements.


Expected Errors and resolution.

To avoid this error use APPLYNOOPUPDATES key word on Replicat param file.

2014-04-11 10:25:10  ERROR   OGG-01168  Oracle GoldenGate Delivery for Oracle, reptgt.prm:  Encountered an update for target table TARGET.TABLE, which has no unique key defined.  KEYCOLS can be used to define a key.  Use ALLOWNOOPUPDATES to process the update without applying it to the target database.  Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.
2014-04-11 10:25:10  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, reptgt.prm:  PROCESS ABENDING.