新加表到Oracle Golden Gate的数据复制中

1.    适用版本

操作方法在任何平台的Oracle GoldenGate - Version: 10.4.0.46  及以后的版本用有效。

2.    目标

实现向已经上线运行的 Oracle GoldenGate 的环境中增加新的需要复制数据的表。

3.    方法

数据复制工具 Oracle GoldenGate 提供了两种增加方法，我们这里逐个介绍。

3.1     第一种：使用 handlecollision 参数

参数 handlecollision 应用于复制进程，就是目标库上的 replicat 进程的配置。

如果在 replicat 上配置了参数，那么复制进程工作时，将会对重复数据和丢失的数据做错误处理。

如果我们要求两点的话，使用该参数是个绝佳选择。

第一、               我们在加新表的 OGG 同步环境中时，能保证源库上对此表不做任何 DML 操作，当然 DDL 操作也不能有。

第二、       我们加了新表后，目标库上的数据不管，手工校验一次，发现不一致再手工处理也可以。

当然，在新加表的过程中，最好是源表不做任何操作。

 

以下步骤是ORACLE 官网提供的，这里不做翻译了。

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 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)
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     第二种：不使用 handlecollision 参数

这种方法是通用的。因为在实际项目中，我们既不能保证源表的数据在操作 OGG 时无变化，由不能要求项目上接受数据差异或者丢失。

这种方法使用基于表的 SCN 方式同步差异数据。

在同步完成后，可以除掉关于 SCN 的同步配置。方法简明扼要，很容易理解。

以下步骤是ORACLE 官网提供的，这里也不做翻译了。

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 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.    参数说明

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 alternate handling 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 NOHANDLECOLLISIONS 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 could have a global NOHANDLECOLLISIONS setting, and then use HANDLECOLLISIONS within MAP statements to enable it only for certain tables.