Copying the newly added table to Oracle Golden Gate

1. Applicable version
The operation method is valid in Oracle GoldenGate-Version: 10.4.0.46 or later versions on any platform.

2. Objectives
Add a new table for data replication to the online Oracle GoldenGate environment.

3. Method
Oracle GoldenGate provides two ways to add data replication tools.

3.1 first: Use the handlecollision Parameter
The handlecollision parameter is used to configure the replicat process in the target database.

If a parameter is configured on replicat, the duplicate data and lost data are incorrectly processed when the replication process is working.

If we require two points, using this parameter is an excellent choice.

First, in the OGG synchronization environment of the new table, we can ensure that the source database does not perform any DML operations on the table. Of course, DDL operations cannot.

2. After a new table is added, the data in the target database does not matter and is manually verified once. If the inconsistency is found, the data can be processed manually.

Of course, in the process of adding a new table, it is best that the source table is not operated.

 

The following steps are provided on the ORACLE official website.

I) 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) include the tables that you need to add into the extract parameter file and save it
3) start the extract
4) include the tables that you need to add into the extract pump parameter file and save it
5) start the pump
6) do the initial load for the new tables (ie you can take the export and import of the new tables that need to added for replication from source the target database)
7) 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;

6) start the replicat
7) 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:-4 and 5th step can be skipped if the pump is not configured.

 

3.2 Second: Do not use the handlecollision Parameter
This method is common. In actual projects, we cannot ensure that the data in the source table does not change during operations on OGG. Therefore, we cannot require the project to accept data differences or loss.

This method uses table-based SCN to synchronize differential data.

After the synchronization is complete, you can remove the synchronization configuration about SCN. The method is concise and easy to understand.

The following steps are provided on the ORACLE official website and are not translated here.

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

CURRENT_SCN
------------------------
5343407
7) 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

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

9) 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 );

10) start the replicat

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

 

4. parameter description
HANDLECOLLISIONS | NOHANDLECOLLISIONS

Valid for Replicat

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. these errors can occur during an initial load, when data from source tables is being loaded to target tables while GoldenGate is replicating transactional changes that are being made to those tables. when GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS causes Replicat to overwrite duplicate records in the target tables and provides Nate alterhandling of errors for missing records.

You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS in the following ways:

● You can use either HANDLECOLLISIONS or NOHANDLECOLLISIONS at the root level of the parameter file to affect all MAP statements.

● You can use HANDLECOLLISIONS and have as on-off switches for groups of tables to enable or disable error handling as needed. One remains in effect for all subsequent MAP statements until the other is encountered.

● You can use HANDLECOLLISIONS and NOHANDLECOLLISIONS within a MAP statement to enable and disable the functionality for a specific table. See page 204.

Any of the preceding methods can be combined. the use within a MAP statement overrides other settings. the use as a toggle overrides a global setting. for example, you cocould have a global NOHANDLECOLLISIONS setting, and then use HANDLECOLLISIONS within MAP statements to enable it only for certain tables.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12