6 Configuring Oracle GoldenGate for Active-Active Configuration
Topics:
6.1 Overview of an Active-Active Configuration
Oracle GoldenGate supports an active-active, bidirectional configuration, where there are two systems with identical sets of data that can be changed by application users on either system. Oracle GoldenGate replicates transactional data changes from each database to the other to keep both sets of data current.
In a bidirectional configuration, there is a complete set of active Oracle GoldenGate processes on each system. Data captured by an Extract process on one system is propagated to the other system, where it is applied by a local Replicat process.
This configuration supports load sharing. It can be used for disaster tolerance if the business applications are identical on any two peers.
Oracle GoldenGate supports active-active configurations for:
-
DB2 on z/OS, LUW, and IBM i
-
MySQL
-
Oracle
-
PostgreSQL
-
SQL Server
6.2 Considerations for an Active-Active Configuration
The following considerations apply in an active-active configuration. In addition, review the Oracle GoldenGate installation and configuration document for your type of database to see if there are any other limitations or requirements to support a bi-directional configuration.
6.2.1 Application Design
When using Active-Active replication, the time zones must be the same on both systems so that timestamp-based conflict resolution and detection can operate.
Active-active replication is not recommended for use with commercially available packaged business applications, unless the application is designed to support it. Among the obstacles that these applications present are:
-
Packaged applications might contain objects and data types that are not supported by Oracle GoldenGate.
-
They might perform automatic DML operations that you cannot control, but which will be replicated by Oracle GoldenGate and cause conflicts when applied by Replicat.
-
You probably cannot control the data structures to make modifications that are required for active-active replication.
Parent topic: Considerations for an Active-Active Configuration
6.2.2 Keys
For accurate detection of conflicts, all records must have a unique, not-null identifier. If possible, create a primary key. If that is not possible, use a unique key or create a substitute key with a KEYCOLS
option of the MAP
and TABLE
parameters. In the absence of a unique identifier, Oracle GoldenGate uses all of the columns that are valid in a WHERE
clause, but this will degrade performance if the table contains numerous columns.
To maintain data integrity and prevent errors, the following must be true of the key that you use for any given table:
-
contain the same columns in all of the databases where that table resides.
-
contain the same values in each set of corresponding rows across the databases.
Parent topic: Considerations for an Active-Active Configuration
6.2.3 Database-Generated Values
Do not replicate database-generated sequential values, such as Oracle sequences, in a bi-directional configuration. The range of values must be different on each system, with no chance of overlap. For example, in a two-database environment, you can have one server generate even values, and the other odd. For an n-server environment, start each key at a different value and increment the values by the number of servers in the environment. This method may not be available to all types of applications or databases. If the application permits, you can add a location identifier to the value to enforce uniqueness.
Parent topic: Considerations for an Active-Active Configuration
6.2.4 Database Configuration
One of the databases must be designated as the trusted source. This is the primary database and its host system from which the other database is derived in the initial synchronization phase and in any subsequent resynchronizations that become necessary. Maintain frequent backups of the trusted source data.
Parent topic: Considerations for an Active-Active Configuration
6.3 Preventing Data Looping
In a bidirectional configuration, SQL changes that are replicated from one system to another must be prevented from being replicated back to the first system. Otherwise, it moves back and forth in an endless loop, as in this example:
-
A user application updates a row on system A.
-
Extract extracts the row on system A and sends it to system B.
-
Replicat updates the row on system B.
-
Extract extracts the row on system B and sends it back to system A.
-
The row is applied on system A (for the second time).
-
This loop continues endlessly.
To prevent data loopback, you may need to provide instructions that:
-
prevent the capture of SQL operations that are generated by Replicat, but enable the capture of SQL operations that are generated by business applications if they contain objects that are specified in the Extract parameter file.
-
identify local Replicat transactions, in order for the Extract process to ignore them.
6.3.1 Identifying Replicat Transactions
To configure Extract to identify Replicat transactions, follow the instructions for the database from which Extract will capture data.
Topics:
6.3.1.1 DB2 z/OS, DB2 LUW, and DB2 for i
Identify the Replicat user name by using the following parameter statement in the Extract parameter file.
TRANLOGOPTIONS EXCLUDEUSER user
This parameter statement marks all DDL and DML transactions that are generated by this user as Replicat transactions. The user name is included in the transaction record that is read by Extract.
Parent topic: Identifying Replicat Transactions
6.3.1.2 MySQL
Identify the name of the Replicat checkpoint table by using the following parameter statement in the Extract parameter file.
TRANLOGOPTIONS FILTERTABLE table_name
Replicat writes a checkpoint to the checkpoint table at the end of each of its transactions as part of its checkpoint procedure. (This is the table that is created with the ADD CHECKPOINTTABLE
command.) Because every Replicat transaction includes a write to this table, it can be used to identify Replicat transactions in a bidirectional configuration. FILTERTABLE
identifies the name of the checkpoint table, so that Extract ignores transactions that contain any operations on it.
Parent topic: Identifying Replicat Transactions
6.3.1.3 SQL Server
(CDC Extract) Identify the name of the Replicat checkpoint table by using the following parameter statement in the Extract parameter file and ensure that the Replicat checkpoint table has been enabled for supplemental logging with the ADD TRANDATA
command.
TRANLOGOPTIONS FILTERTABLE table_name
Replicat writes a checkpoint to the checkpoint table at the end of each of its transactions as part of its checkpoint procedure. (This is the table that is created with the ADD CHECKPOINTTABLE
command). Because every Replicat transaction includes a write to this table, it can be used to identify Replicat transactions in a bi-directional configuration. FILTERTABLE
identifies the name of the checkpoint table, so that Extract ignores transactions that contain any operations on it.
(Classic Extract) By default, Extract ignores the Replicat's transactions, however, if you modify the Replicat's transaction name with the DBOPTIONS TRANSNAME
parameter, then you must exclude those transactions by using the following parameter statement in the Extract parameter file.
TRANLOGOPTIONS EXCLUDETRANS transaction_name
This parameter statement is only required if the Replicat transaction name is set to something other than the default of ggs_repl
.
Parent topic: Identifying Replicat Transactions
6.3.1.4 Oracle
There are multiple ways to identify Replicat transaction in an Oracle environment. When Replicat is in classic or integrated mode, you use the following parameters:
-
Replicats set a tag of 00 by default. Use
DBOPTIONS
with theSETTAG
option in the Replicat parameter file to change the tag that Replicat sets. Replicat tags the transactions being applied with the specified value, which identifies those transactions in the redo stream. Valid values are a single TAG value consisting of hexadecimal digits.
-
Use the
TRANLOGOPTIONS
parameter with theEXCLUDETAG
option in the Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with theSETTAG
value.The following shows how
SETTAG
can be set in the Replicat parameter file:DBOPTIONS SETTAG 0935
The following shows how
EXCLUDETAG
can be set in the Extract parameter file:TRANLOGOPTIONS EXCLUDETAG 0935
If you are excluding multiple tags, each must have a separate
TRANLOGOPTIONS EXCLUDETAG
statement specified.
You can also use the transaction name or userid of the Replicat user to identify Replicat transactions. You can choose which of these to ignore when you configure Extract. See Preventing the Capture of Replicat Transactions (Oracle).
For more information, see Reference for Oracle GoldenGate.
Parent topic: Identifying Replicat Transactions
6.3.2 Preventing the Capture of Replicat Operations
Depending on which database you are using, you may or may not need to provide explicit instructions to prevent the capture of Replicat operations.
- Preventing the Capture of Replicat Transactions (Oracle)
- Preventing Capture of Replicat Transactions (Other Databases)
Parent topic: Preventing Data Looping
6.3.2.1 Preventing the Capture of Replicat Transactions (Oracle)
To prevent the capture of SQL that is applied by Replicat to an Oracle database, there are different options depending on the Extract capture mode:
-
When Extract is in classic or integrated capture mode, use the
TRANLOGOPTIONS
parameter with theEXCLUDETAG
tag
option. This parameter directs the Extract process to ignore transactions that are tagged with the specified redo tag. See Identifying Replicat Transactions to set the tag value. This is the recommended approach for Oracle. -
When Extract is in classic capture mode, use the Extract
TRANLOGOPTIONS
parameter with theEXCLUDEUSER
orEXCLUDEUSERID
option to exclude the user name or ID that is used by Replicat to apply the DDL and DML transactions. MultipleEXCLUDEUSER
statements can be used. The specified user is subject to the rules of theGETREPLICATES
orIGNOREREPLICATES
parameter. See Preventing Capture of Replicat Transactions (Other Databases) for more information.
Parent topic: Preventing the Capture of Replicat Operations
6.3.2.2 Preventing Capture of Replicat Transactions (Other Databases)
To prevent the capture of SQL that is applied by Replicat to other database types (including Oracle, if Extract operates in classic capture mode), use the following parameters:
-
GETAPPLOPS | IGNOREAPPLOPS
: Controls whether or not data operations (DML) produced by business applications except Replicat are included in the content that Extract writes to a specific trail or file. -
GETREPLICATES | IGNOREREPLICATES
: Controls whether or not DML operations produced by Replicat are included in the content that Extract writes to a specific trail or file.
Parent topic: Preventing the Capture of Replicat Operations
6.3.3 Replicating DDL in a Bidirectional Configuration
There are additional consideration when replicating DDL bidirectionally, supported in Oracle and MySQL databases. For information DDL support for Oracle, see Configuring DDL Support Using Oracle GoldenGate for Oracle Database. For information on DDL support in MySQL, see Using DDL Replication in Using Oracle GoldenGate for Heterogeneous Databases.
Parent topic: Preventing Data Looping
6.4 Managing Conflicts
Uniform conflict-resolution procedures must be in place on all systems in an active-active configuration. Conflicts should be identified immediately and handled with as much automation as possible; however, different business applications will present their own unique set of requirements in this area.
Because Oracle GoldenGate is an asynchronous solution, conflicts can occur when modifications are made to identical sets of data on separate systems at (or almost at) the same time. Conflicts occur when the timing of simultaneous changes results in one of these out-of-sync conditions:
-
A uniqueness conflict occurs when Replicat applies an insert or update operation that violates a uniqueness integrity constraint, such as a
PRIMARY KEY
orUNIQUE
constraint. An example of this conflict type is when two transactions originate from two different databases, and each one inserts a row into a table with the same primary key value. -
An update conflict occurs when Replicat applies an update that conflicts with another update to the same row. Update conflicts happen when two transactions that originate from different databases update the same row at nearly the same time. Replicat detects an update conflict when there is a difference between the old values (the before values) that are stored in the trail record and the current values of the same row in the target database.
-
A delete conflict occurs when two transactions originate at different databases, and one deletes a row while the other updates or deletes the same row. In this case, the row does not exist to be either updated or deleted. Replicat cannot find the row because the primary key does not exist.
For example, UserA on DatabaseA updates a row, and UserB on DatabaseB updates the same row. If UserB's transaction occurs before UserA's transaction is synchronized to DatabaseB, there will be a conflict on the replicated transaction.
A more complicated example involves three databases and illustrates a more complex ordering conflict. Assume three databases A, B, and C. Suppose a user inserts a row at database A, which is then replicated to database B. Another user then modifies the row at database B, and the row modification is replicated to database C. If the row modification from B arrives at database C before the row insert from database A, C will detect a conflict.
Where possible, try to minimize or eliminate any chance of conflict. Some ways to do so are:
-
Configure the applications to restrict which columns can be modified in each database. For example, you could limit access based on geographical area, such as by allowing different sales regions to modify only the records of their own customers. As another example, you could allow a customer service application on one database to modify only the
NAME
andADDRESS
columns of a customer table, while allowing a financial application on another database to modify only theBALANCE
column. In each of those cases, there cannot be a conflict caused by concurrent updates to the same record. -
Keep synchronization latency low. If UserA on DatabaseA and UserB on DatabaseB both update the same rows at about the same time, and UserA's transaction gets replicated to the target row before UserB's transaction is completed, conflict is avoided. See Tuning the Performance of Oracle GoldenGate for suggestions on improving the performance of the Oracle GoldenGate processes.
To avoid conflicts, replication latency must be kept as low as possible. When conflicts are unavoidable, they must be identified immediately and resolved with as much automation as possible, either through the Oracle GoldenGate Conflict Detection and Resolution (CDR) feature, or through methods developed on your own. Custom methods can be integrated into Oracle GoldenGate processing through the SQLEXEC
and user exit functionality. See Configuring Conflict Detection and Resolution for more information about using Oracle GoldenGate to handle conflicts.
For Oracle database, the automatic Conflict Detection Resolution (CDR) feature exists. To know more, see Oracle GoldenGate Automatic Conflict Detection and Resolution in the Oracle Database XStream Guide.
6.5 Additional Information
The following documentation provides additional information of relevance to configuring Oracle GoldenGate.
-
For additional system requirements, process configuration, and database setup requirements, see the Oracle GoldenGate installation and configuration document for your database type.
-
For detailed instructions on configuring Oracle GoldenGate change capture and delivery groups, see Configuring Online Change Synchronization.
-
For additional tuning options for Oracle GoldenGate, see Tuning the Performance of Oracle GoldenGate.
-
For complete syntax and descriptions of the Oracle GoldenGate commands and parameters, see Reference for Oracle GoldenGate.
6.6 Creating an Active-Active Configuration
See the Quickstart Bidirectional Replication for steps to configure an active-active bidirectional replication.
6.6.1 Prerequisites on Both Systems
Perform these prerequisite tasks on both systems:
-
Create a Replicat checkpoint table (unless using Oracle integrated Replicat). See Creating a Checkpoint Table for instructions.
-
Configure the Manager process.
Parent topic: Creating an Active-Active Configuration
6.6.2 Configuration from Primary System to Secondary System
These steps add the processes necessary to send data from the primary system to the secondary database.
To Configure the Primary Extract Group
Perform these steps on the primary system.
-
Use the
ADD EXTRACT
command to create a primary Extract group. For documentation purposes, this group is calledext_1
.ADD EXTRACT
ext_1
, {TRANLOG | INTEGRATED TRANLOG}, BEGINtime
-
Use the
ADD EXTTRAIL
command to add a local trail. For documentation purposes, this trail is calledlocal_trail_1
.ADD EXTTRAIL
local_trail_1
, EXTRACText_1
For
EXTRACT
, specify theext_1
group to write to this trail -
Use the
EDIT PARAMS
command to create a parameter file for theext_1
group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.-- Identify the Extract group: EXTRACT
ext_1
-- Specify database login information as needed for the database: [SOURCEDBdsn_1
][, USERIDALIASalias
] -- Specify the local trail that this Extract writes to -- and the encryption algorithm: ENCRYPTTRAILalgorithm
EXTTRAILlocal_trail_1
-- Exclude Replicat transactions. Uncomment ONE of the following: -- DB2 z/OS, DB2 LUW, DB2 IBM i, and Oracle (classic capture): -- TRANLOGOPTIONS EXCLUDEUSERReplicat_user
-- Oracle (classic capture) alternative to EXCLUDEUSER: -- EXCLUDEUSERIDOracle_uid
-- Oracle integrated capture: -- EXCLUDETAGtag
-- SQL Server: -- TRANLOGOPTIONS FILTERTABLE schema.checkpointtable" -- -- Teradata: -- SQLEXEC 'SET SESSION OVERRIDE REPLICATION ON;' -- SQLEXEC 'COMMIT;' -- Capture before images for conflict resolution: GETBEFORECOLS (ONoperation
{ALL | KEY | KEYINCLUDING (col_list
) | ALLEXCLUDING (col_list
)}) -- Log all scheduling columns for CDR and if using integrated Replicat LOGALLSUPCOLS -- Specify tables to be captured and (optional) columns to fetch: TABLE [container
.|catalog
.]owner
.* [, FETCHCOLScols
| FETCHCOLSEXCEPTcols
];
To Configure the Data Pump
Perform these steps on the primary system.
-
Use the
ADD EXTRACT
command to create a data pump group. For documentation purposes, this group is calledpump_1
.ADD EXTRACT
pump_1
, EXTTRAILSOURCElocal_trail_1
, BEGINtime
For
EXTTRAILSOURCE
, specifylocal_trail_1
as the data source. -
Use the
ADD RMTTRAIL
command to add a remote trail that will be created on the secondary system. For documentation purposes, this trail is calledremote_trail_1
.ADD RMTTRAIL
remote_trail_1
, EXTRACTpump_1
For
EXTRACT
, specify thepump_1
data pump to write to this trail.See Reference for Oracle GoldenGate for additional
ADD RMTTRAIL
options.Use the
EDIT PARAMS
command to create a parameter file for thepump_1
group. Include the following parameters plus any others that apply to your database environment.-- Identify the data pump group: EXTRACT
pump_1
-- Specify database login information as needed for the database: [SOURCEDBdsn_1
][, USERIDALIASalias
] -- Decrypt the data only if the data pump must process it. -- DECRYPTTRAIL -- Specify the name or IP address of the secondary system -- and optional encryption of data over TCP/IP: RMTHOSTOPTIONSsystem_2
, MGRPORTport_number
, ENCRYPTencryption_options
-- Specify remote trail and encryption algorithm on secondary system: ENCRYPTTRAILalgorithm
RMTTRAILremote_trail_1
-- Specify tables to be captured: TABLE [container
.|catalog
.]owner
.*;
To Configure the Replicat Group
Perform these steps on the secondary system.
-
Create the Replicat checkpoint table after using the
DBLOGIN
command to connect to the database. SeeADD CHECKPOINTTABLE
in Command Line Interface Reference for Oracle GoldenGate. -
Run the command:
ADD CHECKPOINTTABLE schema.checkpointtable
-
Use the
ADD REPLICAT
command to create a Replicat group. For documentation purposes, this group is calledrep_1
.ADD REPLICAT
rep_1
[, PARALLEL | INTEGRATED | COORDINATED [MAXTHREADSnumber
]] , EXTTRAILremote_trail_1
, CHECKPOINTTABLE schema.checkpointtableFor
EXTTRAIL
, specifyremote_trail_1
as the trail that this Replicat reads. -
Use the
EDIT PARAMS
command to create a parameter file for therep_1
group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.-- Identify the Replicat group: REPLICAT
rep_1
-- Specify database login information as needed for the database: [TARGETDBdsn_2
][, USERIDALIASalias
] -- Specify error handling rules: REPERROR (error
,response
) -- Set redo tag for Oracle only replicat via settag -- Default is 00. SETTAGtag_value
-- Valid for Oracle only. Specify tables for delivery, threads if coordinated Replicat -- and conflict-resolution: MAP [container
.|catalog
.]owner
.*, TARGETowner
.*, COMPARECOLS (ONoperation
{ALL | KEY | KEYINCLUDING (col_list
) | ALLEXCLUDING (col_list
)}), RESOLVECONFLICT (conflict type
(resolution_name
,resolution_type
COLS (col
[,...])) [, THREAD (thread_ID
)] [, THREADRANGE (thread_range
[,column_list
])] ; -- Specify mapping of exceptions to exceptions table: MAP [container
.|catalog
.]owner
.*, TARGETowner
.exceptions
, EXCEPTIONSONLY;
Parent topic: Creating an Active-Active Configuration
6.6.3 Configuration from Secondary System to Primary System
These steps add the processes necessary to send data from the secondary system to the primary database.
To Configure the Primary Extract Group
Perform these steps on the secondary system.
Note:
This is a reverse image of the configuration that you just created.
-
Use the
ADD EXTRACT
command to create a primary Extract group. For documentation purposes, this group is calledext_2
.ADD EXTRACT
ext_2
, {TRANLOG | INTEGRATED TRANLOG}, BEGINtime
-
Use the
ADD EXTTRAIL
command to add a local trail. For documentation purposes, this trail is calledlocal_trail_2
.ADD EXTTRAIL
local_trail_2
, EXTRACText_2
For
EXTRACT
, specify theext_2
group to write to this trail. -
Use the
EDIT PARAMS
command to create a parameter file for theext_2
group. Include the following parameters plus any others that apply to your database environment. For possible additional required parameters, see the Oracle GoldenGate installation and setup guide for your database.-- Identify the Extract group: EXTRACT
ext_2
-- Specify database login information as needed for the database: [SOURCEDBdsn_2
][, USERIDALIASalias
] -- Specify the local trail that this Extract writes to -- and the encryption algorithm: ENCRYPTTRAILalgorithm
EXTTRAILlocal_trail_2
-- Exclude Replicat transactions. Uncomment ONE of the following: -- DB2 z/OS, DB2 LUW, DB2 IBM i, and Oracle: -- TRANLOGOPTIONS EXCLUDEUSERReplicat_user
-- Oracle alternative to EXCLUDEUSER: -- EXCLUDEUSERIDOracle_uid
-- Oracle integrated capture: -- EXCLUDETAGtag
-- SQL Server: -- TRANLOGOPTIONS EXCLUDETRANS FILTERTABLE schema.checkpointtable -- Oracle: -- TRACETABLEtrace_table_name
-- Log all scheduling columns for CDR and if using integrated Replicat LOGALLSUPCOLS -- Capture before images for conflict resolution: GETBEFORECOLS (ONoperation
{ALL | KEY | KEYINCLUDING (col_list
) | ALLEXCLUDING (col_list
)}) -- Specify tables to be captured and (optional) columns to fetch: TABLE [container
.|catalog
.]owner
.* [, FETCHCOLScols
| FETCHCOLSEXCEPTcols
];Note:
To replicate Oracle DBFS data, specify the internally generated local read-write DBFS tables in the
TABLE
statement on each node. For more information on identifying these tables and configuring DBFS for propagation by Oracle GoldenGate, see Applying the Required Patch in Using Oracle GoldenGate for Oracle Database.
To Configure the Data Pump
Perform these steps on the secondary system.
-
Use the
ADD EXTRACT
command to create a data pump group. For documentation purposes, this group is calledpump_2
.ADD EXTRACT
pump_2
, EXTTRAILSOURCElocal_trail_2
, BEGINtime
For
EXTTRAILSOURCE
, specifylocal_trail_2
as the data source. -
Use the
ADD RMTTRAIL
command to add a remote trail that will be created on the primary system. For documentation purposes, this trail is calledremote_trail_2
.ADD RMTTRAIL
remote_trail_2
, EXTRACTpump_2
For
EXTRACT
, specify thepump_2
data pump to write to this trail. -
Use the
EDIT PARAMS
command to create a parameter file for thepump_2
group. Include the following parameters plus any others that apply to your database environment.-- Identify the data pump group: EXTRACT
pump_2
-- Specify database login information as needed for the database: [SOURCEDBdsn_2
][, USERIDALIASalias
] -- Decrypt the data only if the data pump must process it. -- DECRYPTTRAIL -- Specify the name or IP address of the primary system -- and optional encryption of data over TCP/IP: RMTHOSTOPTIONSsystem_1
, MGRPORTport_number
, ENCRYPTencryption_options
-- Specify the remote trail and encryption algorithm on the primary system: ENCRYPTTRAILalgorithm
RMTTRAILremote_trail_2
-- Specify tables to be captured: TABLE [container
.|catalog
.]owner
.*;Note:
To replicate Oracle DBFS data, specify the internally generated local read-write DBFS tables in the
TABLE
statement on each node. For more information on identifying these tables and configuring DBFS for propagation by Oracle GoldenGate, see Configuring the DBFS File System in Using Oracle GoldenGate for Oracle Database.
To Configure the Replicat Group
Perform these steps on the primary system.
-
Create the Replicat checkpoint table after using the
DBLOGIN
command to connect to the database. SeeADD CHECKPOINTTABLE
in Command Line Interface Reference for Oracle GoldenGate. -
Run the command:
ADD CHECKPOINTTABLE schema.checkpointtable
-
Use the
ADD REPLICAT
command to create a Replicat group. For documentation purposes, this group is calledrep_2
.ADD REPLICAT
rep_2
[, PARALLEL | INTEGRATED | COORDINATED [MAXTHREADSnumber
]] , EXTTRAILremote_trail_1
, CHECKPOINTTABLE schema.checkpointtableFor
EXTTRAIL
, specifyremote_trail_1
as the trail that this Replicat reads. -
Use the
EDIT PARAMS
command to create a parameter file for therep_2
group. Include the following parameters plus any others that apply to your database environment.-- Identify the Replicat group: REPLICAT
rep_2
-- Specify database login information as needed for the database: [TARGETDBdsn_1
][, USERIDALIASalias
] -- Specify error handling rules: REPERROR (error
,response
) -- Specify tables for delivery, threads if coordinated Replicat -- and conflict-resolution: MAP [container
.|catalog
.]owner
.*, TARGETowner
.*, COMPARECOLS (ONoperation
{ALL | KEY | KEYINCLUDING (col_list
) | ALLEXCLUDING (col_list
)}), RESOLVECONFLICT (conflict type
(resolution_name
,resolution_type
COLS (col
[,...])) [, THREAD (thread_ID
)] [, THREADRANGE (thread_range
[,column_list
])] ; -- Specify mapping of exceptions to exceptions table: MAP [container
.|catalog
.]owner
.*, TARGETowner
.exceptions
, EXCEPTIONSONLY;Note:
To replicate Oracle DBFS data, specify the internally generated local read-write DBFS tables in the
TABLE
statement on each node.
Parent topic: Creating an Active-Active Configuration
6.7 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.
Topics:
- Overview of the Oracle GoldenGate CDR Feature
- Configuring the Oracle GoldenGate Parameter Files for Error Handling
- Configuring the Oracle GoldenGate Parameter Files for Conflict Resolution
- Making the Required Column Values Available to Extract
- Configuring Oracle GoldenGate CDR
- CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD
- CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX
- CDR Example 3: UPDATEROWEXISTS with USEDELTA, USEMAX, and IGNORE
6.7.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 scalar data types such as:
-
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.
Parent topic: Configuring Conflict Detection and Resolution
6.7.2 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.
- Tools for Mapping Extra Data to the Exceptions Table
- Sample Exceptions Mapping with Source and Target Columns Only
- Sample Exceptions Mapping with Additional Columns in the Exceptions Table
Parent topic: Configuring Conflict Detection and Resolution
6.7.2.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 theCOLMAP
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 theCOLMAP
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.
6.7.2.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 ;
6.7.2.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.
6.7.3 Configuring the Oracle GoldenGate Parameter Files for Conflict Resolution
The following parameters are required to support conflict detection and resolution.
- Use the
GETBEFORECOLS
option of the ExtractTABLE
parameter to specify columns for which you want Extract to capture the before image of an update or delete operation. For DB2 databases, use theGETUPDATEBEFORES
parameter instead ofGETBEFORECOLS
, which is not supported for DB2. - Use the
COMPARECOLS
option of theMAP
parameter in the Replicat parameter file to specify columns that are to be used with before values in the ReplicatWHERE
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 theWHERE
clause; this may not be enough for conflict detection). - Use the
RESOLVECONFLICT
option of theMAP
parameter to specify conflict resolution routines for different operations and conflict types. You can useRESOLVECONFLICT
multiple times in aMAP
statement to specify different resolutions for different conflict types. However, you cannot useRESOLVECONFLICT
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. See the examples starting on CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD, for more information on these parameters.
Parent topic: Configuring Conflict Detection and Resolution
6.7.4 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 forUPDATE
operations and the before image of all supplementally logged columns for bothUPDATE
andDELETE
operations.
For detailed information about these parameters and commands, see the Reference for Oracle GoldenGate. 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.
Parent topic: Configuring Conflict Detection and Resolution
6.7.5 Configuring Oracle GoldenGate CDR
Here are the steps to configure the source database, target database, and Oracle GoldenGate for conflict detection and resolution.
Topics:
6.7.5.1 Viewing CDR Statistics
The CDR feature provides the following methods for viewing the results of conflict resolution.
6.7.5.1.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
Parent topic: Viewing CDR Statistics
6.7.5.1.2 GGSCI
You can view CDR statistics from GGSCI by using the STATS REPLICAT
command with the REPORTCDR
option:
STATS REPLICAT group
, REPORTCDR
Parent topic: Viewing CDR Statistics
6.7.5.1.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.
Parent topic: Viewing CDR Statistics
6.7.6 CDR Example 1: All Conflict Types with USEMAX, OVERWRITE, DISCARD
This example resolves all conflict types by using the USEMAX
, OVERWRITE
, and DISCARD
resolutions.
- Table Used in this Example
- MAP Statement with Conflict Resolution Specifications
- Description of MAP Statement
- Error Handling
- INSERTROWEXISTS with the USEMAX Resolution
- UPDATEROWEXISTS with the USEMAX Resolution
- UPDATEROWMISSING with OVERWRITE Resolution
- DELETEROWMISSING with DISCARD Resolution
- DELETEROWEXISTS with OVERWRITE Resolution
Parent topic: Configuring Conflict Detection and Resolution
6.7.6.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);
6.7.6.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)), );
6.7.6.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 ReplicatWHERE
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 theUSEMAX
resolution: If the row exists during an insert, use thelast_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 theUSEMAX
resolution: If the row exists during an update, use thelast_mod_time
column as the resolution column: If the value in the trail is greater, apply the update. -
If you use
USEMIN
orUSEMAX
, and the values are exactly the same, thenRESOLVECONFLICT
isn't triggered and the incoming row is ignored. If you useUSEMINEQ
orUSEMAXEQ
, and the values are exactly the same, then the resolution is triggered. -
For a
DELETEROWEXISTS
conflict, use theOVERWRITE
resolution: If the row exists during a delete operation, apply the delete. -
For an
UPDATEROWMISSING
conflict, use theOVERWRITE
resolution: If the row does not exist during an update, change the update to an insert and apply it. -
For a
DELETROWMISSING
conflict use theDISCARD
resolution: If the row does not exist during a delete operation, discard the trail record.Note:
As an alternative to
USEMAX
, you can use theUSEMAXEQ
resolution to apply a>=
condition. For more information, see Reference for Oracle GoldenGate.
6.7.6.4 Error Handling
For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.
6.7.6.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 6-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' |
|
Target database image |
name='Mary' phone='111111' address='Ralston' salary=200 balance=500 comment='aaa' last_mod_time='9/1/10 1:00' |
|
Initial |
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'. |
|
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 |
6.7.6.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 6-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' |
|
After image in trail |
phone='222222' address='Holly' last_mod_time='9/1/10 5:00' |
|
Target database image |
name='Mary' phone='1234567890' address='Oracle Pkwy' salary=100 balance=600 comment='com' last_mod_time='9/1/10 6:00' |
|
Initial |
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 All columns are used in the |
|
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 |
6.7.6.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 6-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 |
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 |
|
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 |
6.7.6.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 6-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 |
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 |
SQL applied by Replicat to resolve the conflict |
None |
Because |
6.7.6.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 6-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 |
Initial |
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 A no-data-found error occurs because of the difference between the before and current values. |
|
SQL bind variables: 1)'Mary' |
Because |
6.7.7 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.
Parent topic: Configuring Conflict Detection and Resolution
6.7.7.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);
Parent topic: CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX
6.7.7.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)));
Parent topic: CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX
6.7.7.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 theUSEDELTA
resolution logic for thesalary
column so that the change in value will be added to the current value of the column. -
Per
DEFAULT
, use theUSEMAX
resolution logic for all other columns in the table (the default column group), using thelast_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 oflast_mod_time
in the trail record is greater than the current value oflast_mod_time
in the target database, the changes toname
,phone
,address
,balance
,comment
andlast_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.
Parent topic: CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX
6.7.7.4 Error Handling
For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.
Table 6-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' |
|
After image in trail |
phone='222222' address='Holly' salary=200 comment='new' last_mod_time='9/1/10 5:00' |
|
Target database image |
name='Mary' phone='1234567890' address='Oracle Pkwy' salary=600 balance=600 comment='com' last_mod_time='9/1/10 4:00' |
|
Initial |
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 |
|
SQL bind variables: 1)200 2)100 3)'Mary' |
Per 600 + (200 - 100) = 700 |
|
SQL bind variables: 1)'222222' 2)'Holly' 3)'new' 4)'9/1/10 5:00' 5)'Mary' 6)'9/1/10 5:00' |
Per Note that the |
Parent topic: CDR Example 2: UPDATEROWEXISTS with USEDELTA and USEMAX
6.7.8 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.
Parent topic: Configuring Conflict Detection and Resolution
6.7.8.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);
6.7.8.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));
6.7.8.3 Description of MAP Statement
-
For an
UPDATEROWEXISTS
conflict, where a target row exists onUPDATE
but non-key columns are different, use two different resolutions depending on the column:-
Per the
delta_res_method
resolution, use theUSEDELTA
resolution logic for thesalary
andbalance
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 theUSEMAX
resolution logic for theaddress
andlast_mod_time
columns. Thelast_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 oflast_mod_time
in the trail record is greater than the current value oflast_mod_time
in the target database, the changes toaddress
andlast_mod_time
are applied to the target; otherwise, they are ignored in favor of the target values. -
Per
DEFAULT
, use theIGNORE
resolution logic for the remaining columns (phone
andcomment
) in the table (the default column group). Changes to these columns will always be ignored by Replicat.
-
-
Per
COMPARECOLS
, use all columns except thecomment
column as the comparison columns for conflict detection forUPDATE
operations. Comment will not be used in theWHERE
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 theUSEMAXEQ
resolution to apply a>=
condition. For more information, see Reference for Oracle GoldenGate.
6.7.8.4 Error Handling
For an example of error handling to an exceptions table, see Configuring the Oracle GoldenGate Parameter Files for Error Handling.
Table 6-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 |
|
After image in trail |
phone='222222' address='Holly' salary=200 comment='new' last_mod_time='9/1/10 5:00' |
|
Target database image |
name='Mary' phone='1234567890' address='Ralston' salary=600 balance=600 comment='com' last_mod_time='9/1/10 4:00' |
|
Initial |
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 |
|
SQL bind variables: 1)200 2)100 3)'Mary' |
For |
|
SQL bind variables: 1)'Holly' 2)'9/1/10 5:00' 3)'Mary' 4)'9/1/10 5:00' |
Because the after value of Note that the |
|
SQL bind variables: 1)'222222' 2)'new' 3)'Mary' |
|