10 Configuring Conflict Detection and Resolution

This chapter contains instructions for using the Oracle GoldenGate Conflict Detection and Resolution (CDR) feature. Conflict detection and resolution is required in active-active configurations, where Oracle GoldenGate must maintain data synchronization among multiple databases that contain the same data sets.

This chapter includes the following sections:

10.1 Overview of the Oracle GoldenGate CDR Feature

Oracle GoldenGate Conflict Detection and Resolution (CDR) provides basic conflict resolution routines that:

  • Resolve a uniqueness conflict for an INSERT.

  • Resolve a "no data found" conflict for an UPDATE when the row exists, but the before image of one or more columns is different from the current value in the database.

  • Resolve a "no data found" conflict for an UPDATE when the row does not exist.

  • Resolve a "no data found" conflict for a DELETE when the row exists, but the before image of one or more columns is different from the current value in the database.

  • Resolve a "no data found" conflict for a DELETE when the row does not exist.

To use conflict detection and resolution (CDR), the target database must reside on a Windows, Linux, or UNIX system. It is not supported for databases on the NonStop platform.

CDR supports data types that can be compared with simple SQL and without explicit conversion:

  • NUMERIC

  • DATE

  • TIMESTAMP

  • CHAR/NCHAR

  • VARCHAR/ NVARCHAR

This means that these column types can be used with the COMPARECOLS parameter, the GETBEFORECOLS parameter, and as the resolution column in the USEMIN and USEMAX options of the RESOLVECONFLICT parameter. Only NUMERIC columns can be used for the USEDELTA option of RESOLVECONFLICT. Do not use CDR for columns that contain LOBs, abstract data types (ADT), or user-defined types (UDT).

Conflict resolution is not performed when Replicat operates in BATCHSQL mode. If a conflict occurs in BATCHSQL mode, Replicat reverts to GROUPTRANSOPS mode, and then to single-transaction mode. Conflict detection occurs in all three modes. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

10.2 Configuring Oracle GoldenGate CDR

Follow these steps to configure the source database, target database, and Oracle GoldenGate for conflict detection and resolution. These steps are:

10.2.1 Making the Required Column Values Available to Extract

To use CDR, the following column values must be logged so that Extract can write them to the trail.

  • The full before image of each record. Some databases do not provide a before image in the log record, and must be configured to do so with supplemental logging. For most supported databases, you can use the ADD TRANDATA command for this purpose.

  • Use the LOGALLSUPCOLS parameter to ensure that the full before and after images of the scheduling columns are written to the trail. Scheduling columns are primary key, unique index, and foreign key columns. LOGALLSUPCOLS causes Extract to include in the trail record the before image for UPDATE operations and the before image of all supplementally logged columns for both UPDATE and DELETE operations.

  • For NonStop SQL/MX source databases, create or alter the table to have the no auditcompress attribute.

For detailed information about these parameters and commands, see the Reference for Oracle GoldenGate for Windows and UNIX. See the examples starting on CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD for more information on how these parameters work with CDR.

10.2.2 Configuring the Oracle GoldenGate Parameter Files for Conflict Resolution

The following parameters are required to support conflict detection and resolution.

  1. Use the GETBEFORECOLS option of the Extract TABLE parameter to specify columns for which you want Extract to capture the before image of an update or delete operation. For DB2 databases, use the GETUPDATEBEFORES parameter instead of GETBEFORECOLS, which is not supported for DB2.
  2. Use the COMPARECOLS option of the MAP parameter in the Replicat parameter file to specify columns that are to be used with before values in the Replicat WHERE clause. The before values are compared with the current values in the target database to detect update and delete conflicts. (By default, Replicat only uses the primary key in the WHERE clause; this may not be enough for conflict detection).
  3. Use the RESOLVECONFLICT option of the MAP parameter to specify conflict resolution routines for different operations and conflict types. You can use RESOLVECONFLICT multiple times in a MAP statement to specify different resolutions for different conflict types. However, you cannot use RESOLVECONFLICT multiple times for the same type of conflict. Use identical conflict-resolution procedures on all databases, so that the same conflict produces the same end result. One conflict-resolution method might not work for every conflict that could occur. You might need to create several routines that can be called in a logical order of priority so that the risk of failure is minimized.

Note:

Additional consideration should be given when a table has a primary key and additional unique indexes or unique keys. The automated routines provided with the COMPARECOLS and RESOLVECONFLICT parameters require a consistent way to uniquely identify each row. Failure to consistently identify a row will result in an error during conflict resolution. In these situations the additional unique keys should be disabled or you can use the SQLEXEC feature to handle the error thrown and resolve the conflict.

For detailed information about these parameters, see Reference for Oracle GoldenGate for Windows and UNIX. See the examples starting on CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD, for more information on these parameters.

10.2.3 Configuring the Oracle GoldenGate Parameter Files for Error Handling

CDR should be used in conjunction with error handling to capture errors that were resolved and errors that CDR could not resolve.

  1. Conflict resolution is performed before these other error-handling parameters: HANDLECOLLSIONS, INSERTMISSINGUPDATES, and REPERROR. Use the REPERROR parameter to assign rules for handling errors that cannot be resolved by CDR, or for errors that you do not want to handle through CDR. It might be appropriate to have REPERROR handle some errors, and CDR handle others; however, if REPERROR and CDR are configured to handle the same conflict, CDR takes precedence. The INSERTMISSINGUPDATES and HANDLECOLLISIONS parameters also can be used to handle some errors not handled by CDR. See the Reference for Oracle GoldenGate for Windows and UNIX for details about these parameters.
  2. (Optional) Create an exceptions table. When an exceptions table is used with an exceptions MAP statment (see Configuring the Oracle GoldenGate Parameter Files for Error Handling), Replicat sends every operation that generates a conflict (resolved or not) to the exceptions MAP statement to be mapped to the exceptions table. Omit a primary key on this table if Replicat is to process UPDATE and DELETE conflicts; otherwise there can be integrity constraint errors.

    At minimum, an exceptions table should contain the same columns as the target table. These rows will contain each row image that Replicat applied to the target (or tried to apply).

    In addition, you can define additional columns to capture other information that helps put the data in transactional context. Oracle GoldenGate provides tools to capture this information through the exceptions MAP statement (see Configuring the Oracle GoldenGate Parameter Files for Error Handling). Such columns can be, but are not limited to, the following:

    • The before image of the trail record. This is a duplicate set of the target columns with names such as col1_before, col2_before, and so forth.

    • The current values of the target columns. This also is a duplicate set of the target columns with names such as col1_current, col2_current, and so forth.

    • The name of the target table

    • The timestamp of the conflict

    • The operation type

    • The database error number

    • (Optional) The database error message

    • Whether the conflict was resolved or not

  3. Create an exceptions MAP statement to map the exceptions data to the exceptions table. An exceptions MAP statement contains:
    • (Required) The INSERTALLRECORDS option. This parameter converts all mapped operations to INSERTs so that all column values are mapped to the exceptions table.

    • (Required) The EXCEPTIONSONLY option. This parameter causes Replicat to map operations that generate an error, but not those that were successful.

    • (Optional) A COLMAP clause. If the names and definitions of the columns in the exceptions table are identical to those of the source table, and the exceptions table only contains those columns, no COLMAP is needed. However, if any names or definitions differ, or if there are extra columns in the exceptions table that you want to populate with additional data, use a COLMAP clause to map all columns.

10.2.3.1 Tools for Mapping Extra Data to the Exceptions Table

The following are some tools that you can use in the COLMAP clause to populate extra columns:

  • If the names and definitions of the source columns are identical to those of the target columns in the exceptions table, you can use the USEDEFAULTS keyword instead of explicitly mapping names. Otherwise, you must map those columns in the COLMAP clause, for example:

    COLMAP (exceptions_col1 = col1, [...])
    
  • To map the before image of the source row to columns in the exceptions table, use the @BEFORE conversion function, which captures the before image of a column from the trail record. This example shows the @BEFORE usage.

    COLMAP (USEDEFAULTS, exceptions_col1 = @BEFORE (source_col1), &
    exceptions_col2 = @BEFORE (source_col2), [...])
    
  • To map the current image of the target row to columns in the exceptions table, use a SQLEXEC query to capture the image, and then map the results of the query to the columns in the exceptions table by using the 'queryID.column' syntax in the COLMAP clause, as in the following example:

    COLMAP (USEDEFAULTS, name_current = queryID.name, phone_current = queryID.phone, [...])
    
  • To map timestamps, database errors, and other environmental information, use the appropriate Oracle GoldenGate column-conversion functions. For example, the following maps the current timestamp at time of execution.

    res_date = @DATENOW ()
    

See Sample Exceptions Mapping with Additional Columns in the Exceptions Table, for how to combine these features in a COLMAP clause in the exceptions MAP statement to populate a detailed exceptions table.

See Reference for Oracle GoldenGate for Windows and UNIX for the usage and syntax of the parameters and column-conversion functions shown in these examples.

10.2.3.2 Sample Exceptions Mapping with Source and Target Columns Only

The following is a sample parameter file that shows error handling and simple exceptions mapping for the source and target tables that are used in the CDR examples that begin. This example maps source and target columns, but no extra columns. For the following reasons, a COLMAP clause is not needed in the exceptions MAP statement in this example:

  • The source and target exceptions columns are identical in name and definition.

  • There are no other columns in the exceptions table.

    Note:

    This example intentionally leaves out other parameters that are required in a Replicat parameter file, such as process name and login credentials, as well as any optional parameters that may be required for a given database type. When using line breaks to split a parameter statement into multiple lines, use an ampersand (&) at the end of each line.

        -- REPERROR error handling: DEFAULT represents all error types. DISCARD
        -- writes operations that could not be processed to a discard file.
    REPERROR (DEFAULT, DISCARD)
        -- Specifies a discard file.
    DISCARDFILE /users/ogg/discards/discards.dsc, PURGE
        -- The regular MAP statement with the CDR parameters
    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)), &
    );
        -- Starts the exceptions MAP statement by mapping the source table to the
        -- exceptions table.
    MAP fin.src, TARGET fin.exception, &
        -- directs Replicat only to map operations that caused the error specified
        -- in REPERROR.
    EXCEPTIONSONLY, &
        -- directs Replicat to convert all the exceptions to inserts into the
        -- exceptions table. This is why there cannot be a primary key constraint
        -- on the exceptions table.
    INSERTALLRECORDS 
    ;
    

10.2.3.3 Sample Exceptions Mapping with Additional Columns in the Exceptions Table

The following is a sample parameter file that shows error handling and complex exceptions mapping for the source and target tables that are used in the CDR examples that begin. In this example, the exceptions table has the same rows as the source table, but it also has additional columns to capture context data.

Note:

This example intentionally leaves out other parameters that are required in a Replicat parameter file, such as process name and login credentials, as well as any optional parameters that may be required for a given database type. When using line breaks to split a parameter statement into multiple lines, use an ampersand (&) at the end of each line.

    -- REPERROR error handling: DEFAULT represents all error types. DISCARD
    -- writes operations that could not be processed to a discard file.
REPERROR (DEFAULT, DISCARD)
    -- Specifies the discard file.
DISCARDFILE /users/ogg/discards/discards.dsc, PURGE
    -- The regular MAP statement with the CDR parameters
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)) 
);
    -- Starts the exceptions MAP statement by mapping the source table to the -- exceptions table.
MAP fin.src, TARGET fin.exception, &
    -- directs Replicat only to map operations that caused the error specified
    -- in REPERROR.
EXCEPTIONSONLY, &
    -- directs Replicat to convert all the exceptions to inserts into the
    -- exceptions table. This is why there cannot be a primary key constraint
    -- on the exceptions table.
INSERTALLRECORDS &
    -- SQLEXEC query to select the values from the target record before the
    -- Replicat statement is applied. These are mapped to the *_target
    -- columns later.
SQLEXEC (id qry, query 'select name, phone, address, salary, balance, & comment, last_mod_time from fin.tgt where name = :p1', PARAMS(p1 = name )), &
    -- Start of the column mapping, specifies use default column definitions.
COLMAP ( &
    -- USEDEFAULTS maps the source columns to the target exceptions columns
    -- that receive the after image that Replicat applied or tried to apply.
    -- In this case, USEDEFAULTS can be used because the names and definitions 
    -- of the source and target exceptions columns are identical; otherwise
    -- the columns must be mapped explicitly in the COLMAP clause.
USEDEFAULTS, &
    -- captures the timestamp when the resolution was performed.
res_date = @DATENOW (), &
    -- captures and maps the DML operation type.
optype = @GETENV ('LASTERR', 'OPTYPE'), &
    -- captures and maps the database error number that was returned.
dberrnum = @GETENV ('LASTERR', 'DBERRNUM'), &
    -- captures and maps the database error that was returned.
dberrmsge = @GETENV ('LASTERR', 'DBERRMSG'), &
    -- captures and maps the name of the target table
tabname = @GETENV ('GGHEADER', 'TABLENAME'), &
    -- If the names and definitions of the source columns and the target
    -- exceptions columns were not identical, the columns would need to
    -- be mapped in the COLMAP clause instead of using USEDEFAULTS, as 
    -- follows:
       -- name_after = name, &
       -- phone_after = phone, &
       -- address_after = address, &
       -- salary_after = salary, &
       -- balance_after = balance, &
       -- comment_after = comment, &
       -- last_mod_time_after = last_mod_time &
    -- maps the before image of each column from the trail to a column in the
    -- exceptions table.
name_before = @BEFORE (name), &
phone_before = @BEFORE (phone), &
address_before = @BEFORE (address), &
salary_before = @BEFORE (salary), &
balance_before = @BEFORE (balance), &
comment_before = @BEFORE (comment), &
last_mod_time_before = @BEFORE (last_mod_time), &
    -- maps the results of the SQLEXEC query to rows in the exceptions table
    -- to show the current image of the row in the target.
name_current = qry.name, &
phone_current = qry.phone, &
address_current = qry.address, &
salary_current = qry.salary, &
balance_current = qry.balance, &
comment_current = qry.comment, &
last_mod_time_current = qry.last_mod_time)
;

For more information about creating an exceptions table and using exceptions mapping, see Handling Replicat Errors during DML Operations.

Once you are confident that your routines work as expected in all situations, you can reduce the amount of data that is logged to the exceptions table to reduce the overhead of the resolution routines.

10.2.4 Viewing CDR Statistics

The CDR feature provides the following methods for viewing the results of conflict resolution.

10.2.4.1 Report File

Replicat writes CDR statistics to the report file:

Total CDR conflicts                 7
    CDR resolutions succeeded       6
    CDR resolutions failed          1
    CDR INSERTROWEXISTS conflicts   1
    CDR UPDATEROWEXISTS conflicts   4
    CDR UPDATEROWMISSING conflicts
    CDR DELETEROWEXISTS conflicts      1
    CDR DELETEROWMISSING conflicts     1

10.2.4.2 GGSCI

You can view CDR statistics from GGSCI by using the STATS REPLICAT command with the REPORTCDR option:

STATS REPLICAT group, REPORTCDR

10.2.4.3 Column-conversion Functions

The following CDR statistics can be retrieved and mapped to an exceptions table or used in other Oracle GoldenGate parameters that accept input from column-conversion functions, as appropriate.

  • Number of conflicts that Replicat detected

  • Number of resolutions that Replicat resolved

  • Number of resolutions that Replicat could not resolve

To retrieve these statistics, use the @GETENV column-conversion function with the 'STATS' or 'DELTASTATS' information type. The results are based on the current Replicat session. If Replicat stops and restarts, it resets the statistics.

You can return these statistics for a specific table or set of wildcarded tables:

@GETENV ('STATS','TABLE','SCHEMA.TABLNAME','CDR_CONFLICTS')
@GETENV ('STATS','TABLE','SCHEMA.TABLNAME','CDR_RESOLUTIONS_SUCCEEDED')
@GETENV ('STATS','TABLE','SCHEMA.TABLNAME','CDR_RESOLUTIONS_FAILED')

You can return these statistics for all of the tables in all of the MAP statements in the Replicat parameter file:

@GETENV ('STATS','CDR_CONFLICTS')
@GETENV ('STATS','CDR_RESOLUTIONS_SUCCEEDED')
@GETENV ('STATS','CDR_RESOLUTIONS_FAILED')

The 'STATS' information type in the preceding examples can be replaced by 'DELTASTATS' to return the requested counts since the last execution of 'DELTASTATS'.

For more information about @GETENV, see Reference for Oracle GoldenGate for Windows and UNIX.

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.

  • If you use USEMIN or USEMAX, and the values are exactly the same, then RESOLVECONFLICT isn't triggered and the incoming row is ignored. If you use USEMINEQ or USEMAXEQ, and the values are exactly the same, then the resolution is triggered.

  • 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.

    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.4 Error Handling

For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.

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.

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'
address='Oracle Pkwy'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 3:00'

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.

Target database image

name='Mary'
phone='111111'
address='Ralston'
salary=200
balance=500
comment='aaa'
last_mod_time='9/1/10 1:00'

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

SQL bind variables:

1)'Mary'
2)'1234567890'
3)'Oracle Pkwy'
4)100
5)100
6)NULL
7)'9/1/10 3:00'

This SQL returns a uniqueness conflict on 'Mary'.

UPDATE applied by Replicat to resolve the conflict

SQL bind variables:

1)'1234567890'
2)'Oracle Pkwy'
3)100
4)100
5)NULL
6)'9/1/10 3:00'
7)'Mary'
8)'9/1/10 3:00'

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.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-2 UPDATEROWEXISTS Conflict with USEMAX Resolution

Image SQL Comments

Before image in trail

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 3:00'

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.

Target database image

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=600
comment='com'
last_mod_time='9/1/10 6:00'

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

SQL bind variables:

1)'222222'
2)'Holly'
3)'9/1/10 5:00'
4)'Mary'
5)'1234567890'
6)'Oracle Pkwy'
7)100
8)100
9)NULL
10)'9/1/10 3:00'

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

SQL bind variables:

1)'Mary'
2)'222222'
3)'Holly'
4)100
5)100
6)NULL
7)'9/1/10 5:00'
8)'Mary'
9)'9/1/10 5:00'

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.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-3 UPDATEROWMISSING Conflict with OVERWRITE Resolution

Image SQL Comments

Before image in trail

name='Jane'
phone='333'
address='Oracle Pkwy'
salary=200
balance=200
comment=NULL
last_mod_time='9/1/10 7:00'

N/A

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

SQL bind variables:

1)'4444'
2)'Holly'
3)'9/1/10 8:00'
4)'Jane'
5)'333'
6)'Oracle Pkwy'
7)200
8)200
9)NULL
10)'9/1/10 7:00'

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

SQL bind variables:

1)'Jane'
2)'4444'
3)'Holly'
4)200
5)200
6)NULL
7)'9/1/10 8:00'

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.

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-4 DELETEROWMSING Conflict with DISCARD Resolution

Image SQL Comments

Before image in trail

name='Jane'
phone='4444'
address='Holly'
salary=200
balance=200
comment=NULL
last_mod_time='9/1/10 8:00'

N/A

After image in trail

None

N/A

Target database image

None (row missing)

N/A

Initial DELETE applied by Replicat that detects the conflict

SQL bind variables:

1)'Jane'
2)'4444'
3)'Holly'
4)200
5)200
6)NULL
7)'9/1/10 8:00'

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.

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.

Table 10-5 DELETEROWEXISTS Conflict with OVERWRITE Resolution

Image SQL Comments

Before image in trail

name='Mary'
phone='222222'
address='Holly'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 5:00'

N/A

After image in trail

None

N/A

Target database image

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=600
comment=com
last_mod_time='9/1/10 7:00'

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

SQL bind variables:

1)'Mary'
2)'222222'
3)'Holly'
4)100
5)100d
6)NULL
7)'9/1/10 5:00'

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).

10.4 CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX

This example resolves the condition where a target row exists on UPDATE but non-key columns are different, and it uses two different resolution types to handle this condition based on the affected column.

10.4.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.4.2 MAP Statement

MAP fin.src, TARGET fin.tgt,
    COMPARECOLS
    (ON UPDATE KEYINCLUDING (address, phone, salary, last_mod_time),
    ON DELETE KEYINCLUDING (address, phone, salary, last_mod_time)),
    RESOLVECONFLICT (
    UPDATEROWEXISTS,
    (delta_res_method, USEDELTA, COLS (salary)),
    (DEFAULT, USEMAX (last_mod_time)));

10.4.3 Description of MAP Statement

For an UPDATEROWEXISTS conflict, where a target row exists on UPDATE but non-key columns are different, use two different resolutions depending on the column:

  • Per the delta_res_method resolution, use the USEDELTA resolution logic for the salary column so that the change in value will be added to the current value of the column.

  • Per DEFAULT, use the USEMAX resolution logic for all other columns in the table (the default column group), using the last_mod_time column as the resolution column. This column is updated with the current time whenever the row is modified; the value of this column in the trail is compared to the value in the target. If the value of last_mod_time in the trail record is greater than the current value of last_mod_time in the target database, the changes to name, phone, address, balance, comment and last_mod_time are applied to the target.

Per COMPARECOLS, use the primary key (name column) plus the address, phone, salary, and last_mod_time columns as the comparison columns for conflict detection for UPDATE and DELETE operations. (The balance and comment columns are not compared.)

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.4.4 Error Handling

For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.

Table 10-6 UPDATEROWEXISTS with USEDELTA and USEMAX

Image SQL Comments

Before image in trail

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 3:00'

last_mod_time='9/1/10 3:00 is the before image of the resolution column for the USEMAX resolution.

salary=100 is the before image for the USEDELTA resolution.

After image in trail

phone='222222'
address='Holly'
salary=200
comment='new'
last_mod_time='9/1/10 5:00'

last_mod_time='9/1/10 5:00 is the after image of the resolution column for USEMAX. Since there is an after image, this will be used to determine the resolution.

Target database image

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=600
balance=600
comment='com'
last_mod_time='9/1/10 4:00'

last_mod_time='9/1/10 4:00 is the current image of the resolution column in the target against which the resolution column value in the trail is compared.

salary=600 is the current image of the target column for the USEDELTA resolution.

Initial UPDATE applied by Replicat that detects the conflict

SQL bind variables:

1)'222222'
2)'Holly'
3)200
4)'new'
5)'9/1/10 5:00'
6)'Mary'
7)'1234567890'
8)'Oracle Pkwy'
9)100
10)'9/1/10 3:00'

This SQL returns a no-data-found error because the values for the salary and last_mod_time are different. (The values for comment and balance are also different, but these columns are not compared.)

UPDATE applied by Replicat to resolve the conflict for salary, using USEDELTA.

SQL bind variables:

1)200
2)100
3)'Mary'

Per USEDELTA, the difference between the after image of salary (200) in the trail and the before image of salary (100) in the trail is added to the current value of salary in the target (600). The result is 700.

600 + (200 - 100) = 700

UPDATE applied by Replicat to resolve the conflict for the default columns, using USEMAX.

SQL bind variables:

1)'222222'
2)'Holly'
3)'new'
4)'9/1/10 5:00'
5)'Mary'
6)'9/1/10 5:00'

Per USEMAX, because the after value of last_mod_time in the trail record is greater than the current value in the database, the row is updated with the after values from the trail record.

Note that the salary column is not set here, because it is resolved with the UPDATE from the USEDELTA resolution.

10.5 CDR Example 3: UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE

This example resolves the conflict where a target row exists on UPDATE but non-key columns are different, and it uses three different resolution types to handle this condition based on the affected column.

10.5.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.5.2 MAP Statement

MAP fin.src, TARGET fin.tgt,
    COMPARECOLS
    (ON UPDATE ALLEXCLUDING (comment)),
    RESOLVECONFLICT (
    UPDATEROWEXISTS,
    (delta_res_method, USEDELTA, COLS (salary, balance)),
    (max_res_method, USEMAX (last_mod_time), COLS (address, last_mod_time)),
    (DEFAULT, IGNORE));

10.5.3 Description of MAP Statement

  • For an UPDATEROWEXISTS conflict, where a target row exists on UPDATE but non-key columns are different, use two different resolutions depending on the column:

    • Per the delta_res_method resolution, use the USEDELTA resolution logic for the salary and balance columns so that the change in each value will be added to the current value of each column.

    • Per the max_res_method resolution, use the USEMAX resolution logic for the address and last_mod_time columns. The last_mod_time column is the resolution column. This column is updated with the current time whenever the row is modified; the value of this column in the trail is compared to the value in the target. If the value of last_mod_time in the trail record is greater than the current value of last_mod_time in the target database, the changes to address and last_mod_time are applied to the target; otherwise, they are ignored in favor of the target values.

    • Per DEFAULT, use the IGNORE resolution logic for the remaining columns (phone and comment) in the table (the default column group). Changes to these columns will always be ignored by Replicat.

  • Per COMPARECOLS, use all columns except the comment column as the comparison columns for conflict detection for UPDATE operations. Comment will not be used in the WHERE clause for updates, but all other columns that have a before image in the trail record will be used.

    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.5.4 Error Handling

For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.

Table 10-7 UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE

Image SQL Comments

Before image in trail

name='Mary'
phone='1234567890'
address='Oracle Pkwy'
salary=100
balance=100
comment=NULL
last_mod_time='9/1/10 3:00

last_mod_time='9/1/10 3:00 is the before image of the resolution column for the USEMAX resolution.

salary=100 and balance=100 are the before images for the USEDELTA resolution.

After image in trail

phone='222222'
address='Holly'
salary=200
comment='new'
last_mod_time='9/1/10 5:00'

last_mod_time='9/1/10 5:00 is the after image of the resolution column for USEMAX. Since there is an after image, this will be used to determine the resolution.

salary=200 is the only after image available for the USEDELTA resolution. For balance, the before image will be used in the calculation.

Target database image

name='Mary'
phone='1234567890'
address='Ralston'
salary=600
balance=600
comment='com'
last_mod_time='9/1/10 4:00'

last_mod_time='9/1/10 4:00 is the current image of the resolution column in the target against which the resolution column value in the trail is compared for USEMAX.

salary=600 and balance=600 are the current images of the target columns for USEDELTA.

Initial UPDATE applied by Replicat that detects the conflict

SQL bind variables:

1)'222222'
2)'Holly'
3)200
4)'new'
5)'9/1/10 5:00'
6)'Mary'
7)'1234567890'
8)'Oracle Pkwy'
9)100
10)100
11)'9/1/10 3:00'

This SQL returns a no-data-found error because the values for the address, salary, balance and last_mod_time columns are different.

UPDATE applied by Replicat to resolve the conflict for salary, using USEDELTA.

SQL bind variables:

1)200
2)100
3)'Mary'

For salary, there is a difference of 100, but there was no change in value for balance, so it is not needed in the update SQL. Per USEDELTA, the difference (delta) between the after (200) image and the before image (100) of salary in the trail is added to the current value of salary in the target (600). The result is 700.

UPDATE applied by Replicat to resolve the conflict for USEMAX.

SQL bind variables:

1)'Holly'
2)'9/1/10 5:00'
3)'Mary'
4)'9/1/10 5:00'

Because the after value of last_mod_time in the trail record is greater than the current value in the database, that column plus the address column are updated with the after values from the trail record.

Note that the salary column is not set here, because it is resolved with the UPDATE from the USEDELTA resolution.

UPDATE applied by Replicat for IGNORE.

SQL bind variables:

1)'222222'
2)'new'
3)'Mary'

IGNORE is specified for the DEFAULT column group (phone and comment), so no resolution SQL is applied.