Infrastructure at your Service

Hervé Schweitzer

Adding new tables to an existing Oracle GoldenGate replication

Once a replication environment is set up, it’s rarely forever. Adding tables to an Oracle GoldenGate replication group with minimal impact to the current replication is quite a difficult operation. This posting should help you with this task.

In the example below, tables from schema G001 will be added to an existing replication setup from the database PROD1 to REP1.

CFG_ADV_COND
CFG_NARRATIVE_TEMPLATE
CFG_REG_REPORT_RULES
CMN_LOOKUP
CMN_USER_LOGIN

 

Stop the replication environment

  • Connect to the source database PROD1 and stop all extract groups for the replication to the database REP1
[email protected]:~/ [PROD1] PROD1
[email protected]:~/ [PROD1] cdgh
[email protected]:/u99/app/goldengate/gss/11.1.1.1.0/ [PROD1] ggsci

GGSCI (server1) 1> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPG001 00:00:00 00:00:06
EXTRACT RUNNING G001 00:00:00 00:00:05

GGSCI (server1) 2> stop extract *
Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED DPG001 00:00:00 00:00:01
EXTRACT STOPPED G001 00:00:00 00:00:13
  • Connect to the target database REP1 and stop the replicat group coming from the database PROD1
GGSCI (server2) 1> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING PROD1 00:00:00 00:00:04

GGSCI (server2) 2> stop REPLICAT PROD1
GGSCI (server2) 3> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED PROD1 00:00:00 00:00:04

 

Update the extract environment with the new tables

  • Now we can update the extract group G001 on the source database with the new tables to be replicated. Edit the G001 parameter file and add the new tables (vi editor)
GGSCI (server1) 30> edit params G001
. . . 
table G001SCHEMA.CFG_ADV_COND;
table G001SCHEMA.CFG_NARRATIVE_TEMPLATE;
table G001SCHEMA.CFG_REG_REPORT_RULES;
table G001SCHEMA.CMN_LOOKUP;
table G001SCHEMA.CMN_USER_LOGIN;
  • Add supplemental login on source database PROD1 for the new added tables
GGSCI (server1) 7> DBLOGIN userid goldengate, password ******
Successfully logged into database.

GGSCI (server1) 8> add trandata G001SCHEMA.CFG_ADV_COND
GGSCI (server1) 9> add trandata G001SCHEMA.CFG_NARRATIVE_TEMPLATE
GGSCI (server1) 10> add trandata G001SCHEMA.CFG_REG_REPORT_RULES
GGSCI (server1) 11> add trandata G001SCHEMA.CMN_LOOKUP
GGSCI (server1) 13> add trandata G001SCHEMA.CMN_USER_LOGIN
  • Configure the source database to extract the additional five tables and we can restart the corresponding extract groups
GGSCI (server1) 30> start extract *
GGSCI (server1) 31> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPG001 00:23:02 00:00:03
EXTRACT RUNNING G001 00:00:00 00:00:06

 

Initial Load from the new tables

As the addtional tables from the source database PROD1 are extracted, the initial load (expdp/impdp) from the data can be started on the target database REP1.
Attention, we have to start the export for a specific transaction point (SCN) , in order to begin the replication target database from the same SCN.

Thus we have to going to read the current_scn from the source database for the synchronization (explained later).

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 1657153626

 

  • Create an expdp parfile with the above selected SCN and the additional tables to replicate
[email protected]:~/app/oracle/admin/PROD1/create/goldengate/add_tables [PROD1] cat expdp_additional_tables.par

flashback_scn=1657153626
SCHEMAS=G001SCHEMA
DUMPFILE=export_tables_G001SCHEMA.dmp
LOGFILE=export_tables_G001SCHEMA.log
INCLUDE=TABLE:"IN('CFG_ADV_COND','CFG_NARRATIVE_TEMPLATE','CFG_REG_REPORT_RULES','CMN_LOOKUP','CMN_USER_LOGIN')"
DIRECTORY=DATAPUMPDIR

 

  • Start the export from the above tables as user system
[email protected]:~/app/oracle/admin/PROD1/create/goldengate/ [PROD1] expdp parfile=expdp_additional_tables.par

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=expdp_additional_tables.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 21.25 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "G001SCHEMA"."CMN_USER_LOGIN" 8.735 MB 286163 rows
. . exported "G001SCHEMA"."CFG_ADV_COND" 1.523 MB 1617 rows
. . exported "G001SCHEMA"."CFG_REG_REPORT_RULES" 100.5 KB 714 rows
. . exported "G001SCHEMA"."CFG_NARRATIVE_TEMPLATE" 9.789 KB 85 rows
. . exported "G001SCHEMA"."CMN_LOOKUP" 6.585 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
 /u00/app/oracle/admin/PROD1/dmp/export_ARGUS_tables_G001SCHEMA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:01:22
  • Copy the dumpfile from server1 to server2
[email protected]:~/app/oracle/admin/PROD1/create/goldengate/ [PROD1] 
scp /u00/app/oracle/admin/PROD1/dmp/export_tables_G001SCHEMA.dmp [email protected]:~/app/oracle/admin/REP1/dmp/
export_tables_G001SCHEMA.dmp 100% 11MB 10.7MB/s 00:00
  • Create an impdp parfile in order to load the tables from the above created dumpfile
Take care: the correct REMAP and EXCLUDE parameter must be configured based on your environment requirement

[email protected]:~/app/oracle/admin/REP1/create/goldengate/add_tables/ [REP1] cat impdp_PROD1_tables.par

DUMPFILE=export_tables_G001SCHEMA.dmp
LOGFILE=import_tables_G001SCHEMA.log
REMAP_SCHEMA=G001SCHEMA:G001_PROD1
REMAP_TABLESPACE=DATA_01:PROD1_DATA
REMAP_TABLESPACE=DATA_02:PROD1_DATA
REMAP_TABLESPACE=INDEX_01:PROD1_DATA
REMAP_TABLESPACE=INDEX_02:PROD1_DATA
DIRECTORY=DATAPUMPDIR
EXCLUDE=GRANT
EXCLUDE=CONSTRAINT
EXCLUDE=REF_CONSTRAINT
  • Start the import with impdp as user system using the above parameter file
[email protected]:~/app/oracle/admin/REP1/create/goldengate/add_tables/ [REP1] impdp parfile=impdp_PROD1_tables.par

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=impdp_PROD1_tables.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "G001_PROD1"."CMN_USER_LOGIN" 8.735 MB 286163 rows
. . imported "G001_PROD1"."CFG_ADV_COND" 1.523 MB 1617 rows
. . imported "G001_PROD1"."CFG_REG_REPORT_RULES" 100.5 KB 714 rows
. . imported "G001_PROD1"."CFG_NARRATIVE_TEMPLATE" 9.789 KB 85 rows
. . imported "G001_PROD1"."CMN_LOOKUP" 6.585 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:49:06

 

Add temporary the new tables to replication group

Now the new tables to replicate can be temporary add to the replication group PROD1 with the above selected SCN number, as starting point for the replication.

Attention: Within the GoldenGate configuration file we speak about CSN and not SCN, but both contains the same information (System Change Number).

[email protected]:~/app/goldengate/ggs/11.1.1.1.0/[REP1] ggsci

GGSCI (server2)> edit params PROD1

REPLICAT PROD1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD *****
DISCARDFILE /u00/app/goldengate/ggs/11.1.1.1.0/discard/PROD1_discard.txt, append, megabytes 10
MAP G001schema.CFG_ADV_COND ,TARGET G001_PROD1.CFG_ADV_COND ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 1657153626);
MAP G001schema.CFG_NARRATIVE_TEMPLATE ,TARGET G001_PROD1.CFG_NARRATIVE_TEMPLATE ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 1657153626);
MAP G001schema.CFG_REG_REPORT_RULES ,TARGET G001_PROD1.CFG_REG_REPORT_RULES ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 1657153626);
MAP G001schema.CMN_LOOKUP ,TARGET G001_PROD1.CMN_LOOKUP ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 1657153626);
MAP G001schema.CMN_USER_LOGIN ,TARGET G001_PROD1.CMN_USER_LOGIN ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 1657153626);
MAP G001schema.*, TARGET G001_PROD1.*
  • Start the replication group PROD1 with the new added tables
GGSCI (server2) 2> start replicat PROD1

Sending START request to MANAGER ...
REPLICAT PROD1 starting
  • Check the configuration until the both databases are synchronised
GGSCI (server2) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING PROD1 00:00:00 00:00:00

 

Remove the temporary added new tables from the replication group file

As both databases are again synchronized including the new tables, the temporary added filter can be removed from the replication group file.

  • Stop the replication group PROD1

GGSCI (server2) 4> stop replicat PROD1

  • Set back the original extraction configuration file for the froup PROD, without the filters for the new files
[email protected]:~/app/goldengate/ggs/11.1.1.1.0/[REP1] ggsci

GGSCI (server2) 2> edit params PROD1

REPLICAT PROD1
ASSUMETARGETDEFS
USERID goldengate, PASSWORD *****
DISCARDFILE /u00/app/goldengate/ggs/11.1.1.1.0/discard/PROD1_discard.txt, append, megabytes 10
MAP G001schema.*, TARGET G001_PROD1.*
  • Restart the replication
GGSCI (server2) 3> start REPLICAT PROD1
GGSCI (server2) 4> info all

Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING PROD1 00:00:00 00:00:00

 

Conclusion

The integration of additional tables into a existing GoldenGate replication environment is not very complex, but the steps order must be exactly and carefully followed. Have fun with Oracle Goldengate!

14 Comments

  • Chandra Cheedella says:

    Excellent article. Thanks for the detailed procedure.

  • Ryan says:

    Thank you. This helped me get the mental gears going. But I quickly think it can be improved.

    Perhaps my solution might only work with Oracle (I’m only familiar with GoldenGate on Oracle). But, it seems that your process would interrupt the replication for the entire time you are exporting and importing the new tables. I don’t think that’s necessary. The whole process can be done with only two momentary outages. One blip at the beginning to restart the extract, and one blip at the end to restart the replicat (or two, the second one being to remove the filters). I’m leaving out some of the basic steps like supplemental logging and things, they’re still important of course. Below is the high-level process I’m thinking of, and will test of course, but is sound in principle.

    1) Edit extract par file. Add new table[s] (begin now)
    2) Restart extract
    3) Record current SCN of source database, AND the trail file suffix # currently being written to
    4) Pull export from source database
    5) Run import into target database
    6) Stop replicat. Record CSN from checkpoint table. (if you’re not using checkpoint table b/c you’re using integrated extract, I would argue that you should anyway. It is invaluable in a situation where you lose a checkpoint or trail file)..
    6) Edit replicat par file. Add new table[s] with CSN filter (>) on each using CSN from step #3
    7) Edit replicat par file. Modify “old” tables. Add CSN filter (>) using CSN from step #6
    8) Alter replicat extseqno to be seq from step #3, Alter extrba to 0
    9) Start replicat

    • Ryan says:

      Shoot. I try to be a good proofreader.

      – Need to increment last four step numbers to 7, 8, 9 and 10
      – Step #6 should read: (…if you’re not using checkpoint table b/c you’re using integrated ***REPLICAT***…)

    • Erin says:

      Hi Ryan,
      How do I “Record CSN from checkpoint table”? I only see RBA in that table, is that it? but later in FILTER it seems we’d have to use CSN?
      How do I do ” 8) Alter replicat extseqno to be seq from step #3, Alter extrba to 0″ exactly?
      Thanks!
      Erin

  • Hervé Schweitzer says:

    Hi Ryan,

    Thanks for your comments ! it’s true you can keep the replication running during the exp/imp procedure.

    Regards
    Hervé

  • Naresh says:

    HI Hervé.. Pls. suggest me in following scenario where
    After note-down SCN suppose (100) at source side . i inserted one more row to source table . after complete import and start replicator group i’m not getting that record which i added i.e record after SCN(100) . Would you pl suggest me what i’m missing.

  • Ash says:

    Hi Please advise,
    I am facing the error while starting the replicate. error in MAP statement as shown below :
    Error:
    2017-08-16 20:39:32 ERROR OGG-00375 Error in FILTER clause.

    I used below statement :
    MAP ST.STRUC_VER ,TARGET exy_OGG.STRUC_VER ,FILTER ( @GETENV (“TRANSACTION”, “CSN”) > 9924381720438);

    even I have tried so many combinations of spaces ( space before after commas, bracket and all) but no luck. Could you please tell me, where I am wrong. I am trying to resolve this issue from last so many days but no luck.

    Regards
    Ash

  • Ash says:

    Sorry forget to tell you my OGGversion is “Version 12.1.2.1.9” , database version is Oracle 11g and LInus version is Server release 6.8.

  • Hervé Schweitzer says:

    Hi Ashm

    Try it with single quotes, I wrote this blog with GoldenGate 11, and Oracle changed the quotes format in 12.

    MAP ST.STRUC_VER ,TARGET exy_OGG.STRUC_VER ,FILTER ( @GETENV (‘TRANSACTION’, ‘CSN’) > 9924381720438);

    or do you have to set the below parameter

    Table 1-1 Comparison of Input Requirements for [NO]USEANSISQLQUOTES

    Regards
    Hervé

  • Nour says:

    Excuse-me I am new in GG, I don’t understand why we use expdp/impdp, if we use GG ?!!

    • Hervé Schweitzer says:

      Hi Nour,

      Expdp/impdp is only used for the initialload, afterwards the replication will be done with GoldenGate.
      For you information, it exist multiple solution to do the initialload.

      Regards
      Hervé

  • Madhu says:

    Hi ,

    Getting below error ,while adding new table to existing replicat

    OGG-01298 Column function diagnostic message: could not find column “TRANSACTION”.

    Please help me resource this one

  • Tushar says:

    Why have not you used DEFGEN file ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Hervé Schweitzer
Hervé Schweitzer

Chief Technology Officer (CTO) and Principal Consultant