10 Configuring Conflict Detection and Resolution
10.3 CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD
This example resolves all conflict types by using the USEMAX, OVERWRITE, and DISCARD resolutions.
10.3.1 Table Used in this Example
The examples assume identical Oracle databases.
CREATE TABLE tgt(
name varchar2(30) primary key, phone varchar2(10),
address varchar2(100),
salary number, balance number, comment varchar2(100), last_mod_time timestamp);
At the source database, all columns are supplementally logged:
ADD TRANDATA scott.src, COLS (name, phone, address, salary, balance, comment, last_mod_time);
10.3.2 MAP Statement with Conflict Resolution Specifications
MAP fin.src, TARGET fin.tgt,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_mod_time)), RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_mod_time)), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD)), );
10.3.3 Description of MAP Statement
The following describes the MAP statement:
■ Per COMPARECOLS, use the before image of all columns in the trail record in the Replicat WHERE clause for updates and deletes.
■ Per DEFAULT, use all columns as the column group for all conflict types; thus the resolution applies to all columns.
■ For an INSERTROWEXISTS conflict, use the USEMAX resolution: If the row exists during an insert, use the last_mod_time column as the resolution column for deciding which is the greater value: the value in the trail or the one in the database. If the value in the trail is greater, apply the record but change the insert to an update. If the database value is higher, ignore the record.
■ For an UPDATEROWEXISTS conflict, use the USEMAX resolution: If the row exists during an update, use the last_mod_time column as the resolution column: If the value in the trail is greater, apply the update.
■ For a DELETEROWEXISTS conflict, use the OVERWRITE resolution: If the row exists during a delete operation, apply the delete.
■ For an UPDATEROWMISSING conflict, use the OVERWRITE resolution: If the row does not exist during an update, change the update to an insert and apply it.
■ For a DELETROWMISSING conflict use the DISCARD resolution: If the row does not exist during a delete operation, discard the trail record.
10.3.4 Error Handling
For an example of error handling to an exceptions table, see Section 10.2.3,
"Configuring the Oracle GoldenGate Parameter Files for Error Handling."
Note: As an alternative to USEMAX, you can use the USEMAXEQ
resolution to apply a >= condition. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.
10.3.5 INSERTROWEXISTS with the USEMAX Resolution
For this example, the USEMAX resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve an insert where the row exists in the source and target, but some or all row values are different.
10.3.6 UPDATEROWEXISTS with the USEMAX Resolution
For this example, the USEMAX resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve an update where the row exists in the source and target, but some or all row values are different.
Table 10–1 INSERTROWEXISTS Conflict with USEMAX Resolution
Image SQL Comments
Before image in trail None (row was inserted on the source).
N/A
After image in trail name='Mary' phone='1234567890'
last_mod_time='9/1/10 3:00 is the after image of the resolution column. Since there is an after image, this will be used to determine the resolution.
last_mod_time='9/1/10 1:00 is the current image of the resolution column in the target against which the resolution column value in the trail is compared.
Initial INSERT applied by Replicat that detects the conflict
This SQL returns a uniqueness conflict on
’Mary’.
UPDATE applied by Replicat to resolve the conflict
Because USEMAX is specified for
INSERTROWEXISTS, Replicat converts the insert to an update, and it compares the value of last_mod_time in the trail record with the value in the database. The value in the record is greater, so the after images for columns in the trail file are applied to the target.
10.3.7 UPDATEROWMISSING with OVERWRITE Resolution
For this example, the OVERWRITE resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve the case where the target row is missing. The logical resolution, and the one used, is to overwrite the row into the target so that both databases are in sync again.
Table 10–2 UPDATEROWEXISTS Conflict with USEMAX Resolution
Image SQL Comments
Before image in trail name='Mary' phone='1234567890'
last_mod_time='9/1/10 3:00 is the before image of the resolution column.
After image in trail phone='222222' address='Holly'
last_mod_time='9/1/10 5:00'
last_mod_time='9/1/10 5:00 is the after image of the resolution column. Since there is an after image, this will be used to determine the resolution.
last_mod_time='9/1/10 6:00 is the current image of the resolution column in the target against which the resolution column value in the trail is compared.
Initial UPDATE applied by Replicat that detects the conflict
This SQL returns a no-data-found error because the values for the balance, comment, and last_mod_time are different in the target.
All columns are used in the WHERE clause because the COMPARECOLS statement is set to ALL.
UPDATE applied by Replicat to resolve the conflict
Because the after value of last_mod_time in the trail record is less than the current value in the database, the database value is retained. Replicat applies the operation with a WHERE clause that contains the primary key plus a last_mod_time value set to less than 9/1/10 5:00. No rows match this criteria, so the statement fails with a "data not found" error, but Replicat ignores the error because a USEMAX resolution is expected to fail if the condition is not satisfied.
10.3.8 DELETEROWMISSING with DISCARD Resolution
For this example, the DISCARD resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve the case where the target row is missing. In the case of a delete on the source, it is
acceptable for the target row not to exist (it would need to be deleted anyway), so the resolution is to discard the DELETE operation that is in the trail.
Table 10–3 UPDATEROWMISSING Conflict with OVERWRITE Resolution
Image SQL Comments
Before image in trail name='Jane' phone='333'
After image in trail phone='4444' address='Holly'
last_mod_time='9/1/10 8:00' Target database image None (row for Jane is missing) Initial UPDATE applied by
Replicat that detects the conflict
This SQL returns a no-data-found error.
All columns are used in the WHERE clause because the COMPARECOLS statement is set to ALL.
INSERT applied by Replicat to resolve the conflict
The update is converted to an insert because OVERWRITE is the resolution. The after image of a column is used if available; otherwise the before image is used.
Table 10–4 DELETEROWMSING Conflict with DISCARD Resolution
Image SQL Comments
Before image in trail name='Jane' phone='4444'
After image in trail None N/A
10.3.9 DELETEROWEXISTS with OVERWRITE Resolution
For this example, the OVERWRITE resolution is illustrated with the applicable before and after images for the record in the trail and in the database. It shows how to resolve the case where the source row was deleted but the target row exists. In this case, the OVERWRITE resolution applies the delete to the target.
Target database image None (row missing) N/A
Initial DELETE applied by Replicat that detects the conflict
This SQL returns a no-data-found error.
All columns are used in the WHERE clause because the COMPARECOLS statement is set to ALL.
SQL applied by Replicat to resolve the conflict
None Because DISCARD is specified as the
resolution for DELETEROWMISSING, so the delete from the trail goes to the discard file.
Table 10–5 DELETEROWEXISTS Conflict with OVERWRITE Resolution
Image SQL Comments
Before image in trail name='Mary' phone='222222'
After image in trail None N/A
Target database image name='Mary'
The row exists on the target, but the phone, address, balance, comment, and last_mod_time columns are different from the before image in the trail.
Initial DELETE applied by Replicat that detects the conflict
All columns are used in the WHERE clause because the COMPARECOLS statement is set to ALL.
A no-data-found error occurs because of the difference between the before and current values.
DELETE applied by Replicat to resolve the conflict
SQL bind variables:
1)'Mary'
Because OVERWRITE is the resolution. the DELETE is applied using only the primary key (to avoid an integrity error).
Table 10–4 (Cont.) DELETEROWMSING Conflict with DISCARD Resolution
Image SQL Comments