1. Reference for Oracle GoldenGate
  2. Oracle GoldenGate Common Components
  3. Oracle GoldenGate Parameters
  4. ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES

ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES

Valid For

Replicat

Description

Use ALLOWNOOPUPDATES and NOALLOWNOOPUPDATES to control how Replicat responds to a no-op 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 it 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 change. However, Oracle GoldenGate captures 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 does not get updated.

You can use the internal parameter APPLYNOOPUPDATES to force the UPDATE to be applied. APPLYNOOPUPDATES overrides ALLOWNOOPUPDATES. If both are specified, Replicat applies updates for which there are key columns for the source and target tables. By default, Oracle GoldenGate abends with the following message when there is a key on the source table but no key on 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.

If ALLOWNOOPUPDATES is specified when the HANDLECOLLISIONS or INSERTMISSINGUPDATES parameter is being used, and if Oracle GoldenGate has all of the target key values, Oracle GoldenGate applies an UPDATE by using all of the columns of the table in the SET clause and the WHERE clause (invoking APPLYNOOPUPDATES behavior). This is necessary so that Oracle GoldenGate can determine whether the row is present or missing. If it is missing, Oracle GoldenGate converts the UPDATE to an INSERT.

To enable ALLOWNOOPUPDATES in a configuration where DDL replication is not active, stop all processes, add ALLOWNOOPUPDATES to the GLOBALS parameter file, and then restart the processes. To disable ALLOWNOOPUPDATES again, remove it from the GLOBALS file and then restart the processes.

To enable ALLOWNOOPUPDATES functionality in a configuration where DDL support is active, take the following steps.

  1. Add the ALLOWNOOPUPDATES parameter to the GLOBALS parameter file.

  2. Update the GGS_SETUP table in the DDL schema by using the following SQL. 

    UPDATE owner.GGS_SETUP SET value='0' WHERE
property='ALLOWNOOPUPDATES';
COMMIT;

  3. Restart all Oracle GoldenGate processes including Manager. From this point on, Oracle GoldenGate selects non-validated or non-valid primary keys as a unique identifier.

To disable ALLOWNOOPUPDATES functionality when DDL support is active, take the following steps.

  1. Remove ALLOWNOOPUPDATES from the GLOBALS parameter file.

  2. Update the record that you added to the GGS_SETUP table to 0.

  3. Update the GGS_SETUP table in the DDL schema by using the following: 

    UPDATE owner.GGS_SETUP SET value='1' WHERE 
property='NOALLOWNULLABLEKEYS'; 
COMMIT;

  4. Restart all of the Oracle GoldenGate processes.

Default

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

Syntax

ALLOWNOOPUPDATES | NOALLOWNOOPUPDATES