How to add new tables for running OGG extract and replicat

There are two ways to add the new tables for OGG replication 

à using handlecollision
à without using handlecollisions

A) using handlecollisions
----------------------------
1)stop the extract,pump and replicat

 once the extract is stopped, wait for the pump to catch up before stopping it.

 once the pump is stopped, wait for the replicat to catch up before stopping it.

2)Enable Supplemental Logging at Table Level on source side

 GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>

3)include the tables that you need to add into the extract parameter file and save it
4)start the extract
5)include the tables that you need to add into the extract pump parameter file and save it
6)start the pump
7)do the initial load for the the new tables( ie you can take the export and import of the new tables that need to to added for replication from source the target database)
8)Wait for the initial load(export and import) to be completed and then include the tables that you need to add into the replicat parameter file with HANDLECOLLISIONS parameter
eg: MAP hr.dep, TARGET hr.dep, HANDLECOLLISIONS;
MAP hr.country, TARGET hr.country, HANDLECOLLISIONS;

9) start the replicat
10) once the lag becomes zero remove the HANDLECOLLISIONS from the replicat parameter file and restart the replicat
eg :-
MAP hr.dep, TARGET hr.dep;
MAP hr.country, TARGET hr.country;

NOTE:-  step 4 and 5 can be skipped if the pump is not configured.


B)without using handlecollision
--------------------------------------
1) stop the extract,pump and replicat

once the extract is stopped, wait for the pump to catch up before stopping it.

        once the pump is stopped, wait for the replicat to catch up before stopping it.

2)Enable Supplemental Logging at Table Level on source side

GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>  

3)add the new table in extract parameter file and save it
4)start the extract
5)add the new table in extract pump parameter file and save it
6)start the extract pump
7)get the current SCN from the source database
eg:-
SQL> select current_scn from v$database;

CURRENT_SCN
------------------------
5343407

8) Check that there are no open DML transactions against the table. If there are open transactions, make sure that their starting SCN is higher than the one obtained in step 4) , i.e. 5343407

9)re-sync the the newly added table from source to target(using normal export/import).
Make sure to use FLASHBACK_SCN parameter for the export.

10) Add the table in the replicat parameter file including the below option( FILTER ( @GETENV ("TRANSACTION", "CSN") > <scn_number obtained from source db>) )as shown in the below example
eg:-
MAP source.test1, TARGET target.test1 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);
MAP source.test2, TARGET target.test2 ,FILTER ( @GETENV ("TRANSACTION", "CSN") > 5343407);

11)start the replicat

12)verify the tables on source and table and once the lag is zero remove the filter parameter from the replicat parameter file and restart.

NOTE:  if you are using HANDLECOLLISIONS method, and if you are doing on a table with no Primary key and unique key then you might end up with Duplicate data in the table, so i prefer you go always with WITHOUT HANDLECOLLISIONS method in these cases.

Ref:[ Doc ID 1332674.1]