為客戶部署的Oracle GoldenGate在測試階段出現如下的錯誤：

2012-04-24 10:45:20 ERROR OGG-01168 Oracle GoldenGate Delivery for Oracle, rep1.prm: Encountered an update for target table CCENSE.ST_CARDOPERATORSTATTMP, which has no unique key defined. KEYCOLS can be used to define a key. Use ALLOWNOOPUPDATES to process the update without applying it to the target database. Use APPLYNOOPUPDATES to force the update to be applied using all columns in both the SET and WHERE clause.

資料同步出問題的表沒有主鍵，客戶執行insert的時候沒有問題，在執行update的時候報了上面的錯誤。以上的報錯涉及三個引數，KEYCOLS，ALLOWNOOPUPDATES，APPLYNOOPUPDATES。下面我們分別討論這3個引數：

1）KEYCOLS
KEYCOLS關鍵字用於對沒有主鍵的表，指定能夠唯一標識資料的欄位，這樣Oracle GoldenGate同樣可以完成資料的同步（同步UPDATE操作）。
將目標資料庫的replicat配置修改為：
MAP CCENSE.ST_CARDOPERATORSTATTMP, TARGET CCENSE.ST_CARDOPERATORSTATTMP, KEYCOLS(cardsfid)
重啟目標資料庫的replicat程式即可完成資料的同步，但需要注意的是，使用這種方式必須把所有的表列出來，不能指定為.*。

2）ALLOWNOOPUPDATES

官方文件解釋如下：

ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES

Valid for Replicat

Use ALLOWNOOPUPDATES and NOALLOWNOOPUPDATES to control how Replicat responds to a “noop”

operation. A no-op operation is one in which there is no effect on the target table. The

following are some examples of how this can occur.

● The source table has a column that does not exist in the target table, or has a column

that was excluded from replication (with a COLSEXCEPT clause). In either case, if that

source column is updated, there will be no target column name to use in the SET clause

within the Replicat SQL statement.

● An update is made that sets a column to the same value as the current one. The

database does not log the new value, because it did not really change. However, Oracle

GoldenGate extracts the operation as a change record because the primary key was

logged — but there is no column value for the SET clause in the Replicat SQL statement.

By default (NOALLOWNOOPUPDATES), Replicat abends with an error because these types of

operations do not update the database. With ALLOWNOOPUPDATES, Replicat ignores the

operation instead of abending. The statistics reported by Replicat will show that an update

was made, but the database will not be updated.

You can use the internal parameter APPLYNOOPUPDATES to force the update to be applied.

APPLYNOOPUPDATES overrides ALLOWNOOPUPDATES. If both are specified then updates with

only key columns will be applied. By default, Oracle GoldenGate will abend with the

following message if it only has source key columns but there is no key defined for the target table.

2011-01-25 02:28:42 GGS ERROR 160 Encountered an update for target

table TELLER, which has no unique key defined. KEYCOLS can be used to

define a key. Use ALLOWNOOPUPDATES to process the update without applying

it to the target database. Use APPLYNOOPUPDATES to force the update to

be applied using all columns in both the SET and WHERE clause.

Exceptions when error-handling is in place

If ALLOWNOOPUPDATES is specified when the HANDLECOLLISIONS or INSERTMISSINGUPDATES

parameters are being used, and if Oracle GoldenGate has all of the target key values, then

Oracle GoldenGate will not ignore the update, but instead will apply it using all key

columns in the SET clause and the WHERE clause (invoking APPLYNOOPUPDATES behavior). This

is necessary so Oracle GoldenGate can detect if the row being updated is missing. If it is,

then Oracle GoldenGate turns the update into an insert.

Default NOALLOWNOOPUPDATES (only applies if the table does not have a key)

Syntax ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES

該引數的含義就是在同步到目標資料庫的時候忽略出現的NO-OP操作，NO-OP操作包括目標資料庫沒有主資料庫的欄位（兩邊資料庫欄位不同），還有一種情況是執行UPDATE語句修改的資料和原有資料一樣，沒有發生變化。以上兩種情況叫做NO-OP操作，遭遇NO-OP操作gg會被終止。

3）APPLYNOOPUPDATES
該引數的含義就是不忽略出現的NO-OP操作，而是在目標資料庫強制執行UPDATE修改操作，且只更新鍵值資料（主鍵或KEYCOLS指定的鍵值），如果表沒有主鍵，也沒有使用KEYCOLS關鍵字指定欄位，那麼將更新表資料的所有欄位，如果沒有相應記錄，將INSERT新插入一條。
官方文件解釋如下：

APPLYNOOPUPDATES | NOAPPLYNOOPUPDATES

Valid for Replicat

Use APPLYNOOPUPDATES to force a “no-op” update to be applied using all columns in both the

SET and WHERE clauses. See ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES for a description of

“no-op.”

APPLYNOOPUPDATES uses whatever data is in the trail. If there is a primary key update

record, it uses the before columns from the source. If there is a regular (non-key) update,

it assumes that the after value is the same as the before value (otherwise it would be a

primary key update). The preceding assumes source and target keys are identical. If they

are not, you must use a KEYCOLS clause in the TABLE statement on the source.

Default NOAPPLYNOOPUPDATES

Syntax APPLYNOPUPDATES | NOAPPLYNOPUPDATES

請注意，我們在討論NO-OP操作的時候，NO-OP操作並不包含UPDATE沒有主鍵的表，所以我們遇到的這個問題和ALLOWNOOPUPDATES和APPLYNOOPUPDATES這兩個引數沒有關係。透過實踐證明，在replicat配置檔案中加入這兩個引數（或只加入ALLOWNOOPUPDATE引數），在更新沒有主鍵，也沒有指定KEYCOLS欄位的表的時候同樣會報錯。對沒有主鍵，但加上了KEYCOLS欄位後的表執行UPDATE操作成功同步了資料。

如果沒有在源資料庫啟用記錄表的supplement log，同樣會收到以上的報錯，原因是在於沒有記錄沒有主鍵表的supplement log資料，並將其傳遞到目標資料庫，執行以下操作可以啟用記錄表的supplement log：
cd $GGHOME
./ggsci
dblogin userid ,password
add trandata .
完成以上操作之後，可以解決由此問題導致的update不能同步。

結論
在處理沒有主鍵的表，且對錶有UPDATE操作的時候，應該為這些表在replicat配置檔案的MAP設定中加上KEYCOLS關鍵字，後面的跟上能夠唯一標識資料的一個欄位或多個欄位，甚至可以把表的所有欄位加在KEYCOLS關鍵字後面。

Oracle GoldenGate 11.2 OGG-01168

相關文章