| 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:
...
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:
...
- 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
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:
...
- IGNORE: skip the transaction
- RETRY: try again
- ABORT: abort the apply process
- RESTART: rollback and restart the transaction
- ROLLBACK:rollbacks the transaction
Handling errors on source database, partially executed statements
The mine (and then apply as well) process follow the changes as they are written to redo logs by Oracle. This also defines it's (sometime peculiar) behavior in case of errors, statement rollbacks, statement restarts etc. In general:
...