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

oracle@server1:~/ [PROD1] PROD1

oracle@server1:~/ [PROD1] cdgh

oracle@server1:/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

oracle@server1:~/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

oracle@server1:~/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

oracle@server1:~/app/oracle/admin/PROD1/create/goldengate/ [PROD1] 

scp /u00/app/oracle/admin/PROD1/dmp/export_tables_G001SCHEMA.dmp oracle@server2:~/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

oracle@server2:~/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

oracle@server2:~/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).

oracle@server2:~/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

oracle@server2:~/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

=============================================================================

Oracle DBA Information at one place

Search This Blog