Versions Compared

Old Version 1

changes.mady.by.user Copy Space for Confluence

Saved on

compared with

New Version Current

changes.mady.by.user Jakub Sejba

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The response to a conflict can be configured before the conflict occurs. This is done with conflict handlers. The configuration can be specified for each replicated table and for each operation type separately, and for the special case ("data" divergence) when zero or more than one row are affected. The Dbvisit Replicate command to set conflict handlers is SET_CONFLICT_HANDLERS.

The options for conflict handlers are as follows:

Operations"NO_DATA" and "TOO_MANY" handlers"Error" handler
Updatediscard | force | retry | overwrite | pause | abort | newer | older | plsql | sql | errordiscard | force | retry | overwrite | pause | abort | newer | older | plsql | sql | error
Deletediscard | force | retry | overwrite | pause | abort | plsql | sql | errordiscard | force | retry | overwrite | pause | abort | plsql | sql | error
Insertn/adiscard | force | retry | pause | abort | plsql |
sql |
error
Transactionn/adiscard | retry |
overwrite |
pause | abort
| plsql
|
sql |
error

The transaction handler is used for errors in DDL and during commit/rollback.

...

  • log: the conflict is logged, and an error PLOG is created with the offending statement
  • nolog: the conflict is not logged
  • fast_nolog: the conflict is not logged into the database and it may be omitted from conflict counters altogether
  • log_transaction: the conflict is logged, and an error PLOG is created with the offending transaction
  • default: same as log

...

No Format
dbvrep> help set_conflict_handlers
SET_CONFLICT_HANDLERS: Set apply conflict handlers.
SET_CONFLICT_HANDLERS FOR TABLE owner.name FOR operation ON type TO handler [logging] [additional info]
SET_CONFLICT_HANDLERS FOR DEFAULT FOR operation ON type TO handler [logging] [additional info] owner.name is name of the table on SOURCE database, use
DEFAULT to set it for future added/created tables
operation is one of UPDATE, DELETE, INSERT, TRANSACTION
type is one of ERROR, TOO_MANY, NO_DATA (TOO_MANY and NO_DATA available for UPDATE or DELETE only)
handler is one of RETRY, PAUSE, ABORT, DISCARD, FORCE, OVERWRITE, PLSQL, SQL, NEWER, OLDER (NEWER and OLDER available for DATA type only)
logging is one of LOG, NOLOG, FAST_NOLOG, LOG_TRANSACTION (FAST_NOLOG may optimize processing and not update conflict counters)
Additional info is 'BY COLUMNS comma-delimited-column-list 'for NEWER/OLDER, 'full-procedure-name' for PLSQL and 'regular expression' for SQL.

...



Example setting a conflict handler on a table:

 
 No Format
bgColorCCC
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE SCOTT.AVI_OBJECTS FOR UPDATE ON NO_DATA TO OVERWRITE
 Note
Source tablename is used while setting conflict handlers.

To show what the conflict handler has been set to:
 No Format
bgColorCCC
dbvrep> show_conflict_handlers for table scott.avi_objects 
The table called SCOTT.AVI_OBJECTS on source is handled on apply as follows:
UPDATE (error): handler: RETRY logging: LOG
UPDATE (no_data): handler: OVERWRITE logging: LOG
UPDATE (too_many): handler: OVERWRITE logging: LOG
DELETE (error): handler: RETRY logging: LOG
DELETE (no_data): handler: RETRY logging: LOG
DELETE (too_many): handler: RETRY logging: LOG
INSERT (error): handler: RETRY logging: LOG
TRANSACTION (error): handler: RETRY logging: LOG
 

Example
The following example shows an example of a duplicate row conflict handler.
Create a simple table with two duplicate rows:
 No Format
bgColorCCC
create table scott.x (i number);
insert into x values (1);
insert into x values (1);
commit;
 

On the source database, the user issues the following update:
 No Format
bgColorCCC
update x set i=2 where i=1; 
commit;
This update will affect 2 rows in the table.

The MINE process will see this as two row changes, so the APPLY process will execute: 
 No Format
bgColorCCC
(#1) update x set i=2 where i=1;
(#2) update x set i=2 where i=1;
The first statement conflicts with "2 rows updated".
 
There are several ways to handle this conflict:
  1. FORCE: both rows are changed. APPLY now executes #2 - and conflicts again, but this time with "0 rows updated" as both records now i=2. This conflict has to be handled as well with a NO_DATA conflict handler. This second conflict has to be ignored because the second update is no longer needed. 
  2. SQL (rownum=1): only one row is changed. APPLY now executes #2 - only 1 row is updated, so no conflict.
Option 2) is the best approach – it handles only duplicates, not other conflicts. 
Option 1) would (silently) handle other types of conflicts.

The syntax for Option 2 is:
UPDATE: 
 No Format
bgColorCCC
SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/
 

DELETE:
 No Format
bgColorCCC
SET_CONFLICT_HANDLERS FOR TABLE SCOTT.X FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
 
 


 Info
Current conflicts will not be affected by newly defined CONFLICT HANDLERS. To resolve current conflicts there are 2 options:
  1. Manually resolve with RESOLVE CONFLICT, OR
  2. Restart the APPLY process. This will enable the newly defined conflict handler to manage the current conflict.
 

For the full syntax for conflict handlers see Full Command-Line Reference