首页 » GoldenGate » Alert: Oracle GoldenGate 19 was change ALLOWNULLABLEKEYS to NOALLOWNULLABLEKEYS

Alert: Oracle GoldenGate 19 was change ALLOWNULLABLEKEYS to NOALLOWNULLABLEKEYS

  • 2021/10/15
  • GoldenGate
  • 710 views
  • Alert: Oracle GoldenGate 19 was change ALLOWNULLABLEKEYS to NOALLOWNULLABLEKEYS已关闭评论

OGG 19.*默认改变原来OGG 12版本ALLOWNULLABLEKEYS为NOALLOWNULLABLEKEYS， 这样会导致在replicat时因为不考虑可为空的唯一索引做为标示键,而使用全字段组合在一起作为行的唯一标识，但是EXTRACE的源库又只有这个唯一键的附加日志，trail中也只有该唯一列更新列时，在replicat时会产生OGG-1403记录不存在，而丢失更新，insert不受影响， 同时oggerr日志中记录的是INFO OGG-06441，这样更不会引起人的注意，ogg add trandata 也会有提示。

 INFO    OGG-06441Unique key UK.... for table ....  cannot be used due to the inclusion of virtual columns, 
or user-defined datatypes, or extended long varchar columns, or function-based index columns.

而后来又有客户要求，上面的info级别不拖，Oracle更改了上面的提示有info 到warning ,并制作了 Bug 30523672 fix,并且增加了nullable columns原因，已经在ogg 20.* 版本自带， 发现目前OTN上已经下线了ogg 这个版本，目前是ogg 20.3, 修改后的提示如下:

 WARNING OGG-06441 Unique key xxx for table  xxx cannot be used due to the inclusion of virtual
columns, nullable columns, or user-defined datatypes, or extended long varchar columns, or function-based index columns.

NOALLOWNULLABLEKEYS 官方文档
Description

Use NOALLOWNULLABLEKEYS to change the key selection logic so that it does not consider a nullable unique key as a viable candidate for uniquely identifying a row. When disabled, the nullable unique keys are viable candidates. The default value for NOALLOWNULLABLEKEYS is set to true.

Allowing Oracle GoldenGate to use a nullable key can cause data corruption, as Oracle treats each row with a NULL value as a key column and as a separate unique value. It is recommended to use NOALLOWNULLABLEKEYS unless you are absolutely sure that the key column does not contain any NULL values.

Be careful when using this parameter because it impacts the contents of the trail file and all installations must be in sync when using this parameter.

Upon upgrade to Oracle GoldenGate 19c, it is recommended that you query DBA_LOGSTDBY_NOT_UNIQUE view. If SCHEMATRANDATA is not being used, then for each table in DBA_LOGSTDBY_NOT_UNIQUE view, add KEYCOLS that mirror key columns returned by INFO TRANDATA, DELETE TRANDATA, or ADD TRANDATA for table to select or use a key with non-NULL columns.

解决方法
A.
源和目标库的OGG GLOBALS 文件增加ALLOWNULLABLEKEYS 回退到OGG 12的行为，源库表增加全列附加日志。
— or —
B.
replicat 参数中指定KEYCOLS逻辑唯一列，确保应用的表中没有null记录。

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：