How to use NOALLOWNOOPUPDATES/ALLOWNOOPUPDATES Replicat parameter [ID 1144303.1]

		修改时间 27-JUL-2010 类型 HOWTO 状态 PUBLISHED

In this Document
Goal
Solution

Applies to:

Oracle GoldenGate - Version: 10.0.0.0 and later [Release: 10.0.0 and later ]
Information in this document applies to any platform.

Goal

Describes the usage of NOALLOWNOOPUPDATES/ALLOWNOOPUPDATES parameter

Solution

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

================================================================================================

A no-op operation is one in which there is no net 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 APPLYNOOPUPDATES parameter 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.

2005-08-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

2.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 clause. See ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES for a description of “no-op.”

APPLYNOOPUPDATES uses whatever it has from the trail. If there is a primary key update record, then 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, then a KEYCOLS clause needs to be used in the TABLE statement on the source.

Default NOAPPLYNOOPUPDATES

Syntax APPLYNOPUPDATES | NOAPPLYNOPUPDATES

显示相关信息 相关的

产品

Middleware > Data Integration > GoldenGate > Oracle GoldenGate

错误

ERROR HANDLING; ERROR 160

[@more@]

ITPub博客

goldengate 的ALLOWNOOPUPDATES 参数

Applies to:

Goal

Solution

最新文章