| Align |
|---|
The replication is a row-based one; that is, the changes are mined row-by-row on the mine. Thus while any SQL issued on the source database can change arbitrary number of rows, Dbvisit does not care about the actual SQL issued, only about the changes made to individual rows.The major consequence is that the SQL issued at the apply database is not the same issued against mine – instead, each SQL updates/deletes/inserts exactly one row, applying just one change. Thus if the SQL actually affects zero or more than one rows, data divergence occurred. Another type of conflict is any error reported by Oracle – this can range from usual primary key or foreign key violation (another type of data divergence) to purely technical reasons (cannot extend datafile). The last type of conflict is lock timeout – if the apply waits for a row lock more than WATCHDOG_TIMEOUT seconds, a conflict is also reported. Note that when applying a row-change, previous values of the row at apply are checked (by means of adding them to the where clause). This effectively detects if the data in that row are inconsistent between mine and apply, and this is reported as a conflict, "0 rows updated/deleted." This can happen for update or delete only (there is no previous row to check for insert) and due to its special role, it can be handled differently. |
Configuring conflict handling
The response to a conflict can be configured before the conflict occurs. The configuration can be specified for each replicated table and for each operation type separately, and for the special case ("data" divergence) mentioned above.
The options are as follows:
| Operations | "Data" handler | "Error" handler |
|---|---|---|
| Update | discard | retry | overwrite | pause | abort | newer | older | sql | plsql | error | discard | retry | overwrite | pause | abort | plsql | error |
| Delete | discard | retry | overwrite | pause | abort | newer | older | sql | plsql | error | discard | retry | overwrite | pause | abort | plsql | error |
| Insert | n/a | discard | retry | overwrite | pause | abort | plsql | error |
| Transaction | n/a | discard | retry | overwrite | pause | abort | plsql | error |
The transaction handler is used for DDL, commits etc.
The "Data" handler is used only once for a conflict – if using this handler leads to a conflict again, "Error" handler is used for the next attempt. For the next change SQL, the Data conflict will be used again.
Additionally, logging may be specified:
- log: the conflict is logged, and a error plog is created with the offending statement
- nolog: the conflict is not logged
- fast_nolog: the conflict is not logged and it may be omitted from conflict counters altogether
- log_transaction: the conflict is logged, and a error plog is created with the offending transaction
- default: same as log
See the command chapter or use HELP SET_CONFLICT_HANDLERS for the exact syntax.
Use SHOW_CONFLICT_HANDLERS to query current settings of handlers.
Available handlers
- DISCARD: ignore the offending SQL and continue with the next one
- OVERWRITE: do not check old values, try again with just primary key in the where clause (thus this will fail if there is no row at all on apply with the PK value)
- NEWER,OLDER: look into target table (by primary key) and get values of specified columns (usually dates or number sequence). If the source row is newer/older, the operation becomes OVERWRITE, otherwise DISCARD.
- PLSQL: call user-specied PL/SQL function. The function must have prototype:
f(apply_old_data table%rowtype, has_found_apply_row boolean, primary_key_data table%rowtype, new_data table%rowtype) return number;
The return values are:
- 0: discard
- 1: overwrite
- 2: retry
- 3: the PL/SQL function resolved the conflict by itself and made all necessary changes.
The function must not issue a commit, as the transaction may be yet rolled back, if a rollback happened on source.
- RETRY: wait a few seconds (set by variable RETRY_TIME ) and try again
- PAUSE: wait for manual user resolution
- ABORT: kill apply process
- ERROR: rollback the transaction, continue applying other transactions
Handling current conflict (resolve conflict)
If the apply was paused due to a conflict, or it is retrying in a loop the same SQL again and again, you can instruct it about what to do next using the command:
RESOLVE CONFLICT id AS resolutionThe conflict id is the number shown in the status bar, the resolution can be:
...
To see the SQL that is causing the conflict, logon to the apply (target database) as the Dbvisit Replicate schema owner (dbvrep) and query table:
DBRSAPPLY_CONFLICT_LOGThis lists all the conflicts encountered, including failing SQL.
Handling errors on source database, partially executed statements
...