GoldenGate SEQUENCE 同步

GoldenGate SEQUENCE 同步

最近用GoldenGate实现了Windows Oracle10g 到Linux Oracle11g的迁移。

记录一下遇到的一个问题：
数据同步到Oracle11g后，测试发现某些应用插入数据时报主键冲突。经调查发现是一些利用sequence做主键的表有问题，sequence的最大值小于主键的最大值。对比源数据库和目标数据库的sequence发现，有些sequence的值没有同步到目标数据库。

查询GoldenGate的官方文档后发现可以用FLUSH SEQUENCE的方式来同步sequence：
GGSCI>FLUSH SEQUENCE scott.seq*

Oracle GoldenGate
Windows and UNIX Reference Guide
11g Release 1 Patch Set 1 (11.1.1.1)
E21512-02
中第82页记录了这段描述：

Use FLUSH SEQUENCE immediately after you start Extract for the first time during an initia
synchronization or a re-synchronization. This command updates an Oracle sequence so
that initial redo records are available at the time that Extract starts to capture transaction
data. Normally, redo is not generated until the current cache is exhausted. The flush give
Replicat an initial start point with which to synchronize to the correct sequence value on
the target system. From then on, Extract can use the redo that is associated with the usua
cache reservation of sequence values.
The following database procedures are used by this command and normally are installed
by running the sequence.sql script when Oracle GoldenGate is installed.

Before using FLUSH SEQUENCE, issue the DBLOGIN command as the database user that has
EXECUTE privilege on the updateSequence procedure.
To use this command, the GGSCHEMA parameter must be used in the GLOBALS file to specify
the schema in which the sequence.sql procedure is installed. This can be the same schema
that is used for the Oracle GoldenGate DDL support and any other database objects that
are owned by Oracle GoldenGate.